Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder
    Join Date
    Dec 2005
    Posts
    217
    Thanks
    1
    Thanked 0 Times in 0 Posts

    avoid repeated mysql_real_escape_string?

    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.

  • #2
    Regular Coder
    Join Date
    Dec 2009
    Location
    UK
    Posts
    495
    Thanks
    0
    Thanked 58 Times in 58 Posts
    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
    My site: JayGilford.com
    Resources:
    PHP Pagination Class | Getting all page links | Handling PHP Errors properly
    If you like a users help, show your appreciation with the rep and thanks buttons :)

  • #3
    Senior Coder Dormilich's Avatar
    Join Date
    Jan 2010
    Location
    Behind the Wall
    Posts
    3,132
    Thanks
    12
    Thanked 332 Times in 328 Posts
    Quote Originally Posted by PHPycho View Post
    above approach is free of SQL injection
    almost free, there are still ways around that.
    Quote Originally Posted by JAY6390 View Post
    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.
    Last edited by Dormilich; 01-19-2010 at 01:12 PM.

  • #4
    Regular Coder
    Join Date
    Dec 2005
    Posts
    217
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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) ? 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), $sql1);
        }

        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

  • #5
    Senior Coder Dormilich's Avatar
    Join Date
    Jan 2010
    Location
    Behind the Wall
    Posts
    3,132
    Thanks
    12
    Thanked 332 Times in 328 Posts
    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?
    Last edited by Dormilich; 01-20-2010 at 11:06 AM.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •