10

In my code, I execute several valid SQL statements. But one of statement was invalid ($mysqli->prepare returns false), but no error code was returned ($mysqli->error returns false).

Here an example of the code:

$mysqli = new mysqli('host', 'user', 'password', 'database');

// First query (valid)
if(($oStatement = $mysqli->prepare('SELECT column FROM table;')) === false)
  throw new Exception('Error in statement: ' . $mysqli->error);

// ...

// Second query (invalid)
if(($oStatement = $mysqli->prepare('SELECT column_which_doesnt_exist FROM table;')) === false)
  throw new Exception('Error in statement: ' . $mysqli->error);

Why doesn't it return the error message?

David Gausmann
  • 1,570
  • 16
  • 20

2 Answers2

9

I've spent a while to find the error. I didn't find something here, so I post this Q&A-Answer:

$mysqli->error returned an empty string, because the destructor of reused variable $oStatement had reset the error text.

In the error case the second $mysqli->prepare returned false and has set the error text. Then the return value (false) will be assigned to $oStatement. $oStatement contains a mysqli statement object, whose destructor will be called as part of the assignment. The destructor is called without any errors and resets the error text.

The correct solution is either to use different variables for each statement or to reset the statement variable before each assignment:

$mysqli = new mysqli('host', 'user', 'password', 'database');

// First query (valid)
$oStatement = false;
if(($oStatement = $mysqli->prepare('SELECT column FROM table;')) === false)
  throw new Exception('Error in statement: ' . $mysqli->error);

// ...

// Second query (invalid)
$oStatement = false;
if(($oStatement = $mysqli->prepare('SELECT column_which_doesnt_exist FROM table;')) === false)
  throw new Exception('Error in statement: ' . $mysqli->error);

With this solution the error text is valid and can be thrown.

David Gausmann
  • 1,570
  • 16
  • 20
  • Interesting finding, thank you for sharing. JFYI http://stackoverflow.com/questions/18457821/how-to-make-mysqli-throw-exceptions-using-mysqli-report-strict – Your Common Sense Nov 21 '16 at 12:19
3

Thank you for sharing, that's indeed a very interesting behavior.

Yet, for clarity, there is a better way:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli('host', 'user', 'password', 'database');

// First query (valid)
$oStatement = $mysqli->prepare('SELECT column FROM table;');

// ...

// Second query (invalid)
$oStatement = $mysqli->prepare('SELECT column_which_doesnt_exist FROM table;');

the first line here tells mysqli to throw exceptions by itself, so you are getting both an exception and a code that is much, much cleaner

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345