avoid repeated mysql_real_escape_string?

[PHPycho]

Consider:

PHP Code:

$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:
1>

PHP Code:

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


2>

PHP Code:

$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.

[JAY6390]

If you're comfortable with sprintf consider using my mressf function (mysql_real_escape_string and sprintf in one)

PHP Code:

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

PHP Code:

$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

[Dormilich]

Originally Posted by PHPycho

above approach is free of SQL injection

almost free, there are still ways around that.

Originally Posted by JAY6390

PHP Code:

$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.

[PHPycho]

I have made one such utility regarding automatic sql escaping:
Please have a look and comment/suggest if there is any rooms for improvements.

PHP Code:

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

function escapeString($input){
    if(function_exists('mysql_real_escape_string')){    
        $input = mysql_real_escape_string($input);
    }elseif (function_exists('mysql_escape_string')) {
        $input = mysql_escape_string($input);
    }else{
        $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);    
}

//usage:
$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 


Thanks

[Dormilich]

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, MySQLi), that already provide this functionality?