PHP - English

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:

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


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”>


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:

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


(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 Now let’s look at the PHP code.


$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);

$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”;



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:


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


Kaynak : ‘sitesinden alıntı

Yorum Yap