Use Mysql aes_encrypt() and aes_decrypt() functions in Python Code

Question

I want to use MYSQL aes_encrypt() and aes_decrypt() functions from Python to encrypt important information. I am aware, we can implement these using the python crypto functions

My Scenario is : From the POST request I have list of varying fields

{
"email"  :  "abc@gmail.com",
"phone"     :   "1234567890",
"key1"       :  "value1",
"key2"       :  "value2",
"key3"       :  "value3"
}

This JSON data can vary, for example it can also have other keys (key5,key6,....) or none. So I need a build a dynamic SQL query, such as

sql = "Insert into dummy.test(email, phone, key1, key2, key3) values(email, phone, AES_ENCRYPT('value1', 'secret key'), AES_ENCRYPT('value2', 'secret key'), AES_ENCRYPT('value3', 'secret key'))"
cursor.execute(sql)

or

values =(email, phone, AES_ENCRYPT('value1', 'secret key'), AES_ENCRYPT('value2', 'secret key'), AES_ENCRYPT('value3', 'secret key')) #TUPLE
sql = "Insert into dummy.test(email, phone, key1, key2, key3) values(%s,%s,%s,%s,%s)"
cursor.execute(sql,values)

Is there any possible way to implement any of the way above.? Is this the good approach? Thanks

Answer 1

Since AES_ENCRYPT is a MySQL and not a Python function and to avoid SQL Injection attacks, you need to do it this way:

sql = """Insert into dummy.test(email, phone, key1, key2, key3)
         values(%s, %s, AES_ENCRYPT(%s, 'secret key'), AES_ENCRYPT(%s, 'secret key'), AES_ENCRYPT(%s, 'secret key'))"""
cursor.execute(sql, (email_value, phone_value, key1_value, key2_value, key3_value))

where email_value, phone_value, etc. are the values that were posted respectively for form fields email, phone, etc.