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