Why is PDO better for escaping MySQL queries/querystrings than mysql_real_escape_string?

by

As the current answers go into details while your question is more aimed at a general overview, I’ll give it a try:

The PDO classes aim to encapsulate all the functionality needed to interact with a database. They do this by defining ‘methods’ (OO parlor for functions) and ‘properties’ (OO parlor for variables). You’d use them as a complete replacement for all the ‘standard’ functions you are using now for talking to a database.

So instead of calling a series of the ‘mysql_doSomething()’ functions, storing their results in your own variables, you would ‘instantiate’ an object from the PDO class (‘class’ = abstract definition, ‘object’ = concrete, usable instance of a class) and call methods on that object to do the same.

As an example, without PDO, you’d do something like this:

// Get a db connection
$connection = mysql_connect('someHost/someDB', 'userName', 'password');
// Prepare a query
$query = "SELECT * FROM someTable WHERE something = " . mysql_real_escape_string($comparison) . "'";
// Issue a query
$db_result = mysql_query($query);
// Fetch the results
$results = array();
while ($row = mysql_fetch_array($db_result)) {
  $results[] = $row;
}

while this would be the equivalent using PDO:

// Instantiate new PDO object (will create connection on the fly)
$db = new PDO('mysql:dbname=someDB;host=someHost');
// Prepare a query (will escape on the fly)
$statement = $db->prepare('SELECT * FROM someTable WHERE something = :comparison');
// $statement is now a PDOStatement object, with its own methods to use it, e.g.
// execute the query, passing in the parameters to replace
$statement->execute(array(':comparison' => $comparison));
// fetch results as array
$results = $statement->fetchAll();

So on first glance, there is not much difference, except in syntax. But the PDO version has some advantages, the biggest one being database independence:

If you need to talk to a PostgreSQL database instead, you’d only change mysql:to pgsql: in the instantiating call new PDO(). With the old method, you’d have to go through all your code, replacing all ‘mysql_doSomething()’ functions with their ‘pg_doSomthing()’ counterpart (always checking for potential differences in parameter handling). The same would be the case for many other supported database engines.

So to get back to your question, PDO basically just gives you a different way to achieve the same things, while offering some shortcuts/improvements/advantages. For example, escaping would happen automatically in the proper way needed for the database engine you are using. Also parameter substitution (prevents SQL Injections, not shown in example) is much easier, making it less error prone.

You should read up on some OOP basics to get an idea of other advantages.

More Related Contents:

  1. mysql_escape_string VS mysql_real_escape_string
  2. PHP PDO and Mysql [closed]
  3. How to properly set up a PDO connection
  4. PHP PDO prepared statements
  5. PDO MySQL: Use PDO::ATTR_EMULATE_PREPARES or not?
  6. PDO Prepared Inserts multiple rows in single query
  7. Why I am getting Cannot pass parameter 2 by reference error when I am using bindParam with a constant value?
  8. mysql PDO how to bind LIKE
  9. Connect PHP to MSSQL via PDO ODBC
  10. PDO closing connection
  11. Unable to retrieve UTF-8 accented characters from Access via PDO_ODBC
  12. Fetching single row, single column with PDO
  13. How to connect to mssql using pdo through PHP and Linux?
  14. How do I loop through a MySQL query via PDO in PHP?
  15. Binding params for PDO statement inside a loop
  16. Escape string to use in mail()
  17. PDO fetchAll array to one dimensional
  18. PDO try-catch usage in functions
  19. How do I enable php to work with postgresql?
  20. Call to a member function fetch() on boolean [duplicate]
  21. How to prevent PDO from interpreting a question mark as a placeholder?
  22. PHP PDO returning single row
  23. Laravel 5.4 on PHP 7.0: PDO Exception – Could not find driver (MySQL)
  24. Using LIKE in bindParam for a MySQL PDO Query [duplicate]
  25. How to handle PDO exceptions [duplicate]
  26. php artisan migrate throwing [PDO Exception] Could not find driver – Using Laravel
  27. Get Last Executed Query in PHP PDO
  28. PHP mysql_real_escape_string() -> stripslashes() leaving multiple slashes
  29. PDO and MySQL Fulltext searches
  30. Difference between PDO->query() and PDO->exec()

Leave a Comment