Django and field encryption with database ENCRYPTBYKEY function

Question

I am using Django for an application capturing some sensitive data, and I need those data to be encrypted in the database. I have to use a database encryption.

So to save my fields, I use:

query = """
    OPEN SYMMETRIC KEY MyKey
    DECRYPTION BY CERTIFICATE MyCertificate

    UPDATE mytable
    SET name = ENCRYPTBYKEY(KEY_GUID('MyKey'), %s)
    WHERE id = %s

    CLOSE SYMMETRIC KEY MyKey
"""
args = ["Test Name", data.id]

cursor = connection.cursor()
cursor.execute(query, args)
transaction.commit_unless_managed()

The field is saved in database but, when I uncrypt the data saved in base, I get 0x540065007300740020004E0061006D006500 where I should get 0x54657374204E616D65.

Do someone know why there are some 0x00 bytes inserted in my string?

I have tried to get the query string using connection.queries[-1] and run it directly in the database, and the data is clean when I uncrypt it.

Thierry J · Accepted Answer · 2013-04-22 07:07:06Z

1

Apparently, SQL Server is implicitly doing some kind of string conversion.

I solved my problem by casting the data as a varchar before encryption:

query = """
    OPEN SYMMETRIC KEY MyKey
    DECRYPTION BY CERTIFICATE MyCertificate

    UPDATE mytable
    SET name = ENCRYPTBYKEY(KEY_GUID('MyKey'), CAST(%s AS VARCHAR(1023)))
    WHERE id = %s

    CLOSE SYMMETRIC KEY MyKey
"""
args = ["Test Name", data.id]

cursor = connection.cursor()
cursor.execute(query, args)
transaction.commit_unless_managed()

answered Apr 22, 2013 at 7:07

Thierry J

2,19717 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Django and field encryption with database ENCRYPTBYKEY function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related