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