Thursday, January 04, 2007

PHP: SQL Injection Best Practice in MySQL

It is important to avoid SQL injection when working with mysql, and so it is common to use a function like mysql_real_escape_string() in PHP.

At php.net, they suggest using sprintf and a function called quote_smart as described on their mysql_real_escape_string documentation page as a "best practice" method.

php.net [good]:
$query = sprintf(
"SELECT * FROM users WHERE user='%s' AND password='%s'",
mysql_real_escape_string($user),
mysql_real_escape_string($password));


php.net [better]:
$query = sprintf(
"SELECT * FROM users WHERE user=%s AND password=%s",
quote_smart($user),
quote_smart($password));


qsprintf [best]:
$query = qsprintf(
"SELECT * FROM users WHERE user=%s AND password=%s",
$user,
$password);

correction: Of the 3 examples above, the 2 previous used to have single quotes around %s, but this is incorrect as quote_smart or myquote will add single quotes.

note: using sprintf either qsprintf functions will get screwed up when you have a " like 's%' " statement in sql or similar 'like' statements using % (the percentage character).

<?php
function myquote($value)
{
if (get_magic_quotes_gpc())
$value = stripslashes($value);
if (is_numeric($value))
return "'$value'";
return "'".mysql_real_escape_string($value)."'";
}

function qsprintf()
{
$numargs = func_num_args();
$arg_list = func_get_args();
$format = $arg_list[0];
$next_arg_list = array();
for ($i = 1; $i < $numargs; $i++)
$next_arg_list[] = myquote($arg_list[$i]);
return vsprintf($format, $next_arg_list);
}
?>


notes: I modified their quote_smart function and renamed it to myquote in my code sample below because mysql doesn't care if you try to insert '123' into a numeric field (including single quotes) and that way I wouldn't lose leading zeros when inserting something like '01234' into a character field.

1 comment:

bill said...

Excellent!!!! Your qsprintf is cleaner in expression in that the function calls quote_smart don't need to be the argument list, as you handle that inside your function by called myquote for each argument.