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
Last activity: Feb 3, 2022, 05:25 PM