Sample Header Ad - 728x90

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

0 votes
1 answer
395 views
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?
Asked by Guessed (153 rep)
Feb 2, 2022, 11:16 PM
Last activity: Feb 3, 2022, 05:25 PM