last updated: --Wed Nov 1 10:19:03 EST 2006--
page owned by: bridgeman@hws.edu
| CPSC 343 | Database Theory and Practice | Fall 2004 |
This document is intended to be a quick start to using PHP to connect to MySQL. Refer to the PHP Language Quick Start for information on the PHP language itself and integrating PHP into web pages, the PHP Web Quick Start for information on getting information about the web server environment, processing HTML forms, and working with sessions and cookies, or the PHP Local Quick Start for local details such as where to actually put your PHP pages so you can try them out.
Many details are omitted or simplified in these QuickStarts (e.g. not all syntax variations are presented). For more detailed and extensive information, check out one of the links below:
This Quick Start describes using the mysqli (MySQL Improved Extension) for connecting to MySQL.
There are four basic steps for connecting to a database:
Establish a connection to the database.
This is done with the mysqli_connect function, which takes the database host, username, password, and database to use as parameters. (about mysqli_connect) For example:
<?php
$link = mysqli_connect("sbridgem","guest","guestpass","sailors");
?>
The return value (stored in $link) can be tested to determine whether or not the connection was successfully established. (See the full example below for how to do this.) The connection can fail if there is no MySQL server on the named host, if the username or password are not correct or the user doesn't have access to the named database, or if the database doesn't exist.
Send the query to the database.
This is done with the mysqli_query function, which takes the database connection and the query to execute as parameters. (about mysqli_query) For example:
<?php
$query = "SELECT S.Sid,S.Sname,B.Bid,B.Bname,R.Day FROM BOAT B ".
"NATURAL JOIN RESERVATION R NATURAL JOIN SAILOR S ".
"ORDER BY S.Sid ASC, B.Bid ASC,R.Day ASC";
$result = mysqli_query($link,$query);
?>
The return value (stored in $result) can be tested to determine whether or not the query was successful. (See the full example below for how to do this.) The query can fail if the query contains a syntax error, the named tables and/or columns don't exist, or if the user doesn't have permission to carry out the query.
Process the results.
There are a number of functions which retrieve information about the results of the query. (about mysqli_fetch_assoc) The following example uses mysqli_fetch_assoc to retrieve rows of the result table and format them using an HTML table:
<table border=1 cellpadding=2>
<tr>
<th>Sailor ID</th>
<th>Sailor Name</th>
<th>Boat Name</th>
<th>Boat ID</th>
<th>Reservation Date</th>
</tr>
<?php
// print results
while ( $row = mysqli_fetch_assoc($result) ) {
print "<tr>";
print "<td>".$row["Sid"]."</td>";
print "<td>".$row["Sname"]."</td>";
print "<td>".$row["Bname"]."</td>";
print "<td>".$row["Bid"]."</td>";
print "<td>".$row["Day"]."</td>";
print "</tr>\n";
}
?>
</table>
Each call to mysqli_fetch_assoc returns the next row in the result table, and the return value evaluates to false if there are no more rows to retrieve. () The row is returned as an array indexed by the column names of the result table.
Free the query results and close the connection.
mysqli_free_result frees the result (only necessary if results were returned e.g. for SELECT queries but not INSERT queries), and mysqli_close closes the connection: (about mysqli_free_result, about mysqli_close)
<?php mysqli_free_result($result); mysqli_close($link); ?>
All of these elements come together, along with error-checking, in allreservations.php (which displays all of the reservation information in the sailors database):
<html>
<head>
<title>Sailor Reservations</title>
</head>
<body>
<h1>Sailor Reservations</h1>
<?php
// establish connection
$link = mysqli_connect("sbridgem","guest",
"guestpass","sailors"); /* dangerous! - password
is stored in plain
text */
// check connection
if ( !$link ) {
?>
<blockquote>
<p><b>Connection failed.</b><br>
<?php print mysqli_connect_error(); ?></p> <!-- dangerous! - error
message can reveal
information of use to
an attacker -->
</blockquote>
<?php
} else { // connection is good...
$query = "SELECT S.Sid,S.Sname,B.Bid,B.Bname,R.Day FROM BOAT B ".
"NATURAL JOIN RESERVATION R NATURAL JOIN SAILOR S ".
"ORDER BY S.Sid ASC, B.Bid ASC,R.Day ASC";
$result = mysqli_query($link,$query);
// check success
if ( !$result ) {
?>
<blockquote>
<p><b>Error executing query.</b><br>
<?php print mysqli_error($link); ?></p> /* dangerous! - error
message can reveal
information of use to
an attacker */
</blockquote>
<?php
} else {
// format the results nicely in a table
?>
<table border=1 cellpadding=2>
<tr>
<th>Sailor ID</th>
<th>Sailor Name</th>
<th>Boat Name</th>
<th>Boat ID</th>
<th>Reservation Date</th>
</tr>
<?php
// print results
while ( $row = mysqli_fetch_assoc($result) ) {
print "<tr>";
print "<td>".$row["Sid"]."</td>";
print "<td>".$row["Sname"]."</td>";
print "<td>".$row["Bname"]."</td>";
print "<td>".$row["Bid"]."</td>";
print "<td>".$row["Day"]."</td>";
print "</tr>\n";
}
?>
</table>
<?php
// free result when we are done
mysqli_free_result($result);
}
// close link
mysqli_close($link);
}
?>
</body>
</html>
(about mysqli_connect_error, about mysqli_error)
User-supplied information (such as what comes from a form) can be integrated into the query - just concatenate the appropriate values into the appropriate query text. Keep in mind that all non-integer values must be surrounded by single quotes (and you shouldn't make the user enter the quotes). For example, the following example contains:
$query = "SELECT B.Bname,B.Bid,R.Day FROM BOAT B NATURAL JOIN ".
"RESERVATION R NATURAL JOIN SAILOR S WHERE S.Sname='".
$_POST["sailor"]."' ORDER BY B.Bid ASC, R.Day ASC";
Pay close attention to the single quotes following S.Sname= and just before ORDER BY - these surround the sailor name value taken from the form.
Note: if you include user-supplied information in a query, be sure to check out both the Subtleties and Security sections below in order to allow certain (valid) input to work and to protect against SQL injection attacks. The following example does not take such precautions!
The following (reservationquery.html) presents a form where the user can enter a username and password for connecting to the database as well as the name of the particular sailor of interest:
<html>
<head>
<title>Sailor Reservation Query</title>
</head>
<body>
<h1>Sailor Reservation Query</h1>
<form action="reservationquery.php" method="POST">
<table border=0 cellpadding=3>
<tr><td>Username:</td><td><input type="text" name="username"></td></tr>
<tr><td>Password:</td><td><input type="password" name="password"></td></tr>
<tr><td>Display reservations for:</td>
<td><input type="text" name="sailor"> (sailor name)</td></tr>
</table>
<input type="submit" value="get reservations">
<input type="reset" value="clear">
</form>
</body>
</html>
reservationquery.php executes the query and displays the results:
<html>
<head>
<title>Query Results</title>
</head>
<body>
<h1>Query Results</h1>
<p>Reservations for <?php print $_POST["sailor"]; ?>:</p>
<?php
// establish connection
$link = mysqli_connect("sbridgem",$_POST["username"],
$_POST["password"],"sailors");
// check connection
if ( !$link ) {
?>
<blockquote>
<p><b>Connection failed.</b><br>
<?php print mysqli_connect_error(); ?></p> <!-- dangerous! - error
message can reveal
information of use to
an attacker -->
</blockquote>
<?php
} else { // connection is good...
$query = "SELECT B.Bname,B.Bid,R.Day FROM BOAT B NATURAL JOIN ".
"RESERVATION R NATURAL JOIN SAILOR S WHERE S.Sname='".
$_POST["sailor"]."'";
/* dangerous! - user input is used without any validity checking */
$result = mysqli_query($link,$query);
// check success
if ( !$result ) {
?>
<blockquote>
<p><b>Error executing query.</b><br>
<?php print mysqli_error($link); ?></p> <!-- dangerous! - error
message can reveal
information of use to
an attacker -->
</blockquote>
<?php
} else {
// format the results nicely in a table
?>
<table border=1 cellpadding=2>
<tr><th>Boat Name</th><th>Boat ID</th><th>Reservation Date</th></tr>
<?php
// print results
while ( $row = mysqli_fetch_assoc($result) ) {
print "<tr>";
print "<td>".$row["Bname"]."</td>";
print "<td>".$row["Bid"]."</td>";
print "<td>".$row["Day"]."</td>";
print "</tr>\n";
}
?>
</table>
<?php
// free result when we are done
mysqli_free_result($result);
}
// close link
mysqli_close($link);
}
?>
</body>
</html>
Certain characters, like single quotes, have a special meaning to MySQL - but also may occur within the value of an attribute. For example, consider the query for finding reservations for a given sailor:
<?php
$query = "SELECT B.Bname,B.Bid,R.Day FROM BOAT B NATURAL JOIN ".
"RESERVATION R NATURAL JOIN SAILOR S WHERE S.Sname='".
$_POST["sailor"]."'";
?>
If the user enters the name Dustin, there's no problem - $_POST["sailor"] has the value Dustin and substituting that into the query yields
SELECT B.Bname,B.Bid,R.Day FROM BOAT B NATURAL JOIN RESERVATION R NATURAL JOIN SAILOR S WHERE S.Sname='Dustin'
This is a legal query. But what if the user enters D'Anna as the sailor name? Substituting that into the query yields
SELECT B.Bname,B.Bid,R.Day FROM BOAT B NATURAL JOIN RESERVATION R NATURAL JOIN SAILOR S WHERE S.Sname='D'Anna'
This is no longer legal because the apostrophe in "D'Anna" matches with the first single quote surrounding the sailor name.
The solution is to escape any potentially special characters in the user input so that they are treated as literals rather than as special characters. Exactly what is special depends on the database; for MySQL, the function mysqli_real_escape_string() does the appropriate escaping. (about mysqli_real_escape_string) Thus, a better way to write the PHP line above is the following:
<?php
$sailor = mysqli_real_escape_string($link,$_POST["sailor"]);
$query = "SELECT B.Bname,B.Bid,R.Day FROM BOAT B NATURAL JOIN ".
"RESERVATION R NATURAL JOIN SAILOR S WHERE S.Sname='".$sailor."'";
?>
It is not necessary to create a new $sailor variable, but doing so makes the second line more readable.
The special symbols % and _ occur in LIKE conditions. They aren't, however, escaped by mysqli_real_escape_string and thus will be interpreted as special characters instead of literals if they appear in user input. Use addcslashes to escape these characters: (about addcslashes)
<?php
$sailor = addcslashes(mysqli_real_escape_string($link,$_POST["sailor"]),
"%_");
$query = "SELECT B.Bname,B.Bid,R.Day FROM BOAT B NATURAL JOIN ".
"RESERVATION R NATURAL JOIN SAILOR S WHERE S.Sname LIKE '".$sailor."%'";
?>
The first parameter to addcslashes is the string to be processed; the second parameter is a string containing the character to be escaped. addcslashes must be done after mysqli_real_escape_string so that mysqli_real_escape_string won't try to escape the backslashes that addcslashes adds. This particular query finds reservations for all sailors whose name starts with the value the user entered in the form.
One level of security is that provided by the database itself. MySQL provides user accounts and allows fine-grained control over exactly what actions a given account is allowed on a given database or table. While you will not have access to create your own accounts and adjust permissions, this is an important thing to factor into your design in "real world" applications. This can help prevent a number of the attacks described below by simply denying the user permission to carry them out.
Another level of security is provided by the program accessing the database, which can perform checks to ensure that inappropriate requests are not carried out. One of the most common vulnerabilities is SQL injection - where a user inserts arbitrary (and generally unintended) data into an SQL query, gaining unauthorized access to the database. Vulnerability to SQL injection is the result of insufficient validation of user-supplied input.
To understand how injection can occur and what some of the consquences might be, consider the original sailor reservations example again:
<?php
$query = "SELECT B.Bname,B.Bid,R.Day FROM BOAT B NATURAL JOIN ".
"RESERVATION R NATURAL JOIN SAILOR S WHERE S.Sname='".
$_POST["sailor"]."'";
?>
The intent, of course, is that the user entered the name of a sailor into the appropriate box on the form. But what if the user entered Dustin'; DELETE FROM RESERVATION WHERE Sid<>' instead? Substituting that value in yields
SELECT B.Bname,B.Bid,R.Day FROM BOAT B NATURAL JOIN RESERVATION R NATURAL JOIN SAILOR S WHERE S.Sname='Dustin'; DELETE FROM RESERVATION WHERE Sid<>''
These are two perfectly legal queries...and not what the person writing the reservation query page had in mind! (Imagine if a table storing passwords was substituted for RESERVATION...)
This particular case is not a problem in MySQL because the mysqli_query() function does not permit more than one query to be executed in a single function call. However, what if the user instead enters Dustin' UNION SELECT 'Swift',S.Sid,'1900-01-01' FROM SAILOR S WHERE S.Sid<>' for the sailor name? Substituting that value in yields
SELECT B.Bname,B.Bid,R.Day FROM BOAT B NATURAL JOIN RESERVATION R NATURAL JOIN SAILOR S WHERE S.Sname='Dustin' UNION SELECT 'Swift',S.Sid,'1900-01-01' FROM SAILOR S WHERE S.Sid<>''
This is a single legal query which carries out the intended reservations-for-Dustin query but also includes information about all of the sailor IDs in the database. (Imagine if a table storing passwords was accessed instead of SAILOR...)
This problem can be avoided by escaping all non-numeric user input, as described in the Subtleties above. If the sailor name was escaped before plugging it into the query:
<?php
$sailor = mysqli_real_escape_string($link,$_POST["sailor"]);
$query = "SELECT B.Bname,B.Bid,R.Day FROM BOAT B NATURAL JOIN ".
"RESERVATION R NATURAL JOIN SAILOR S WHERE S.Sname='".$sailor."'";
?>
the result would be a search for reservations for the sailor whose name is Dustin' UNION SELECT 'Swift',S.Sid,'1900-01-01' FROM SAILOR S WHERE S.Sid<>'...which doesn't match any sailor.
Similar attacks are possible with UPDATE or DELETE queries, where an attacker could adjust the WHERE conditions.
Tip #1: Always properly escape user input.
A related example involves numeric input. Consider searching for reservations by sailor ID:
<?php
$sailor = mysqli_real_escape_string($link,$_POST["sailor"]);
$query = "SELECT B.Bname,B.Bid,R.Day FROM BOAT B NATURAL JOIN ".
"RESERVATION R NATURAL JOIN SAILOR S WHERE S.Sid=".$sailor;
?>
Note that the single quotes are left off because $sailor is a numeric value. Even properly escaped, this is vulnerable to a similar attack - just enter 22 UNION SELECT 'Swift',S.Sid,'1900-01-01' FROM SAILOR S WHERE S.Sid<> for the sailor ID to reveal all of the sailor IDs (in addition to the reservations for sailor 22).
A solution in this case is to use single quotes around every bit of user input (it is legal to use single quotes for numbers):
<?php
$sailor = mysqli_real_escape_string($link,$_POST["sailor"]);
$query = "SELECT B.Bname,B.Bid,R.Day FROM BOAT B NATURAL JOIN ".
"RESERVATION R NATURAL JOIN SAILOR S WHERE S.Sid='".$sailor."'";
?>
In the reservations-by-sailor-ID example, putting single quotes around the numeric $sailor value and properly escaping it will result in a query returning an empty table...taking up database time as the query is carried out. This can be avoided by having PHP check that a numeric value is, in fact, numeric using the is_numeric function: (about is_numeric)
<?php
if ( is_numeric($sailor) ) {
$query = "SELECT B.Bname,B.Bid,R.Day FROM BOAT B NATURAL JOIN ".
"RESERVATION R NATURAL JOIN SAILOR S WHERE S.Sid='".$sailor."'";
} else {
// error message
}
?>
Tip #2: Always check the type of numeric input.
Another denial-of-service attack can come from the % and _ wildcards used with LIKE. If not escaped (as described in the Subtleties section above), MySQL will treat them as wildcards. This can be an issue because the placement of % and _ in the LIKE condition has an effect on how quickly a query can be processed - allowing a user control over wildcards means a malicious user could choose a placement designed to slow down the database server. The solution is to also escape all % and _ symbols (using addcslashes) when user input is involved in a LIKE condition.
Some attacks require knowing something about the database schema (column names, column types, table names, etc). To help thwart such attacks, you should avoid revealing information about the database schema in error messages.
This means that things like show_source(), mysqli_connect_error(), and mysqli_error() - which are very useful for debugging - should not be present in a final production website. (about show_source, about mysqli_connect_error, about mysqli_error) Instead, a production website should use a mechanism like error_log to log a detailed error message in a developer-accessible place while presenting only a "safe" generic message to the user. (about error_log)
For example:
<?php
if ( !$link ) {
error_log("[".$_SERVER["PHP_SELF"]."] error: (connection failed) ".
mysqli_connect_error());
?>
<blockquote>
<p><b>Connection failed.</b></p>
</blockquote>
Here is a revised version of the reservations-for-a-particular-sailor page, taking into account all of the things mentioned in Subtleties and Security. Only reservationsquery2.php is shown; reservationsquery2.html is identical to reservationsquery.html except for the name of the script in the form's "action" attribute.
<html>
<head>
<title>Query Results</title>
</head>
<body>
<h1>Query Results</h1>
<p>Reservations for <?php print $_POST["sailor"]; ?>:</p>
<?php
// establish connection
$link = mysqli_connect("sbridgem",$_POST["username"],
$_POST["password"],"sailors");
// check connection
if ( !$link ) {
error_log("[".$_SERVER["PHP_SELF"]."] error: (connection failed) ".
mysqli_connect_error());
?>
<blockquote>
<p><b>Connection failed.</b></p>
</blockquote>
<?php
} else { // connection is good...
$sailor = mysqli_real_escape_string($link,$_POST["sailor"]);
$query = "SELECT B.Bname,B.Bid,R.Day FROM BOAT B NATURAL JOIN ".
"RESERVATION R NATURAL JOIN SAILOR S WHERE S.Sname='".$sailor."'";
$result = mysqli_query($link,$query);
// check success
if ( !$result ) {
error_log("[".$_SERVER["PHP_SELF"]."] error: (executing query) ".
mysqli_error($link));
?>
<blockquote>
<p><b>Error executing query.</b></p>
</blockquote>
<?php
} else {
// format the results nicely in a table
?>
<table border=1 cellpadding=2>
<tr><th>Boat Name</th><th>Boat ID</th><th>Reservation Date</th></tr>
<?php
// print results
while ( $row = mysqli_fetch_assoc($result) ) {
print "<tr>";
print "<td>".$row["Bname"]."</td>";
print "<td>".$row["Bid"]."</td>";
print "<td>".$row["Day"]."</td>";
print "</tr>\n";
}
?>
</table>
<?php
// free result when we are done
mysqli_free_result($result);
}
// close link
mysqli_close($link);
}
?>
</body>
</html>
|
|