24

From a 2012 Smashing Magazine article quite a bold statement is made to avoid AES in MySQL itself. Or as they put it "Why Should You Avoid AES In MySQL?". However, if you search for SQL encryption, you often find the AES_ENCRYPT from (My)SQL mentioned. I am not saying that many search results mean the statement is untrue, but it just got me thinking: are the three quoted reasons below actually true?

What do the security experts here think about the reasons "why PHP’s Mcrypt is superior to MySQL’s AES functions":

  • MySQL needs a database link between the application and database in order for encryption and decryption to occur. This could lead to unnecessary scalability issues and fatal errors if the database has internal failures, thus rendering your application unusable.

I cannot understand this issue; if you encrypt/decrypt in php, you also need to store the values in the database with a database link? If the decryption fails on php's side, this also leads to application failures? What is the point made here?

  • PHP can do the same MySQL decryption and encryption with some effort but without a database connection, which improves the application’s speed and efficiency.

Php can do it "with some effort" and that improves the application's speed? Because you're encrypting before it's sent over the wire, the application (which will handles the encryption) gains increased speed and efficiency? How could that be true? In my opinion, it's about the "best tool for the job", so the argument that "php is capable too" does not mean per se it's the best tool as well. It's just a tool, without the argument why it's the best tool.

  • MySQL often logs transactions, so if the database’s server has been compromised, then the log file would produce both the encryption key and the original value.

This is a valid point if you encrypt values in the database and have logging turned on. At least you should turn off the general query log as the binary log does only log transactions but no select statements. If you don't need logging at all, you could just make the statement "if you use AES in MySQL, turn off all loggin". That sounds more valid to me than "if you want to use AES in MySQL, do it in php".

Can anyone explain to me why above points might be valid and why -in general- it is better to encrypt your data in your (php) application rather than in (My)SQL.

Philipp Kyeck
  • 103
  • 4
Jurian Sluiman
  • 873
  • 2
  • 7
  • 9
  • possible duplicate of [Is it preferable to perform encryption using database functions or code?](http://security.stackexchange.com/questions/25111/is-it-preferable-to-perform-encryption-using-database-functions-or-code) –  Nov 21 '13 at 11:48
  • @TerryChia good catch. I couldn't find that one due to other search terms I used. The question you refer to does answer this question slightly, but I'd like to ask for a review of the three statements above made as well. – Jurian Sluiman Nov 21 '13 at 13:35

4 Answers4

18

The primary reason not to use AES_* functions in MySQL is because they are using ECB block mode of operation, which is insecure.

Read more at:

https://en.wikipedia.org/wiki/Block_cipher_mode_of_operation

Edit: Since MySQL 5.6.17 things have changed and MySQL supports CBC block mode, but it has to be enabled manually. Read http://mysqlserverteam.com/understand-and-satisfy-your-aes-encryption-needs-with-5-6-17/

Matrix
  • 3,988
  • 14
  • 25
  • 2
    Whether ECB is secure, insecure, appropriate or inappropriate, depends upon the data and threat model, as well as upon the access and update requirements. The biggest issue with ECB is that identical blocks of plaintext map to identical blocks of ciphertext. In some cases, the patterns of what blocks are identical could represent a major security leak; in other cases, there won't be any identical blocks. Many other security modes require storing an IV in addition to the data being stored, which can sometimes pose problems of its own. – supercat Apr 05 '15 at 19:36
6

(I disagree that it's a duplicate of Is it preferable to perform encryption using database functions or code? although there is a lot of overlap)

The only justification I can think of for the post - and it is an important and valid one - is that it's easy to scale the application tier (PHP) but hard to scale a relational database tier (MySQL). For the former you just add more server - but the latter needs bigger boxes / coordinated clustering. Hence doing the encryption in PHP reduces the load on the DBMS.

Further, regarding "MySQL needs a database link...", sending the data elsewhere for processing in introduces latency. Calculating the encrypted or decrypted value of some data might be hard work - but it's unlikely to take nearly as long as sending stuff across a network.

But the overriding question is how this fits into the security model - and that's where the question of logs and key management comes in.

Community
  • 1
symcbean
  • 18,278
  • 39
  • 73
5

On all things performance, I am suggesting letting MySQL do the AES encryption is no big deal. It will probably waste (on average) a lot more time on fetching/writing the data than on encrypting it. This is unless all your database ever does is encrypting and decrypting.

Nevertheless there is one big problem with letting MySQL do the AES: you need your PHP application to issue a query against MySQL where the encryption key is passed as plaintext. To protect that you would have to use a secure connection, or else a tcpdump command is easily able to eavesdrop on your PHP-MySQL discussion.

If your PHP app encrypts the data internally, well that's one less exposure of your data and one less component dealing with unencrypted data or viewable keys.

Shlomi Noach
  • 186
  • 1
  • 10
  • This ^. Although I think that there are a lot of issues with encryption as it might open the gate to amplification attacks. Thus using a secure layer as SSL might do it... But again, SSL cannot be really trusted. Anyhow, a captured encrypted password or a captured password is about as dangerous if you can just replay it – Aki Jul 09 '14 at 09:25
5

As per MySQL's documentation on the Encryption functions, http://dev.mysql.com/doc/refman/5.5/en/encryption-functions.html

Caution

Passwords or other sensitive values supplied as arguments to encryption functions are sent in plaintext to the MySQL server unless an SSL connection is used. Also, such values will appear in any MySQL logs to which they are written. To avoid these types of exposure, applications can encrypt sensitive values on the client side before sending them to the server. The same considerations apply to encryption keys. To avoid exposing these, applications can use stored procedures to encrypt and decrypt values on the server side.

To avoid these, they suggest encrypting on the client side.

Karthick
  • 51
  • 1
  • 1
  • 1
    I'm not sure that this addresses the question. This is about a caution on encryption in general, and not just AES. You still need to encrypt server-side if you want to do something like encrypting a column containing sensitive data. – schroeder Dec 01 '14 at 17:43