Is using a database-level MD5 function a bigger security risk than an application level function?

Question

I've got a chunk of code that validates a user's username and password, which goes something like this:

$sql = "SELECT * 
FROM user 
WHERE 
    username='{$_POST['username']}' AND 
    password=MD5('{SALT}{$_POST['password']}')";

Is this any more/less secure than doing it like this?

$sql = "SELECT * 
FROM user 
WHERE 
    username='{$_POST['username']}' AND 
    password='".md5(SALT.$_POST['password'])."'";

Regardless of where/if escaping is done, is the first method vulnerable to sql injection attacks? Would the answer be the same for other database engines besides MySQL?

Answer 1

Speaking about injection, both ways are secure, if you properly escape variables.

The first case will be more vulnerable, if you use complete query logging, and so the password will appear as plain text.

Besides, if your system is affected by some virus that works as proxy between your script and database, it'll be able to catch your password.

One last problem that you may encounter (quite rarely, in fact), is when the system is inflicted with a virus, that reads sensible data from memory.

I hope this makes sense.

Answer 2

You should use prepared statements instead and have a look at this question.

Answer 3

Regardless of where/if escaping is done, is the first method vulnerable to sql injection attacks?

SQL injection will not occur if proper escaping and sanitizing takes place

Would the answer be the same for other database engines besides MySQL?

I think you should look more at the expense taken to perform one action over another. The first method would take less time to execute than the second method, ceteris paribus.

Answer 4

Oh god, please tell me you're doing some type of mysql_escape_string or mysql_real_escape_string or AT LEAST addslashes or addcslashes to any $_POST variables before you insert them into a raw MySQL statement?

I think the most secure way to do this is to:

a) use filter_var or preg_replace to get rid of extraneous characters from the $_POST['username']

b) SELECT the row by the username from MySQL (also grabbing the digested password)

c) compare the message digested version of the password from the $_POST to that of the retrieved row (assuming you don't leave your password cleartext) in your application code, not in the SQL statement

If you do it this way, there's only 1 possible place for injection (username), and it's pretty impossible when you're doing a preg_replace( '/\W/', '', $_POST['username'] ) which removes anything not A-Za-z0-9_- (or change to your username whitelist of characters).

However, if you're doing rock-solid proper sanitization, it really doesn't matter where you do your comparison. Theoretically, though, I'd allow for the least possible interaction with user input and raw SQL statements (i.e. only SELECTing by username and comparing outside of your DB).

Answer 5

To start off with MD5 is prevent to be an insecure algorithm and should never be used for passwords. You should use a staled sha256 and most databases do not have this function call. But even if the database did I think its a bad idea. Not a very bad idea, but its best to keep as few copies of your password around. Often the database can be on a completely different machine, and if that machine where compromised then the attacker could obtain clear text passwords by looking at the quires. In terms of SQL Injection, there is no difference in security and judging by your queries you should be more worried about SQL injection.