Jamie Balfour

Welcome to my personal website.

Find out more about me, my personal projects, reviews, courses and much more here.

Part 6.3Preparing MySQL statements in PHP

Databases are one of the reasons that many websites end up with security loopholes.

PHP being a server side language can protect against these loopholes using prepared statements.

Prepared statements

A prepared statement consists of three steps:

  • A statement is generated
  • MySQL parses and optimises then compiles the statement. It then stores this statement.
  • The statement is given parameters and executed.

Using MySQLi

Prepared statements are generally written like so:

PHP
<?php
	//Use the following command to connect
	$myConnection = mysqli_connect($dbName, $dbUsername, $dbPassword);
	$statement = "SELECT * FROM db WHERE id=? AND id forename=?";
	$prepared = mysqli_prepare ($myConnection, $statement);
?>
		

The important part to note is the assignment of the $statement variable. In the value of this statement, there is a question mark (?). This represents where parameters go.

The $prepared variable is used to store the link to the prepared statement.

Putting in parameters using MySQLi

PHP provides the mysqli_stmt_bind_param function to bind parameters to a prepared statement. This function takes in at least two arguments. The first is the prepared statement, the second is a string of types whilst the third, fourth, fifth and so on are the parameter values:

PHP
<?php
	mysqli_stmt_bind_param ($prepared, "is", 3, "John");
?>
		

Here, the string "is" represents the types of the variables - i.e. integer then string.

The following table shows all of the types that are supported:

Character Type
b BLOB
d Double
i Integer
s String

In order for the results to come out however, the statement must be executed using the mysqli_stmt_execute function. This then leaves the result within the original call to the database.

PHP
<?php
	mysqli_stmt_execute ($prepared);
?>
		

Here are another two examples:

PHP
<?php
	$myConnection = mysqli_connect($dbName, $dbUsername, $dbPassword);

	$statement = "SELECT * FROM db WHERE forename=? AND surname=?";

	$prepared = mysqli_prepare ($myConnection, $statement);
	mysqli_stmt_bind_param ($prepared, "ss", $firstName, $surname);

	$firstname = "John";
	$surname = "Smith";
	mysqli_stmt_execute ($prepared);

	$statement = "SELECT * FROM db WHERE forename=? AND surname=? AND id>?";

	$prepared = mysqli_prepare ($myConnection, $statement);
	mysqli_stmt_bind_param ($prepared, "ssi", $firstname, $surname, $id);

	$firstname = "John";
	$surname = "Smith";
	$id = 3;

	mysqli_stmt_execute ($prepared);
?>
		

Now queries that are run on the $myConnection variable will run on that prepared statement.

Using PDO (the preferred option)

PDO or PHP Data Objects is the preferred option for preparing statemts and it's much easier to work with too.

The first step is to create and store a PDO object instance. This will contain the link to the database within it:

PHP
<?php
	$pdo = new PDO("mysql:dbname=database1;host=localhost", "user1", "password1");
?>
		

In the above, replace database1, localhost, user1 and password1 with correct database connection details.

The object also provides many different methods that perform database manipulation operations.

Putting in parameters using PDO

PHP's PDO object provides the prepare method to prepare parameter placeholders.

Unlike in MySQLi however, PDO does not use the ? and expect a type to be given. Further PDO queries do not even require the values be inserted in order.

In PDO, paramters are represented by their preceeded by a colon (:):

PHP
<?php
	$statement = "SELECT * FROM db WHERE forename=:first_name AND surname=:second_name";
?>
		

Next prepare the statement using the prepare method within the PDO object instance:

PHP
<?php
	$prepared = $pdo->prepare($statement);
?>
		

The final step is to execute it. The execute method on the prepared statement is used for this and requires an associative array which contains the placeholder name as the key and the value as the, well, the value in the array:

PHP
<?php
	$result = $prepared->execute(array(":first_name" => $firstName, ":second_name" => $surname));
?>
		

One can then check if the execution of the statement was successful or not using a standard if statement:

PHP
<?php
	if ($result){
		//Execute some code here
	}
?>
		

Finally, iterating the result is easy too. Assuming that the prepared variable has been used the code would look like:

PHP
<?php
	while ($row = $prepared->fetch(PDO::FETCH_ASSOC)){
		print_r($row);
	}
?>
		

Note the PDO::FETCH_ASSOC constant which will return an associative array as opposed to a PDO object.

Below is the complete program:

PHP
<?php
	$pdo = new PDO("mysql:dbname=database1;host=localhost", "user1", "password1");
  $statement = "SELECT * FROM db WHERE forename=:first_name AND surname=:second_name";
  $prepared = $pdo->prepare($statement);
	$result = $prepared->execute(array(":first_name" => $firstName, ":second_name" => $surname));

	if ($result){
		//Iterate the result
		while ($row = $prepared->fetch(PDO::FETCH_ASSOC)){
			print_r($row);
		}

	}

?>
		
Feedback 👍
Comments are sent via email to me.