What is the PDO equivalent of function mysql_real_escape_string?

by

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

More Related Contents:

  1. how safe are PDO prepared statements
  2. Insert form in MySQL with PDO [duplicate]
  3. Undefined index $_POST
  4. How to check if a row exists in MySQL? (i.e. check if username or email exists in MySQL)
  5. Are PDO prepared statements sufficient to prevent SQL injection?
  6. Reference: What is a perfect code sample using the MySQL extension? [closed]
  7. Getting raw SQL query string from PDO prepared statements
  8. How do I set ORDER BY params using prepared PDO statement?
  9. “Invalid parameter number: parameter was not defined” Inserting data
  10. Having issue with matching rows in the database using PDO
  11. How can I properly use a PDO object for a parameterized SELECT query
  12. How do I return integer and numeric columns from MySQL as integers and numerics in PHP?
  13. PDO with INSERT INTO through prepared statements [closed]
  14. PDO::fetchAll vs. PDO::fetch in a loop
  15. Real escape string and PDO [duplicate]
  16. SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax — PHP — PDO [duplicate]
  17. PDO Connection Test
  18. PHP PDO vs normal mysql_connect
  19. Can I create a database using PDO in PHP?
  20. MySQL Prepared Statements
  21. PHP PDO and MySQLi [duplicate]
  22. PDO fetch one column from table into 1-dimensional array
  23. Getting a PHP PDO connection from a mysql_connect()?
  24. PDO error: SQLSTATE[HY000]: General error: 2031
  25. Use of PDO in classes [duplicate]
  26. Check if row exists in the database before inserting
  27. PHP/PDO/MySQL: inserting into MEDIUMBLOB stores bad data
  28. PDO bindParam into one statement?
  29. Is mysql_real_escape_string enough to Anti SQL Injection?
  30. Installing PDO driver on MySQL Linux server

Leave a Comment