PHP MySQLi Prepared Statements

Posted: 22nd January 2018 by Jab in Web Design
Tags: , ,
0

How SQL Injection Works

The following iconic comic, known as Bobby Tables, is an excellent portrayal of how an SQL injection attack might work. All credit for the image goes to this site for this classic piece of rhetoric.

Bobby Tables

Now that we’re done with the theory, let’s get to practice. Before I start, if you’re wondering exactly how the “Bobby Tables Attack” works, check out this explanation.

How MySQLi Prepared Statements Work

In plain English, this is how MySQLi prepared statements work in PHP:

  1. Prepare an SQL query with empty values as placeholders (with a question mark for each value).
  2. Bind variables to the placeholders by stating each variable, along with its type.
  3. Execute query.

The four variable types allowed:

  • i – Integer
  • d – Double
  • s – String
  • b – Blob

Creating a New MySQLi Connection

Creating a new MySQLi is pretty simple. I suggest naming a file called mysqli_connect.php and place this file outside of your root directly (html, public_html) so your credentials are secure. We’ll also be using exception handling, by utilizing mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT). This might look weird to you, especially if you’ve never used a bitwise operator before. But all it’s doing is reporting all errors, while converting them to exceptions, using the mysqli_sql_exception class.

$mysqli = new mysqli("localhost", "username", "password", "databaseName");
if($mysqli->connect_error) {
  exit('Error connecting to database'); //Should be a message a typical user could understand in production
}
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli->set_charset("utf8mb4");

Insert, Update and Delete

Inserting, updating and deleting have an identical syntax, so they will be combined.

Insert

$stmt = $mysqli->prepare("INSERT INTO myTable (name, age) VALUES (?, ?)");
$stmt->bind_param("si", $_POST['name'], $_POST['age']);
$stmt->execute();
$stmt->close();

Update

$stmt = $mysqli->prepare("UPDATE myTable SET name = ? WHERE id = ?");
$stmt->bind_param("si", $_POST['name'], $_SESSION['id']);
$stmt->execute();
$stmt->close();

Delete

$stmt = $mysqli->prepare("DELETE FROM myTable WHERE id = ?");
$stmt->bind_param("i", $_SESSION['id']);
$stmt->execute();
$stmt->close();

Get Number of Affected Rows

You may also want to check the status of a row you inserted, updated or deleted. Here’s how you would it if you’re updating a row.

$stmt = $mysqli->prepare("UPDATE myTable SET name = ?");
$stmt->bind_param("si", $_POST['name'], $_POST['age']);
$stmt->execute();
if($stmt->affected_rows === 0) exit('No rows updated');
$stmt->close();

In this case, we checked to see if any rows got updated. For reference, here’s the usage for mysqli::$affected_rows return values.

-1 – query returned an error; redundant if there is already error handling for execute()

0 – no records updated on UPDATE, no rows matched the WHERE clause or no query has been executed

Greater than 0 – returns number of rows affected; comparable to mysqli_result::$num_rows for SELECT

Get Rows Matched

A common problem with $mysqli->affectedRows is that it makes it impossible to know why it returned zero on an UPDATE. This is due to the fact that it prints the amount of rows changed, so it makes ambiguous if you update your value(s) with the same data.

An awesome feature that is unique to MySQLi, and doesn’t exist in PDO, is the ability to get more info about a query. You can technically achieve it in PDO, but it can only be done in the connection, therefore you can’t choose.

$stmt = $mysqli->prepare("UPDATE myTable SET name = ?");
$stmt->bind_param("si", $_POST['name'], $_POST['age']);
$stmt->execute();
$stmt->close();
echo $mysqli->info;

This will print:

Rows matched: 1 Changed: 0 Warnings: 0

I find this to be a rather imprudent implementation, as it’s extremely inelegant to use it as is. Luckily we can change that, by converting it to an associative array. All credit goes do this helpful commenter on the PHP docs. While using mysqli->info for UPDATE is by far its most common use case, it can be used for some other query types as well.

