Security escaping when md5'ing in MySQL

Question

In this code

UPDATE table SET field = MD5('{$_POST['variable']}') WHERE id = 1;

Since the updated value is md5'ed, should I still take steps to prevent from injection? Like addslashes() or something like that?

Answer 1

You are not passing a MD5 hash to the SQL query, you are passing the plain text that MySQL will hash later. So yes, you should take steps to prevent SQL Injection here. Or even better, hash it in PHP and then use the hash in the query.

If you are hashing passwords, MD5 is considered weak. Use bcrypt instead.

Answer 2

Since the updated value is md5'ed, should I still take steps to prevent from injection?

In fact the variable in your code has not been MD5'ed when it goes into the query string, so the same injection issues still apply just as they would with any other variable.

If you had done $hashedVar = md5($var) in PHP and then added $hashedVar into the query string, then your question may have been a bit more legitimate, but the way you're doing it with the MD5() as part of the query string itself, the answer is yes, it most certainly does need to be escaped to avoid injection.

However some additional notes:

1. addslashes() is not the correct method to use for escaping PHP strings into SQL. You should use the real escape string function applicable to the DB API that you're using.

   If you're using mysqli or PDO as your DB API, your best option for this is Prepapred Statements. If you're using the older mysql_xxx() functions then you don't have this option, but you should update to one of the other APIs, as the mysql extension is deprecarted.

2. MD5 is not a secure hashing algorithm for passwords. If you're using plain unsalted MD5 for passwords then you are insecure already, before you even start thinking about injection attacks. You should use a better hashing algorithm such as bcrypt.

   Recent PHP versions include a set of password_xx() functions which are designed specifically for providing the best available quality hashing with minimal effort. (these functions are also available for older PHP versions, via a third-party library).

Answer 3

If you are concating a variable into a SQL query (which you should not be doing; you should be using prepared statements), then you need to escape it.

You are first building a string, and then sending that string to MySQL. It doesn't matter that MD5() is in the query. What if I sent '), field2 = 0; -- ' as $_POST['variable'], you'd then get:

UPDATE table SET field = MD5(''), field2 = 0; -- '') WHERE id = 1;

Which as you can see is bad.

Answer 4

If you use PHP to encrypt the string, then your SQL query will be safe from injection.

$string = md5($_POST['variable']);

$sql = "UPDATE table SET variable = '$string'";

That is much safer than using MySQL's md5 function, but it would still be preferable that you get in the habit of making all of your user input safe.

Answer 5

As Spudley mentioned, A query escape should be implemented in order to avoid SQL injection. MD5 is indeed a weak hashing algorithm (some script kiddie tools can crack it in as little as five minutes), and passwords definitely do need to be protected with a more robust hashing algorithm.

That having been said, I would also take into account the threat and risk environment of your system. If you are developing a database for a system that will never be connected to the internet, the only threat is from malicious insider users or mistakes (stupid user risk = very unlikely) and MD5 may be adequate for a short time. If accessible via the internet or a network connection that would allow knowledgeful users to purposely inject SQL commands, your risk is higher and I would strongly recommend anything stronger than MD5 available to you.

Further mitigation measures consist of strong input validation and databse-webserver separations via proxies. Without further detailed information about your system (and please DO NOT post any of those details here), I don't believe I can recommend any further defensive measures.