Preg_match vs. mysql_real_escape_string to prevent sqli
Replies: 2   Views: 2179  Subscribers: 0

Posted by Justin · 27-05-2012 - 09:25

Edited by reece · 12-11-2012 - 04:53
Recently I was in a debate with Litespeed about validating user input being queried to an SQL database to prevent SQLi(SQL Injections).
The resulting methods me and LiteSpeed came up with are the following. 

­mysql_real_escape_string:­
The mysql_real_escape_string function is specifically for validating query input. It is most effective due to its simplicity. How it works is once you connect to the database succesfully, you then call it and it validates the query strings. It accepts one argument(the input string being queried to the database) and automatically checks to make sure it is query safe. The following code snippet demonstrates it's use:
­
mysql_connect("localhost", "root", "");
mysql_select_db("db");
mysql_real_escape_string($userInput);­
­preg_match­ preg_match uses regex(regular expressions) patterns which can be made to limit user input. The advantage of using preg_match is it doesn't revolve around SQL and can be used for a number of things. In effect, using preg_match you can check user input before connecting to a database. The downside is that preg_match is much more complex to use(considering you need to know regex). Here is an example: ­
$input = "thiswillwork";
$match = null;
preg_match("/^[0-9a-zA-Z]{1,1024}$/", $input, $match);
if ($match != null)
{
	//make connection
}­
­Compare and Contrast­ Lets go ahead now and take a look at both methods side by side. Rating each winner as preg_match, neutral, or mysql_real_escape_string, here is the rundown. ­Effectiveness:­ While mysql_real_escape_string is specifically meant for blocking out any type of invalid input, it is limited to only validating after a connection has been made. To the contrary, preg_match can test a string anytime anywhere, however this doesn't seem to matter much. mysql_real_escape_string winner: neutral ­Simplicity:­ When it comes to simplicity, it is much easier hands down to write 'mysql_real_escape_string($input);' then to implement the same functionality using preg_match. On top of the extra code, you will also need to have a decent understanding of regex. Finally, preg_match would leave more room for error since your regex pattern might not be perfect as apposed to using the premade mysql_real_escape_string function which will always make sure that nothing gets passed that shouldn't. winner: mysql_real_escape_string With the above stated, when it comes to the validating user input for SQL queries, mysql_real_escape_string seems to be the most efficient approach.­