Understanding SQL Injection

Introduction

Structured Query Language (SQL) is used to query, operate, and administer database systems such as Microsoft SQL Server, Oracle, or MySQL. The general use of SQL is consistent across all database systems that support it; however, there are intricacies that are particular to each system.

Database systems are commonly used to provide backend functionality to many types of web applications. In support of web applications, user-supplied data is often used to dynamically build SQL statements that interact directly with a database. A SQL injection attack is an attack that is aimed at subverting the original intent of the application by submitting attacker-supplied SQL statements directly to the backend database. Depending on the web application, and how it processes the attacker-supplied data prior to building a SQL statement, a successful SQL injection attack can have far-reaching implications. The possible security ramifications range from authentication bypass to information disclosure to enabling the distribution of malicious code to application users.

This white paper will describe SQL injection attacks, how they are performed, and precautions that should be taken inside applications or networks to reduce risks that are associated with SQL injection attacks.

SQL Injection Explained

A SQL injection attack involves the alteration of SQL statements that are used within a web application through the use of attacker-supplied data. Insufficient input validation and improper construction of SQL statements in web applications can expose them to SQL injection attacks. SQL injection is such a prevalent and potentially destructive attack that the Open Web Application Security Project (OWASP) lists it as the number one threat to web applications.

Ramifications of Successful SQL Injection Attacks

Although the effects of a successful SQL injection attack vary based on the targeted application and how that application processes user-supplied data, SQL injection can generally be used to perform the following types of attacks:

  • Authentication Bypass: This attack allows an attacker to log on to an application, potentially with administrative privileges, without supplying a valid username and password.
  • Information Disclosure: This attack allows an attacker to obtain, either directly or indirectly, sensitive information in a database.
  • Compromised Data Integrity: This attack involves the alteration of the contents of a database. An attacker could use this attack to deface a web page or more likely to insert malicious content into otherwise innocuous web pages. This technique has been demonstrated via the attacks that are described in Mass exploits with SQL Injection at the SANS Internet Storm Center.
  • Compromised Availability of Data: This attack allows an attacker to delete information with the intent to cause harm or delete log or audit information in a database.
  • Remote Command Execution: Performing command execution through a database can allow an attacker to compromise the host operating system. These attacks often leverage an existing, predefined stored procedure for host operating system command execution. The most recognized variety of this attack uses the xp_cmdshell stored procedure that is common to Microsoft SQL Server installations or leverages the ability to create an external procedure call on Oracle databases.

An Example of SQL Injection for Authentication Bypass

One of the many possible uses for SQL injection involves bypassing an application login process. The following example illustrates the general operation of a SQL injection attack. The following HTML form solicits login information from an application user. Although this example uses an HTTP POST request, an attacker could also use HTML forms that use the HTTP GET method.

<form action="/cgi-bin/login" method=post>

 Username: <input type=text name=username> 

 Password: <input type=password name=password> 

<input type=submit value=Login>

When a user enters his or her information into this form and clicks Login, the browser submits a string to the web server that contains the user's credentials. This string appears in the body of the HTTP or HTTPS POST request as:

username=submittedUser&password=submittedPassword

   

An application with a vulnerable login process may accept the submitted information and use it as part of the following SQL statement, which locates a user profile that contains the submitted username and password:

select * from Users where (username = 'submittedUser' and password = 'submittedPassword');

   

Unless an application uses strict input validation, it may be vulnerable to a SQL injection attack. For example, if an application accepts and processes user-supplied data without any validation, an attacker could submit a maliciously crafted username and password. Consider the following string sent by an attacker:

username=admin%27%29+--+&password=+

Once this string is received and URL-decoded, the application will attempt to build a SQL statement using a username of admin') -- and a password that consists of a single space. Placing these items into the previous SQL statement yields:

select * from Users where (username = 'admin') -- and password = ' ');

        

As the previous example demonstrates, the attacker-crafted username changes the logic of the SQL statement to effectively remove the password check. In the above example, an attacker could successfully log in to the application using the admin account without knowledge of the password to that account.

The string of two dash characters (--) that appears in the crafted input is very important; it indicates to the database server that the remaining characters in the SQL statement are a comment and should be ignored. This capability is one of the most important tools that is available to an attacker and without it, it would be difficult to ensure that the malicious SQL statements were syntactically correct.

Although the crafted field, which is the username field in the previous example, must be tailored to the vulnerable application, a large set of documented strings that are readily available on the Internet have proven successful at enabling SQL injection attacks. The previous example may be simplistic, but it illustrates the effectiveness of SQL injection attack techniques.

Blind and Second Order SQL Injection

