mysql_real_escape_string() on all of their variable inputs into a database instead of casting to variable type integers for the int inputs.I created the below bechmark test to demonstrate what performance differences there would be in using each of the possible functions in such a situation.
It’s important to note that all user input variables that are intended to be strings go through the
mysql_real_escape_string() function to ensure proper sanitisation for those strings.But for integers, what about those? should we use the
mysql_real_escape_string() function on them, or should we cast the variable type int?Lets find out what the performance differences are:
PHP Code:
<?php
$link = mysql_connect('localhost', 'username', 'password');
if (!$link)
{
die('Could not connect: ' . mysql_error());
}
echo "Connected successfully<br />\n";
$string = "12345'?;/FOO/FUM\';Example String =";
$num = 500000;
echo "benchmark: Begin<br />\n";
$start_time = microtime(true);
for ($i = 0; $i < $num; $i++)
{
$escaped = mysql_real_escape_string($string);
}
$escape_benchmark = microtime(true) - $start_time;
$start_time = microtime(true);
for ($i = 0; $i < $num; $i++)
{
$intval = intval($string);
}
$intval_benchmark = microtime(true) - $start_time;
$start_time = microtime(true);
for ($i = 0; $i < $num; $i++)
{
$int = (int) $string;
}
$int_benchmark = microtime(true) - $start_time;
echo "escape_string time: $escape_benchmark <br />\n";
echo "intval time: $intval_benchmark <br />\n";
echo "int time: $int_benchmark <br />\n";
echo 'intval vs escape_string difference: ' . ($escape_benchmark - $intval_benchmark) . "<br />\n";
echo 'int vs escape_string difference: ' . ($escape_benchmark - $int_benchmark) . "<br />\n";
echo 'int vs intval difference: ' . ($intval_benchmark - $int_benchmark) . "<br />\n";
echo 'Percent faster intval is than escape: ' . round((1 - ($intval_benchmark / $escape_benchmark)) * 100) . "%<br />\n";
echo 'Percent faster int is than escape: ' . round((1 - ($int_benchmark / $escape_benchmark)) * 100) . "%<br />\n";
echo 'Percent faster int is than intval: ' . round((1 - ($int_benchmark / $intval_benchmark)) * 100) . "%<br />\n";
echo "<br />\nThe result of the sanitisation:<br />\n";
echo "escape_string: $escaped <br />\n";
echo "intval func: $intval <br />\n";
echo 'cast int: ' . $int;
mysql_close($link);
?>
Result:
Connected successfullybenchmark: Begin
escape_string time: 0.697912931442 intval time: 0.349272966385 int time: 0.16778588295 intval vs escape_string difference: 0.348639965057 int vs escape_string difference: 0.530127048492 int vs intval difference: 0.181487083435 Percent faster intval is than escape: 50% Percent faster int is than escape: 76% Percent faster int is than intval: 52%
The result of the sanitisation:
escape_string: 12345\'?;/FOO/FUM\\\';Example String = intval func: 12345 cast int: 12345
From the benchmark, we can deduce that (int) produces a 70% to 75% performance advantage over using
mysql_real_escape_string(); and a 50% performance advantage over using the intval() function.In addition, if you are using an
UPDATE or INSERT SQL query, forcing var type int on integer variables will mean that your query will produce a valid result, whereas an intended int variable that contains a string which goes through the mysql_real_escape_string() function is going to throw an SQL Error if the SQL database is expecting a float or integer value of some sort.Of course, don’t take my word for it... the above script is provided for you to test yourselves as well. :)
If you want to test the benchmark without making a database connect, substitute the
mysql_real_escape_string() with the older mysql_escape_string() function, which does not require a database link to process.Enjoy!
- Highway of Life
