MD5/SHA1 Encryption in PHP vs. MySQL

This is a guest post by Karl Agius.

Last week, there was a brief discussion in the comments for "Creating a PHP CMS Part 7" about whether it was preferable to use PHP's functions, or MySQL's built in functions to digest a password. There was no solid answer at that point, so I ran a small test to determine which way worked best.

The tests

There were four combinations which we want to test:

  1. A simple query from php, with the digested password being passed in as a parameter.
  2. A simple query from php, with the raw password being passed in and digested in the query.
  3. A stored procedure call, with the digested password being passed in as a parameter.
  4. A stored procedure call, with the raw password being passed in and digested in the procedure.

A script was written for this test, to execute each case in 10 batches of 500 repetitions. The table will be dropped between each repetition so that each test starts from the same baseline. Each test will also insert the same data.

Comparison

The following table shows the average duration of an insert/select statement in each case:

Query Stored procedure
PHP 0.4912ms / 0.6498ms 0.4862ms / 0.6396ms
MySQL 0.4658ms / 2.1632ms 0.4624ms / 2.1918ms

The test results were quite a surprise. In terms of insertion speed, there really isn't much to distinguish between the bunch; letting MySQL handle the digest seems to win a few fractions of a millisecond, but given the variance, they're pretty much neck to neck. In terms of select speed however, passing the value to the database when it's already digested wins hands down - this method reads more than three times faster than the other.

In the context of our discussion, this makes the choice a no-brainer. We'll probably want to run a select on the password column in an authentication table a lot more often than we want to insert to it, so regardless of how much faster the insertion is, it's the selection which we're worried about. In this case, it makes sense to stick to the digesting the password in php and passing it to the database.

To SP or not to SP?

Stored procedures appear to work fractionally faster than straight queries in this case, but again, the difference isn't very significant unless you're processing a large volume. It's largely a matter of personal style and preference.

Test script

The test script can be downloaded here. Before using it, make sure you change the host, username, password and database name values in database.php.

Stay Updated

Did you enjoy this post? Don't miss a single post by getting free updates!

7 Comments

  1. September 21, 2009

    Brilliant post, it was very helpful. Thanks for sharing this =D

  2. September 21, 2009

    Thanks for your comment Borrellus, glad you found it interesting.

  3. Saviour Caruana
    September 24, 2009

    Karl, can you try this test with the password field 'digest' indexed?

    The difference being MySQL has to go through all records executing SHA1 for each.

    With an indexed field, the result should probably be the same. My hunch.

  4. September 24, 2009

    Saviour, indexing digest brings down the gap in the reads close enough to even out the field :)

    There is a very tiny advantage in the php digest version, but only a few fractions of a ms, and the reads are faster all around.

    Could you clarify what you mean by "MySQL has to go through all records executing SHA1 for each"?

    Thanks for the tip and comment!

  5. October 14, 2010

    Thanks for this! What Saviour means is that MySQL will perform its WHERE calculations for each record that it reads. In this case, it is having to perform the SHA1 encryption routine once for each record in the table.

Trackbacks/Pingbacks