Safe alternative to mysql_real_escape_string? (PHP)

Question

I am passing a variable to a function that executes a query

The MySQL connection only occurs inside the function, and closes inside the function

I want to be able to safely escape strings BEFORE I send them to the function

I can't use mysql_real_escape_string because it requires a MySQL connection (which is only being made inside the function)

I know the simple answer would be to escape strings inside the function, but I cannot do this because I need to send some escaped, and some non-escaped parts of a string

For example, I need to run the function like this:

myquery("'" . escape_me("My string") . "'");

Notice I am sending two apostrophe's-- unescaped, with an escaped string inside. For this reason I can't do a blanket mysql_real_escape_string on arguments inside of myquery function.

I found the following code, suggesting that I could use it as an alternative to mysql_real_escape_string:

// escape characters
function escape_me($value) {
    $return = '';
    for($i = 0; $i < strlen($value); ++$i) {
        $char = $value[$i];
        $ord = ord($char);
        if($char !== "'" && $char !== "\"" && $char !== '\\' && $ord >= 32 && $ord <= 126)
            $return .= $char;
        else
            $return .= '\\x' . dechex($ord);
    }
    return $return;
}

I do not know if this function is safe from multibyte attacks, but I think I also need to undo the function every time I query

For example, inputting: Testing 3's "OK" is turned into Testing 3x27s x22OKx22 in the database

So my main question is: Do you know if there is another function I can use as an alternative to mysql_real_escape_string that will safely escape characters?

Answer 1

• It's terrible idea to connect every time you're calling this function. A good planned application wouldn't have such odd limitation.

• you can use substitutions, like this
  myquery("SELECT * FROM table WHERE id = %s","My string");

• You can use another way of substitutions, a modern one: prepared statements. it will be described in numerous other answers.

as noone posted it yet, here is rough example

function fetchAll(){
 $args = func_get_args();
 $query = array_shift($args);
 $stmt = $pdo->prepare($query);
 $stmt->execute($args);
 return $stmt->fetchAll();
}
$a=$db->fetchAll("SELECT * FROM users WHERE status=? LIMIT ?,?",$status,$start,$num);

• As long as you're using single-byte encoding or utf-8, no need to use mysql_real_escape_string, so mysql_escape_string(deprecated) or addslashes would be enough

Answer 2

I would suggest passing in an array of values to be escaped and using sprintf() format the query while escaping each of the values. Here is the first part of my query function:

public function querya($query, $args=null){
    //check if args was passed in
    if($args !== null){
        //if an array...
        if(is_array($args)){
            //...escape each value in the args array
            foreach($args as $key=>$value){
                $args[$key] = mysql_real_escape_string($value);
            }
            //add the query to the beginning of the args array
            array_unshift($args, $query);
            //call sprintf with the array as arguments to sprintf
            $query = call_user_func_array("sprintf", $args);
        } else {
            //if args is not an array, then string (single argument passed in).
            $query = sprintf($query, mysql_real_escape_string($args));
        }
    }

    //perform query, other stuff
}