This site is the archived OWASP Foundation Wiki and is no longer accepting Account Requests.
To view the new OWASP Foundation website, please visit https://owasp.org
SQL Injection Prevention Cheat Sheet
Introduction
SQL Injection attacks are unfortunately very common, and this is due to two factors 1) the significant prevalence of SQL Injection vulnerabilities, and 2) the attractiveness of the target (i.e., the database that contains all the interesting/critical data for your application). It’s somewhat shameful that there are so many successful SQL Injection attacks occurring, because it is EXTREMELY simple to avoid SQL Injection vulnerabilities in your code.
SQL Injection flaws are introduced when software developers create dynamic database queries that include user supplied input. To avoid SQL injection flaws is simple. Developers need to either: a) stop writing dynamic queries; and/or b) prevent user supplied input which contains malicious SQL from affecting the logic of the executed query.
This article provides a set of simple techniques for preventing SQL Injection vulnerabilities by avoiding these two problems. These techniques can be used with practically any kind of programming language with any type of database. There are other types of databases, like XML databases, which can have similar problems (e.g., XPath and XQuery injection) and these techniques can be used to protect them as well.
Primary Defenses:
- Option #1: Use of Prepared Statements (Parameterized Queries)
- Option #2: Use of Stored Procedures
- Option #3: Encoding all User Supplied Input
Additional Defenses:
- Also Enforce: Least Privilege
- Also Perform: White List Input Validation
- Unsafe Example
SQL injection flaws typically look like this:
The following (Java) example is UNSAFE, and would allow an attacker to inject code into the query that would be executed by the database. The unvalidated “customerName” parameter that is simply appended to the query allows an attacker to inject any SQL code they want. Unfortunately, this method for accessing databases is all too common.
String query = "SELECT account_balance FROM user_data WHERE user_name = "
+ request.getParameter("customerName");
try {
Statement statement = connection.createStatement( … );
ResultSet results = statement.executeQuery( query );
}
Prepared Statements (Parameterized Queries)
The use of prepared statements (aka parameterized queries) is how all developers should first be taught how to write database queries. They are simple to write, and easier to understand than dynamic queries. Parameterized queries force the developer to first define all the SQL code, and then pass in each parameter to the query later. This coding style allows the database to distinguish between code and data, regardless of what user input is supplied.
Prepared statements ensure that an attacker is not able to change the intent of a query, even if SQL commands are inserted by an attacker. In the safe example below, if an attacker were to enter the userID of tom' or '1'='1, the parameterized query would not be vulnerable and would instead look for a username which literally matched the entire string tom' or '1'='1.
- Safe Java Prepared Statement Example
The following code example uses a PreparedStatement, Java's implementation of a parameterized query, to execute the same database query.
String custname = request.getParameter("customerName"); // This should REALLY be validated too
// perform input validation to detect attacks
String query = "SELECT account_balance FROM user_data WHERE user_name = ? ";
PreparedStatement pstmt = connection.prepareStatement( query );
pstmt.setString( 1, custname);
ResultSet results = pstmt.executeQuery( );
- Safe C# .NET Prepared Statement Example
With .NET, it's even more straightforward. The creation and execution of the query doesn't change. All you have to do is simply pass the parameters to the query using the Parameters.Add() call as shown here.
String query =
"SELECT account_balance FROM user_data WHERE user_name = ?";
try {
OleDbCommand command = new OleDbCommand(query, connection);
command.Parameters.Add(new OleDbParameter("customerName", CustomerName Name.Text));
OleDbDataReader reader = command.ExecuteReader();
// …
} catch (OleDbException se) {
// error handling
}
We have shown examples in Java and .NET but practically all other languages, including Cold Fusion, and Classic ASP, support parameterized query interfaces.
Developers tend to like the Prepared Statement approach because all the SQL code stays within the application. This makes your application relatively database independent. However, other options allow you to store all the SQL code in the database itself, which has both security and non-security advantages. That approach, called Stored Procedures, is described next.
Stored Procedures
Stored procedures have the same effect as the use of prepared statements. They require the developer to define the SQL code first, and then pass in the parameters after. The difference between prepared statements and stored procedures is that the SQL code for a stored procedure is defined and stored in the database itself, and then called from the application. Both of these techniques have the same effectiveness in preventing SQL injection so your organization should choose which approach makes the most sense for you.
- Safe Java Stored Procedure Example
The following code example uses a CallableStatement, Java's implementation of the stored procedure interface, to execute the same database query. The "sp_getAccountBalance" stored procedure would have to be predefined in the database and implement the same functionality as the query defined above.
String custname = request.getParameter("customerName "); // This should REALLY be validated
try {
CallableStatement cs = connection.prepareCall("{call sp_getAccountBalance(?)}");
cs.setString(1, custname);
ResultSet results = cs.executeQuery();
// … result set handling
} catch (SQLException se) {
// … logging and error handling
}
- Safe VB .NET Stored Procedure Example
The following code example uses a SqlCommand, .NET’s implementation of the stored procedure interface, to execute the same database query. The "sp_getAccountBalance" stored procedure would have to be predefined in the database and implement the same functionality as the query defined above.
Try
Dim command As SqlCommand = new SqlCommand("sp_getAccountBalance", connection)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add(new SqlParameter("@CustomerName", CustomerName.Text))
Dim reader As SqlDataReader = command.ExecuteReader()
‘ …
Catch se As SqlException
‘ error handling
End Try
We have shown examples in Java and .NET but practically all other languages, including Cold Fusion, and Classic ASP, support the ability to invoke stored procedures.
For organizations that already make significant or even exclusive use of stored procedures, it is far less likely that they have SQL injection flaws in the first place. However, you still need to be careful with stored procedures because it is possible, although relatively rare, to create a dynamic query inside of a stored procedure that is subject to SQL injection. If dynamic queries can’t be avoided, then validate or properly encode all user supplied input to the dynamic query, before you construct it.
There are also some additional security and non-security benefits of stored procedures that are worth considering. One security benefit is that if you make exclusive use of stored procedures for your database, you can restrict all database user accounts to only have access to the stored procedures. This means that database accounts do not have permission to submit dynamic queries to the database, giving you far greater confidence that you do not have any SQL injection vulnerabilities in the applications that access that database. Some non-security benefits include performance benefits (in most situations), and having all the SQL code in one location, potentially simplifying maintenance of the code and keeping the SQL code out of the application developers hands, leaving it for the database developers to develop and maintain. In rare circumstances, prepared statements actually harm performance. When confronted with this situation, it is best to encode all user supplied input using an encoding routing specific to your database vendor as is described in the next section.
Encoding all User Supplied Input
This third technique is a relatively new approach being advocated by Jeff Williams, who is the lead for OWASP's ESAPI project. It has the advantage that you can apply it to an existing application with almost no affect on the structure of the code. If you are concerned that rewriting your dynamic queries as prepared statements or stored procedures might break your application or adversely affect performance, then this might be the best approach for you.
This technique works like this. Each DBMS supports a character encoding scheme were you can encode special characters in order to indicate to the DBMS that the characters you are providing in the query are intended to be data, and not code. If you then encode all user supplied input using the proper encoding scheme for the database you are using, the DBMS will not confuse that input with SQL code written by the developer, thus avoiding any possible SQL injection vulnerabilities.
To perform this encoding, you need to understand the encoding scheme supported by the DBMS you are using, and write an encoder, or you can use one of the existing database encoders provided by the OWASP ESAPI project. The ESAPI database encoders were developed by reviewing the specs for Oracle and MySQL to understand what their encoding scheme is.
- Full details on ESAPI are available here on OWASP.
- The javadoc for ESAPI is available here at its Google Code repository.
- You can also directly browse the source at Google, which is frequently helpful if the javadoc isn't perfectly clear.
To find the javadoc specifically for the database encoders, click on the ‘Codec’ class on the left hand side. There are lots of Codecs implemented. The two Database specific codecs are OracleCodec, and MySQLCodec.
Just click on their names in the ‘All Known Implementing Classes:’ at the top of the Interface Codec page.
To use a database codec is pretty simple. An Oracle example looks something like:
ESAPI.encodeForSQL( new OracleCodec(), queryparam );
So, if you had an existing Dynamic query being generated in your code that was going to Oracle that looked like this:
String query = "SELECT user_id FROM user_data WHERE user_name = '" + req.getParameter("userID")
+ "' and user_password = '" + req.getParameter("pwd") +"'";
try {
Statement statement = connection.createStatement( … );
ResultSet results = statement.executeQuery( query );
}
You would rewrite the first line to look like this:
Codec ORACLE_CODEC = new OracleCodec();
String query = "SELECT user_id FROM user_data WHERE user_name = '" +
ESAPI.encodeForSQL( ORACLE_CODEC, req.getParameter("userID")) + "' and user_password = '"
+ ESAPI.encodeForSQL( ORACLE_CODEC, req.getParameter("pwd")) +"'";
And it would now be safe from SQL injection, regardless of the input supplied.
If you created a wrapper method called ESAPI.encodeForOracle( param ) so you didn’t have to pass the Oracle codec in each time, then it would be even simpler and look like:
String query = "SELECT user_id FROM user_data WHERE user_name = '"
+ ESAPI.encodeForOracle( req.getParameter("userID")) + "' and user_password = '"
+ ESAPI.encodeForOracle( req.getParameter("pwd")) +"'";
With this type of solution, all your developers would have to do is wrap each user supplied parameter being passed in into an ESAPI.encodeForOracle( ) call or whatever you named it, and you would be done.
At this time, ESAPI currently has database encoders for:
- Oracle
- MySQL (Both ANSI and native modes are supported)
Database encoders for:
- SQL Server
Are forthcoming. If your database encoder is missing, please let us know.
Additional Defenses
Beyond adopting one of the three primary defenses, we also recommend adopting all of these additional defenses in order to provide defense in depth. These additional defenses are:
- Least Privilege
- White List Input Validation
Least Privilege
To minimize the potential damage of a successful SQL injection attack, you should minimize the privileges assigned to every database account in your environment. Do not assign DBA or admin type access rights to your application accounts. We understand that this is easy, and everything just ‘works’ when you do it this way, but it is very dangerous. Start from the ground up to determine what access rights your application accounts require, rather than trying to figure out what access rights you need to take away. Make sure that accounts that only need read access are only granted read access to the tables they need access to. If an account only needs access to portions of a table, consider creating a view that limits access to that portion of the data and assigning the account access to the view instead, rather than the underlying table. Rarely, if ever, grant create or delete access to database accounts.
If you adopt a policy where you use stored procedures everywhere, and don’t allow application accounts to directly execute their own queries, then restrict those accounts to only be able to execute the stored procedures they need. Don’t grant them any rights directly to the tables in the database.
SQL injection is not the only threat to your database data. Attackers can simply change the parameter values from one of the legal values they are presented with, to a value that is unauthorized for them, but the application itself might be authorized to access. As such, minimizing the privileges granted to your application will reduce the likelihood of such unauthorized access attempts, even when an attacker is not trying to use SQL injection as part of their exploit.
White List Input Validation
It is always recommended to prevent attacks as early as possible in the processing of the user’s (attackers) request. Input validation can be used to detect unauthorized input before it is passed to the SQL query. Developers frequently perform black list validation in order to try to detect attack characters and patterns like the ' character or the string 1=1, but this is a massively flawed approach as it is typically trivial for an attacker to avoid getting caught by such filters. Plus, such filters frequently prevent authorized input, like O'Brian, when the ' character is being filtered out.
White list validation is appropriate for all input fields provided by the user. White list validation involves defining exactly what IS authorized, and by definition, everything else is not authorized. If it's well structured data, like dates, social security numbers, zip codes, e-mail addresses, etc. then the developer should be able to define a very strong validation pattern, usually based on regular expressions, for validating such input. If the input field comes from a fixed set of options, like a drop down list or radio buttons, then the input needs to match exactly one of the values offered to the user in the first place. The most difficult fields to validate are so called 'free text' fields, like blog entries. However, even those types of fields can be validated to some degree, you can at least exclude all non-printable characters, and define a maximum size for the input field.
Developing regular expressions can be complicated, and is well beyond the scope of this cheat sheet. There are lots of resources on the internet about how to write regular expressions, including: http://www.regular-expressions.info/. The following provides a few examples of ‘white list’ style regular expressions:
White List Regex Examples
Validating Data from Free Form Text Field for Zip Code (5 digits plus optional -4) ^\d{5}(-\d{4})?$
Validating Data from Fixed List Drop-Down Box For U.S. State Selection
^(AL|AK|AS|AZ|AR|CA|CO|CT|DE|DC|FM|FL|GA|GU|HI|ID|IL|IN|IA|KS|KY|LA|ME|MH|MD|MA|MI|MN|MS|
MO|MT|NE|NV|NH|NJ|NM|NY|NC|ND|MP|OH|OK|OR|PW|PA|PR|RI|SC|SD|TN|TX|UT|VT|VI|VA|WA|WV|WI|WY)$
Validating a Free Form Text Field for allowed chars (numbers, letters, whitespace, .-_)
^[a-zA-Z0-9\s.\-_]+$ (Any number of characters)
^[a-zA-Z0-9\s.\-_]{1-100}$ (This is better, since it limits this field to 1 to 100 characters)
Note: \s matches any whitespace character (i.e., space, tab, carriage return, or linefeed, [ \t\r\n])
Additional Examples are available at the OWASP Validation Regex Repository
Java Regex Usage Example
Example validating the parameter “zip” using a regular expression.
private static final Pattern zipPattern = Pattern.compile("^\d{5}(-\d{4})?$");
public void doPost( HttpServletRequest request, HttpServletResponse response) {
try {
String zipCode = request.getParameter( "zip" );
if ( !zipPattern.matcher( zipCode ).matches() {
throw new YourValidationException( "Improper zipcode format." );
}
.. do what you want here, after its been validated ..
} catch(YourValidationException e ) {
response.sendError( response.SC_BAD_REQUEST, e.getMessage() );
}
}
Some white list validators have also been predefined in various open source packages that you can leverage. Two packages that provide this are:
It is strongly recommended that you use ESAPI to assist with your input validation needs, rather than writing your own validation routines. The OWASP Enterprise Security API (ESAPI) project has predefined validators defined in the org.owasp.esapi.Validator interface and implemented in the DefaultValidator reference implementation. These include:
- getValidDate()
- getValidCreditCard()
- getValidSafeHTML()
- getValidInput()
- getValidNumber()
- getValidFileName()
- getValidRedirectLocation()
With ESAPI, the previous example can be rewritten as follows:
Example validating the parameter “zip” with generic ESAPI input validator.
public void doPost( HttpServletRequest request, HttpServletResponse response) {
try {
String zipCode = Validator.getValidInput("ChangeAddressPage_ZipCodeField",
request.getParameter( "zip" ), "zipCodePattern", 10, false));
.. do what you want with validated ‘zipCode’ param here ..
} catch( ValidationException e ) {
response.sendError( response.SC_BAD_REQUEST, e.getMessage() );
}
}
// zipCodePattern is the name of a property defined in ESAPI.properties, and its value
// is the regular expression: "^\d{5}(-\d{4})?$"
//
// If zipcodes were a frequently used parameter in your application, we would recommend
// that you create your own getValidZipCode() method that builds on top of ESAPI, to make
// it even simpler for your developers to use.
- The overall javadoc for ESAPI is here
- And the javadoc for this specific interface is here.
Related Articles
Description of SQL Injection Vulnerabilities
The OWASP article on SQL Injection Vulnerabilities. The OWASP article on Blind_SQL_Injection Vulnerabilities.
How to Avoid SQL Injection Vulnerabilities
The OWASP Guide article on how to Avoid SQL Injection Vulnerabilities.
How to Review Code for SQL Injection Vulnerabilities
The OWASP Code Review Guide article on how to Review Code for SQL Injection Vulnerabilities.
How to Test for SQL Injection Vulnerabilities
The OWASP Testing Guide article on how to Test for SQL Injection Vulnerabilities.
Other Articles in the Prevention Cheat Sheet Series
The Prevention Cheat Sheet series was initiated with the following article on XSS Defenses:
