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 nmsinc - November 24, 2019

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

nmsinc

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 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