View Full Version : avoid repeated mysql_real_escape_string?

01-19-2010, 12:40 PM

$sql = "SELECT * FROM table WHERE field1='".mysql_real_escape_string($field1_value)."' AND field2 ='".mysql_real_escape_string($field2_value)."'";
$result = mysql_query($sql);
while($row = mysql_fetch_array($result)){
//fetching goes here..

above approach is free of SQL injection but has tedious job of writing mysql_real_escape_string() for every field values.
I would like to know the good approach for auto escaping,so that i don't have to write those escaping manually.
I have seen some styles like:

$sql = "SELECT * FROM table WHERE field1='%s' AND field2 ='%s'";
$result = custom_query($sql, array($field1_value, $field2_value));


$sql = "SELECT * FROM table WHERE field1=? AND field2 =?"; //without using quotes which will be auto detected & quoted accordingly
$result = custom_query($sql, array($field1_value, $field2_value));
Note: above doesn't uses prepared statement.

what will the custom_query() function look like ?
anybody has used similar function?

Thanks in advance for the valueable suggestion.

01-19-2010, 02:00 PM
If you're comfortable with sprintf consider using my mressf function (mysql_real_escape_string and sprintf in one)

function mressf()
$args = func_get_args();
if (count($args) < 2)
return false;
$query = array_shift($args);
$args = array_map('mysql_real_escape_string', $args);
array_unshift($args, $query);
$query = call_user_func_array('sprintf', $args);
return $query;
}Then use it like you would normally

$query = mressf("SELECT * FROM table WHERE field1="%s" AND field2="%s", $field1_value, $field2_value);
It will automatically escape $field1_value and $field2_value for you before inserting them into the query

01-19-2010, 02:08 PM
above approach is free of SQL injection
almost free, there are still ways around that.

$query = mressf("SELECT * FROM table WHERE field1="%s" AND field2="%s", $field1_value, $field2_value);
should throw an "unexpected T_STRING" error due to the " around %s, which close the string.

01-20-2010, 11:22 AM
I have made one such utility regarding automatic sql escaping:
Please have a look and comment/suggest if there is any rooms for improvements.

//utility functions
function escape($input){
return array_map('escape', $input);
$input = trim($input);
switch (gettype($input)){
case 'string' :
$input = "'".escapeString($input)."'";
case 'boolean' :
$input = ($input === FALSE) ? 0 : 1;
default :
$input = ($input === NULL) ? 'NULL' : $input;
return $input;

function escapeString($input){
$input = mysql_real_escape_string($input);
}elseif (function_exists('mysql_escape_string')) {
$input = mysql_escape_string($input);
$input = addslashes($input);
return $input;

function prepareBindSql($sql, $binds){
if (false === strpos($sql, '?')) {
return $sql;

if ( ! is_array($binds)){
$binds = array($binds);

foreach ($binds as $val){
$val = escape($val);

// Just in case the replacement string contains the bind
// character should be temporarily replaced by a marker
$val = str_replace('?', '{%bind_marker%}', $val);
$sql = preg_replace("#".preg_quote('?', '#')."#", str_replace('$', '\$', $val), $sql, 1);

return str_replace('{%bind_marker%}', '?', $sql);

function customQuery($sql, $binds = false){
if(false !== $binds){
$sql = prepareBindSql($sql, $binds);
return mysql_query($sql);

$sql = "SELECT * FROM table WHERE int_field = ? AND string_field = ?";
$result = customQuery($sql, array($int_field_value, $string_field_value)); //this will auto escape & quote according to data types


01-20-2010, 12:03 PM
if you want to use Prepared Statements (which is awesome and even more secure that mysql_real_escape_string()), why don’t you use PHP’s built-in DB classes (PDO (http://php.net/pdo), MySQLi (http://php.net/mysqli)), that already provide this functionality?