CODEWITHSUNDEEP
c#sql-serverencryption
user6349792
user6349792

Reputation:

SQL encryption only returning first character

I have the following query

UPDATE mytable 
SET col1 = ENCRYPTBYPASSPHRASE ('Key', col2) 
FROM mytable

when I decrypt it using

SELECT CONVERT(VARCHAR(20), DECRYPTBYPASSPHRASE ('Key', col1)) 
FROM mytable

The result returned is only the first character, for example if the field contains "Computer" the result is only "C".

Upvotes: 4

Views: 2728

Answers (3)

Tejasvi Hegde
Tejasvi Hegde

Reputation: 2922

Use CONVERT with data type and size of the value you are encrypting updating. Looks like EncryptByKey does not recognize the data properly as per column schema.

Try as below

ENCRYPTBYKEY(KEY_GUID('<Key Name>'), CONVERT(varchar(20),col1))

Upvotes: 0

user6349792
user6349792

Reputation:

After investigation I had come to many issues so I will post what I came across, so anyone can benefit from it.

  • If you changed to data type of the SQL column to varbinary then make sure that when you decrypt the data, you use the same old data type. That is if you had a column of varchar that contains data and then you changed it to varbinary, you must decrypt it using varchar, if you use nvarchar ,you will get garbage data.
  • You must encrypt and decrypt using the same way. That is if you are loading the password from a stored procedure and use it in encrypting,and the SAME EXACT password is loaded using a function for decryption, u will also get garbage data (I tested it but I did not know why is this behaviour!)may be internally there is some difference between how data is returned from SP and functions.

Hope this helps anyone out there !

Upvotes: 5

Martin Smith
Martin Smith

Reputation: 453298

col2 is probably nvarchar not varchar. Try

SELECT CONVERT(NVARCHAR(20), DECRYPTBYPASSPHRASE ('Key', col1)) 
FROM mytable

In nvarchar the code points for standard ASCII letters are the same as for ASCII but padded out with a 0x00 byte.

When you cast that to varchar that it is treated as a null character that terminates the string.

Upvotes: 11

Related Questions