Detecting whether a varchar value is already encrypted by EncryptByPassPhrase() in SQL Server



  • Starting with a varchar(100) column named Password in a table Users with one record in it, after performing an update on the column to encrypt it with EncryptByPassPhrase() on the Password column the following varbinary(8000) string is generated:

    0x0342342565CF0....
    

    ...and for simplicity, the string has been abbreviated with .....

    To be able to store the encrypted varbinary(8000) output in the same Password column, the encryption function is wrapped convert(varchar(8000), EncryptByPassPhrase()).

    Running a select * from Users; produces this output of its two columns and one record:

    Name     | Password
    ---------+----------
    Benjamin |          
    

    As you can see, Password appears blank. However, running Select len(Password); reveals a value of 52, so there is definitely a string stored in Password and it includes non-printable characters.

    So, the question is, how can the blank varchar(100) string be tested for its first two varbinary characters being 0x?

    The idea is to create a wrapper encrypt function that decides if the field is already encrypted, not to encrypt it again if it detects that the string passed to it starts with 0x?



    • Never store encrypted passwords. Store one-way hashes of the salted passwords.
    • Don't store a VARBINARY value in a VARCHAR column lest you risk never getting it back.
    • The fact that your client software shows you a hexadecimal representation of a binary string starting with 0x does not at all mean that 0x is part of the value.
    • There is no reliable way to tell by looking at a sequence of bytes if that sequence is encrypted in any way.



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2