CODEWITHSUNDEEP
sqldjangoencryption
Thierry J
Thierry J

Reputation: 2189

Django and field encryption with database ENCRYPTBYKEY function

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.

Upvotes: 1

Views: 538

Answers (1)

Thierry J
Thierry J

Reputation: 2189

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()

Upvotes: 1

Related Questions