Database management systems that work with the database language SQL are widely popular but have always been vulnerable to manipulations during data input. User input that hasn’t been masked enough or contains metacharacters such as quotation marks or semicolons makes an easy catch for predators. One possible solution to this problem is to use prepared statements, which are pre-prepared instructions for the database that aren’t given values until they’re run.

What makes this method so special, and when can it be used? In what follows, we use the example of MySQL to show how prepared statements work and how they can be used for database management.

What are prepared statements?

Prepared statements are ready-to-use templates for queries in SQL database systems, which don’t contain values for the individual parameters. Instead, these statement templates work with variables or placeholders that are only replaced with the actual values inside the system – unlike with manual input, in which values are already assigned at execution.

All major SQL database management systems like MySQL, MariaDB, Oracle, Microsoft SQL Server, and PostgreSQL support prepared statements. Most of these applications use a NoSQL binary protocol. However, some systems such as MySQL use typical SQL syntax for implementation. In addition, some programming languages like Java, Perl, Python, and PHP support prepared statements with their standard library or extensions. If you use PHP for database access, you have the choice between using the object-oriented interface PHP Data Objects (PDO) or the PHP extension MSQLi for implementing prepared statements.

Why does it make sense to use prepared statements in MySQL and co.?

The main reason for working with prepared statements in database management systems like MySQL is security. The biggest problem with standard access to SQL databases is probably that they can be easily manipulated. What you’re dealing with in this case is an SQL Injection, in which code is inserted or adapted in order to gain access to sensible data or gain complete control of the database. Prepared statements in PHP or other languages don’t have this vulnerability, since they’re only assigned concrete values within the system.

Tip

A requirement for the high safety standard of prepared statements is that none of its components are generated from an external source.

But protection against SQL injections isn’t the only argument for using prepared statements: Once they’ve been analyzed and compiled, prepared statements can be used over and over again by the database system (with the appropriately modified values). In other words, they use fewer resources and are faster than manual database queries when it comes to SQL tasks that have to be repeatedly executed.

How exactly do prepared statements work?

Leaving out the syntax of the underlying scripting language and idiosyncrasies of individual database management systems, integrating and using a prepared statement generally happens in the following stages:

Stage 1: Preparing the prepared statements

The first step is to create a statement template – in PHP, you can do this with the function prepare(). Instead of concrete values for the relevant parameters, the above-mentioned placeholders (also called bind variables) are inserted. They’re typically marked with a “?”, as in the following example.

INSERT INTO Products (Name, Price) VALUES (?, ?);

Complete prepared statements are then forwarded to the database management system.

Stage 2: Processing the statement template with the DBMS

The statement template will then be parsed by the database management system so that it can be compiled, i.e. converted into an executable statement. The prepared statement is also optimized as a part of this process.

Stage 3: Execution of the prepared statement

The processed template can later be executed in the database system as often as desired. The only requirement for this is appropriate input from the connected application or data source, which has to provide the values for the placeholder fields. With reference to the code example from Stage 1, this could be the values “Book” (Name) and “10” (Price) or “Computer” and “1000”.

Tutorial: How to use prepared statements in MySQL with MySQLi

Now that we’ve seen how prepared statements work in general, we can take a look at how to use these practical statements with concrete examples. In the following tutorial, we’ll use

  • MySQL as the database management system and
  • PHP as the prepared statement language.

Current versions of MySQL support the use of prepared statements from the server side based on a binary protocol that contains all the SQL commands for updating data and also records all updates since the last data backup. The PHP extension MySQLi, which also supports prepared statements using a binary protocol, serves as the interface in this tutorial.

Tip

A frequently used alternative to MySQLi as a prepared statement API is the object-oriented interface PDO (PHP Data Objects) This option is by far the most beginner-friendly solution.

PREPARE, EXECUTE, and DEALLOCATE PREPARE: The three basic SQL commands for using prepared statements

There are three SQL commands that play a crucial role in prepared statements in MySQL databases:

The command “PREPARE“ is necessary for preparing a prepared statement for use and for assigning it a unique name under which it can be controlled later in the process.

PREPARE stmt_name FROM preparable_stmt

For the execution of prepared statements in SQL, you’ll need the command “EXECUTE“. You can refer to the relevant prepared statement by entering the name that was generated with “PREPARE”. A statement can be executed as often as you’d like – you can use it to define various variables or transfer new values for the variables you set.

EXECUTE stmt_name
	[USING @var_name [, @var_name] ...]

In order to deallocate a PHP prepared statement, use the command “DEALLOCATE PREPARE“. Alternatively, statements can be automatically deallocated at the end of a session. Deallocation is important because otherwise you’ll quickly reach the limit defined by the system variable max_prepared_stmt_count. Then you won’t be able to create any new prepared statements.

