Exactly What Is SQL Injection?

Before you read about the importance of SQL injection, you need to understand what SQL injection is. When a user uses a Web-based search engine by typing in search terms in the text box and submitting the form, how do you think the results are found? Likely, the application queries a database that contains all of the information to look for the results. Here is an example of such a SQL query that could be used to perform the search:

 SELECT * FROM Links WHERE Keywords LIKE '%security%'

The query returns records from the Links table, where the Keywords column contains the word security . In a Microsoft ASP.NET application, the ability for the user to specify the Keywords to find when returning the search results might look like the following:

 string strKeyword = Request.QueryString["keyword"]; string sqlQuery = "SELECT * FROM Links WHERE Keywords LIKE '%" +     strKeyword + "%'";

The query constructed and stored in sqlQuery would be executed, and then would use the resulting records to construct the Web page to return to the user. For instance, when the client specifies a search term of Bananas, strQuery becomes SELECT * FROM Links WHERE Keywords LIKE '% Bananas %' , which will return all of the records where the Keywords contain Bananas . Notice, that user-supplied data is used when constructing the SQL query.

Note	Throughout this chapter, user-supplied data is in bold type in the SQL statements to indicate where an attacker could supply input that might cause a SQL injection bug.

Recall from Chapter 10, HTML Scripting Attacks, that using the value returned from Request.QueryString without any sanitization could lead to cross-site scripting attacks. In this example, it also can lead to SQL injection. These types of security vulnerabilities are why it is important for an application to validate input prior to using it.

The goal of a SQL injection attack is to alter the logic of the SQL statement. Because the attacker can supply any value for strKeyword in the query string, the attacker just needs to supply a value that contains a single quotation mark (') to break out of the SQL statement, such as with the following example URL (%20 is a hex-encoded space):

 http://localhost/search.aspx?keyword=bug';DROP%20TABLE%20Links;--

The SQL command using this query string would become

 SELECT * FROM Links WHERE Keywords LIKE '%bug';DROP TABLE Links;--%'

Notice that the value bug';DROP TABLE Links;-- is injected into the SQL statement. If this is allowed and the connection used to connect to the database has the proper permissions, the Links table would be dropped (deleted) when the query is executed. The two hyphens (--) are used to comment out the rest of the query so that an error doesn t occur. (We discuss more about using comments to help cause a SQL injection later in this chapter.) Although this is a simple example to illustrate a SQL injection bug, these types of bugs do exist and can be a seriousthreat.

Important

Running SQL queries such as the DROP TABLE Links example are considered destructive test cases. You should not run these types of SQL queries against production systems because they could cause data to be lost.