0

Here is an invalid SQL and I m expecting an error, but the PDO error seems always 00000, what did I do wrong?

<?php
run('select now()');
run('pls give me an error');

function run($sql) {
    $pdo = new PDO('mysql:host=localhost;db=mydb', $user, $pass);
    echo $sql . "<br>";
    $sth = $pdo->prepare($sql);
    $sth->execute();
    $row = $sth->fetch(PDO::FETCH_ASSOC);
    print_r($row);
    print_r($pdo->errorInfo());
}

And here is the result:

select now()
Array
(
    [now()] => 2017-10-03 02:58:09
)
Array
(
    [0] => 00000
    [1] => 
    [2] => 
)
pls give me an error
Array
(
    [0] => 00000
    [1] => 
    [2] => 
)

But I have another page running against the same db and get this error:

Err 1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'please give me an error' at line 1

updated

The other page is able to produce error is actually using the following:

$sth = $pdo->query($sql);
print_r($pdo->errorInfo());
Masivuye Cokile
  • 4,754
  • 3
  • 19
  • 34
SIDU
  • 2,258
  • 1
  • 12
  • 23

2 Answers2

1

For the syntactically or any other way incorrect prepared statements to throw you need to disable prepared statements emulation:

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

By default PDO is set up to emulate those, which honestly does not make much sense.

With emulated prepares disabled PDO::prepare() method creates a temporary server-side object that holds a prepared statement then executes it.

Additionally you may want to enable PDO exceptions, that way it's harder to not handle unexpected query failures:

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

References:

zerkms
  • 249,484
  • 69
  • 436
  • 539
  • well, if doing as per your way: if there is error on prepare(), i need to catch error on execute() -- i never had this situation before, and imagin i m running 2 pages on same db, i imagine db settings are all same DEFAULT. why one page always no error ? – SIDU Oct 03 '17 at 03:12
  • @SIDU I'm not sure I'm following your question. It's a PDO configuration, not database's one. – zerkms Oct 03 '17 at 03:13
  • Thanks zerkms! Just wonder if some default PDO setting was changed last week? – SIDU Oct 03 '17 at 04:31
  • Come on, you missed the question. The rant on the emulation is irrelevant here. – Your Common Sense Oct 03 '17 at 04:48
  • @SIDU if you changed something - something could have changed. If you did not change anything - nothing is supposed to change. – zerkms Oct 03 '17 at 06:35
0

I've no idea why errorInfo() sometimes doesn't work but I confirm he behavior.

Given that setting error mode to exceptions always works and given that exceptions are much more useful than manual error checking, this function is useless anyway.

So, change your function this way 

function run($pdo, $sql, $params = null) {
    $sth = $pdo->prepare($sql);
    $sth->execute($params);
    return $stmt;
}

$pdo = new PDO('mysql:host=localhost;db=mydb', $user, $pass);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

run($pdo, 'select now()');
run($pdo, 'pls give me an error');

and have your error message first class

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • The "strange" behavior comes from the fact that `PDOStatement::fetch` does not adhere to the SQL-92 standard. The [note of terry](http://php.net/manual/en/pdostatement.fetch.php#62536) is the only place on web where I found this piece of info. It's in regard of the empty recordset, though. Note that it should be "_...and returns **FALSE** to the caller_". An important piece: "_This also prevents the exception mechanism from firing._". –  Oct 04 '17 at 04:33
  • Anyway this behavior is observable only in the case of `fetch()`, not in the other cases, like `fetchAll()`, `query()`, etc. I ran some tests on `fetch()` two weeks ago and, as I recall, in some test-cases, the error code remained indeed - unexpectedly - `00000`. –  Oct 04 '17 at 04:33