{DEALLOCATE | DROP} PREPARE stmt_name

Which SQL statements can be used as MySQL prepared statements

You can process and execute almost all SQL statements that are supported by MySQL as prepared statements. One exception are diagnostic statements, which are excluded in order to comply with SQL standards. Specifically, the following statements are not supported:

  • SHOW WARNINGS
  • SHOW COUNT(*) WARNINGS
  • SHOW ERRORS
  • SHOW COUNT(*) ERRORS

In addition, it’s not possible to generate templates for SQL queries related to the system variables warning_count and error_count.

On the other hand, the following statements can be used:

ALTER TABLE
ALTER USER
ANALYZE TABLE
CACHE INDEX
CALL
CHANGE MASTER
CHECKSUM {TABLE | TABLES}
COMMIT
{CREATE | DROP} INDEX
{CREATE | RENAME | DROP} DATABASE
{CREATE | DROP} TABLE
{CREATE | RENAME | DROP} USER
{CREATE | DROP} VIEW
DELETE
DO
FLUSH {TABLE | TABLES | TABLES WITH READ LOCK | HOSTS | PRIVILEGES
	| LOGS | STATUS | MASTER | SLAVE | USER_RESOURCES}
GRANT
INSERT
INSTALL PLUGIN
KILL
LOAD INDEX INTO CACHE
OPTIMIZE TABLE
RENAME TABLE
REPAIR TABLE
REPLACE
RESET {MASTER | SLAVE}
REVOKE
SELECT
SET
SHOW {WARNINGS | ERRORS}
SHOW BINLOG EVENTS
SHOW CREATE {PROCEDURE | FUNCTION | EVENT | TABLE | VIEW}
SHOW {MASTER | BINARY} LOGS
SHOW {MASTER | SLAVE} STATUS
SLAVE {START | STOP}
TRUNCATE TABLE
UNINSTALL PLUGIN
UPDATE

Syntactic idiosyncrasies in the SQL syntax of prepared statements in PHP

Compared with standard SQL syntax, the syntax of prepared statements has some distinctive features worth noting. The most important feature is the use of placeholders for parameter values, which is what makes prepared statements so interesting for accessing database management systems. In MySQL 8.0 and up, these placeholders are also possible for “OUT” and “INOUT” parameters in “PREPARE” and “EXECUTE” statements. For “IN” parameters, they’re even available independent of the database system version. Further special features of prepared statement syntax include the following:

  • SQL syntax for PHP prepared statements cannot be nested. This means that a statement that is passed to a “PREPARE” statement cannot also be a “PREPARE”, “EXECUTE”, or “DEALLOCATE PREPARE” statement.
  • Prepared statements can be used in stored procedures (function for calling complete sets of statements).
  • Multiple statements are not possible within a prepared statement or within strings separated by semicolons.

Prepared statements in MySQLi: Example

This example shows how an input with PHP prepared statements in MySQLi looks:

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Establish connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Verify connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
// Preparation of prepared statements
$stmt = $conn->prepare("INSERT INTO MyCustomers (FirstName, LastName, Email) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $firstname, $lastname, $email);
// Setting the parameters and execution
$FirstName = "John";
$LastName = "Smith";
$Email = "john@example.com";
$stmt->execute();
$FirstName = "Jane";
$LastName = "Smith";
$Email = "jane@example.com";
$stmt->execute();
$FirstName = "Sarah";
$LastName = "Smith";
$Email = "sarah@example.com";
$stmt->execute();
echo “New entries created successfully";
$stmt->close();
$conn->close();
?>

This PHP script establishes the connection to the MySQL database with ($conn), at which point the individual server data needs to be entered.

The crucial prepared statement part begins with the line "INSERT INTO MyCustomers (FirstName, LastName, Email) VALUES (?, ?, ?)”. The customer database “MyCustomers” will receive input (INSERT INTO) in the columns “FirstName”, “LastName” and “Email”. Placeholders are used for VALUES, which are marked using question marks.

Next, the parameters need to be bound (bind_parameters). In addition, the database also needs information about what type of data is being dealt with. The argument “sss” used here indicates that all three parameters will be strings. Some possible alternative data types would be:

  • i: INTEGER (whole number)
  • d: DOUBLE (also called a float, a number with a decimal point or a number in exponential form)
  • b: BLOB (collection of binary data)
aN5KqxK1slc.jpg To display this video, third-party cookies are required. You can access and change your cookie settings here.
Was this article helpful?
Go to Main Menu