Is using a database-level MD5 function a bigger security risk than an application level function?
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['passw开发者_开发问答ord']}')";
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?
You should use prepared statements instead and have a look at this question.
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.
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 SELECT
ing by username and comparing outside of your DB).
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.
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.
精彩评论