function mysql_safe_string($value) {
if(is_numeric($value)) return $value;
elseif(empty($value)) return 'NULL';
elseif(is_string($value)) return '\''.mysql_real_escape_string($value).'\'';
elseif(is_array($value)) return implode(',',array_map('mysql_safe_string',$value));
}
function mysql_safe_query($format) {
$args = array_slice(func_get_args(),1);
$args = array_map('mysql_safe_string',$args);
$query = vsprintf($format,$args);
$result = mysql_query($query);
if($result === false) echo '<div class="mysql-error"><strong>Error: </strong>',mysql_error(),'<br/><strong>Query: </strong>',$query,'</div>';
return $result;
}
// example
$result = mysql_safe_query('SELECT * FROM users WHERE username=%s', $username);
if(is_numeric($value)) return $value;
elseif(empty($value)) return 'NULL';
elseif(is_string($value)) return '\''.mysql_real_escape_string($value).'\'';
elseif(is_array($value)) return implode(',',array_map('mysql_safe_string',$value));
}
function mysql_safe_query($format) {
$args = array_slice(func_get_args(),1);
$args = array_map('mysql_safe_string',$args);
$query = vsprintf($format,$args);
$result = mysql_query($query);
if($result === false) echo '<div class="mysql-error"><strong>Error: </strong>',mysql_error(),'<br/><strong>Query: </strong>',$query,'</div>';
return $result;
}
// example
$result = mysql_safe_query('SELECT * FROM users WHERE username=%s', $username);
Just use mysql_safe_query in place of mysql_query and you should be safe from SQL injection attacks. Use %s in place of any variables, and append them as arguments. Don’t quote your strings, it’ll be done for you automatically. Arrays will be flattened for you automatically and concatenated with commas. You can delete the error-echoing line if you want, but I find it useful for development.
2 thoughts on “SQL Injection Safe Queries Redux”
Nice.
Only hitch is that mysql_real_escape_string() really needs the mysql resource as the second parameter. In some conditions, it seems to return an empty string if the db resource is not present.
I’ve seen this just a few times on different setups, and I’m not sure what the common factor is. Seems to be happening more just lately.
bucabay
This assumes you’re already connected to a database. Unless you have multiple connections open, you shouldn’t need the link identifier should you? In any case, it’s not hard to add 🙂 I’ve never had any problems with it, but I’ve only tested it on a few servers. If all else fails, addslashes might do the trick.
Mark