MySQL input/output validation

While talking about SQL injection one may ask how to secure their webapp. Here’s a function I currently use, adapted from php.net. DO NOT trust my lousy functions . Just because I believe it’s secure doesn’t mean it is . It bases on mysql_real_escape_string() so make sure you run PHP >= 4.3.0.


function check_gpc_input($value)
{
if(!empty($value))
{
// truncation (see comments)
$value = substr($value,0,20);
}
// Stripslashes if magic quotes enabled
if (get_magic_quotes_gpc())
{
$value = stripslashes($value);
}
// Quote if not a number
if (!ctype_digit($value))
{
$value = "'" . mysql_real_escape_string($value) . "'";
}
else
{
$value = intval($value);
}
return $value;
}


usage:
$cat = check_gpc_input($_GET["id"]);
$query = "SELECT id,name FROM users WHERE id = ".$cat."";

Note that this function is only for GPC (GET/POST/COOKIE) input. Do not use it to validate output from your database or other input. Instead make sure you use htmlentities() if you output data to prevent XSS.

Also consider that your validation depends on your webapp. I would advice to take those examples only as idea how your functions could look like.

References: sla.ckers, Ronalds secure programming flowchart, 0×000000 thread