1

Need: I need to be able to store AES encrypted user data (journal entries) in my database and those entries need to be plain-text searchable.

Issue: MySQL outputs queries to its logs in plain text.

History: I've already accomplished the desired behavior by letting MySQL handle encryption and searching with AES_DYCRYPT and LIKE, but it means that MySQL is outputting my users' entries along with my AES key in plain text to its logs.

Possible Solution: I've attempted moving my encryption step to the server before sending data to MySQL, but not being able to have the DB handle decryption and therefore plain-text search really complicates things. I don't want to have to pull in all of a specific user's entries and then filter them based on the text query from the server.

Question: How big of a security hole is the fact that MySQL outputs all queries to its logs in plain text when deciding to use MySQL's AES_ENCRYPT function? Should I just use MySQL's encryption functions so I can also use its LIKE feature, or should I encrypt on the server and look into other solutions such as storing a hashed word index as a new column so I can still provide some sort of text search?

I should note that I'm using a hosted MySQL solution which, at the price point I'm currently paying, does not allow me to manage admin settings myself. The hosting company has access to the logs.

2

Using MySQL's built-in functions for encrypting and decrypting information is generally not recommended for exactly the reason that you described - MySQL is often configured to log queries. If that's the case, the plain text data (and the encryption/decryption keys!) are written in plain text to the logs!

Having said that - If you must use aes_encrypt and aes_decrypt to encrypt the information that you are storing in your database, then you should disable the logging. See http://dev.mysql.com/doc/refman/5.7/en/query-log.html for information on how to do that.

If your MySQL instance is running on a host that does not allow you to disable the logging, then it may be time to host MySQL yourself (which is not hard to do on a VPS).

Last but not least - statements such as ... where aes_decrypt(fieldname, key) like '%xyz%' are going to be sloooowwww when your tables start getting bigger, because MySQL will have to decrypt the data in each row and apply the like comparison. Both of these operations (the decryption and the like) are fairly resource intensive, and indexes won't help.

  • Thanks for the response! Do you have any advice for speeding up queries with text search and encryption? Does using an index and offset help, or does MySQL just decrypt all entries given the WHERE clause then search them until the offset is satisfied? – jpodwys Jan 29 '16 at 2:22
  • when you say, 'using an index and offset', it sounds like you might be referring to using a LIMIT clause in your SQL SELECT statements. I was referring to the kind of indexes that MySQL uses to speed up queries (see dev.mysql.com/doc/refman/5.7/en/mysql-indexes.html). – mti2935 Jan 29 '16 at 11:56
  • Also, in some configurations, only queries that make changes to the database (such as insert, update, delete, etc) are written to the logs, and select statements are not written. If this is the case on your MySQL server, then you may be able to mitigate this problem by only using aes_encrypt and aes_decrypt in select statements. – mti2935 Jan 29 '16 at 11:56
  • Another great point. Perhaps I can encrypt on the server and decrypt from select statements. I was curious about pagination--will a LIMIT make things faster? – jpodwys Jan 29 '16 at 14:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.