Welcome to my website. My name is Jamie Balfour and I am a computer scientist and a teacher. My website focuses on me but I also write articles and reviews on and related to technology as well as tutorials designed to help you learn things I know.
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:
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.
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.
Retweet@MetroUK: 'My black lab ran off last night... look who I find him with.'😂🐐 https://t.co/ds4ymTxnn2
7 hours ago
Sad about @andy_murray but still exceptionally proud of his achievements! Loved every moment (some were extremely i… https://t.co/zvsmM4u9IJ
11 days ago
I don't even have the slightest bit of trust for this man. https://t.co/z0FYRms3GR
19 days ago
19 days ago