PHP Prepared Statements

In a previous section, you saw one way to add a new record to the database. We used the INSERT statement to do this:

$SQL = "INSERT INTO tbl_address_book (First_Name, Surname, Address) VALUES ('Paul', 'McCartney', 'Penny Lane')";

However, the above SQL uses hard-coded values ('Paul', 'McCartney', 'Penny Lane'). What usually happens, though, is that you have textboxes where people can enter details. When a button is clicked these VALUES are then used in a SQL statement to add a new record. Before we insert a new record, though, we'll explain Prepared Statements.

So far, we've used SQL that pulled all the records from a database, or SQL that inserted hard-coded values like 'Paul' and 'McCartney'. More often than not, however, this is not what you want to do. What usually happens is that you have textboxes where people can enter details, like usernames, passwords, and email addresses. You then get these values from a HTML Form and do something with them. In this lesson, you'll see how to pull records from a database based on an email typed into a textbox. For this, we'll use something called a Prepared Statement.

 

Prepared Statements

When you ask users to enter details into a textbox and click a button, you are opening your database up to attacks, especially from something called a SQL Injection attack. For example, take this SQL Statement:

$SQL = "SELECT * FROM members WHERE email = '$email' ";

Here, we're selecting all the records from a database table called members. The SQL this time has a WHERE clause added. The WHERE clause is used when you want to limit the results to only records that you need. After the word "WHERE", you type a column name from your database (email, in our case). You then have an equals sign, followed by the value you want to check. The value we want to check is coming from the variable called $email. This is surrounded with single quotes.

When an email address is entered in the text box on our form, this value goes straight into the variable without any checks. An attacker could type something else into the textbox, trying to manipulate your SQL statement. They could add a DELETE part to delete records from your database, or a DROP clause to delete the entire database itself. There are lots of ways an attacker could inject SQL into your code. So you need to defend yourself against attacks.

To prevent SQL Injection attacks like these, you can use a Prepared Statement. Let's see how they work.

 

The PHP Scripts

Along with the database folder amongst the files you downloaded, there is a PHP script called testPrep.php (in the scripts folder). We'll use this script, and the database, to teach you about prepared statements.

Open up the testPrep.php file and you'll see some PHP at the top and a FORM in the BODY section of the HTML. The form is just this:

<FORM NAME ="form1" METHOD ="POST" ACTION ="testPrep.php">

email address <INPUT TYPE = 'TEXT' Name ='email' value="<?PHP print $email ; ?>">

<INPUT TYPE = "Submit" Name = "Submit1" VALUE = "Login">

</FORM>

It's a simple form, with a textbox and a button. The textbox is for an email address. When a correct email address is entered, we'll print out a row from the database. The row contains an ID number, a username, a password, and the email address. So we're querying the database table to see if the email address entered into the textbox matches an email address from the table.

In fact, test it out. Load up the testPrep.php into your web browser. Enter the following email address into the textbox:

test1@test1.com

Now click the button. If you're connected to your server, you should the following printed out:

1
test1
test1
test1@test1.com

