What is the PDO equivalent of function mysql_real_escape_string?

Question

I am modifying my code from using mysql_* to PDO. In my code I had mysql_real_escape_string(). What is the equivalent of this in PDO?

Answer 1

Well No, there is none!

Technically there is PDO::quote() but it is rarely ever used and is not the equivalent of mysql_real_escape_string()

That's right! If you are already using PDO the proper way as documented using prepared statements, then it will protect you from MySQL injection.

---

# Example:

Below is an example of a safe database query using prepared statements (pdo)

  try {
     // first connect to database with the PDO object. 
     $db = new \PDO("mysql:host=localhost;dbname=xxx;charset=utf8", "xxx", "xxx", [
       PDO::ATTR_EMULATE_PREPARES => false, 
       PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
     ]); 
 } catch(\PDOException $e){
     // if connection fails, show PDO error. 
   echo "Error connecting to mysql: " . $e->getMessage();
 }

And, now assuming the connection is established, you can execute your query like this.

if($_POST && isset($_POST['color'])){ 

    // preparing a statement
    $stmt = $db->prepare("SELECT id, name, color FROM Cars WHERE color = ?");

    // execute/run the statement. 
    $stmt->execute(array($_POST['color']));

    // fetch the result. 
    $cars = $stmt->fetchAll(\PDO::FETCH_ASSOC); 
    var_dump($cars); 
 }

Now, as you can probably tell, I haven't used anything to escape/sanitize the value of $_POST["color"]. And this code is secure from myql-injection thanks to PDO and the power of prepared statements.

---

It is worth noting that you should pass a charset=utf8 as attribute, in your DSN as seen above, for security reasons, and always enable PDO to show errors in the form of exceptions.

PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION

so errors from you database queries won't reveal sensitive data like your directory structure, database username etc.

Last but not least, there are moments when you should not trust PDO 100%, and will be bound to take some extra measures to prevent sql injection, one of those cases is, if you are using an outdated versions of mysql [ mysql =< 5.3.6 ] as described in this answer

But, using prepared statements as shown above will always be safer, than using any of the functions that start with mysql_

Good reads

PDO Tutorial for MySQL Developers

Answer 2

There is none*! The object of PDO is that you don’t have to escape anything; you just send it as data. For example:

$query = $link->prepare('SELECT * FROM users WHERE username = :name LIMIT 1;');
$query->execute([':name' => $username]); # No need to escape it!

As opposed to:

$safe_username = mysql_real_escape_string($username);
mysql_query("SELECT * FROM users WHERE username = '$safe_username' LIMIT 1;");

_{* Well, there is one, as Michael Berkowski said! But there are better ways.}

Answer 3

$v = '"'.mysql_real_escape_string($v).'"'; 

is the equivalent of $v = $this->db->quote($v); be sure you have a PDO instance in $this->db so you can call the pdo method quote()

Answer 4

There is no need of mysql_real_escape_string in PDO.

PDO itself adjust special character in mysql query ,you only need to pass anonymous parameter and bind it run time.like this Suppose you have user table with attribute name,email and password and you have to insert into this use prepare statement like this you can pass name as => $name="Rajes'h ";

it should execute there is no need of equivalent of mysql_real_escape_string

$stmt="INSERT into user(name,email,password) VALUES(:name,:email,:password)";
try{
   $pstmt=$dbh->prepare($stmt);//$dbh database handler for executing mysql query
   $pstmt->bindParam(':name',$name,PDO::PARAM_STR);
   $pstmt->bindParam(':email',$email,PDO::PARAM_STR);
   $pstmt->bindParam(':password',$password,PDO::PARAM_STR);
   $status=$pstmt->execute();
   if($status){
    //next line of code 
   }


}catch(PDOException $pdo){
     echo $pdo->getMessage();
}

Answer 5

The simplest solution I've found for porting to PDO is the replacement for mysql_real_escape_string() given at https://www.php.net/manual/en/mysqli.real-escape-string.php#121402. This is by no means perfect, but it gets legacy code running with PDO quickly.

@samayo pointed out that PDO::quote() is similar but not equivalent to mysql_real_escape_string(), and I thought it might be preferred to a self-maintained escape function, but because quote() adds quotes around the string it is not a drop in replacement for mysql_real_escape_string(); using it would require more extensive changes.

Answer 6

In response to a lot of people's comments on here, but I can't comment directly yet (not reached 50 points), there ARE ACTUALLY needs to use the $dbh->quote($value) EVEN when using PDO and they are perfectly justifiable reasons...

1. If you are looping through many records building a "BULK INSERT" command, (I usually restart on 1000 records) due to exploiting InnoDb tables in MySQL/Maria Db. Creating individual insert commands using prepared statements is neat, but highly inefficient when doing bulk tasks!
2. PDO can't yet deal with dynamic IN(...) structures, so when you are building a list of IN strings from a list of user variables, YOU WILL NEED TO $dbh->quote($value) each value in the list!

So yes, there is a need for $dbh->quote($value) when using PDO and is probably WHY the command is available in the first place.

PS, you still don't need to put quotes around the command, the $dbh->quote($value) command also does that for you.

Out.

Answer 7

If to answer the original question, then this is the PDO equivalent for mysql_real_escape_string:

function my_real_escape_string($value, $connection) {
    /* 
    // this fails on: value="hello'";
    return trim ($connection->quote($value), "'");
    */
    return substr($connection->quote($value), 1, -1);       
}

btw, the mysqli equivalent is:

function my_real_escape_string($value, $connection) {
    return mysqli_real_escape_string($connection, $value);
}