Page 1 of 1

avoid repeated mysql_real_escape_string?

Posted: Tue Jan 19, 2010 5:40 am
by PHPycho
Consider:

Code: Select all

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

Code: Select all

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

Code: Select all

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

Re: avoid repeated mysql_real_escape_string?

Posted: Tue Jan 19, 2010 5:48 am
by VladSun

Re: avoid repeated mysql_real_escape_string?

Posted: Tue Jan 19, 2010 3:09 pm
by tr0gd0rr
I like using a style like Oracle's named binding:

http://us3.php.net/mysql_real_escape_string#89851 :

Code: Select all

// $sql = "SELECT * FROM users WHERE user = :user AND password = :password";
// mysql_bind($sql, array('user' => $user, 'password' => $password));
// mysql_query($sql);
 
function mysql_bind(&$sql, $vals) {
    foreach ($vals as $name => $val) {
        $sql = str_replace(":$name", "'" . mysql_real_escape_string($val) . "'", $sql);
    }
}

Re: avoid repeated mysql_real_escape_string?

Posted: Tue Jan 19, 2010 4:31 pm
by VladSun
Looks good, but it can't handle NULL values.

Re: avoid repeated mysql_real_escape_string?

Posted: Tue Jan 19, 2010 5:23 pm
by tr0gd0rr
True. The version I actually use is much more complex and allows nulls, converts true/false to 1/0, creates IN() for arrays and accepts expression objects. Or of course you can use mysqli and replace the named parameters with ? and do real binding. That is probably ideal for most situations.

Mostly I just hate binding with question marks because counting is error prone, especially when you change a query to add/remove a parameter.

Re: avoid repeated mysql_real_escape_string?

Posted: Tue Jan 19, 2010 5:39 pm
by VladSun
tr0gd0rr wrote:Mostly I just hate binding with question marks because counting is error prone, especially when you change a query to add/remove a parameter.
:drunk: Absolutely!

Re: avoid repeated mysql_real_escape_string?

Posted: Tue Jan 19, 2010 5:43 pm
by VladSun

Code: Select all

$sql = str_replace(":$name", "'" . mysql_real_escape_string($val) . "'", $sql);
This could cause problems if the data itself contains a placeholder string.

Re: avoid repeated mysql_real_escape_string?

Posted: Tue Jan 19, 2010 5:50 pm
by tr0gd0rr
Yes. It also fails if you have two parameters that begin the same such as "user" and "user_id" when "user" comes first in the array. You can do a preg_replace_all() to make sure that you are running through the string only once.

Re: avoid repeated mysql_real_escape_string?

Posted: Wed Jan 20, 2010 4:45 am
by 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.

Code: Select all

//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

Re: avoid repeated mysql_real_escape_string?

Posted: Wed Jan 20, 2010 4:52 am
by Eran
There's not much point in checking for the existence of mysql_real_escape_string() on every escape operation, that function has been available since PHP 4.3 which has been available for more than 7 years now.

Re: avoid repeated mysql_real_escape_string?

Posted: Wed Jan 20, 2010 5:13 am
by VladSun
PHPycho wrote:Please have a look and comment/suggest if there is any rooms for improvements.
VladSun wrote:
tr0gd0rr wrote:Mostly I just hate binding with question marks because counting is error prone, especially when you change a query to add/remove a parameter.
:drunk: Absolutely!
:)