Preparing MySQL statements in PHP
Databases are one of the reasons that many websites end up with security loopholes.
PHP being a server side language can protect against these loopholes using prepared statements.
A prepared statement consists of three steps:
- A statement is generated
- MySQL parses and optimises then compiles the statement. It then stores this statement.
- The statement is given parameters and executed.
Prepared statements are generally written like so:
<?php //Use the following command to connect $myConnection = mysqli_connect($dbName, $dbUsername, $dbPassword); $statement = "SELECT * FROM db WHERE id=? AND id forename=?"; $prepared = mysqli_prepare ($myConnection, $statement); ?>
The important part to note is the assignment of the
$statement variable. In the value of this statement, there
is a question mark (?). This represents where parameters go.
$prepared variable is used to store the link to the prepared statement.
Putting in parameters using MySQLi
PHP provides the
mysqli_stmt_bind_param function to bind parameters to a prepared statement. This
function takes in at least two arguments. The first is the prepared statement, the second is a string of types whilst the
third, fourth, fifth and so on are the parameter values:
<?php mysqli_stmt_bind_param ($prepared, "is", 3, "John"); ?>
Here, the string
"is" represents the types of the variables - i.e. integer then string.
The following table shows all of the types that are supported:
In order for the results to come out however, the statement must be executed using the
function. This then leaves the result within the original call to the database.
<?php mysqli_stmt_execute ($prepared); ?>
Here are another two examples:
<?php $myConnection = mysqli_connect($dbName, $dbUsername, $dbPassword); $statement = "SELECT * FROM db WHERE forename=? AND surname=?"; $prepared = mysqli_prepare ($myConnection, $statement); mysqli_stmt_bind_param ($prepared, "ss", $firstName, $surname); $firstname = "John"; $surname = "Smith"; mysqli_stmt_execute ($prepared); $statement = "SELECT * FROM db WHERE forename=? AND surname=? AND id>?"; $prepared = mysqli_prepare ($myConnection, $statement); mysqli_stmt_bind_param ($prepared, "ssi", $firstname, $surname, $id); $firstname = "John"; $surname = "Smith"; $id = 3; mysqli_stmt_execute ($prepared); ?>
Now queries that are run on the
$myConnection variable will run on that prepared statement.
Using PDO (the preferred option)
PDO or PHP Data Objects is the preferred option for preparing statemts and it's much easier to work with too.
The first step is to create and store a PDO object instance. This will contain the link to the database within it:
<?php $pdo = new PDO("mysql:dbname=database1;host=localhost", "user1", "password1"); ?>
In the above, replace database1, localhost, user1 and password1 with correct database connection details.
The object also provides many different methods that perform database manipulation operations.
Putting in parameters using PDO
PHP's PDO object provides the
prepare method to prepare
Unlike in MySQLi however, PDO does not use the
? and expect a type to be
given. Further PDO queries do not even require the values be inserted in order.
In PDO, paramters are represented by their preceeded by a colon (:):
<?php $statement = "SELECT * FROM db WHERE forename=:first_name AND surname=:second_name"; ?>
Next prepare the statement using the
prepare method within the PDO object instance:
<?php $prepared = $pdo->prepare($statement); ?>
The final step is to execute it. The
execute method on the prepared statement
is used for this and requires an associative array which contains the placeholder name as the key and
the value as the, well, the value in the array:
<?php $prepared->execute(array(":first_name" => $firstName, ":second_name" => $surname)); ?>