Apache Security - Ivan Ristic [153]
. $_REQUEST["customerid"];
$result = mysql_query($query);
if (!$result) {
die("Failed to execute query [$query]: " . mysql_error( ));
}
// show the result
while ($row = mysql_fetch_assoc($result)) {
echo "USERNAME = " . $row["username"] . "
";
}
// close the connection
mysql_close( );
?>
This script might be written by a programmer who does not know about SQL injection attacks. The script is designed to accept the customer ID as its only parameter (named customerid). Suppose you request a page using the following URL:
http://www.example.com/view_customer.php?customerid=1
The PHP script will retrieve the username of the customer (in this case, ivanr) and display it on the screen. All seems well, but what we have in the query in the PHP file is the worst-case SQL injection scenario. The customer ID supplied in a parameter becomes a part of the SQL query in a process of string concatenation. No checking is done to verify that the parameter is in the correct format. Using simple URL manipulation, the attacker can inject SQL commands directly into the database query, as in the following example:
http://www.example.com/view_customer.php?customerid=1%20OR%20customerid%3D2
If you specify the URL above, you will get two usernames displayed on the screen instead of a single one, which is what the programmer intended for the program to supply. Notice how we have URL-encoded some characters to put them into the URL, specifying %20 for the space character and %3D for an equals sign. These characters have special meanings when they are a part of a URL, so we had to hide them to make the URL work. After the URL is decoded and the specified customerid sent to the PHP program, this is what the query looks like (with the user-supplied data emphasized for clarity):
SELECT username FROM customers WHERE customerid = 1 OR customerid=2
This type of SQL injection is the worst-case scenario because the input data is expected to be an integer, and in that case many programmers neglect to validate the incoming value. Integers can go into an SQL query directly because they cannot cause a query to fail. This is because integers consist only of numbers, and numbers do not have a special meaning in SQL. Strings, unlike integers, can contain special characters (such as single quotation marks) so they have to be converted into a representation that will not confuse the database engine. This process is called escaping and is usually performed by preceding each special character with a backslash character. Imagine a query that retrieves the customer ID based on the username. The code might look like this:
$query = "SELECT customerid FROM customers WHERE username = '"
. $_REQUEST["username"] . "'";
You can see that the data we supply goes into the query, surrounded by single quotation marks. That is, if your request looks like this:
http://www.example.com/view_customer.php?username=ivanr
The query becomes:
SELECT customerid FROM customers WHERE username = 'ivanr'
Appending malicious data to the page parameter as we did before will do little damage because whatever is surrounded by quotes will be treated by the database as a string and not a query. To change the query an attacker must terminate the string using a single quote, and only then continue with the query. Assuming the previous query construction, the following URL would perform an SQL injection:
http://www.example.com/view_customer.php?username=ivanr'%20OR
%20username%3D'jelena'--%20
By adding a single quote to the username parameter, we terminated the string and entered the query space. However, to make the query work, we added an SQL comment start (--) at the end, neutralizing the single quote appended at the end of the query in the code. The query becomes:
SELECT customerid FROM customers WHERE username = 'ivanr'
OR username='jelena'-- '
The query returns two customer IDs, rather than the one intended by the programmer. This type of attack is actually often more difficult to do than the attack in which