DEV Community

Cover image for MySql Encryption

Posted on

MySql Encryption

So recently I was working on an app that allowed people to create and share movements between the app's users. Primarily it was for political movements but it can be used for all sorts of things. Eventually, I would continue to add the feature to let users send private messages between each other. One of the main focuses was to allow the messages to not be seen by others unless you were one of the two people. I looked into what exactly I could do to handle an issue like that so surfed the web and the first thing I found was on encryption. From there I dove deeper into how I could use it on my backend. My app's backend was using MySQL as its database so I went to their docks and check around for documentation on encryption. It turns out MySQL has built-in functions for encrypting and decrypting. The two primary functions for handle this sort of thing for MySQL were.

MySQL AES_ENCRYPT() function encrypts a string using AES algorithm.

MySQL AES_DECRYPT() function decrypts an encrypted string using AES algorithm to return the original string. It returns NULL if it detects invalid data.

AES stands for Advanced Encryption Standard. This function encodes the data with 128 bits key length but it can be extended up to 256 bits key length. It encrypts a string and returns a binary string. The return result will be NULL when an argument is NULL.

The encryption function looks like this

AES_ENCRYPT(str, key_str);

The decryption function looks like this

AES_DECRYPT(crypt_str, key_str);

In both cases, key_str stands just refer to the key u pass that will serve later as a sort of password to retrieve the encrypted data. For that reason, your key_str should be kept safe and hidden from people who aren't on your dev team. This can be bad for obvious reasons, but you could still just pass in the key directly and it would still work during decryption. Another important thing to think about is storing that encrypted data. When the function AES_ENCRYPT runs it will convert the passed in data into an unreadable code which case it will also be increased in size since things like padding get added. So as a developer you need to figure out how much space you should be saving. This was a big part for me since I was encrypting user messages which could get really long. On a larger scale, I would have considered using a whole different type of database. With my time and other things in play, I came to the conclusion that it would be better to save using a simple doctype DB instead of a relational one for some other time. But still, these are things you have to think about during the planning process of your application. Which leads me to this cool function that can help you determine how much space you should allocate.

16 × (trunc(string_length / 16) + 1)

Using that function helped me gauge how much space a single message should allocate after encryption.
Another good workaround to managing exactly how much data will be getting sent to the back-end is to limit it on the client-side. That way you know how much to leave for the message. 
Now when it came to decrypting the data it was pretty much the same u pass in what you want from the DB and you give the function the correct key_string from before. Here is an example of an insert query with the functions in action.

INSERT INTO testtable VALUE(AES_ENCRYPT('mytext','passw'));

SELECT description, AES_DECRYPT(description,'passw')
FROM testtable;

That pretty much sums up how using the built-in encryption and decryption functions work on a basic level.

Top comments (0)