How to handle PDO connection errors properly?

Question

I try to handle or catch possible errors with PHP/MySQL for security reasons and would like to know if I'm doing it right. The first case: I use it as a function and call it always when I need a database connection. The second case: I am not sure how to handle it. I put all prepared statements in if conditions but that's pretty awkward. And what about $stmt->execute? This could also fail and to handle this also in an if condition can really get confusing. I hope there is a better way to go.

First:

function pdo () {
  try {
    $pdo = new PDO('mysql:host=localhost;dbname=dbname', 'user', 'pw');
  }
  catch(PDOException $e) {
    header("Location: handle_error.php");
    exit;
  }
  return ($pdo);
}

Second:

if ($stmt = $pdo->prepare("SELECT a FROM b WHERE c = :c")) {
  $stmt->execute(array(':c' => $c));
  $result = $stmt->fetch();
  echo 'All fine.';
}
else {
  echo 'Now we have a problem.';
}

Welcome yo Stack Overflow! I already have a first class answer, but the way you asked suggests this is rather off topic on Stack Overflow and your question is more suitable for https://codereview.stackexchange.com. Either kindly ask there or rephrase it to "How..." like @how to properly connect with PDO and handle errors" — Your Common Sense, Sep 03 '19 at 16:20

Dharman · Accepted Answer · 2019-09-04T22:11:31.013

Your current code has some flaws. Let me give you few pointers.

You do not need the function you have created, at least not in the current form. Every time you call this function it creates a new PDO object, which can hinder your script's performance. Ideally you would want to have only one connection throughout the execution of your whole script.
When creating new PDO connection you need to remember 3 things: to set proper connection charset, enable error reporting, and disable emulated prepares.
- Proper charset is important. Without it your data could get corrupted or even leave you vulnerable to SQL injection. The recommended charset is utf8mb4.
- Enabling error reporting saves you the trouble of manually checking every single function call for failures. You just need to tell PHP to trigger exceptions when an error occurs and find a suitable way of logging the errors on your server (read more: My PDO Statement doesn't work)
- Emulated prepares are enabled by default, but the truth is you are better off without them. If your database supports native prepared statements, and most do, then use them instead.
The full code should look something like this:
```
$options = [
    \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
    \PDO::ATTR_EMULATE_PREPARES => false,
];
$pdo = new \PDO('mysql:host=localhost;dbname=dbname;charset=utf8mb4', 'user', 'pw', $options);
```
Don't catch the exceptions unless you know what to do with them and are positively sure you need to do so. Exceptions are best left be; let PHP handle them together with the other exceptions/errors/warnings. After all why make an exception just for PHP exceptions? All PHP errors should be handled the same. Whatever you do never print (including die, exit, echo, var_dump) the error message manually on the screen. This is a huge security issue if such code ever makes its way into production.

If your PDO connection is set to throw exceptions on errors, you never need to use if statements to check return code of prepare() or execute().

Thank you for explaining the pdo connection in detail. Now I dont use the connenction function any more, instead I include_once a separate database_setting.php file with the correct information on the top of every php file, that needs it. I hope this is correct so far? — Nino, Sep 05 '19 at 06:48

How to handle PDO connection errors properly?

1 Answers1