In situations where data from a backend SQL database is not returned directly to the user or attacker, it may be necessary for an attacker to use the Blind SQL Injection technique. With this technique, an attacker can determine whether a SQL statement was executed using means other than the direct presentation of data. Using Blind SQL Injection, an attacker could perform reconnaissance, obtain sensitive information, or alter database contents, including authentication credentials.

One example of the Blind SQL Injection technique is the introduction of a delay as part of a malicious SQL statement. Depending on the database software in use, an attacker could build a SQL statement that is designed to cause a database server to perform a time-consuming action. With the MySQL database software, it may be possible to craft a SQL statement using the sleep() function. For example, incorporating sleep(10) into a malicious query will create a 10-second delay. An attacker could induce a recognizable delay on database servers that do not contain the sleep() function by executing an operating system command or time-consuming sub-query or attempting to establish an outbound HTTP connection. Should the time-consuming SQL statement be executed, the web application may take noticeably longer to respond than is typical. This method allows attackers to determine whether their SQL statements are being executed with some level of certainty.

Second Order SQL Injection attacks involve user-submitted data that is first stored in the database, then retrieved and used as part of a vulnerable SQL statement. This class of vulnerability is more difficult to locate and exploit, but Second Order SQL Injection attacks justify data validation prior to the execution of all SQL statements in an application, as well as the comprehensive use of parameterized queries.

Defending Against SQL Injection Attacks

A SQL injection attack can be detected and potentially blocked at two locations in an application traffic flow: in the application and in the network.

Defenses in the Application

There are several ways in which an application can defend against SQL injection attacks. The primary approaches include validation of user-supplied data, in the form of whitelisting or blacklisting, and the construction of SQL statements such that user-supplied data cannot influence the logic of the statement.

Blacklisting and Whitelisting

Within an application itself, there are two approaches to input validation that can defend against SQL injection attacks: blacklisting and whitelisting. With blacklisting, specific, known malicious characters are removed from or replaced in user input. Although this approach is often implemented, largely due to the ease at which it can be accomplished, it is not effective when compared to whitelisting. Blacklisting can fail to properly handle complex obfuscation, which could allow an attacker to subvert filters and potentially inject SQL statements. This failure often occurs as a result of evolving attack techniques and filters that are not comprehensive or not implemented correctly.

Alternately, whitelisting examines each piece of user input against a list of permitted characters. This approach is more effective in mitigating the risk of SQL injection, as it is more restrictive concerning which types of input are allowed. Well-implemented whitelisting should examine each piece of user-supplied data against the expected data format.

Regardless of the approach, it will most likely be necessary to tailor the allowed characters by the input field type or by the input field class (for example, text or numeric data). The input validation and sanitization functions that are used to filter SQL injection enabling characters can potentially be generalized and used to filter characters that are indicative of cross-site scripting attacks. More information about cross-site scripting is available in the Applied Intelligence white paper Understanding Cross-Site Scripting (XSS) Threat Vectors.

The application must act deterministically when it receives invalid characters from a user. Different levels of response may be appropriate based on the circumstances in which the unexpected data is being processed and the effect it could have. For example, applications should patently reject users that submit two dash characters (--) or a semi-colon character (;) as part of the login name or password, and a high-severity alert should be sent to application administrators. However, this somewhat harsh response may not be appropriate when an application receives a single apostrophe character as part of a street address that is submitted by an authenticated user who is entering package shipping information. Nonetheless, the application should behave as intended, and a notification should be sent to application administrators if the submitted apostrophe was not handled properly by the application.

The implementation of input validation and sanitization should contain alerting functionality. This functionality should alert an application administration or development team when unexpected input has been received because it could indicate that the filters may be incorrectly blocking valid, but unexpected data or the application may be under attack. In either case, changes may be required to the filtering functionality of the application.

At first glance, it might appear that these data validation and sanitization techniques could be implemented in client-side JavaScript to be run in a user's browser. From a security perspective, however, it must be assumed that any and all types of data will originate from a user's browser, regardless of the client-imposed safeguards that are in place. Nonetheless, client-side data validation techniques can enhance application usability.

Fortifying SQL Statements

Parameterized queries in ASP.NET, prepared statements in Java, or similar techniques in other languages should be used comprehensively in addition to strict input validation. Each of these techniques performs all required escaping of dangerous characters before the SQL statement is passed to the underlying database system. The following example depicts the use of prepared statements in Java and illustrates how SQL statements are built without user-supplied data and then augmented with the data in such a manner that the structure and intent of the SQL statement cannot be altered:



String sql = "select * from Users where (username = ? and password = ?)"; 



