PHP Prepared Statements
Preprocessed statements are very useful for preventing MySQL injection.
Prepared statements and binding parameters
Prepared statements are used to execute multiple identical SQL statements and are more efficient.
The prepared statement works as follows:
Preprocessing: Create a SQL statement template and send it to the database. Reserved valuesb are marked with the parameter "?". For example:
INSERT INTO MyGuests ( firstname , lastname , email ) < span class = "pln"> VALUES (?, ?, < /span> ?)
Database analysis, compilation, query optimization on SQL statement templates, and storage of results without output.
Execute: Finally, pass the value of the application binding to the parameter ("?" tag), and the database executes the statement. The application can execute the statement multiple times if the values of the parameters are different.
Compared to executing SQL statements directly, prepared statements have two main advantages:
Prepared statements greatly reduce analysis time, and 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, not the entire statement.
Prepared statements are very useful for SQL injection, because the parameter values are sent using different protocols to ensure the legality of the data.
MySQLi prepared statements
The following example uses prepared statements in MySQLi and binds the corresponding parameters:
Example (MySQLi uses prepared statements)
Parse each line of code for the following examples:
In the SQL statement, we used a question mark (?), where we can replace the question mark with an integer, string, double-precision floating-point, and Boolean value.
Next, let's look at the bind_param () function:
This function binds SQL parameters and tells the database parameter values. 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)
- d-double (double precision floating point)
- s- string
- b-BLOB (binary large object: binary large object)
Each parameter requires a type.
By telling the database parameter the data type, 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.|
Preprocessed statements in PDO
In the following example, we use prepared statements and bind parameters in PDO: