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.

Improve this question

2 Answers 2

Reset to default
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.

Improve this answer
4
  • 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, 2016 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, 2016 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, 2016 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, 2016 at 14:54
0

This is an old post now but anyone reading it should know that the bin logs do not store the AES keys. I have verified this on my system that also replicates the DB to another server. There are no KEYS in the binlog Version 5.3.75. If like me, you are replicating to another server ALWAYS use SSL to do this as the KEY will be part of this transmission.

As another precaution I read in the AES KEY as a CONSTANT from another FILE that is inaccessible to my webserver, so if hacked the keys are not present in web scripts. Finally, if your scripts have error messages do not output the QUERY or if needed like me replace the KEY with some ************

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .