Someone asked me today what the performance difference would be if they used mysql_real_escape_string() on all of their variable inputs into a database instead of casting to integers for the int inputs.
So I created the following benchmark for them and thought I would share it with you all. :)
It’s important to note that
all user input
strings must to go through mysql_real_escape_string() function for proper sanitisation.
But for integers, what about those? should we use the escape_string function on them, or force to var 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 successfully
benchmark: 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
So from the benchmark, we can determine that (int) produces a 70% - 75% performance advantage over using mysql_real_escape_string();
In addition, if you are using an UPDATE or INSERT query, forcing var type int on integer variables will mean that your query will function properly, whereas an intended int variable that contains a string which goes through the 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. :)
Enjoy!
- Highway of Life