PHPBuilder - Pro PHP Security: Preventing SQL Injection



RSS Twitter
Articles Application Architecture

Pro PHP Security: Preventing SQL Injection

by: PHP Builder Staff
|
November 1, 2006

Chapter 12 is reprinted with permission by Apress
PHP is an extremely powerful yet easy-to-learn scripting language, affording even relatively inexperienced programmers the opportunity to create complex, dynamic websites. It is, however, notoriously difficult to ensure privacy and security of internet services. In this book, we will provide you with the security background every web developer needs, along with PHP-specific knowledge and code that you can use to protect the integrity of your own applications. We begin with an overview of server security that shows you how to assess privacy in a shared hosting environment, keep developers out of production servers, maintain up-to-date software, provide encrypted channels, and control access to your systems.
The discussion then turns to preventing common vulnerabilities in PHP scripts. We explain how to secure your scripts against SQL injection, prevent cross-site scripting and remote execution, and stop the hijacking of temporary files and sessions. The final part of the book is devoted to implementing secure applications. You'll learn how to verify user identities, authorize and track application use, PHP Security avoid data loss, safely execute high-risk system commands, and use web services securely. Whether you have learned just enough PHP to be dangerous, or have years of experience dealing with security issues, this book offers a wealth of information that can help you to make your online applications more secure.
We began Part 3 with a discussion in Chapter 11 of keeping your PHP scripts secure by careful validation of user input. We continue that discussion here, focusing on user input that participates in your scripts' interaction with your databases. Your data is, after all, probably your most treasured resource. Your primary goal in writing scripts to access that data should be to protect your users' data at all costs. In the rest of this chapter, we'll show you ways to use PHP to do that.

What SQL Injection Is

There is no point to putting data into a database if you intend never to use it; databases are designed to promote the convenient access and manipulation of their data. But the simple act of doing so carries with it the potential for disaster. This is true not so much because you yourself might accidentally delete everything rather than selecting it. Instead, it is that your attempt to accomplish something innocuous could actually be hijacked by someone who substitutes his own destructive commands in place of yours. This act of substitution is called injection.
Every time you solicit user input to construct a database query, you are permitting that user to participate in the construction of a command to the database server. A benign user may be happy enough to specify that he wants to view a collection of men's long-sleeved burgundy- colored polo shirts in size large; a malicious user will try to find a way to contort the command that selects those items into a command that deletes them, or does something even worse. Your task as a programmer is to find a way to make such injections impossible.
How SQL Injection Works
Constructing a database query is a perfectly straightforward process. It typically proceeds something like this (for demonstration purposes, we'll assume that you have a database of wines, where one of the fields is the grape variety):
  1. You provide a form that allows the user to submit something to search for. Let's assume that the user chooses to search for wines made from the grape variety "lagrein."
  2. You retrieve the user's search term, and save it by assigning it to a variable, something like this:
    SnyderSouthwell_5084.book  Page 250  Saturday, July 16, 2005  6:14 AM
    $variety = $_POST['variety'];
    
    So that the value of the variable $variety is now this:
    lagrein
    
  3. You construct a database query, using that variable in the WHERE clause, something like this:
    $query = "SELECT * FROM wines WHERE variety='$variety'";
    
    so that the value of the variable $query is now this:
    SELECT * FROM wines WHERE variety='lagrein'
    
  4. You submit the query to the MySQL server.
  5. MySQL returns all records in the wines table where the field variety has the value "lagrein."
So far, this is very likely a familiar and comfortable process. Unfortunately, sometimes familiar and comfortable processes lull us into complacency. So let's look back at the actual construction of that query.
  1. You created the invariable part of the query, ending it with a single quotation mark, which you will need to delineate the beginning of the value of the variable:
    $query = "SELECT * FROM wines WHERE variety = '";
    
  2. You concatenated that invariable part with the value of the variable containing the user's submitted value: $query .= $variety;
  3. You then concatenated the result with another single quotation mark, to delineate the end of the value of the variable:
    $query .= "'";
    
    The value of $query was therefore (with the user input in bold type) this:
    SELECT * FROM wines WHERE variety = 'lagrein'
    
    The success of this construction depended on the user's input. In this case, you were expecting a single word (or possibly a group of words) designating a grape variety, and you got it. So the query was constructed without any problem, and the results were likely to be just what you expected, a list of the wines for which the grape variety is "lagrein." Let's imagine now that your user, instead of entering a simple grape variety like "lagrein" (or even "pinot noir"), enters the following value (notice the two included punctuation marks):
    lagrein' or 1=1;
    

1
|
2
Next Page »

Comment and Contribute

Your comment has been submitted and is pending approval.

Author:

Comment:



Comment:

(Maximum characters: 1200). You have characters left.