MySQL/MariaDB provides us with a number of useful functions for implementing data encryption and compression. This is especially handy when the programming language in use doesn’t offer these capabilities. And even more so, if our application makes heavy use of stored procedures in the backend, we have an arsenal of simple-to-use and widely implemented functions at our disposal.
It’s important to note that if you need to store a string returned from an encryption or compression function, it must be stored using BINARY, VARBINARY, or BLOB data types. This prevents any issues when attempting to reverse the process, i.e., to decrypt the encrypted string back to its original value. Personally, I really like using VARBINARY. But as my colleagues like to say: “André, you don’t have to like anything!”…
Another important point to consider is that the MD5 and SHA-1 encryption algorithms, widely used in web applications, are quite outdated and can be easily cracked (reversed). So, if you’re aiming for real security, consider using other encryption functions provided by MySQL/MariaDB.
And what is an algorithm? Think of it like a recipe, outlining which ingredients to mix, in what order and quantity, how to prepare them, and for how long. Learn more at: http://pt.wikipedia.org/wiki/Algoritmo
To run the examples below, use any MySQL/MariaDB client, such as: mysql, Query Browser, Workbench, SQLyog, etc.
Encryption
AES Algorithm (Advanced Encryption Standard)
Certainly, it is the most secure encryption algorithm and function available in MySQL/MariaDB. It normally uses 128-bit encryption level. However, it is possible—by modifying the MySQL/MariaDB source code—to achieve higher encryption levels. If this is necessary for your application, I can assist you in this regard.
Encrypt: AES_ENCRYPT(, )
Decrypt: AES_ENCRYPT(, )
Example:
mysql> SET @mysecret = AES_ENCRYPT(‘my_strong_password_123′,’a0f1d0’); /* Move encryption result Variable @mysecret*/
mysql> SELECT @mysecret; /* Show how the string looks ‘my_strong_password_123’ Encrypted*/
Result=> ¨rMœVñåþÓCbF&¶ŒLÌÁ%p4Ñ’Ú¹ôÁHïô
mysql> SELECT AES_DECRYPT(@mysecret,’a0f1d1′); /* Let’s try to decrypt using the wrong encryption key. */
Result => NULL => It didn’t work…
mysql> SELECT AES_DECRYPT(@mysecret,’a0f1d0′); /* Now with the correct encryption key. */
Result => my_strong_password_123 It works! 1e2531a1189fb34814109099d2eddae6
As a function, the AES_ENCRYPT e/ou AES_DECRYPT It can be used in valid SQL commands/statements, such as:SET, SELECT, UPDATE, INSERT, DELETE, REPLACE, etc.
MD5 (Message-Digest Algorithm 5)
Developed by RSA Data Security Inc. and described in RFC 1321, it is widely used for integrity verification of packets in P2P (peer-to-peer) protocols and for login security. It converts data into a 128-bit hash (a set of bits). The big problem with MD5 is that it is one-way: you cannot reverse its hash back to the original string. No matter the length of the string converted to MD5, its size will always be 32 hexadecimal characters, so it can be stored in a CHAR, VARCHAR, or TEXT field.
mysql> SELECT MD5(‘my_strong_password_123_in_its_very_very_very_long_version_2010_and_so_on’);
Result => 1e2531a1189fb34814109099d2eddae6 => string 32-character hexadecimal
mysql> SELECT MD5(‘123’);
Result => 202cb962ac59075b964b07152d234b70 => string 32-character hexadecimal
mysql> SET @secret_key = MD5(‘my_strong_password_123_in_its_very_very_very_long_2010_version_and_so_it_goes’);
mysql> SELECT @chave_secreta = MD5(‘my_strong_password_123_in_its_very_very_very_long_2010_version_and_so_it_goes’);
Result => 1 = True => So, key_secret is equal to the string converted by MD5.
… continues
Visit our Blog
Learn more about databases
Learn about monitoring with advanced tools
Have questions about our services? Visit our FAQ
Want to see how we’ve helped other companies? Check out what our clients say in these testimonials!
Discover the History of HTI Tecnologia
