EncryptByPassPhrase/DecryptByPassfrase, why don't this work?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » EncryptByPassPhrase/DecryptByPassfrase, why...

EncryptByPassPhrase/DecryptByPassfrase, why don't this work?

Rate Topic

Display Mode

Topic Options

Author

Message

edvard 19773

Posted Monday, October 01, 2012 1:37 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, June 17, 2013 6:22 AM
Points: 42, Visits: 83

This query returns a lot of results:

select Convert(Char,DecryptByPassPhrase('[PASSPHRASE]',cpr_encrypted)) from dbo.personal

I have changed the actual passphrase to [PASSPHRASE] above.

one of the results is:

'070564-2869 '

Without the quotation marks, but there is a lot of blanks in it.

the field is defined as cpr_encrypted varbinary(256)

Then i use this to find it again:

select COUNT(*) from dbo.personal where CPR_encrypted = Convert(Char,DecryptByPassPhrase('[PASSPHRASE]','070564-2869 '))

Which returns zero (nul NULL, zero)

What am i doing wrong?

Best regards

Edvard Korsbæk

Post #1366346

GilaMonster

Posted Monday, October 01, 2012 1:50 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:52 AM
Points: 38,104, Visits: 30,395

I think you've got that query the wrong way around. You're comparing the encrypted column with the results of the decryption function. You should be comparing the decrypted value with the results of the decryption function applied to the column.

Also, you're not specifying a length for char, you should. do you know what the default is?

Something roughly like this

select COUNT(*) 
from dbo.personal 
where  Convert(Char[<whatever the string length is>,DecryptByPassPhrase('[PASSPHRASE]',CPR_encrypted)) = 
'070564-2869'

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1366353

edvard 19773

Posted Monday, October 01, 2012 1:57 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, June 17, 2013 6:22 AM
Points: 42, Visits: 83

Thanks!
did the trick even without lengt for char.
I wonder a bit - 070564-2869 - has 11 characters, but should i use 'With blanks'?

Best regards

Edvard Korsbæk

Post #1366361

GilaMonster

Posted Monday, October 01, 2012 2:04 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:52 AM
Points: 38,104, Visits: 30,395

With blanks?

SQL ignores trailing spaces when comparing strings. Just make sure that the char length is longer than the string you're converting.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1366373

« Prev Topic | Next Topic »

Permissions