Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

EncryptByPassPhrase/DecryptByPassfrase, why don't this work? Expand / Collapse
Author
Message
Posted Monday, October 1, 2012 1:37 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 8:12 AM
Points: 91, Visits: 158
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
Posted Monday, October 1, 2012 1:50 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:39 PM
Points: 42,953, Visits: 36,110
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
Posted Monday, October 1, 2012 1:57 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 8:12 AM
Points: 91, Visits: 158
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
Posted Monday, October 1, 2012 2:04 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:39 PM
Points: 42,953, Visits: 36,110
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 »

Add to briefcase

Permissions Expand / Collapse