SQL Primer | HACKING EXPOSED WEB APPLICATIONS, 3rd Edition

Remember the web application architecture presented in Chapter 1? We're focusing on the datastore. So, let's review how the web server interacts with the database. Where a web server only understands the HTTP protocol, database servers only understand a specific language: SQL. We can draw on many examples of why the web server connects to the database, but we'll use the ubiquitous user login page.

When a user logs into the site, the web application collects two pieces of information, the username and password. The application takes these two parameters and creates a SQL statement that will collect some type of information from the database. At this point however, only the web server (the login.php page, for example) has performed any actions. Next, the web server connects to the database. This connection might be established once and maintained for a long time in connection pools, or established each time the two servers need to communicate. Either way, the web server uses its own username and password to authenticate to the database.

The web server is now talking to the database. So, login.php passes the user credentials (username and password) in as a SQL statement to the database. The database accepts the statement, executes it, and then responds with something like "the username and password match" or "username not found". It is up to the application, login.php, to handle the response from the database.

SQL is a powerful part of the application. There are a few other ways to store, query, and manage massive amounts of data other than using a database. That is also why it is so important to understand how a SQL statement can be misused.

Tip	Throughout this chapter, the terms SQL query and SQL statement are used synonymously. Typically, a query refers to the use of a SELECT statement, whereas statement may refer to the use of INSERT, UPDATE, or other commands as well as SELECT.

Syntax

The Structured Query Language (SQL) grew out of IBM research that desired to establish a standard for manipulating the information in relational databases. It would be impossible to convey all of the rules, intricacies, and capabilities of the language in a single chapter. This section will strive to introduce you to its basic syntax and common uses.

SQL provides a rich set of instructions and functions that can be combined to create statements that access and manipulate data. Simple queries bear a large resemblance to English. One of the most basic queries is to select a record (synonymous with "row") from a table based on limiting criteria. For example, here is a simple query that looks for all records in UserTable in which the FirstName column has the value 'Mike':

 SELECT * FROM UserTable WHERE FirstName='Mike';

If more than one person named Mike exists in the table, then the query will return multiple records. In many cases, a developer may wish to further restrict the query to return a fewer number of records. For example, the following query looks for records in which the FirstName column matches 'Mike' and the LastName column matches anything that starts with a capital S:

 SELECT * FROM UserTable WHERE FirstName='Mike' AND LastName LIKE 'S%';

At this point it's important to pause and examine some syntax rules for SQL statements. After all, the most common SQL injection attacks try to disrupt a SQL statement's syntax.

Queries are terminated by a semicolon.
String values are delineated by single quotes, e.g., 'foobar'
Parentheses can be used to group logical criteria, e.g., SELECT * FROM table

WHERE a=b AND (c=d OR e=f)

SELECT, INSERT, And UPDATE

Every database offers dozens of functions and data manipulation statements. We'll introduce three that you are most likely to encounter. Knowledge of how these statements are used, especially in complex queries, will help you understand how SQL injection vulnerabilities are discovered and, more importantly, how they can be exploited. Table 7-1 lists the basic syntax of these statements. The limiting_criteria and val arguments are typically the ones populated by the application based on data received from the user. Those arguments are the ones most often targeted in a SQL injection

Table 7-1: Common SQL Instructions
Statement	Description
SELECT	Obtain one or more records from a table. SELECT expression FROM table WHERE limiting_criteria
INSERT	Add a new record to a table. INSERT INTO table ( col1, col2, col3, ) VALUES ( val1, val2, val3, )
UPDATE	Modify a current record in a table. UPDATE table SET column = expression WHERE limiting_criteria

This section is intended to provide a brief introduction to SQL. More functions and advanced query constructions are shown throughout the rest of this chapter. If you would like more information on SQL, check out examples in the MySQL documentation, a web site like http://sqlcourse.com/, or the reference book SQL In A Nutshell (O'Reilly).