preg_match_all('/(\S[^:]+): (\d+)/', $mysqli->info, $matches); 
$infoArr = array_combine ($matches[1], $matches[2]);
var_export($infoArr);

Now this will output an array.

['Rows matched' => '1', 'Changed' => '0', 'Warnings' => '0']

Get Latest Primary Key Inserted

$stmt = $mysqli->prepare("INSERT INTO myTable (name, age) VALUES (?, ?)");
$stmt->bind_param("si", $_POST['name'], $_POST['age']);
$stmt->execute();
echo $mysqli->insert_id;
$stmt->close();

Check if Duplicate Entry

This is useful if you were to create a unique constraint on a table, so duplicates aren’t allowed. You can even do this for multiple columns, so it will have to be that exact permutation. If exception handling were turned off, you’d check the error code with $mysqli->errno. With exception handling turned on, you could choose between that or the generic exception method $e->getCode(). Note, this differs from PDOException, which will print the SQLSTATE, rather than the error code.

Here’s a list of error messages. The error code for a duplicate row entry from either an update or insert is 1062 and SQLSTATE is 23000. To specifically check for SQLSTATE, you must use $mysqli->sqlstate.

try {
  $stmt = $mysqli->prepare("INSERT INTO myTable (name, age) VALUES (?, ?)");
  $stmt->bind_param("si", $_POST['name'], $_POST['age']);
  $stmt->execute();
  $stmt->close();
} catch(Exception $e) {
  if($mysqli->errno === 1062) echo 'Duplicate entry';
}

This is how you would set a unique constraint:

ALTER TABLE myTable ADD CONSTRAINT unique_person UNIQUE (name, age)

Select

All select statements in parameterized queries will start off about the same. However, there is a key difference to actually storing and fetching the results. The two methods that exist are get_result() and bind_result().

get_result()

This is the more versatile of the two, as it can be used for any scenario. It should be noted that this requires mysqlnd, which has been included in PHP since 5.3 and has been the default native driver since 5.4, as stated here. I doubt many people are using older versions than that, so you should generally stick with get_result().

This essentially exposes the regular, non-prepared mysqli_result api. Meaning, that once you do $result = get_result(), you can use it exactly the same way you’d use $result = $mysqli->query().

Now you can use the following methods for fetching one row at a time or all at once. Here’s just some of the most common ones, but you can take a look at the entire mysqli_result class for all of its methods.

One Row

  • $result->fetch_assoc() – Fetch an associative array
  • $result->fetch_row() – Fetch a numeric array
  • $result->fetch_object() – Fetch an object array

All

  • $result->fetch_all(MYSQLI_ASSOC) – Fetch an associative array
  • $result->fetch_all(MYSQLI_NUM) – Fetch a numeric array
$stmt = $mysqli->prepare("SELECT * FROM myTable WHERE name = ?");
$stmt->bind_param("s", $_POST['name']);
$stmt->execute();
$result = $stmt->get_result();
if($result->num_rows === 0) exit('No rows');
while($row = $result->fetch_assoc()) {
  $ids[] = $row['id'];
  $names[] = $row['name'];
  $ages[] = $row['age'];
}
var_export($ages);
$stmt->close();

Output:

[22, 18, 19, 27, 36, 7]

bind_result()

You might be wondering, why even use bind_result()? I personally find it to be far inferior to get_result() in every scenario, except for when fetching a single row into separate variables. Also, before get_result() existed and mysqlnd became built into PHP, this was your only option, which is why a lot of legacy code might be using it.

The most annoying part about using bind_result() is that you must bind every single column you select and then traverse the values in a loop. This is obviously not ideal for a plethora of values or to use with *. The star selector is especially annoying to use with bind_result(), since you don’t even know what those values are without looking in the database. Additionally, this makes your code exceedingly unmaintainable with changes to the table. This usually won’t matter, as you shouldn’t be using the wildcard selector in production mode anyway (but you know you are).

$stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE name = ?");
$stmt->bind_param("s", $_POST['name']);
$stmt->execute();
$stmt->store_result();
if($stmt->num_rows === 0) exit('No rows');
$stmt->bind_result($idRow, $nameRow, $ageRow); 
while($stmt->fetch()) {
  $ids[] = $idRow;
  $names[] = $nameRow;
  $ages[] = $ageRow;
}
var_export($ids);
$stmt->close();

Output:

[106, 221, 3, 55, 583, 72]

Fetch Associative Array

I find this to be the most common use case typically. I will also be utilizing chaining in the following, though that’s obviously not necessary.

$stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE name = ?");
$stmt->bind_param("s", $_POST['name']);
$stmt->execute();
$arr = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
if(!$arr) exit('No rows');
var_export($arr);
$stmt->close();

If you need to modify the result set, then you should probably use a while loop with fetch_assoc() and fetch each row one at a time.

$arr = [];
$stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE name = ?");
$stmt->bind_param("s", $_POST['name']);
$stmt->execute();
$result = $stmt->get_result();
while($row = $result->fetch_assoc()) {
  $arr[] = $row;
}
if(!$arr) exit('No rows');
var_export($arr);
$stmt->close();

Output:

[
  ['id' => 27, 'name' => 'Jessica', 'age' => 27], 
  ['id' => 432, 'name' => 'Jimmy', 'age' => 19]
]

You can actually do this using bind_result() as well, although it was clearly not designed for it. Here’s a clever solution, though I personally feel like it’s something that’s cool to know is possible, but realistically shouldn’t be used.

Fetch Numeric Array

This follows the same format as an associative array. To get the entire array in one command, without a loop, you’d use mysqli_result->fetch_all(MYSQLI_NUM). If you need to fetch the results in a loop, you must to use mysqli_result->fetch_row().

$stmt = $mysqli->prepare("SELECT location, favorite_color, age FROM myTable WHERE name = ?");
$stmt->bind_param("s", $_POST['name']);
$stmt->execute();
$arr = $stmt->get_result()->fetch_all(MYSQLI_NUM);
if(!$arr) exit('No rows');
var_export($arr);
$stmt->close();

And of course, the while loop adaptation.

$arr = [];
$stmt = $mysqli->prepare("SELECT location, favorite_color, age FROM myTable WHERE name = ?");
$stmt->bind_param("s", $_POST['name']);
$stmt->execute();
$result = $stmt->get_result();
while($row = $result->fetch_row()) {
  $arr[] = $row;
}
if(!$arr) exit('No rows');
var_export($arr);
$stmt->close();

Output:

[
  ['Boston', 'green', 28], 
  ['Seattle', 'blue', 49],
  ['Atlanta', 'pink', 24]
]

Fetch Single Row

I personally find it simpler to use bind_result() when I know for fact that I will only be fetching one row, as I can access the variables in a cleaner manner.

$stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE name = ?");
$stmt->bind_param("s", $_POST['name']);
$stmt->execute();
$stmt->store_result();
if($stmt->num_rows === 0) exit('No rows');
$stmt->bind_result($id, $name, $age);
$stmt->fetch();
echo $name; //Output: 'Ryan'
$stmt->close();

Now you can use just simply use the variables in bind_result(), like $name since you know they will only contain one value, not an array.

Here’s the get_result() version:

$stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE name = ?");
$stmt->bind_param("s", $_POST['name']);
$stmt->execute();
$arr = $stmt->get_result()->fetch_assoc();
if(!$arr) exit('No rows');
var_export($arr);
$stmt->close();

You would then use the variable as $arr['id'] for example.

Output:

['id' => 36, 'name' => 'Kevin', 'age' => 39]

Fetch Array of Objects

This very similar to fetching an associative array. The only main difference is that you’ll be accessing it like $arr[0]->age. Also, in case you didn’t know, objects are pass by value, while arrays are by reference.

$arr = []
$stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE id = ?");
$stmt->bind_param("s", $_SESSION['id']);
$stmt->execute();
$result = $stmt->get_result();
while($row = $result->fetch_object()) {
  $arr[] = $row;
}
if(!$arr) exit('No rows');
var_export($arr);
$stmt->close();

Output:

[
  stdClass Object ['id' => 27, 'name' => 'Jessica', 'age' => 27], 
  stdClass Object ['id' => 432, 'name' => 'Jimmy', 'age' => 19]
]

You can even add property values to an existing class as well. However, it should be noted that there is a potential gotcha, according to this comment in the PHP docs. The problem is that if you have a default value in your constructor with a duplicate variable name, it will fetch the object first and then set the constructor value, therefore overwriting the fetched result. Weirdly enough, there was a “bug” from PHP 5.6.21 to 7.0.6 where this wouldn’t happen. Even though this violates principles of OOP, some people would like this feature, even though it was bug in certain versions. Something like PDO::FETCH_PROPS_LATE in PDO should be implemented in MySQLi to give you the option to choose.

class myClass {}
$arr = [];
$stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE id = ?");
$stmt->bind_param("s", $_SESSION['id']);
$stmt->execute();
$result = $stmt->get_result();
while($row = $result->fetch_object('myClass')) {
  $arr[] = $row;
}
if(!$arr) exit('No rows');
var_export($arr);
$stmt->close();

As the comment states, this is how you would do it correctly. All you need is a simple if condition to check if the variable equals the constructor value — if it doesn’t, just don’t set it in the constructor. This is essentially the same as using PDO::FETCH_PROPS_LATE in PDO.

class myClass {
  private $id;
  public function __construct($id = 0) {
    if($this->id === 0) $this->id = $id;
  }
}
$arr = [];
$stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE id = ?");
$stmt->bind_param("s", $_SESSION['id']);
$stmt->execute();
$result = $stmt->get_result();
while($row = $result->fetch_object('myClass')) {
  $arr[] = $row;
}
if(!$arr) exit('No rows');
var_export($arr);
$stmt->close();

Another unexpected, yet potentially useful behavior of using fetch_object('myClass') is that you can modify private variables. I’m really not sure how I feel about this, as this seems to violate principles of encapsulation.

Conclusion

bind_result() – best used for fetching single row without too many columns or *; extremely inelegant for associative arrays.

get_result() – is the preferred one for almost every use-case.

Like

You would probably think that you could do something like:

$stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE Name LIKE %?%");

But this is not allowed. The ? placeholder must be the entire string or integer literal value. This is how you would do it correctly.

$search = "%{$_POST['search']}%";
$stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE name LIKE ?"); 
$stmt->bind_param("s", $search);
$stmt->execute();
$arr = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
if(!$arr) exit('No rows');
var_export($arr);
$stmt->close();

Where In Array

This is definitely something I’d like to see improved in MySQLi. For now, using MySQLi prepared statements with WHERE IN is possible, but feels a little long-winded.

Side note: The following two examples use the splat operator for argument unpacking, which requires PHP 5.6+. If you are using a version lower than that, then you can substitute it with call_user_func_array().

$inArr = [12, 23, 44];
$clause = implode(',', array_fill(0, count($inArr), '?')); //create 3 question marks
$types = str_repeat('i', count($inArr)); //create 3 ints for bind_param
$stmt = $mysqli->prepare("SELECT id, name FROM myTable WHERE id IN ($clause)");
$stmt->bind_param($types, ...$inArr);
$stmt->execute();
$resArr = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
if(!$resArr) exit('No rows');
var_export($resArr);
$stmt->close();

With Other Placeholders

The first example showed how to use the WHERE IN clause with dummy placeholder solely inside of it. What if you wanted to use other placeholders in different places?

$inArr = [12, 23, 44];
$clause = implode(',', array_fill(0, count($inArr), '?')); //create 3 question marks
$types = str_repeat('i', count($inArr)); //create 3 ints for bind_param
$types .= 'i'; //add 1 more int type
$fullArr = array_merge($inArr, [26]); //merge WHERE IN array with other value(s)
$stmt = $mysqli->prepare("SELECT id, name FROM myTable WHERE id IN ($clause) AND age > ?");
$stmt->bind_param($types, ...$fullArr); //4 placeholders to bind
$stmt->execute();
$resArr = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
if(!$resArr) exit('No rows');
var_export($resArr);
$stmt->close();

Multiple Prepared Statements in Transactions 

This might seem odd why it would even warrant its own section, as you can literally just use prepared statements one after another. While this will certainly work, this does not ensure that your queries are atomic. This means that if you were to run ten queries, and one failed, the other nine would still succeed. If you want your SQL queries to execute only if they all succeeded, then you must use transactions.

try {
  $mysqli->autocommit(FALSE); //turn on transactions
  $stmt1 = $mysqli->prepare("INSERT INTO myTable (name, age) VALUES (?, ?)");
  $stmt2 = $mysqli->prepare("UPDATE myTable SET name = ? WHERE id = ?");
  $stmt1->bind_param("si", $_POST['name'], $_POST['age']);
  $stmt2->bind_param("si", $_POST['name'], $_SESSION['id']);
  $stmt1->execute();
  $stmt2->execute();
  $stmt1->close();
  $stmt2->close();
  $mysqli->autocommit(TRUE); //turn off transactions + commit queued queries
} catch(Exception $e) {
  $mysqli->rollback(); //remove all queries from queue if error (undo)
  throw $e;
}

Reuse Same Template, Different Values

try {
  $mysqli->autocommit(FALSE); //turn on transactions
  $stmt = $mysqli->prepare("INSERT INTO myTable (name, age) VALUES (?, ?)");
  $stmt->bind_param("si", $name, $age);
  $name = 'John';
  $age = 21;
  $stmt->execute();  
  $name = 'Rick';
  $age = 24;
  $stmt->execute();
  $stmt->close();
  $mysqli->autocommit(TRUE); //turn off transactions + commit queued queries
} catch(Exception $e) {
  $mysqli->rollback(); //remove all queries from queue if error (undo)
  throw $e;
}

Error Handling

Fatal error: Uncaught Error: Call to a member function bind_param() on boolean

Anyone who’s used MySQLi prepared statements has seen this message at some point, but what does it mean? Pretty much nothing at all. So how do you fix this, you might ask? To start, don’t forget to turn on exception handling, instead of error handling mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT) when you create a new connection.

Exception Handling

All of the mysqli functions return false on failure, so you could easily just check for truthiness on each function and report errors with $mysqli->error. However, this is very tedious, and there’s a more elegant way of doing this if you enable internal reporting. I recommend doing it this way, as it’s much more portable from development to production.

This can be used in production too, as long as you have an error log set up for all errors; this needs to be set in the php.ini. Please don’t ever report errors directly on your site in production. You’ll be kicking yourself for such a silly mistake. The placement of mysqli_report() matters also. if you place it before creating a new connection then it will output your password too; otherwise, it will just report everything after, like your queries.

Here’s what your php.ini file should look like in production: do both display_errors = Off and log_errors = On. Also, keep in mind that each page should really only be using a single, global, try/catch block, rather than wrapping each query individually. The only exception to this is with transactions, which would be nested, but throw its own exception, so the global try/catch can “catch” it.

try {
  $stmt = $mysqli->prepare("DELETE FROM myTable WHERE id = ?");
  $stmt->bind_param("i", $_SESSION['id']);
  $stmt->execute();
  $stmt->close();

  $stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE name = ?");
  $stmt->bind_param("s", $_POST['name']);
  $stmt->execute();
  $arr = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
  $stmt->close();

  try {
    $mysqli->autocommit(FALSE); //turn on transactions
    $stmt = $mysqli->prepare("INSERT INTO myTable (name, age) VALUES (?, ?)");
    $stmt->bind_param("si", $name, $age);
    $name = 'John';
    $age = 21;
    $stmt->execute();  
    $name = 'Rick';
    $age = 24;
    $stmt->execute();
    $stmt->close();
    $mysqli->autocommit(TRUE); //turn off transactions + commit queued queries
  } catch(Exception $e) {
    $mysqli->rollback(); //remove all queries from queue if error (undo)
    throw $e;
  }  
} catch (Exception $e) {
  error_log($e);
  exit('Error message for user to understand');
}

Custom Exception Handler

As stated earlier, you can alternatively use set_exception_handler() on each page (or a global redirect). This gets rid of the layer of curly brace nesting. If you are using transactions, you should still use a try catch with that, but then throw your own exception.

set_exception_handler(function($e) {
  error_log($e);
  exit('Error deleting');
});
$stmt = $mysqli->prepare("DELETE FROM myTable WHERE id = ?");
$stmt->bind_param("i", $_SESSION['id']);
$stmt->execute();
$stmt->close();

Gotcha with Exception Handling

You’d expect for all MySQLi errors to be converted to exceptions with mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT). Oddly enough, I noticed that it still gave me a warning error when bind_param() had too many or too little bound variables or types. The message outputted is as follows:

Warning: mysqli_stmt::bind_param(): Number of variables doesn’t match number of parameters in prepared statement

A solution to this is to use a global error handler to trigger an exception. An example of this could be:

set_error_handler(function($errno, $errstr, $errfile, $errline) {
  throw new Exception("$errstr on line $errline in file $errfile");
});

This only happened on runtime warnings, but I converted all errors to exceptions. I see no problem doing this, but there are some people who are strongly against it.

Some Extras

Do I Need $stmt->close()?

Great question. Both $mysqli->close() and $stmt->close() essentially have the same effect. The former closes the MySQLi connection, while the latter closes the prepared statement. TLDR; both are actually generally not even necessary in most cases, since both will close once the script’s execution is complete anyway. There’s also a function to simply free the memory associated with the MySQLi result and prepared statement, respectively: $result->free() and $stmt->free(). I myself, will likely never use it, but if you’re interested, here’s the one for the result and for the the parameterized query. The following should also be noted: both $stmt->close() and the end of the execution of the script will the free up the memory anyway.

Final verdict: I usually just do $mysqli->close() and $stmt->close(), even though it can be argued that it’s a little superfluous. If you are planning on using the same variable $stmt again for another prepared statements, then you must either close it, or use a different variable name, like $stmt2. Lastly, I have never found a need to simply free them, without closing them.

Classes: mysqli vs. mysqli_stmt vs. mysqli_result

One thing you may have realized along the way is that there are certain methods that exist in two of the classes, like an alias almost. I personally believe it would be better to only have one version, like in PDO, to avoid confusion.

  • mysqli::$affected_rows or mysqli_stmt::$affected_rows – Belongs to mysqli_stmt. Works the same with either, but will be an error if called after the statement is closed with either method
  • mysqli_result::$num_rows or mysqli_stmt::$num_rows – $result->num_rows can only be used with get_result(), while $stmt->num_rows can only be used with bind_result().
  • mysqli::$insert_id or mysqli_stmt::$insert_id – Belongs to mysqli. Better to use $mysqli->insert_id, since it will still work even after $stmt->close() is used. There’s also a note on the PHP docs from 2011 stating that $stmt->insert_id will only get the first executed query. I tried this on my current version of 7.1 and this doesn’t seem to be the case. The recommended one to use is the mysqli class version anyway.

Source: websitebeaver.com