Questions about shifting from mysql to PDO

I have recently decided to switch all my current plain mysql queries performed with php mysql_query to PDO style queries to improve performance, portability and security. I just have some quick questions for any experts in this database interaction tool

1. Will it prevent injection if all statements are prepared? (I noticed on php.net it wrote ‘however, if other portions of the query are being built up with unescaped input, SQL injection is still possible‘ I was not exactly sure what this meant). Does this just mean that if all variables are run through a prepare function it is safe, and if some are directly inserted then it is not?

2. Currently I have a connection at the top of my page and queries performed during the rest of the page. I took a look at PDO in more detail and noticed that there is a try and catch procedure for every query involving a connection and the closing of that connection. Is there a straightforward way to connecting and then reusing that connection without having to put everything in a try or constantly repeat the procedure by connecting, querying and closing?

3. Can anyone briefly explain in layman’s terms what purpose a set_exception_handler serves?

I appreciate any advice from any more experienced individuals.

Answer

1. There’s no silver bullet of SQL injection prevention. One can have an unsafe query even though you prepare it.

   $sql = "SELECT * FROM MyTable WHERE id = " . $_GET["id"];
   $stmt = $pdo->prepare($sql);
   

   See? Prepare just takes a string and prepares it as an SQL query. You can still interpolate unsafe content into the string before you prepare it. The prepare only sees a string, it doesn’t know whether you wrote it literally or if parts of the string came from untrustworthy sources.

   You can use a parameter placeholder in the SQL query and then when you call execute() on the prepared statement, you supply the dynamic value. But you can use a parameter placeholder only in place of a literal value in an SQL expression — other types of dynamic content in a query can’t be parameterized. See my presentation SQL Injection Myths and Fallacies for examples and lots of other info about SQL injection.

2. I don’t put every PDO call in a try block. I write a class to encapsulate data access for some logically cohesive portion of my app. When I call that class, I wrap the call in a try block. If anything goes wrong in one of potentially many database access operations within that class, I catch it and deal with it.

3. You can use set_exception_handler() in lieu of a catch block. If an exception occurs in your app, but you don’t catch it and it bubbles all the way up the stack until it would have aborted the script, this function is called. Imagine your whole PHP script is in one top-level try block, and you’re declaring code that would go in the corresponding catch block.

   I never use set_exception_handler(). After the function runs, your script halts execution anyway, so there’s no opportunity to re-try the operation that spawned the exception. Also it operates at the top-level scope, so you lose the context of exception. The only thing you can do at that point is pretty-print the exception message and bail out. I prefer to handle exceptions closer to their origin, so I can add some information about the context of the exception, or do some other things before the PHP script halts.

---

Re your comments:

You shouldn’t use mysql_real_escape_string() for table names or column names, because the rules for quoting identifiers are different from the rules for quoting literal string values. Just don’t interpolate input from an external or untrusted source into your SQL query.

I wrote a code example in my presentation using an associative array so that if user input matches a known value, it uses that as a key in the associative array to look up the legitimate name of a table (or column, in my example). This means you don’t have to use any escaping/quoting function, because you don’t interpolate untrusted content into your SQL query. You only interpolate values that you have pre-defined in your associative array.

Regarding exceptions, what I mean is this (at a high level):

$domainObject = new MyDomain();

try {
  $domainObject->create_report($formInput);
} catch (PDOException $e) {
  // Report error politely so the user knows what happened
  // and what they can do to fix it.
}

The work inside create_report() is complex and probably involves multiple SQL queries, any of which might go wrong in multiple ways. You don’t necessarily need to catch exceptions for every SQL operation inside that function, you could just catch any and all exceptions that pop out of the function, and deal with them in one place, in the code that calls create_report().

Also, you probably don’t want to just spew the verbatim exception message at them, since they won’t know what to make of that.