avoid repeated mysql_real_escape_string?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
PHPycho
Forum Contributor
Posts: 336
Joined: Fri Jan 06, 2006 12:37 pm

avoid repeated mysql_real_escape_string?

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: avoid repeated mysql_real_escape_string?

Post by VladSun »

There are 10 types of people in this world, those who understand binary and those who don't
User avatar
tr0gd0rr
Forum Contributor
Posts: 305
Joined: Thu May 11, 2006 8:58 pm
Location: Utah, USA

Re: avoid repeated mysql_real_escape_string?

Post 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);
    }
}
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: avoid repeated mysql_real_escape_string?

Post by VladSun »

Looks good, but it can't handle NULL values.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
tr0gd0rr
Forum Contributor
Posts: 305
Joined: Thu May 11, 2006 8:58 pm
Location: Utah, USA

Re: avoid repeated mysql_real_escape_string?

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: avoid repeated mysql_real_escape_string?

Post 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!
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: avoid repeated mysql_real_escape_string?

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
tr0gd0rr
Forum Contributor
Posts: 305
Joined: Thu May 11, 2006 8:58 pm
Location: Utah, USA

Re: avoid repeated mysql_real_escape_string?

Post 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.
User avatar
PHPycho
Forum Contributor
Posts: 336
Joined: Fri Jan 06, 2006 12:37 pm

Re: avoid repeated mysql_real_escape_string?

Post 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
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: avoid repeated mysql_real_escape_string?

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: avoid repeated mysql_real_escape_string?

Post 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!
:)
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply