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.
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.
Using MySQLi
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.
The $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:
Character | Type |
---|---|
b | BLOB |
d | Double |
i | Integer |
s | String |
In order for the results to come out however, the statement must be executed using the mysqli_stmt_execute
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
parameter placeholders.
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 $result = $prepared->execute(array(":first_name" => $firstName, ":second_name" => $surname)); ?>
One can then check if the execution of the statement was successful or not using a standard if statement:
<?php if ($result){ //Execute some code here } ?>
Finally, iterating the result is easy too. Assuming that the prepared
variable has been used the code would look like:
<?php while ($row = $prepared->fetch(PDO::FETCH_ASSOC)){ print_r($row); } ?>
Note the PDO::FETCH_ASSOC
constant which will return an associative array
as opposed to a PDO object.
Below is the complete program:
<?php $pdo = new PDO("mysql:dbname=database1;host=localhost", "user1", "password1"); $statement = "SELECT * FROM db WHERE forename=:first_name AND surname=:second_name"; $prepared = $pdo->prepare($statement); $result = $prepared->execute(array(":first_name" => $firstName, ":second_name" => $surname)); if ($result){ //Iterate the result while ($row = $prepared->fetch(PDO::FETCH_ASSOC)){ print_r($row); } } ?>