(If you're getting database errors, make sure you have your configure.php file in the right place, as explained in a previous section.)

So the ID from the returned row is 1, the username and password are both test1, and the email address is test1@test1.com. Now let's look at the PHP code.

<?PHP

$email = "";

if (isset($_POST['Submit1'])) {

require '../configure.php';
$email = $_POST['email'];

$database = "membertest";
$db_found = new mysqli(DB_SERVER, DB_USER, DB_PASS, $database );

if ($db_found) {

$SQL = $db_found->prepare('SELECT * FROM members WHERE email = ?');
$SQL->bind_param('s', $email);
$SQL->execute();

$result = $SQL->get_result();

if ($result->num_rows > 0) {

while ( $db_field = $result->fetch_assoc() ) {

print $db_field['ID'] . "<BR>";
print $db_field['username'] . "<BR>";
print $db_field['password'] . "<BR>";
print $db_field['email'] . "<BR>";

}

}
else {

print "No records found";

}

$SQL->close();
$db_found->close();

}
else {

print "Database NOT Found ";

}

}

?>

The first line to examine is this one:

$email = $_POST['email'];

This just get the text from the textbox on the form. But notice that we're not doing any error checking here to see if it is a valid email address, or that the user hasn't entered anything malicious. (You can do error checking here, if you want. But we'll keep it simple so as not to overcomplicate the code. We're going to be using a prepared statement, so any malicious SQL that has been added will get converted into a string, not a SQL statement.)

The next few lines set up the database username and password, as well as the server and database name. Then we have this line:

$db_found = new mysqli(DB_SERVER, DB_USER, DB_PASS, $database );

This is a new way to connect to the database and server. Previously we used two steps: mysqli_connect, and mysqli_select. Now, we're just using one step: mysqli. In between the round brackets of mysqli, we have four things: the server name, the username, the password, and the name of the database. (The first three are coming from the required file, configure.php. You saw how to set this up in a previous lesson.) Notice the new keyword after the equal sign. You need this to set up a new database object.

Inside of an IF statement, we then have this line:

$SQL = $db_found->prepare('SELECT * FROM members WHERE email = ?');

This is the first line of our prepared statement. We're preparing the SQL that we want to execute on the database table. To the right of an equal sign, we have the name of our database object, $db_found. Because it's an object you need two symbols without any space between them: a dash (-) and greater than symbol (>). Next comes the inbuilt function prepare. In between the round brackets of prepare, you need to type your SQL. We're selecting all the records WHERE a certain condition is met. The curious bit is this:

WHERE email = ?

The question mark is a placeholder, often called a parameter. The email part is the name of a field in our database table. The placeholder, that ?, is going to be replaced with an actual value. We do this on the next line:

$SQL->bind_param('s', $email);

Notice that the $SQL variable is now an object, which is why it has the -> symbols after it. Then we have an inbuilt function called bind_param. As its name suggests, this is used to bind values to those parameters you set up in the prepare function. Between the round brackets of bind_param we have a single letter s surrounded by single quotes. After a comma goes the value you want to bind, in our case this is the value we got from the textbox on the form - the email address. (The variable name doesn't have to match the field name in your database.)

You can bind to more than one value. You may, for example want to check a username as well as the email address. In which case, your prepare function might look like this:

$SQL = $db_found->prepare('SELECT * FROM members WHERE email = ? AND username = ?');

Notice we have an AND part separating the two fields email and username. Each field has its own equal sign and question mark placeholder

The bind_param function would then be this:

$SQL->bind_param('ss', $email, $user);

We have two letter s's now, one for each of the parameters. The $email and $user would be variables that we get from the HTML form.

The 's' is short for string. You may have fields in your database that are numerical, in which case you need and i (for Integer) or d (for double):

$SQL->bind_param('i', $some_integer_value);
$SQL->bind_param('d', $some_double_value);

There's also a 'b', which is short for BLOB:

$SQL->bind_param('b', $some_blob_value);

Once you have bound your parameters, you can go ahead and execute:

$SQL->execute();

To get some results back, you need this:

$result = $SQL->get_result();

The inbuilt function now is get_result. This will return an array of rows from your table.

You can test to see if any row were returned:

if ($result->num_rows > 0) {
}

Here, we use the inbuilt function num_rows. We're testing to see if it's greater than zero. If it is, we have this:

while ( $db_field = $result->fetch_assoc() ) {

print $db_field['ID'] . "<BR>";
print $db_field['username'] . "<BR>";
print $db_field['password'] . "<BR>";
print $db_field['email'] . "<BR>";

}

We're using a while loop to loop round the returned array. The array can be traversed with:

$db_field = $result->fetch_assoc()

The $db_field is just a variable name. You can call it almost anything you like. But $result->fetch_assoc( ) places each row from your database in the variable. You can then do something with each row:

print $db_field['ID'] . "<BR>";
print $db_field['username'] . "<BR>";
print $db_field['password'] . "<BR>";
print $db_field['email'] . "<BR>";

We're just printing out each row.

In the next tutorial, you'll see how to insert and update records using a prepared statement.