Encrypt database data

I imaging something like this might work:

 AES_DECRYPT(crypt_str,key_str)

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?

Thanks

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,

That's very helpful! Thank you, Daniel.

Regards,

Greg

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

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!

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

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,

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