The mysql_real_escape_string function is a very important PHP function in preventing MySQL injection attacks in PHP web applications. This short tutorial will summarizes the best practices of using MySQL real escape string. The objective is to prevent application problems and ensure security of your web applications.
First: Never turn on magic_quotes_gpc function in your PHP configuration.
Reason: Magic_quotes_gpc is deprecated as of PHP 5.3.0, well this is not only the main reason in stop using it. One of the primary reasons are that this function is know to cause problems in PHP web applications in the long run particularly if you use this along with mysql_real_escape_string in preventing MySQL injection attacks.
As an overview, if you do not know this function very well. The overall use is to automatically escape all quotes in your PHP application regardless whether or not you are using it for MySQL queries. Well, your web application only faces risk to MySQL injection if you do not sanitize PHP POST variables before doing MySQL queries. But magic_quotes_gpc function escapes all quotes!
This is where problems can start. Again if you do not know whether magic_quotes_gpc is turn on and turn off. It is best to look at your PHP configuration file (php.ini) and make sure it is disabled:
magic_quotes_gpc = Off
if you turn it on and you use mysql_real_escape_string, then you end up double escaping your quotes which is NOT good.
Second: mysql_real_escape_string function does not prevent injection in non-string inputs
Reason: Supposing you have this string input (with quotes):
To prevent any possibility of MySQL injection attacks, it will escape the quote so it will become:
So with quoted strings, the function actually prevents the injection. The problem is now if your web form is accepting a non-string input such as a number:
Supposing a hacker will not input a number but inject the following:
1 OR 1=1
If you are using mysql_real_escape_string, you will see that it will not prevent this type of attacks. So the solution is to use the PHP intval function. The following therefore is the recommended method of protecting MySQL injection attacks to integer inputs (NOT a string):
<?php $number=intval($number); $result = mysql_query("SELECT `animal` FROM `test` WHERE `number`='$number'") or die(mysql_error());
It means that before using it to the MySQL SELECT query statement, it will ensure that the variable is of integer value and nothing else.
Third: Always re-validate user inputs to ensure that the data is of certain type, properties and limits
A common mistake is relying only on mysql_real_escape_string to prevent ALL possible SQL injection attacks. The best practice is to re-validate everything before using it in the queries in addition to mysql_real_escape_string. Supposing you are accepting a password in a web form and that password SHOULD always be less than 12 characters. So ensure it in your PHP scripts that this is validated. Also if you are accepting an decimal input, then ensure that your inputs are decimal. Having this security concept in mind will further strengthen your application because ALL inputs are strongly validated.