Tom Says: Safe code is boring code! Why??
Previous page:
Solving Sudoku in OCaml
Next page:
Shepard Tones
For the following examples, assume that we are connecting to a database called mydatabase with the following tables defined:
table `people`: id: int name: varchar(100) age: int
There are many very simple things that can go wrong when working on a PHP page. Use this checklist to make sure you've covered all the easy cases before getting frustrated:
All basic SQL queries look like one of these:
Some examples of valid SELECT queries:
This query will set everybody's name to Jeff:
UPDATE people SET name = "Jeff"
This query will make the user with ID 4 the name Jeff
UPDATE people SET name = "Jeff" WHERE id=4
Note: the last ?> in a PHP file is not required so long as no HTML needs to come after it.
Notice that there are some commented lines in the examples. These lines are examples of various ways of doing simple debugging. For example, to make sure that you have typed an SQL query correctly, you can echo it to the page; then you can go to the page, copy the query, and paste it to the MySQL server by hand (for example, via phpMyAdmin) to see full error messages. You can also use the function mysql_error() to show error messages resulting from a query.
<?php mysql_connect("localhost", "dbuser", "dbpassword"); mysql_select_db("mydatabase"); $sql = "SELECT id, name, age FROM people"; // echo $sql; $results = mysql_query($sql); // echo mysql_error(); while($row = mysql_fetch_array($results)) { echo "<p>"; echo "User #" . $row['id'] . ":"; echo "Name: " . $row['name'] . ", "; echo "Age: " . $row['age'] . "."; echo "</p>"; }
All variables persist through transitions between PHP and MySQL, and PHP blocks surrounded by { } can even have HTML in them.
<?php
mysql_connect("localhost", "dbuser", "dbpassword");
mysql_select_db("mydatabase");
$sql = "SELECT id, name, age FROM people";
// echo $sql;
$results = mysql_query($sql);
// echo mysql_error();
while($row = mysql_fetch_array($results)) {
?>
<p>User #<?php echo $row['id']; ?>:
Name: <?php echo $row['name']; ?>,
Age: <?php echo $row['age']; ?>.</p>
<?php
}
<?php
mysql_connect("localhost", "dbuser", "dbpassword");
mysql_select_db("mydatabase");
$sql = "SELECT name FROM people WHERE id=1";
// echo $sql;
$result = mysql_query($sql);
// echo mysql_error();
$name = mysql_result($result, 0, 0);
echo "<p>Name: $name</p>";
You have some freedom with the data you embed in the end of a PHP page's URL. For example, all of these URLs point to the same PHP page, page.php:
Even though all of the URLs point to the same PHP file, some of the URLs pass special variables to the page. They are accessible through the $_GET array. With URL #2, $_GET['id'] is "4", with URL #3, $_GET['name'] is "jeff", and with URL #4, $_GET['action'] is "search" and $_GET['query'] is "mom".
You can use these variables to make SQL queries specific for that page:
<?php
// this page is requested with a URL like: page.php?name=jeff
mysql_connect("localhost", "dbuser", "dbpassword");
mysql_select_db("mydatabase");
$sql = "SELECT id FROM people WHERE name='" . $_GET['name'] . "'";
// echo $sql;
$result = mysql_query($sql);
// echo mysql_error();
$id = mysql_result($result, 0, 0);
echo "<p>User #" . $id . "'s name is " . $_GET['name'] . "</p>";
It's worth taking a closer look at the way the SQL query is put together:
$sql = "SELECT id FROM people WHERE name='" . $_GET['name'] . "'";
Say that someone visits our page with page.php?name=jeff. If you were writing the command by hand, you might write the query like this:
SELECT id FROM people WHERE name='jeff'
… but you're not writing this by hand; you're writing PHP to build the query. That's why we have to build the query in parts and put them back together. The following code does pretty much the same thing as before:
$part1 = "SELECT id FROM people WHERE name='"; $part2 = $_GET['name']; $part3 = "'"; $sql = $part1 . $part2 . $part3;
Note that the single quotes at the end of part 1 and in part 3 are mandatory, especially if the value is not a number. Imagine what would happen if someone went to the URL page.php?name=jeff+stanton (the '+' is interpreted as a space). Without the quotes, the query would look like this:
SELECT id FROM people WHERE name=jeff stanton
That is not value SQL! The query is epic fail! (Try running it by hand to see why!)
Furthermore, on a real, live web site, you would have to do even more to make this code safe. To see why, imagine that someone created a URL so that $_GET['name'] was "jeff o'connor". Imagine what the query would look like:
SELECT id FROM people WHERE name='jeff o'connor'
That query also contains a lot of fail. As far as SQL is concerned, name is just "jeff o" (because that's where the quoted string ends) and then tries to read "connor'" as some kind of SQL, which it's not.
The solution is to use the function mysql_real_escape_string, which makes sure that user input doesn't have any special characters that could mess up the query (and makes them safe if they do). It is used like this:
$sql = "SELECT id FROM people WHERE name='" . mysql_real_escape_string($_GET['name']) . "'";
I cannot stress enough how vital this is for all PHP pages that use variables provided by the user in the URL, or a form.
Posted Jul 20, 2008, in the evening.