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?

Solution 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

Solution 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.}

Solution 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()

Solution 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();
}

View more solutions

Comments

[Peter Mortensen]

Similar: What is the equivalent of real_escape_string() for PDO?

[Michael Berkowski]

Technically there's PDO::quote() but this is the right answer and it isn't a direct equivalent of mysql_real_escape_string().

[Dharman]

Why won't you use double quotes for query? You wouldn't have to escape

[Ry-]

@Dharman: I like exaggerating difficulty with deprecated stuff =) Okay, okay, I changed it.

[Your Common Sense]

@PeeHaa埽 there is not. While PDO::quote() does the complete formatting, escaping and quoting, and thus can be used safely, mysql_real_escape_string does incomplete formatting, and shouldn't be used as a protection measure.

[Nitsan Baleli]

Alez's answer is working for me because it uses $conn->prepare rather than $conn->query (which gave me an error) and then executes.

[Ry-]

@balelinitsan: Thanks! Fixed now.

[tod]

What exactly do you want to show here? this question has already well accepted answer.

[Simo]

I wanted to show the equivalent PDO method to the function mysql_real_escape_string, in one instruction, wich is the method "quote()".

[andy]

Wow really your answer is just 1 security breach. There is no need to escape input anymore??? What about xss attacks... You should always sanitize user input..

[PeeHaa]

@andy What the hell are you talking about? This question is about escaping for sql. Not about password hashing, CSRF prevention, XSS prevention or fire hazard prevention

[andy]

@PeeHaa I understand, but I think it is misleading to say there is no more "need" te sanitize your input. Because you stil have to.. Maybe there is no more need for sql escaping, but there is still need for all the other attacks.

[Andz]

There absolutely IS something close to - but not exactly - equivalent to mysql_real_escape_string(), as other people here have mentioned. It is important to explain exactly why one should understand the philosophy of prepared statements, but there's no reason to LIE to people just to get them to adopt the correct practice. I found myself needing PDO::quote() for compatibility purposes, otherwise big portions of our codebase needed to be changed overnight.

[Ry-]

This isn’t a correct equivalent; try it with hello'.

[samayo]

@MichaelBerkowski I'm curious. Why is that the right answer?

[Michael Berkowski]

@samayo I don't know why I chose the words I did 4 years ago, but prepare()/execute() has always been the preferred method. PDO::quote() is hardly ever used in practice and isn't exactly equivalent to mysql_real_escape_string().

[Madara's Ghost]

@andy A note about sanitizing: You always sanitize when you generate a program from within another program, an SQL query is a program, an HTML document is a program, a JavaScript program is... well, you get it. Whenever you generate values to be used in another language, you need to sanitize those values to ensure that you don't hit that language's special characters. It's not even about security, it's about correctness. If my name is Madara O'connor, I'm not trying to attack you with an SQL injection, my name just has a quote in it.

[Douwe]

I have thousands of query's using a home grown mysql wrapper from way before pdo became standard. The wrapper uses the old mysql functions and now needs to be ported as part of an effort to move this codebase to php7. Prepared statements simply will not do because of typing issues. Why is it 'pedantic' to mention PDO::quote while it is the answer to the question being asked?

[samayo]

@Douwe What you are talking about is migration and/or integration related, whereas the answer and the question are focused on security. So the context is different here. OP is asking an alternative to mysql_real_escape_string as to a way to avoid SQL injection

[Dan W.]

@samayo What if you need to dynamically set the table name? I don't think you can do that with PDO?

[samayo]

@Dan W. Yes you can do it, otherwise the use for PDO would be severely limited. Use the same query as you would for mysql, just be careful not to use raw input to create a db/table dynamically. I am on mobile so I can't do much, but google "pdo create table"

[Dan W.]

@samayo That's for creating a table if I understand you correctly. But if I want to SELECT from a dynamic tablename it throws an error.

[samayo]

Have you checkes this answer? stackoverflow.com/a/182353/1640606 i remeber being able to select dynamically a table, but you have to build a string query first, then use that to select the table

[Your Common Sense]

Now it is much worse

[Dharman]

No. I disagree. You should never ever put data into SQL query. No matter what it is or how uncomfortable it is. PDO actually is making it much easier for you to do IN() clauses thanks to bind-in-execute.

[Andrew Foster]

It wouldn't be in the available function set if there was no need for it. So how would you perform your bulk insert using PDO? Care to show an example of the bind-in-execute with a variable length list of user IN elements?

[Dharman]

There are so many more functions, which have very limited use case in PHP. The problem is not that they exists, but because people abuse them, Your example is abusing it. Prepared statements are easier, safer and better than manually putting quoted data into SQL queries.

[Andrew Foster]

AND SLOWER. WAY SLOWER.

[Your Common Sense]

Just use prepared statements and you'd never need neither quoting or escap8ng

[Dharman]

One of the benefits of prepared statements is that they are actually faster. I do not know how you are using them, but even if you are preparing a new query each time, you should not see much of a performance impact.

[Andrew Foster]

The performance is on the Db. Doing 7000 individual inserts takes way longer than doing a bulk insert, so you have to build up your SQL string first.

[Your Common Sense]

"Way slower" is a bit of exaggeration. In case your innodb is configured in paranoid mode, just wrap your inserts in a transaction. That's the only case known to me that could justify such a definition

[Andrew Foster]

So if quote() should NEVER be used. Why is it in the function set? PS: Thanks for the IN() example.

[Dharman]

As I said PHP has functions which are almost never used. This is a very good example of it. Even the PHP manual suggest quote should not be used. Just forget this function even exists.