CODEWITHSUNDEEP
sql-serverencryptioninsert
Danish
Danish

Reputation: 751

SQL EncryptByKey not working on insert, works on update

I'm implementing encryption into an insert query , however on decryption the code is unreadable. however - when i encrypt the same record by using update, the record is decrypted correctly

to test , I used the following code to determine what's going on 

SELECT
 EncryptByKey(Key_GUID('Data_Enc_Key'), 'abc123') as Encrypted,
 CONVERT(nvarchar(50),DecryptByKey(
    CONVERT(varbinary(128),EncryptByKey(Key_GUID('Data_Enc_Key'), 'abc123'))
 )) as Decrypted

in theory, it should encrypt and the same value 'abc123' but it doesn't

on the actual table I'm getting a similar set of characters on decrypt as the above query for the records that are inserted using encrypted insert

Anyone has any idea what's going on ?

--OPEN CERTIFICATE
OPEN SYMMETRIC KEY Enc_Key DECRYPTION BY CERTIFICATE ENC_CERT

----WORKS
Update tblMembers
SET password_enc = EncryptByKey(
        Key_GUID('Enc_Key'),
        password
        )
WHERE
id = XXX
AND email = XXX

----DOES NOT WORK - ON DECRYPTION INCORRECT RESULT
INSERT INTO tblMembers (email,password,password_enc) VALUES ('[email protected]','abc123', EncryptByKey(Key_GUID('Enc_Key'),'abc123'))


-- DECRYPT FOR BOTH INSERT AND UPDATE
select 
TOP 5 *
,
        CONVERT(nvarchar,
            DecryptByKey(
                password_enc                
                )
            )

from tblMembers
WHERE id = XXX
AND email = XXX

Upvotes: 2

Views: 2901

Answers (2)

Ravi Ram
Ravi Ram

Reputation: 24488

The following INSERT code is working:

INSERT INTO tblMembers (email,password,password_enc)
VALUES (
   '[email protected]',
   'abc123', 
   EncryptByKey(Key_GUID('Enc_Key'), N'abc123')
)

The last line EncryptByKey requires the string abc123 has N before the string.

Upvotes: 0

jhmt
jhmt

Reputation: 1421

I think you have to use nvarchar for 'abc123' so the first SELECT statement should be 

SELECT
 EncryptByKey(Key_GUID('Data_Enc_Key'), N'abc123') as Encrypted,
 CONVERT(nvarchar(50),DecryptByKey(
    CONVERT(varbinary(128),EncryptByKey(Key_GUID('Data_Enc_Key'), N'abc123'))
 )) as Decrypted

Upvotes: 2

Related Questions