Post reply

EncryptByPassPhrase/DecryptByPassfrase, why don't this work?

  • October 1, 2012 at 1:37 am

    #282326

    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

  • October 1, 2012 at 1:50 am

    #1543801

    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, MVP, M.Sc (Comp Sci)
    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

  • October 1, 2012 at 1:57 am

    #1543806

    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

  • October 1, 2012 at 2:04 am

    #1543816

    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, MVP, M.Sc (Comp Sci)
    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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply