The Best Way to Perform MYSQLI Prepared Statements in PHP
I have been working with MYSQLI prepared statements for a long time. I have never seen an awesome tutorial on the best way to perform those. So, I experimented with things and found my own way, which I'm going to write in this article. After going through this article you will learn the best and simplest way to do MYSQLI prepared statements while preventing SQL Injection.
For ease, I have divided this article into 4 topics.
Brief Intro to Prepared Statements
While programmers are using SQL queries to connect with databases, hackers joined the party using SQL Injection. To prevent this problem, prepared statements were introduced. This is how it works.
- First, a query without variables is sent to the database (? is used as the placeholder for variables)
- Then, variables are sent with the message: "Dear database, these are the variables. So, please don't execute these as SQL commands"
- Finally, the query can be executed with any number of different variables.
Let's see how to perform a simple SELECT MYSQLI prepared statement in PHP. (Replace HOST, USERNAME, PASSWORD, DATABASE with your database credentials.)
<?php
// connecting to the db
$mysqli = new mysqli(HOST, USERNAME, PASSWORD, DATABASE);
$id = 2;
$stmt = $mysqli -> prepare('SELECT email FROM users WHERE id = ? LIMIT 1');
$stmt -> bind_param('i', $id);
$stmt -> execute();
$stmt -> store_result();
$stmt -> bind_result($email);
$stmt -> fetch();
echo $email;
If everything is fine, you will see the email of the user with id 2. But, there's a problem...
The Major Problem
The major problem is, all of the above functions can fail and return false.
If they do, you won't see anything other than a bunch of error messages. And, there's a good news too! All of those functions return true (or another variable that evaluates to true) on success.
The Solution
So, the simple answer is checking whether the current step was successful before running the next one. You can change your PHP script like this to do that.
<?php
// connecting to the db
$mysqli = new mysqli(HOST, USERNAME, PASSWORD, DATABASE);
$id = 2;
$stmt = $mysqli -> prepare('SELECT email FROM users WHERE id = ? LIMIT 1');
if (
$stmt &&
$stmt -> bind_param('i', $id) &&
$stmt -> execute() &&
$stmt -> store_result() &&
$stmt -> bind_result($email) &&
$stmt -> fetch()
) {
echo $email;
} else {
echo 'Prepared Statement Error';
}
Let me explain how this works.
How the "if" statement with and (&&) operator works is, it goes one by one. If the first one evaluates to (returns) true, it will check the next one. Then the next one. The same process continues. If all returned true the code inside if statement will execute.
Examples
1. SELECT Multiple Rows
In the above example, we selected one row from the database. Now, in this step, we will select multiple rows and return them as a multidimensional array.
Assume that we have a table with several user's data (name and email). We are going to select all the data of the users who are admins and display each user's name and email.
<?php
$mysqli = new mysqli(HOST, USERNAME, PASSWORD, DATABASE);
$role = 'admin';
$stmt = $mysqli -> prepare('SELECT name, email FROM users WHERE role = ?');
if (
$stmt &&
$stmt -> bind_param('s', $role) &&
$stmt -> execute() &&
$stmt -> store_result() &&
$stmt -> bind_result($name, $email)
) {
while ($stmt -> fetch()) {
echo "$name: $email <br>";
}
} else {
echo 'Prepared Statement Error';
}
We know while statement loops while the condition is true. As fetch() method returns true until selected rows are finished, we can use this way to loop through rows. In each time we run fetch() method, $name, and $email variables are set to each value of the current row. Finally, you will see all users' names and emails.
2. INSERT
Insert statements are pretty easy to perform with MYSQLI Prepared statements. You can also insert multiple rows with the same prepared statement.
<?php
$mysqli = new mysqli(HOST, USERNAME, PASSWORD, DATABASE);
// open the mysqli prepared statement globally
$stmt = $mysqli -> prepare('INSERT INTO users (name, email) VALUES (?,?)');
function insertUser($name, $email) {
global $stmt;
// using prepared statement several times with different variables
if (
$stmt &&
$stmt -> bind_param('ss', $name, $email) &&
$stmt -> execute()
) {
// new user added
}
}
insertUser('George', '[email protected]');
insertUser('Jack', '[email protected]');
- First, we connect to the database.
- Then, we initiate a MYSQLI prepared statement with an INSERT query.
- Next, we create a function to execute the prepared statement with different variable values.
- Finally, we enjoy adding any number of users!
Important! This is the fastest way in PHP to insert multiple rows to MYSQL database at the same time.
3. UPDATE
The procedure is quite similar to INSERT. Let's see an example.
<?php
$mysqli = new mysqli(HOST, USERNAME, PASSWORD, DATABASE);
$stmt = $mysqli -> prepare('UPDATE users SET name = ? WHERE id = ? LIMIT 1');
$name = 'Joe';
$id = 1;
if (
$stmt &&
$stmt -> bind_param('si', $name, $id) &&
$stmt -> execute() &&
$stmt -> affected_rows === 1
) {
echo 'Updated';
} else {
echo 'Not updated';
}
Here, we can check the affected_rows variable in the $stmt object to verify the update. (Optional)
Debugging
Every programmer should know how to debug a code. In my experience, MYSQLI statements is a place that we can make mistakes frequently, which causes bugs.
First, you should know that the both MYSQLI variable ($mysqli in above examples) and statement variable ($stmt in above examples) have a property named error ($mysqli -> error and $stmt -> error). These two properties are really important in debugging.
Where to use $mysqli -> error?
Let's think we have the following code and it isn't updating our database.
<?php
$mysqli = new mysqli(HOST, USERNAME, PASSWORD, DATABASE);
$stmt = $mysqli -> prepare('UPDATE usres SET name = ? WHERE id = ? LIMIT 1');
$name = 'Joe';
$id = 1;
if (
$stmt &&
$stmt -> bind_param('si', $name, $id) &&
$stmt -> execute() &&
$stmt -> affected_rows === 1
) {
echo 'Updated';
} else {
echo 'Not updated';
}
Don't spend hours to find the error! Just add echo $mysqli -> error; statement to else block.
<?php
$mysqli = new mysqli(HOST, USERNAME, PASSWORD, DATABASE);
$stmt = $mysqli -> prepare('UPDATE usres SET name = ? WHERE id = ? LIMIT 1');
$name = 'Joe';
$id = 1;
if (
$stmt &&
$stmt -> bind_param('si', $name, $id) &&
$stmt -> execute() &&
$stmt -> affected_rows === 1
) {
echo 'Updated';
} else {
echo $mysqli -> error;
}
Now, it will show something like: Table 'usres' doesn't exist. Then, easily we can find our typo: written 'usres' instead of 'users'.
Important! If you have any error in your SQL query and the preparation of the statement fails, you can't use $stmt -> error as $stmt is just a boolean (false), not an object.
$mysqli -> error didn't say anything?
Let's use $stmt -> error
If the error property of MYSQLI variable didn't say anything, that means your prepared statement was successfully prepared. The error can be in bind_param or other related functions. Those errors are stored in $stmt -> error
<?php
$mysqli = new mysqli(HOST, USERNAME, PASSWORD, DATABASE);
$stmt = $mysqli -> prepare('UPDATE users SET name = ? WHERE id = ? LIMIT 1');
$name = 'Joe';
$id = 1;
if (
$stmt &&
$stmt -> bind_param('si', $name, $id) &&
$stmt -> execute() &&
$stmt -> affected_rows === 1
) {
echo 'Updated';
} else {
echo $stmt -> error;
}
Now it will echo out any error related to the prepared statement.
Debugging for a live website
Never ever do above two steps in a live website. Reading error messages of databases is a favorite way for hackers to get into the database of your website. If your website is live, either you can temporarily make your website down and do above testing or follow the following steps.
- Check the query (no typos, etc.)
- Check bind_param function. Make sure its first parameter is correct and matches the number of question marks in the query.
- Try the query in phpMyAdmin or any other database management software.
- Try your own debugging techniques.
- Ask for help online.
Conclusion
In this tutorial, we learned about the best way to perform MYSQLI prepared statements. These methods was found by me and I'm really happy to make this tutorial public for you. Please make sure that you leave a comment about how useful it was for you. Thanks a lot for reading!
