16.1 Introduction to SQL

 <  Day Day Up  >  

According to Merriam-Webster, a database is "a usually large collection of data organized especially for rapid search and retrieval (as by a computer)." In other words, a database is a structured collection of records. Without delving into types of databases, we will note that when most people talk about databases they mean relational databases, exemplified by such commercial products as Oracle, Microsoft SQL Server, Sybase, MySQL, or PostgreSQL. Relational databases store data in the form of related tables of records. The relationship between tables is manifested in the form of linked records. So, a value in one table might be linked to a value in some other table, which is then called a foreign key .

Such tables of data can be accessed or "queried" using specially formatted request statements. The standard for this formatting is called Structured Query Language (SQL). SQL first came into being as SEQUEL, designed by IBM in 1974. SEQUEL quickly found its way into commercial database systems (such as Oracle, in 1979) and became widespread soon after.

SQL was standardized by the American National Standards Institute (ANSI) in 1991. Most modern databases support both the SQL standard (such as SQL 92) and various vendor-specific extensions, sometimes developed to optimize performance and allow better interoperability with other products of the vendor.

Thus, a relational database is a data storage solution queried using SQL statements. Obviously, databases find innumerable uses in modern information technology. With the advent of the Internet, databases became used to drive web sites and various web applications. That is how SQL injection attacks achieved notoriety. And that is where we start our journey into SQL injection.

16.1.1 SQL Commands

The following section provides a few SQL basics. Table 16-1 shows some of the popular SQL commands with examples of their uses. SQL includes much more than these, but almost every database application uses some of these commands.

Table 16-1. Common SQL commands

SQL command




Extract data from the database.

SELECT * FROM user_table;


Combine the results of several SELECT queries together, removing duplicate records.

SELECT first, last FROM customers WHERE city = `NYC' UNION SELECT first, last FROM prospects WHERE city = `NYC'


Put new data in the database table, add a new row to the table.

INSERT INTO itemfeatures VALUES (130012, 4);


Change the records in the database.

UPDATE items SET description = `New Honeypot' WHERE item_id = 150002;


Delete specific records from a table.

DELETE FROM alerts WHERE devicetypeid = 13 AND alarmid NOT IN (1,2,5) ;


Create new data structures (such as tables) within the database.

CREATE TABLE high as SELECT * FROM events WHERE name = 2;


Remove the table from the database.

DROP TABLE user_table;


Modify the database table by adding columns .

ALTER TABLE user_table ADD address varchar(30);

In addition to the commands in Table 16-1, there are some command modifiers that we use throughout this chapter. Table 16-2 shows some of the important ones.

Table 16-2. SQL command modifiers

SQL command




Used to define the fields to be processed by the SELECT, INSERT, DELETE, and other commands

SELECT * FROM user_table WHERE username ='anton';


Facility used to do approximate matching within the WHERE clause; the `%' indicates the wildcard

SELECT * FROM user_table WHERE username LIKE 'anton%';


Binary logic operators used, for example, within WHERE clauses

SELECT * FROM user_table WHERE username ='anton' AND password='correcto';


Used to specify the inserted or changed values for the INSERT and UPDATE commands

INSERT INTO user_table (username, password) VALUES (`anton', 'correcto');

The commands in Table 16-1 may be executed on a database system in many different ways. The simplest is the database shell . Here's how to run some of the above commands using the MySQL database shell called "mysql" on a Linux system.

 #  mysql  $  use FPdb;  $  select count(*) from events;  74568576 

The commands above first specify a database to use (called "FPdb") and then query the table called "events" for a total number of records, which is returned on the next line. For most databases, the command needs to be terminated by a ";" character.

Other commands may also be run from a shell, and the results are captured in a file. In the case of a database-driven web site or web application, the commands are likely run on a database through some sort of an API, such as JDBC [2] or ODBC. [3]

[2] According to Sun, "JDBC technology is an API that lets you access virtually any tabular data source from the Java programming language. It provides cross-DBMS connectivity to a wide range of SQL databases" (http://java.sun.com/products/jdbc/).

[3] ODBC (Open DataBase Connectivity) is Microsoft API that allows abstraction of a program from a database.

Before we delve into attacks, we will show how relational databases and SQL are used in modern applications, using examples from database-driven web sites deployed on Windows and Unix.

16.1.2 Use of SQL

A modern, database-driven web site is characterized by a conspicuous lack of the classic * .html or * .htm extensions for files, preferring instead the newer extensions * .asp , * .php , or many others. Such extensions indicate the use of scripting languages with embedded database commands. The * .asp (which stands for Active Server Pages) extension is common on Windows as it is a Microsoft format. * .php (which uses the PHP language; see http://www.php.net) is common on all *.php platforms.

Each file, such as index.php , contains scripting language commands and usually at least some SELECT queries. These queries are used to combine the content taken from the database with some site-specific formatting performed by the script.

For example, the PHP-Nuke's web site framework builds various types of web site content ( user forums, polls , news, ads, and others) using PHP and a SQL database. The user is responsible for populating the database with content, while the scripting language code builds the actual site structure. Ultimately, the dynamically generated HTML is sent to a visiting user's browser for display without being stored on a disk on the server.

The database scripting PHP code is full of statements such as the following:

 SELECT main_module from ".$prefix."_main SELECT * FROM ".$prefix."_referrer SELECT pwd FROM ".$prefix."_authors WHERE aid='$aid' SELECT user_password FROM ".$user_prefix."_users WHERE user_id='$uid' SELECT active FROM ".$prefix."_modules WHERE title='$module' SELECT topicname FROM ".$prefix."_topics WHERE topicid='$topic' SELECT mid, title, content, date, expire, view FROM ".$prefix."_message WHERE  active='1' $querylang 

Without going into specifics of the PHP scripting language and the details of the application, we can say that most such commands extract various pieces of data from the database and then pass this data to other parts of the application for display. Some others (most likely those mentioning $password ) [4] refer to user authentication. These likely extract user credentials from the database and compare them with user input supplied through the web site.

[4] $password (or anything else with a $ sign) indicates a variable used within the PHP script. Those familiar with Perl will recognize the similarity. While we are not talking specifically about PHP or Perl here, we will use a convention of $variable indicating a value changed within the application and passed to the database.

There are many other ways that SQL is used to drive the frontend application (that is, the part of the application visitble to the user ”the opposite of "backend" components such as the database), but web site frameworks provide the most graphic and familiar example. Thus, we use them as examples throughout the chapter.

 <  Day Day Up  >  

Security Warrior
Security Warrior
ISBN: 0596005458
EAN: 2147483647
Year: 2004
Pages: 211

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net