Help - PDO Frequently Asked Questions

Question

What is it?

This is a list of frequently asked questions regarding PHP data objects.

Why is this?

Since PDO has some features unknown to ordinary PHP users, questions regarding prepared statements and error handling in PDOs are quite common. Thus, it is simply a place where they can all be found.

What should I do here?

If your question was closed with this list, please find your question below and apply the correction to your code. It is also a good idea to take a brief look at other issues to prepare for other common traps.

List

• PDO request error, but I do not see any errors. How to get error message from PDO?
• How to use prepared statements using the LIKE statement?
• How to create a prepared statement for an IN () statement?
• Can I use a prepared PDO statement to bind an identifier (table or field name) or syntax keyword?
• Report prepared by PDO causes error in LIMIT statement

see also

• Link - what does this symbol mean in PHP?
• Link - What does this error mean in PHP?
• Link - what is a variable scope, what variables are available from where and what are <undefined variable "errors?

Answer 1

PDO request error, but I do not see any errors. How to get error message from PDO?

To be able to see database errors, you need to set PDO errmode to exceptions. Exceptions are better than ordinary errors in different ways: they always contain a stack trace, they can be caught using try..catch or processed using a special error handler. And even unprocessed, they act like regular PHP errors, providing all the important information by following the error reporting settings on the site.

Please note that setting this mode as the connection parameter will allow PDO to exclude exceptions from connection errors, which is very important.
So, here is an example to properly create a PDO connection:

$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); 

Thus, you will always be notified of all database errors that occur during query execution. Note that you should be able to see PHP errors in general. On a real site, you need to look at the error logs, so the settings should be

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

while on the local development server it is ok to make errors on the screen:

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

and of course, you should never use the error suppression operator ( @ ) before your PDO statements.

In addition, due to many bad examples telling about wrapping each PDO statement in a try..catch block, I have to make a separate note:

DO NOT use the try..catch statement to echo the error message. An exception for exceptions is already great for this purpose, because it will act just like other PHP errors - so you can determine the behavior using the site settings - so you will get an error message without this useless code. While an unconditional error echo may reveal some sensitive information to a potential attacker, but confuse an honest visitor,

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

Answer 2

PDO report throws error in LIMIT clause

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

This problem can be solved either

• disabling emulation mode (since MySQL can sort all placeholders correctly):

   $conn->setAttribute( PDO::ATTR_EMULATE_PREPARES, false ); 

• by binding and setting the correct 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(); 

Answer 3

How can I use prepared statements using the LIKE statement?

A prepared statement can represent a complete data literal . Not part of a literal, non-complex expression or identifier. But only a string or a number only . So, a very common mistake is this request:

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

If you ponder this question a little, you will realize that, being inside single quotes, the question mark becomes a literal question mark, without any special meaning for prepared statements.

So, you need to send the full string literal using a prepared statement. Two ways are possible:

• first create a FULL expression:

   $name = "%$name%"; $stm = $pdo->prepare("SELECT * FROM table WHERE name LIKE ?"); $stm->execute(array($name)); $data = $stm->fetchAll(); 

• or use concatenation inside the query

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

although the latter seems too bloated.