Do we need to test the return value of bind_param when building prepared statements in PHP?

Question

Linked question is here.

I have upgraded the code in the linked question to use a prepared statement.

I now have:

$stmt = $conn->prepare("INSERT INTO `workbook-data` (`workbook-language`, `gui-language`, `foreign-language-group-mode`, `version`) VALUES (?, ?, ?, ?)");
$stmt->bind_param('ssis', $mwblang, $guilang, $flgmode, $version);

$mwblang = mysqli_real_escape_string($conn, $_GET['mwblang']);
$guilang = mysqli_real_escape_string($conn, $_GET['guilang']);
$flgmode = mysqli_real_escape_string($conn, $_GET['flg']);
$version = mysqli_real_escape_string($conn, $_GET['version'] ?? '210061');

if ($stmt->execute()) {
  echo "<br>" . "New record created successfully";
} else {
  echo "Error: " .  mysqli_error($conn);
}

$stmt->close();

According to the documentation for bind_param it states this about the return value:

Returns true on success or false on failure.

In these official examples they don't seem to do any error checking for failure. Should be we testing the return value? I have done so for execute() but I am not sure how much checking is needed.

Btw.: You shouldn't use `mysqli_real_escape_string()` anymore, the prepared statements work without them. — Progman, Apr 04 '21 at 17:53
FYI: *don't* `mysqli_real_escape_string` if you’re using parametrized statements. It’s redundant and only messes up your data. — deceze, Apr 04 '21 at 17:54
@AndrewTruckle You might not need to check for errors manually when you let exceptions throw instead (and catch them of course), see https://stackoverflow.com/questions/14578243/turning-query-errors-to-exceptions-in-mysqli — Progman, Apr 04 '21 at 18:12

Dharman · Accepted Answer · 2021-04-04T19:41:58.127

You should not be checking for the return value of any of mysqli functions. This is just pointless. When you enable proper mysqli error reporting you will be automatically informed of all the errors.

If you decide to keep the error reporting silenced for some strange reason, then you must check every single mysqli function call including bind_param(). If you do check for the return value, make sure to never display the error message on the screen. Log the errors to a file in a safe place.

So, your code should look like this:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$conn = new mysqli('localhost', 'user', 'password', 'test');
$conn->set_charset('utf8mb4'); // always set the charset

// ...

$stmt = $conn->prepare("INSERT INTO `workbook-data` (`workbook-language`, `gui-language`, `foreign-language-group-mode`, `version`) VALUES (?, ?, ?, ?)");
$stmt->bind_param('ssis', $mwblang, $guilang, $flgmode, $version);
$stmt->execute();

or like this:

mysqli_report(MYSQLI_REPORT_OFF);
$conn = new mysqli('localhost', 'user', 'password', 'test');
if ($conn->connect_errno) {
    error_log($conn->connect_error);
}
if (false === $conn->set_charset('utf8mb4')) {
    error_log($conn->error);
}

// ...

if (false === ($stmt = $conn->prepare("INSERT INTO `workbook-data` (`workbook-language`, `gui-language`, `foreign-language-group-mode`, `version`) VALUES (?, ?, ?, ?)"))) {
    error_log($conn->error);
}
if (false === $stmt->bind_param('ssis', $mwblang, $guilang, $flgmode, $version)) {
    error_log($stmt->error);
}
if (false === $stmt->execute()) {
    error_log($stmt->error);
}

Note how there are 3 different ways of reading the error message depending on which function you called. Manual error checking is much more verbose.

Do we need to test the return value of bind_param when building prepared statements in PHP?

1 Answers1