Latest web development tutorials

PHP MySQL prepared statements

Prepared statements to prevent MySQL injection is very useful.


Prepared statements and bound parameters

Prepared statements for executing a plurality of the same SQL statement, and execute more efficiently.

Works of prepared statements as follows:

  1. Pretreatment: Create a template SQL statement sent to the database. The value of the parameter reserved "?" Mark. E.g:

    INSERT 
    	INTO MyGuests (firstname, lastname, email) VALUES(?, ?, ?)
  2. Database analysis, compile, execute SQL statements template query optimization, and stores the result is not output.

  3. Execution: Finally, the values ​​of the parameters passed to the application binding (mark "?"), Database executes the statements. Applications can be executed many times, if the parameter value is not the same.

Compared to the direct execution of SQL statements, prepared statement has two main advantages:

  • Prepared statements dramatically reduces analysis time, only a query (although the statements are executed).

  • Bound parameters to reduce the bandwidth of the server, you only need to send a parameter query instead of the entire statement.

  • Prepared statements against SQL injection is very useful, because after use different protocols to send parameter values ​​to ensure the legitimacy of the data.


MySQLi prepared statements

The following example uses the MySQLi in a prepared statement, and bind the corresponding parameters:

Examples (MySQLi using prepared statements)

<? Php
$ Servername = "localhost";
$ Username = "username";
$ Password = "password";
$ Dbname = "myDB";

// Create connection
$ Conn = new mysqli ($ servername, $ username, $ password, $ dbname);

// Test connection
if ($ conn-> connect_error) {
die ( "Connection failed:" $ conn-> connect_error.);
}

// Pretreatment and binding
$ Stmt = $ conn-> prepare ( "INSERT INTO MyGuests (firstname, lastname, email) VALUES (?,,)??");
$ Stmt-> bind_param ( "sss", $ firstname, $ lastname, $ email);

// Set parameters and perform
$ Firstname = "John";
$ Lastname = "Doe";
$ Email = "john@example.com";
$ Stmt-> execute ();

$ Firstname = "Mary";
$ Lastname = "Moe";
$ Email = "mary@example.com";
$ Stmt-> execute ();

$ Firstname = "Julie";
$ Lastname = "Dooley";
$ Email = "julie@example.com";
$ Stmt-> execute ();

echo "The new record is inserted successfully";

$ Stmt-> close ();
$ Conn-> close ();
?>

Parsing each line of code in the following examples:

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

In SQL statements, we use the question mark (?), Here we can replace the question mark integer, string, double-precision floating-point, and Boolean values.

Next, let us look at bind_param () function:

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

The function bind SQL parameters, and tell the value of the database parameter. "Sss" column processing parameter data types for the remaining parameters. s character tells the database that the parameter string.

There are four types of parameters:

  • i - integer (integer)
  • d - double (double precision floating point)
  • s - string (string)
  • b - BLOB (binary large object: binary large objects)

Each parameter is required to specify the type.

The data type parameter tells the database, you can reduce the risk of SQL injection.

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


PDO prepared statements in

The following examples we use prepared statements in PDO and binding parameters:

Examples (PDO using 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 exception
$ Conn-> setAttribute (PDO :: ATTR_ERRMODE, PDO :: ERRMODE_EXCEPTION);

// Pretreatment and SQL 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 = "john@example.com";
$ Stmt-> execute ();

// Insert another row
$ Firstname = "Mary";
$ Lastname = "Moe";
$ Email = "mary@example.com";
$ Stmt-> execute ();

// Insert another row
$ Firstname = "Julie";
$ Lastname = "Dooley";
$ Email = "julie@example.com";
$ Stmt-> execute ();

echo "The new record is inserted successfully";
}
catch (PDOException $ e)
{
.. Echo $ sql "<br>" $ e-> getMessage ();
}
$ Conn = null;
?>