PreparedStatement preparedStmt = connection.prepareStatement(sql); 

preparedStmt.setString(1, submittedUsername); 

preparedStmt.setString(2, submittedPassword); 



results = preparedStmt.executeQuery(); 

Note that prepared statements and similar technologies are not a panacea; used incorrectly without bind variables, they are no more secure than traditionally constructed dynamic queries. The following example illustrates the incorrect use of prepared statements in Java:

String sql = "select * from Users where (username = '" + submittedUsername + 
         "' and password = '" + submittedPassword + "')"; PreparedStatement preparedStmt = connection.prepareStatement(sql); results = preparedStmt.executeQuery();

In addition to ensuring that the intent of SQL statements cannot be altered by user-supplied data, applications should also catch and remove all SQL-generated error messages before they reach an end user. Although this safeguard may hinder a developer's ability to troubleshooting an application error, which can easily be overcome with additional backend logging, the presentation of SQL errors will greatly aid attackers in successfully exploiting a SQL injection vulnerability. The following example is an overly verbose error message:

com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Table 'sqlInjectionTest.test' doesn't exist 

	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936) 

	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985) 

	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631) 

	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723) 

	at com.mysql.jdbc.Connection.execSQL(Connection.java:3277) 

	at com.mysql.jdbc.Connection.execSQL(Connection.java:3206) 

	at com.mysql.jdbc.Statement.executeQuery(Statement.java:1232) 

	at sqlInjectionBefore.main(before.java:28) 

This error message discloses that the application is using the Java programming language and the MySQL database platform and that the queried database is named "sqlInjectionTest." Each piece of information could assist attackers in crafting their application input, which increases the odds that they will be successful.

Defenses in the Network

Although each application should ideally provide its own input validation, this situation is not always possible. In certain situations, applications cannot be updated to handle user-supplied data in a secure manner. In these circumstances, administrators and developers can add security to an existing application by leveraging technologies in the network, specifically intrusion prevention systems.

Intrusion Prevention System Signatures

In certain situations, it may be possible to detect and prevent SQL injection attacks using an intrusion prevention system (IPS). For an IPS to be effective, it must have visibility into the traffic of the application. For applications that use end-to-end encryption with HTTPS (for example, applications that use HTTPS without termination or acceleration at an intermediate network device), an IPS cannot identify traffic with characteristics of a SQL injection attack.

The Cisco Intrusion Prevention System solution currently contains the following signatures that may indicate the presence of a SQL injection attack. This information is updated as of the S702 Cisco IPS signature update release.

Table 1. SQL Injection–Related Cisco IPS Signatures

Signature ID Description Signature Pack Enabled by Default Comments
1925.0
Symphony CMS BluePRINTs URI SQL Injection
S698
No
Detects an attempt to exploit a URI SQL injection vulnerability in Symphony CMS 'bluePRINTs' parameter
1786.0
Computer Associates Total Defense Suite UNCWS SQL Injection
S697
Yes
Fires on an SQL injection vulnerability in Total Defense Suite product of Computer Associates
5699.0
SalesLogix SQL Injection Vulnerability
S685
No
Detects an attempt to exploit a SQL injection vulnerability in Best Software SalesLogix
41206.0
Oracle Database Workspace Manager SQL Injection
S669
No
Detects an attempt to exploit CVE-2009-0978, a SQL injection vulnerability in the Oracle Workspace Manager component
1221.0
Oracle Database Server DBMS_CDC_PUBLISH SQL Injection
S658
No
Fires upon detecting a specially crafted parameter in procedures DROP_CHANGE_SOURCE or ALTER_CHANGE_SOURCE in Oracle's database server
41526.0
HP Data Protector RequestCopy SQL Injection
S647
An SQL injection vulnerability exists in HP Data Protector products, the flaw is caused by insufficient validation of the type field in a user supplied SOAP request to the DPNECentral web service. A remote unauthenticated attacker can leverage this vulnerability to execute arbitrary SQL queries on a target.
6964.0
Asprox Injection Attempt
S624
No
Detects a SQL injection attack launched by a host infected with the Asprox botnet. This signature looks for the SQL injection attack that begins with the statement, "DECLARE @S VARCHAR(4000);SET @S=CAST" aimed at a .asp web page
5803.0
Sygate Login Servlet SQL Injection
S623
No
Detects the use of SQL injectin to modify administrator passwords and accounts when accessing the Sygate Management Console login servlet
35285.0
Lizamoon SQL Injection
S589
No
Fires upon detecting a host infected by Lizamoon SQL Injection attack
35085.0
Cisco Call Manager SQL Injection
S562
Yes
Fires upon detecting a SQL injection attack against Cisco's Call Manager
21259.0
ActiveCampaign 1-2-All Control Panel Username SQL Injection Vulnerability
S521
No
Detects an SQL injection in index.php on The ActiveCampaign Email Control Panel which due to weak input validation could allow a remote user unauthorized access to the database
24179.0
Oracle Database Server CREATE_TABLES SQL Injection
S482
Yes
Detects a specific attempt at exploiting the "Oracle Database Server CREATE_TABLES SQL Injection" vulnerability as further documented in CVE-2009-1991
5474.1
SQL Query in HTTP Request
S472
Yes
Triggers on seeing traffic in an HTTP request containing the words "insert" and "values". A common SQL injection technique is to craft a request containing syntax similar to "insert into some_table values some_value(s)"
21662.0
Process PHP login parameter SQL Injection Vulnerability
S471
No
Fires upon detecting an attempt to exploit a SQL injection vulnerability in processlogin.php in Bit 5 Blog. A remote attackers can execute arbitrary SQL commands and bypass authentication via crafted username and password parameter as documented in CVE-2006-0320
22380.0
GNUTurk mods.php t_id Parameter SQL Injection
S458
No
Gnuturk is prone to a cross-site scripting vulnerability.Exploiting this vulnerability may allow an attacker to perform cross-site scripting attacks on unsuspecting users in the context of the affected website. As a result, the attacker may be able to steal cookie-based authentication credentials and to launch other attacks.This signature triggers on a crafted URI.This vulnerability is further documented in CVE-2006-4867
22424.0
PHP Labs Top Auction SQL Injection Vulnerability
S458
No
Triggers on an attempt to exploit a SQL vulnerability in PHP Labs Top Aution. A remote attacker can execute arbitrary SQL commands via the category and type parameters in viewcat.php. The vulnerability is documented by CVE-2005-3952.
22439.0
Lizard Cart CMS id Parameter SQL Injection Vulnerability
S458
No
Lizard Cart CMS is prone to multiple SQL injection vulnerabilities. These issues are due to a failure in the application to properly sanitize user-supplied input before using it in SQL queries.Successful exploitation could result in a compromise of the application, disclosure or modification of data, or may permit an attacker to exploit vulnerabilities in the underlying database implementation. This signature triggers on a crafted URI. This vulnerability is further documented in CVE-2006-0087
22142.0
SQL Botnet User-Agent: uil2pn
S448
Yes
Triggers on seeing "User-Agent: uil2pn" in HTTP traffic. This traffic has shown to indicate the possibility of an emerging botnet responsible for some SQL scanning and possibly SQL injection
21443.0
VEGO Web Forum login.php username Parameter SQL Injection
S446
No
Fires upon detecting an attempt to exploit a vulnerability in login.php in VEGO Links Builder. A remote attacker can execute arbitrary SQL commands via the crafted username parameter as documented in CVE-2006-0067.
6048.0
Oracle Database Server SQL SYS.KUPV Injection
S435
No
Detects a Oracle Database Server SQL injection attack
18101.0
IBM Rational ClearQuest Username Parameter SQL Injection
S433
No
Fires on an attempt to exploit a SQL injection vulnerability present in HTTP IBM Rational ClearQuest. Successful exploitation of this vulnerability will grant an attacker with full access to the database itself, and possibly command execution on the database server.
20259.0
inTouch index.php user Parameter SQL Injection
S428
No
Fires upon detecting a crafted username in http request which can be used to exploit a vulnerability in inTouch to perform SQL injection attack, the vulnerability is further documented by CVE-2006-0088
20300.0
Wordcircle index.php password Parameter SQL Injection
S428
No
Fires upon detecting an attempt to exploit a vulnerability in WordCircle, to bypass authentication by a crafted password, and execute arbitrary SQL command. This is further documented by CVE-2006-0205.
5930.21
Generic SQL Injection
S415
Yes
Detects "INSERT INTO table_name" which is an SQL command that can be used to modify the contents of a SQL table.
5998.0
SYS.KUPW-WORKER Package MAIN Procedure SQL Injection Attempt
S404
No
Triggers upon seeing a crafted call to the Data Pump Metadata API function SYS.KUPW$WORKER.MAIN, resulting in execution of privileged SQL statements.
6419.0
Oracle Database dbms_assert Filter Bypass Vulnerability
S404
No
Detects attempts to exploit SQL injection vulnerabilities in Oracle's DBMS_ASSERT package and is documented in CVE-2006-5340.
5937.0
Oracle Database SUBSCRIPTION_NAME Parameter SQL Injection
S404
No
Detects an attempt to exploit an SQL injection vulnerability in DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW, as documented by CVE-2005-1197, the attacker will gain privilege escalation.
5930.0 - 5930.5
General SQL Injection
S349
Yes
Detects SQL keywords in HTTP arguments
3254.1
XML-RPC PHP Command Execution
S308
No
Fires when detecting a SQL injection using the XML_RPC vulnerability.
5474.0
5474.1
SQL Query in HTTP Request
S294
Yes
Detects the presence of encoded words that are indicative of SQL injection attacks
5383.0 - 5383.2
Cyberstrong eShop SQL Injection
S256
No
Fires when an attempt to insert unauthorized SQL queries into a HTTP request to a Cyberstrong eShop script.
5380.0
phpBB SQL injection
S50
No
Triggered when an HTTP request is made for the CGI script 'viewtopic.php' with argument 'topic_id' containing either the word 'union' or a semicolon.
5381.0
VPASP SQL injection
S50
No
Triggered when a request is made for the CGI script 'shopexd.asp' with the argument 'id' containing a semicolon.
5376.0
iisPROTECT Admin SQL Injection
S47
Yes
Fires when an attempt to inject arbitrary SQL statements into the arguments of an HTTP request to iisPROTECT administration interface is detected. This may be an unauthorized attempt to view or manipulate data or execute commands on the database server.
5337.0
HTTP args to xp_cmdshell in HTTP Request
S47
Yes
Detects usage of the Microsoft SQL Server xp_cmdshell in the arguments of an HTTP request
3732.0
MSSQL xp_cmdshell Usage
S44
No
Detects usage of a Microsoft SQL server stored procedure that is used to execute operating system commands
5357.0
IMP SQL Injection
S41
No
Fires upon detecting an sql-injection attempt to mailbox.php.
5347.0
Xoops WebChat SQL Injection
S37
No
Triggers when an HTTP request is made for the script 'index.php' with the 'roomid' argument containing a single-quote or semicolon character.
5259.0
Snitz Forums SQL injection
S24
No
Fires upon detecting a HTTP request to members.asp that includes the charachter ' as a value passed to the parameter M_NAME.
5260.0
Xpede sprc.asp SQL Injection
S24
No
Fires when detecting an HTTP request to sprc.asp with an argument that contains an apostrophe ('). This would be indicative of a SQL insertion attack.
5234.0 - 5234.1
pforum sql-injection
S19
No
Fires upon detecting an sql-injection attempt to logincheck.php.
5236.0
Xoops sql-injection
S19
No
Fires upon detecting a request to userinfo.php that contains a sql-injection attack in a parameter.

 

New signatures are continually being developed based on new security intelligence. The latest signature releases are on the Cisco IPS Services section of the Cisco Security Intelligence Operations Portal.

Firewall Application Layer Protocol Inspection

Application layer protocol inspection performs deep packet inspection of traffic that transits the firewall. Using application layer protocol inspection on Cisco firewalls to mitigate SQL attacks against web servers is discussed in Cisco TAC Security Podcast Episode #16.

Conclusion

This white paper has described SQL injection attacks and their ramifications. There are several approaches to combating these types of attacks; protections in the application, such as whitelisting and parameterized queries, and protections in the network, such as intrusion prevention systems.

Acknowledgments

Tim Sammut (tsammut@cisco.com)
Incident Manager

Tim Sammut is a member of the Security Intelligence Engineering organization at Cisco. Additional content produced by Security Intelligence Engineering is located in the Tactical Resources section of Cisco Security Intelligence Operations.

Mike Schiffman (mschiffm@cisco.com)
Applied Security Researcher

Mike Schiffman is a member of the Applied Security Intelligence (ASI) organization at Cisco. ASI is a frequent contributor to the Cisco Security Blog section of Cisco Security Intelligence Operations.

References

OWASP: SQL Injection
http://www.owasp.org/index.php/SQL_injection

How To: Protect From SQL Injection in ASP.NET
http://msdn.microsoft.com/en-us/library/ms998271.aspx

Using Prepared Statements in Java
http://java.sun.com/docs/books/tutorial/jdbc/basics/prepared.html

Cisco TAC Security Podcast Episode #16 - Mitigating a SQL attack with ASA, IPS and IOS Firewall
https://supportforums.cisco.com/docs/DOC-14890


This document is part of the Cisco Security Intelligence Operations.

This document is provided on an "as is" basis and does not imply any kind of guarantee or warranty, including the warranties of merchantability or fitness for a particular use. Your use of the information on the document or materials linked from the document is at your own risk. Cisco reserves the right to change or update this document at any time.

Back to Top

Cisco Security Intelligence Operations