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.
There were four combinations which we want to test:
- A simple query from php, with the digested password being passed in as a parameter.
- A simple query from php, with the raw password being passed in and digested in the query.
- A stored procedure call, with the digested password being passed in as a parameter.
- 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.
The following table shows the average duration of an insert/select statement in each case:
|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.
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.