MySQL Prepared

MySQL Prepared


MySQL 8.0 provides support for server-side prepared statements. All major SQL database management systems like MySQL, MariaDB, Oracle, Microsoft SQL Server, and PostgreSQL support 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.

A prepared statement (also known as parameterized statement) is simply a SQL query template containing placeholder instead of the actual parameter values. These placeholders will be replaced by the actual values at the time of execution of the statement.

The prepared statement execution consists of two stages: prepare and execute.

  • Prepare — At the prepare stage a SQL statement template is created and sent to the database server. The server parses the statement template, performs a syntax check and query optimization, and stores it for later use.
  • Execute — During execute the parameter values are sent to the server. The server creates a statement from the statement template and these values to execute it.

MySQLi supports the use of anonymous positional placeholder (?), as shown below:

How to write :

      INSERT INTO persons (first_name, last_name, email) VALUES (?, ?, ?);

Example (MySQLi with Prepared Statements)

Output :

 

While, PDO supports both anonymous positional placeholder (?), as well as the named placeholders. A named placeholder begins with a colon (:) followed by an identifier, like this:

 

        INSERT INTO persons (first_name, last_name, email)
         VALUES (:first_name, :last_name, :email);

 

Example (PDO with Prepared Statements) :

Output :

 

Explanation of Code:

Inside the SQL INSERT statement of the first example ( ? ) question marks is used as the placeholders for the firstname, lastname, email fields values.

The  $stmt->bind_param() function  bind variables to the placeholders (?) in the SQL statement template. The placeholders (?) will be replaced by the actual values held in the variables at the time of execution. The type definition string provided as second argument i.e. the "sss" string specifies that the data type of each bind variable is string.

he type definition string specify the data types of the corresponding bind variables and contains one or more of the following four characters:

  • b — binary (such as image, PDF file, etc.)
  • d — double (floating point number)
  • i — integer (whole number)
  • s — string (text)

The number of bind variables and the number of characters in type definition string must match the number of placeholders in the SQL statement template.