Python - Store cryptography keys in SQL database

Question

Working on a "Password Saver" and will be using the module "cryptography" to encrypt the passwords. I need to save the key you generate from cryptography in the database as well, but I am not sure how you actually do this.

Done some google searches myself and it seems to be called a "byte string"? Not really sure what it is though.

This is what I have currently:

from cryptography.fernet import Fernet
import mysql.connector

db = mysql.connector.connect(
    host='localhost',
    user='root',
    password='',
    database='password_saver'
)

cursor = db.cursor()

key = Fernet.generate_key()

cursor.execute(f"INSERT INTO `encryption`(`encryption_key`) VALUES ({key})")

With that code I am getting this error: mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'b'cryptographys key is here')' at line 1

Not sure if there is a specific data type I need to use for SQL or if it is something I need to do in python?

s3dev · Accepted Answer · 2021-04-21 12:25:12Z

TL;DR

Use: key = Fernet.generate_key().decode(), and change the query to a parameterised query.

There are two key elements in play here:

What is a byte string?
SQL injection

Byte String:

Given the rule that everything in a computer is a number, a 'byte string' is just that, it's a string of bytes - which is not human readable. However, the reason we can read b'my string' is because Python is doing us a favour in converting the bytes back into a readable string. But under the hood, it's just a string of bytes.

Further (interesting) reading on the subject

As for converting the byte string into a string (str), something MySQL can store in a VARCHAR field, it's as simple as:

b'my string'.decode()

So in your case, the key will be:

key = Fernet.generate_key().decode()

Another option:

Or, if you want to store the raw byte string in the database, you can convert your encryption_key column to a BLOB data type. This is a 'Binary Large OBject' field, used to store bytes. However, when it comes time to use the key value, you'll have to convert it into a string. So might as well do the conversion up front, and just store the string.

SQL Injection:

Please read up on this, I won't get into it here - but it's important.

The short story is use parameterised queries.

Change this:

cursor.execute(f"INSERT INTO `encryption` (`encryption_key`) VALUES ({key})")

To this:

cursor.execute("INSERT INTO `encryption` (`encryption_key`) VALUES (%s)", (key,))

Collectives™ on Stack Overflow

Python - Store cryptography keys in SQL database

1 Answer 1

TL;DR

Byte String:

SQL Injection:

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

TL;DR

Byte String:

SQL Injection:

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related