PDO Exception Questions - How to Catch Them

Question

I'm using PDO to re-write a website interface for a database. I used to use the mysql extension, but I had never bothered with error handling, and the few error handlers I had were basically copy-paste.

Now I'd like to do this right. However, I'm having issues catching the errors how I'd like (errors like "Duplicate Entry", "Null Value" etc in MySQL). How much of my statement needs to be in the try block? Should all of it be in there? I'm using an Include() to connect to my DB (which has its own error handling), so it's only the query execution which has errors in this code. I can't figure out why it's not catching an error when executing the following code:

try {
  $stmt = $db->prepare("INSERT INTO tbl_user (id, name, password, question, answer)    VALUES (NULL, :name, :password, :question, :answer)");
  $stmt->bindValue(":name", $_POST['name']);
  $stmt->bindValue(":password", $_POST['password']);
  $stmt->bindValue(":question", $_POST['question']);
  $stmt->bindValue(":answer", $_POST['answer']);
  $stmt->execute();
  echo "Successfully added the new user " . $_POST['name'];
} catch (PDOException $e) {
  echo "The user could not be added.<br>".$e->getMessage();
}

So my questions: does ALL OF THAT have to be in the try block? Can I just put the execute in the try block? It should catch the error Duplicate value "John" in key "name", but instead goes through with the success message. (When trying to add two "John" users). I checked in PHPMyAdmin; the index is unique and does throw the error as expected, just not using this code.

If you haven't already, you'll need to set `$db`'s `PDO::ATTR_ERRMODE` to `PDO::ERRMODE_EXCEPTION` so that it throws exceptions on errors. — FtDRbwLXw6, Jun 19 '12 at 14:14
You should check the documentation or the source code and see which functions throw the `PDOException`. Then you'll know which parts of code to put in the try block :) — AlexMorley-Finch, Jun 19 '12 at 14:15
I feel silly.. setting the `ATTR_ERRMODE` fixed it. So how much of the statement should be in the try block? The code might be reused (only maybe a few bindValue() statements would change, then the execute). Is there any benefit to having the entire statement in the try block, or is only the execute() necessary? — StuckAtWork, Jun 19 '12 at 14:20
**Never store plain text passwords!** Please use PHP's [built-in functions](http://jayblanchard.net/proper_password_hashing_with_PHP.html) to handle password security. If you're using a PHP version less than 5.5 you can use the `password_hash()` [compatibility pack](https://github.com/ircmaxell/password_compat). Make sure that you [don't escape passwords](http://stackoverflow.com/q/36628418/1011527) or use any other cleansing mechanism on them before hashing. Doing so *changes* the password and causes unnecessary additional coding. — Jay Blanchard, May 05 '16 at 15:45
@StuckAtWork check the php.net documentation to find out whether something throws exception or not, or just sometimes (depends on the ERRMODE) `bindValue()` itself does not emit exception, just returns boolean, `PDO::prepare(...)` will emit exception, but only if you have `PDO::ERRMODE_EXCEPTION` set .... Therefore the advantage is a code readability - you can have just one `try/catch` block :-) — jave.web, Jun 29 '16 at 19:44

score 20 · Accepted Answer · answered Jun 19 '12 at 14:20

20

You should look at the documentation. But If you dont find anything, you can add another catch :

<?php
try {
  $stmt = $db->prepare("INSERT INTO tbl_user (id, name, password, question, answer)    VALUES (NULL, :name, :password, :question, :answer)");
  $stmt->bindValue(":name", $_POST['name']);
  $stmt->bindValue(":password", $_POST['password']);
  $stmt->bindValue(":question", $_POST['question']);
  $stmt->bindValue(":answer", $_POST['answer']);
  $stmt->execute();
  echo "Successfully added the new user " . $_POST['name'];
} catch (PDOException $e) {
  echo "DataBase Error: The user could not be added.<br>".$e->getMessage();
} catch (Exception $e) {
  echo "General Error: The user could not be added.<br>".$e->getMessage();
}
?>

This must work because all exceptions of PHP plugins herits from the Exception native PHP class. (Since 5.0 if my memory is well).

answered Jun 19 '12 at 14:20

niconoe

1,100
1
11
25

1

I had looked around, but I guess I wrongly assumed that PDO threw exceptions by default. Turns out you need to do as @drrcknlsn said in the first comment. Neither version (`Exception NOR PDOException`) were throwing errors until it was changed. However, this is not a bad idea (there may be other errors which occur) – StuckAtWork Jun 19 '12 at 14:23
1

