PHPMySQL prepared statements

Prepared statements are very useful for preventing MySQL injection.

Prepared statements and bound parameters

Prepared statements are used to execute multiple identical SQL statements more efficiently.

Prepared statements work as follows:

Preprocessing: Create SQL statement templates and send them to the database. Reserved values ​​are marked with the parameter "?". For example:

 INSERT INTO MyGuests (firstname, lastname, email) VALUES(?, ?, ?)

Database parsing, compilation, query optimization on SQL statement templates, and storage of results without output.

Execution: Finally, the application-bound value is passed to the parameter ("?" mark), and the database executes the statement. The application can execute the statement multiple times if the parameter values ​​are different.

Compared with directly executing SQL statements, prepared statements have two main advantages:

Prepared statements greatly reduce analysis time, as only one query is made (although the statement is executed multiple times).

Binding parameters reduces server bandwidth, you only need to send the parameters of the query instead of the entire statement.

Prepared statements are very useful for SQL injection because different protocols are used after parameter values ​​are sent, ensuring the validity of the data.

MySQLi prepared statements

The following example uses prepared statements in MySQLi and binds the corresponding parameters:

Example (MySQLi uses prepared statements)

<?php $servername = " localhost " ; $username = " username " ; $password = " password " ; $dbname = " myDB " ; // Create a connection $conn = new mysqli ( $servername , $username , $password , $dbname ) ; // Detect connection if ( $conn -> connect_error ) { die ( " Connection failed: " . $conn -> connect_error ) ; } // Preprocessing and binding $stmt = $conn -> prepare ( " INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?) " ) ; $stmt -> bind_param ( " sss " , $firstname , $lastname , $email ) ; // Set parameters and execute $firstname = " John " ; $lastname = " Doe " ; $email = " [email protected] " ; $stmt -> execute ( ) ; $firstname = " Mary " ; $lastname = " Moe " ; $email = " [email protected] " ; $stmt -> execute ( ) ; $firstname = " Julie " ; $lastname = " Dooley " ; $email = " [email protected] " ; $stmt -> execute ( ) ; echo " New record inserted successfully " ; $stmt -> close ( ) ; $conn -> close ( ) ; ?>

Parse each line of code for the following example:

"INSERT INTO MyGuests (firstname, lastname, email) VALUES(?, ?, ?)"

In SQL statement, we use question mark (?), here we can replace question mark with integer, string, double precision floating point and boolean value.

Next, let's take a look at the bind_param() function:

$stmt->bind_param("sss", $firstname, $lastname, $email);

This function binds SQL parameters and tells the database the value of the parameters. The "sss" parameter column handles the data types of the remaining parameters. The s character tells the database that the parameter is a string.

There are four types of parameters:

i - integer (integer type)

d - double (double precision floating point type)

s - string

b - BLOB (binary large object: binary large object)

Each parameter requires a specified type.

By telling the database the data type of the parameter, you can reduce the risk of SQL injection.

	Note: If you want to insert other data (user input), validation of the data is very important.

Prepared statements in PDO

In the following example, we use prepared statements and bind parameters in PDO:

Example (PDO uses prepared statements)

<?php $servername = " localhost " ; $username = " username " ; $password = " password " ; $dbname = " myDBPDO " ; try { $conn = new PDO ( " mysql:host= $servername ;dbname= $dbname " , $username , $password ) ; // Set the PDO error mode to exception $conn -> setAttribute ( PDO :: ATTR_ERRMODE , PDO :: ERRMODE_EXCEPTION ) ; // Preprocess SQL and bind parameters $stmt = $conn -> prepare ( " INSERT INTO MyGuests (firstname, lastname, email) VALUES (:firstname, :lastname, :email) " ) ; $stmt -> bindParam ( ' :firstname ' , $firstname ) ; $ stmt -> bindParam ( ' :lastname ' , $lastname ) ; $stmt -> bindParam ( ' :email ' , $email ) ; // Insert row $firstname = " John " ; $lastname = " Doe " ; $email = " [email protected] " ; $stmt -> execute ( ) ; // Insert other rows $firstname = " Mary " ; $lastname = " Moe " ; $email = " [email protected] " ; $stmt -> execute ( ) ; // Insert other rows $firstname = " Julie " ; $lastname = " Dooley " ; $email = " [email protected] " ; $stmt -> execute ( ) ; echo " New record inserted successfully " ; } catch ( PDOException $e ) { echo " Error: " . $e -> getMessage ( ) ; } $conn = null ; ?>