To use this website fully, you first need to accept the use of cookies. By agreeing to the use of cookies you consent to the user of functional cookies. For more information read this page.

Preparing 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.

Preparing statements in PHP

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 in PHP

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.