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

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
	$prepared->execute(array(":first_name" => $firstName, ":second_name" => $surname));
?>
		
Provide feedback on this page
Comments are sent via email to me.