Encrypt database data

17 posts by 3 authors in: Forums > CMS Builder
Last Post: February 18, 2020   (RSS)

By gversion - November 23, 2019

Hello,

I am experimenting with the MySQL encryption feature and seem to have got it working so when I view an encrypted field in phpMyAdmin the data is represented by a BLOB. Very cool!

I’m just wondering how I would go about decrypting the BLOB field in phpMyAdmin so I can view the actual value. Is there an SQL command I can run that uses the encryption key to decrypt the field?

Any info on this would be gratefully received.

Regards,

Greg

By gversion - November 23, 2019 - edited: November 23, 2019

I imaging something like this might work:

 AES_DECRYPT(crypt_str,key_str)

By daniel - December 3, 2019

Hi Greg,

The general form would be this:

SELECT AES_DECRYPT(`table`.`column`, UNHEX(SHA2('password',512))) AS `column` FROM `table`

nmsinc,

Another question. If a credit card or social security number and you want to list the last four characters of this type of data, how would you go about it?

What sort of use case do you have? One simple solution is to just store those last 4 characters unencrypted in a separate field if that meets your security requirements. Otherwise, you'd need to decrypt the whole value and then use something like substr() to retrieve the portion you want.

Let me know any other questions!

Thanks,

Daniel
Technical Lead
interactivetools.com

By gversion - December 5, 2019

That's very helpful! Thank you, Daniel.

Regards,

Greg

By gversion - February 3, 2020

When I set a database encryption key and then choose to encrypt the "first_name" column of my cmsb_accounts table, all the values display as gobbledygook.

See attached.

Is this how it's meant to work?

I was hoping that the data in the database would be stored in an encrypted format but when displayed through my website the data would be readable.

Do I need to update my PHP code to decrypt the data on the fly? If so, are there any techniques for making this decryption more efficient as I imagine it will slow the page load time down quite a bit?

Thank you,

Greg

Attachments:

encrypted.png 28K

By daniel - February 7, 2020

Hi Greg,

I was hoping that the data in the database would be stored in an encrypted format but when displayed through my website the data would be readable.

The data you're retrieving in the screenshot - is this running from a custom query? And have you added the AES_DECRYPT function to it? If this is the case, could you copy the full query so that I can help troubleshoot? This should decrypt the data to its original state, so if you're getting garbled text there's likely an issue somewhere.

Thanks!

Daniel
Technical Lead
interactivetools.com

By gversion - February 8, 2020

Hi Daniel,

Yes, I am using a custom query. I see the values display correctly in /cmsb so I must just need to add the AES_DECRYPT function and then I am sure it will work. I will be in touch again if not!

By the way, can you please tell me where the encryption key is stored? I am just wondering how to prevent a hacker from finding out what the encryption key is, should they manage to access the system.

Thank you,

Greg

By daniel - February 10, 2020

Hi Greg,

By the way, can you please tell me where theencryption key is stored? I am just wondering how to prevent a hacker from finding out what the encryption key is, should they manage to access the system.

The key is stored in the CMSB settings file (e.g. /cmsb/data/settings.dat.php) as "columnEncryptionKey" in the mysql section. To further secure this file, a good first step is to move your data folder out of your web root.

Also, as a quick note, this kind of database encryption isn't generally intended to guard against attackers who have gained full access to the system. As CSMB can inherently read the encrypted data, anyone with file access to the website can use CMSB functions to read the data rather than going through the process to manually decrypt it with the key. The main avenues it guards against are things like attacks that gain access directly to the database or intercepted backup files; contexts where the data can be accessed but not the program files.

Let me know how you get along adding the decryption to your custom functions, or if you have any other questions!

Thanks,

Daniel
Technical Lead
interactivetools.com

By gversion - February 10, 2020

Hi Daniel,

That all makes sense, thank you very much for clarifying.

I have moved the data directory out of the public folder so I think I am looking good.

Thanks again,

Greg