Q . what is the PDO equivalent of mysql_real_escape_string?

Stan Lee. asked, Mar 04 ' 2017

I am using mysql_real_escape_string() function with PDO but its not working and the field goes empty in the database.

Any idea why this is happening.

  • PDO
  • mysql_real_escape_string

1 Answers

Well No, there is none!

Technically there is PDO::quote() but it is hardly ever used in practice and isn't exactly equivalent to mysql_real_escape_string().

That's right! If you are already using PDO the proper way with prepare/execute then it's enough, because, using prepared statements, is enough to protect you from mysql injection.

Example:

To understand how simple all of this is, take a look at the following basic code.

try {
  # First let us connect to our database 
  $db = new \PDO("mysql:host=localhost;dbname=xx;charset=utf8mb4", "xx", "xx", []); 
 } catch(\PDOException $e){
   echo "Error connecting to mysql: ". $e->getMessage();
 }

# And pass optional (but important) PDO attributes
$db->setAttribute(
   PDO::ATTR_EMULATE_PREPARES => false, 
   PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
 ); 

And now, once you are connected to your database, everything becomes simple.

if($_POST && isset($_POST['name'])){
    $stmt = $db->prepare("SELECT id, name, age FROM users WHERE name = ?");
    $rows = $stmt->execute(array($_POST['name']));
    var_dump($rows); 
 }

Now, as you can see I haven't used anything to escape/sanitize the $_POST["name"] array. And this is secure from myql-injection thanks to 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_

Leave a Ansewer