@StuckAtWork: ::bindValue or ::bindParam can fail if you send it objects or arrays. Don't forget that using $_POST directly don't prevent you from XSS injection when sending array here. (PS: I just write that comment here and not above because for an unknown reason, I can't add a comment on your asking-post ?_?) Sorry for that. – niconoe Jun 19 '12 at 14:24
1

First of all, use "isset" to be sure that your "name", "password"... are set. Then, you can use is_string(). You don't need some bad things like mysql_real_escape_string() since PDO do it very well, but be sure your bind param is a string, a numeric or a boolean, depending on what you want to bind. There are native PHP functions like is_string(), is_int(), is_numeric() or, is_boolean() to do that ;) – niconoe Jun 19 '12 at 14:30
Would it be sufficient to add the 3rd parameter to bindValue()? ex `bindValue(":name", $_POST['name'], PDO::PARAM_STR);`? – StuckAtWork Jun 19 '12 at 14:32
2

By not doing the isset, you will get some notice if your keys in $_POST didn't there. so isset is very important. The 3rd value is here to tell what type of value you want to bind but that's not a security. Immagine $_POST["foo"] = false ; `myTable`.`foo` is a BOOL ; and you use PDO::PARAM_STR. PDO will insert you "false" (string), and will may be interpreted as TRUE (bool). Be sure you values are on a good type and, if possible, cast them inteligently to the good type before binding them. Add the 3rd param is better for lisibility and trivial casting only :) – niconoe Jun 19 '12 at 14:40
Thanks a lot; I know there's a lot of documentation on this type of stuff but I find it's never very specific. You've laid it out quite nicely for my implementation. – StuckAtWork Jun 19 '12 at 14:45

score 14 · Answer 2 · edited Jun 20 '20 at 09:12

PDO Exception Questions - How to Catch Them

As a rule -

DO NOT catch them.

For example, your code here should be written this way

$stmt = $db->prepare("INSERT INTO tbl_user (id, name, password, question, answer) VALUES (NULL, :name, :password, :question, :answer)");
$stmt->bindValue(":name", $_POST['name']);
$stmt->bindValue(":password", $_POST['password']);
$stmt->bindValue(":question", $_POST['question']);
$stmt->bindValue(":answer", $_POST['answer']);
$stmt->execute();
echo "Successfully added the new user " . $_POST['name'];

without any try or catch calls. Because you have no particular scenario for handling an exception here (a simple echo is scarcely counts as a handling scenario).

Instead, let it bubble up to the application-wide error handler (don't be scared by the term, PHP already has a built-in one).

However, I'm having issues catching the errors how I'd like (errors like "Duplicate Entry", "Null Value" etc in MySQL).

Only in case if you have a certain scenario, you have to use try-catch operator, but you have to always check, whether the error you've got is one you expected. Otherwise an exception have to be re-thrown:

try {
    $pdo->prepare("INSERT INTO users VALUES (NULL,?,?,?,?)")->execute($data);
} catch (PDOException $e) {
    if ($e->getCode() == 1062) {
        // Take some action if there is a key constraint violation, i.e. duplicate name
    } else {
        throw $e;
    }
}

and of course (as it turned out to be the vere problem for this question), you have to set up PDO in exception mode, either in a constructor parameter of simply by adding the code

$db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

right after connect.

I believe `23000` is what `getCode()` would return in this particular example, populated in `$e->errorInfo[0]` hopefully for all drivers (tested MySQL and MS SQL Server). The `1062` code would be populated in `$e->errorInfo[1]` for MySQL and MS SQL Server populates `2627` in `$e->errorInfo[1]` — Jeff Puckett, Jul 24 '17 at 21:19
You're completely correct Jeff, getCode would return the sql state here which is as you said, 23000 — Shardj, Aug 01 '18 at 15:48
This is the correct answer. Doesn't throw the exception because it wasn't registered. — RichardW11, Sep 24 '20 at 19:39

score -3 · Answer 3 · edited Sep 09 '19 at 02:10

-3

<?php
    $stmt = $db->prepare("INSERT INTO tbl_user (id, name, password, question, answer) VALUES (NULL, :name, :password, :question, :answer)");

    $stmt->bindValue(":name", $_POST['name']);
    $stmt->bindValue(":password", $_POST['password']);
    $stmt->bindValue(":question", $_POST['question']);
    $stmt->bindValue(":answer", $_POST['answer']);
    $inserted = $stmt->execute();

    if($inserted)
        echo "Successfully added the new user " . $_POST['name'];
    else
        echo "Somethig get wrong";
?>

edited Sep 09 '19 at 02:10

Suit Boy Apps

3,005
8
36
54

answered Sep 08 '19 at 23:35

Sami FAREH

3
1

1

Please add some explanation on why your answer works http://stackoverflow.com/help/how-to-answer – Suit Boy Apps Sep 09 '19 at 00:05
1

@SuitBoyApps there cannot be any explanation as this post desn't answer the question at all. – Your Common Sense Sep 09 '19 at 09:47

PDO Exception Questions - How to Catch Them

3 Answers3

DO NOT catch them.

Linked