Encrypt database data

Hi Greg,

I imagine encrypting/decrypting data on the fly will add to the load time of web pages, is that right?

Any additional overhead will always increase page execution time to some degree, but in most cases, I'd expect the overhead from this sort of decryption to not be noticeable unless you're dealing with very large amounts of data (100MB+). I'd more likely anticipate running into memory limit issues before seeing significant performance loss from decryption.

Is there any advice you can offer around ensuring that DataTables with potentially thousands of records still load fast?

Good performance with DataTables is going to be more about optimizing your output, as DataTables executes on the client-side and by default will hold the entire data set in the user's browser's memory and manipulate it with JavaScript. When you start loading large lists of records, this can start to strain the user's computer regardless of how quickly the server initially provides the records. It's possible to mitigate this by reducing the number of columns in the table, but after a certain point, it becomes necessary to use server-side processing (https://datatables.net/examples/data_sources/server_side); an advanced option that allows DataTables to load only the records it currently needs.

Hope that answers your questions!

Thanks,

Hi Daniel,

That's absolutely fantastic support. Thank you so much!

Regards,

Greg

Hi Daniel,

I have been watching this post for some time now and I'm not quite sure on how to decrypt the field that I would need outside CMS. Could you explain this further?

Thanks!

Hi nmsinc,

Essentially, if you're making a custom select query that looks like this:

SELECT `table`.`column` as `column` FROM `table`

You would modify it to this:

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

Where 'password' is the column encryption key you specified in your CMSB settings. If you have CMSB loaded you can use the variable $SETTINGS['mysql']['columnEncryptionKey'] rather than adding it manually.

Let me know if I can clarify any further, or if you have any more questions.

Thanks!

Thanks Daniel for the clarification. I another question. My CMS Admin reports the following in the security section:

"Connections: Your database server doesn't support encrypted connections." and I cannot check the box to the left of this statement so I assume this is protecting the script from doing harm to the database and it's processes!

Do you know hat process I would use to encrypt the SQL database in myPhp admin? I'm using a VPS server!

Thanks

Hi nmsinc,

The two Database Encryption options are related, but separate processes - either one can be used independently, so if your DB does not support encrypted connections you can still encrypt the data. A quick rundown on the two options:

• Connections: This is essentially to connect to the database through SSL (i.e. what HTTPS is for web connections). It encrypts the transmission of the data over the wire but does not change the data in the database itself.
  • Note that if your DB server and webserver are on the same server (e.g. if your database hostname is "localhost") then this option can typically be ignored, as the data is never exposed to the network in the first place.
• Database Data: This is what we've been discussing in this thread so far; encrypting the data within the database itself. This can be done even if the Connection option is not available.

If you do want to enable encrypted connections, this is a setting that has to be enabled through the database config. If you have a server admin or support you can contact I would recommend going through them first. 

Hope that helps clarify; let me know any other questions!

Thanks,