Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Reference — frequently asked questions about PDO

Tags:

php

pdo

People also ask

What is the purpose of a PDO?

PDO provides a data-access abstraction layer, which means that, regardless of which database you're using, you use the same functions to issue queries and fetch data. PDO does not provide a database abstraction; it doesn't rewrite SQL or emulate missing features.

What is PDO prepared statement?

In layman's terms, PDO prepared statements work like this: Prepare an SQL query with empty values as placeholders with either a question mark or a variable name with a colon preceding it for each value. Bind values or variables to the placeholders. Execute query simultaneously.

How can I check my PDO statement?

The easy and best way is to use SET global general_log = 1; and SET global log_output = 'table'; then simply query from the mysql database as SELECT * FROM mysql. general_log . It shows the prepare statement and and the actual query executed on the database.


PDO query fails but I can't see any errors. How to get an error message from PDO?

To be able to see database errors, one have to set PDO errmode to exceptions. Exceptions are better than regular errors in many ways: they always contains a stack trace, they can be caught using try..catch or handled using dedicated error handler. And even unhandled, they act as regular PHP errors providing all the important information, following site-wide error reporting settings.

Note that setting this mode as a connection option will let PDO throw exceptions on connection errors too, which is very important.
So, here is an example for creating a PDO connection right way:

$dsn = "mysql:host=$host;dbname=$db;charset=utf8";
$opt = array(
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    // other options 
);
$pdo = new PDO($dsn, $user, $pass, $opt);

Connecting this way, you will be always notified of all database errors, occurred during query execution. Note that you have to be able to see PHP errors in general. On a live site you have to peek into error logs, so, settings have to be

error_reporting(E_ALL);
ini_set('display_errors',0);
ini_set('log_errors',1);

while on a local development server it's ok to make errors on screen:

error_reporting(E_ALL);
ini_set('display_errors',1);

and of course you should never ever use error suppression operator (@) in front of your PDO statements.

Also, due to many bad examples telling you to wrap every PDO statement into try..catch block, I have to make a distinct note:

DO NOT use try..catch operator just to echo an error message. Uncaught exception is already excellent for this purpose, as it will act just the same way as other PHP errors - so, you can define the behavior using site-wide settings - so, you will have your error message without this useless code. While unconditionally echoed error message may reveal some sensitive information to a potential attacker, yet confuse a honest visitor.

  • A custom exception handler could be added later, but not required. Especially for new users, it is recommended to use unhandled exceptions, as they are extremely informative, helpful and secure.
  • Use try..catch only if you are going to handle the error itself - say, to rollback a transaction.

PDO prepared statement causes an error in LIMIT clause

For compatibility purposes, PDO will just emulate prepared statements by substituting placeholders with actual data, instead of sending them to the server separately, unless told otherwise. And with "lazy" binding (using array in execute()), PDO will treat every parameter as a string. As a result, the prepared LIMIT ?,? query becomes LIMIT '10', '10' which is invalid syntax that causes query to fail.

This issue can be solved either

  • by turning emulation mode off (as MySQL can sort all placeholders properly):

    $conn->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );
    
  • by binding and setting proper type (PDO::PARAM_INT) explicitly:

    $stm = $pdo->prepare('SELECT * FROM table LIMIT ?, ?');
    $stm->bindValue(1, $limit_from,PDO::PARAM_INT);
    $stm->bindValue(2, $per_page,PDO::PARAM_INT);
    $stm->execute();
    $data = $stm->fetchAll();
    

How can I use prepared statements with LIKE operator?

Prepared statement can represent complete data literal only. Not a part of literal, nor a complex expression, nor identifier. But either string or number only. So, a very common pitfall is a query like this:

$sql = "SELECT * FROM t WHERE column LIKE '%?%'";

If you ponder on this query a bit, you'd understand that being inside of single quotes, a question mark become a literal question mark, without any special meaning for the prepared statements.

So, one have to send complete string literal using prepared statement. There are 2 possible ways:

  • either prepare FULL expression first:

    $name = "%$name%";
    $stm  = $pdo->prepare("SELECT * FROM table WHERE name LIKE ?");
    $stm->execute(array($name));
    $data = $stm->fetchAll();
    
  • or use a concatenation inside the query

    $sql = "SELECT * FROM t WHERE column LIKE concat('%',?,'%')";
    

though the latter seems too bloated.