Hyvor Developer
Advertisement

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 MYSQL Injection.

For ease, I have divided this article into 4 topics.

Brief Intro to Prepared Statements

While programmers 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.

SQL Prepared Statements

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.

The Problem of MYSQLI Prepared Statements

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.

Explanation of the solution to perform the MYSQLI prepared statements in the best way

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 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]');


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) has 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 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.

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!

Author SupunKavinda