Difference between revisions of "Preventing SQL Injection in Java"

From OWASP
Jump to: navigation, search
(References)
(Reverting to last version not containing links to www.textcbasliboc.com)
 
(19 intermediate revisions by 9 users not shown)
Line 1: Line 1:
 +
==Status==
 +
Released 14 Jan 2008
 +
 
==Overview==
 
==Overview==
 
As the name implies, SQL injection vulnerabilities allow an attacker to inject (or execute) SQL commands within an application.  It is one of the most wide spread and dangerous application vulnerability.  The CLASP project provides a good overview of [[SQL injection]].
 
As the name implies, SQL injection vulnerabilities allow an attacker to inject (or execute) SQL commands within an application.  It is one of the most wide spread and dangerous application vulnerability.  The CLASP project provides a good overview of [[SQL injection]].
===Example of SQL injection===
+
 
 +
==Example of SQL injection==
 
The following Java servlet code, used to perform a login function, illustrates the vulnerability by accepting user input without performing adequate input validation or escaping meta-characters:
 
The following Java servlet code, used to perform a login function, illustrates the vulnerability by accepting user input without performing adequate input validation or escaping meta-characters:
 
  conn = pool.getConnection( );
 
  conn = pool.getConnection( );
Line 18: Line 22:
 
  select * from user where username='admin' OR '1'='1' and password=' '
 
  select * from user where username='admin' OR '1'='1' and password=' '
 
This allows an attacker to log in to the site without supplying a password, since the ‘OR’ expression is always true.  Using the same technique attackers can inject other SQL commands which could extract, modify or delete data within the database.
 
This allows an attacker to log in to the site without supplying a password, since the ‘OR’ expression is always true.  Using the same technique attackers can inject other SQL commands which could extract, modify or delete data within the database.
===Attack techniques===
+
 
 +
==Attack techniques==
 
For more information on SQL injection attacks see:
 
For more information on SQL injection attacks see:
* http://www.spidynamics.com/papers/SQLInjectionWhitePaper.pdf
+
* http://www.hdm-stuttgart.de/~ms096/SQLInjectionWhitePaper.pdf
 
* http://www.nextgenss.com/papers/advanced_sql_injection.pdf
 
* http://www.nextgenss.com/papers/advanced_sql_injection.pdf
* http://www.appsecinc.com/presentations/Manipulating_SQL_Server_Using_SQL_Injection.pdf  
+
* http://www.appsecinc.com/presentations/Manipulating_SQL_Server_Using_SQL_Injection.pdf
==Defence Strategy==
+
 
To prevent SQL injection, a two pronged approach is recommended:
+
==Defense Strategy==
* Firstly, all data accepted from user input should be thoroughly <b>validated</b> to ensure that the characters received are part of the set of valid characters for that field;
+
To prevent SQL injection:
* Secondly, all data acted on by SQL commands should be <b>escaped for meta-characters</b>.
+
* All queries should be parametrized.
=== Validating Input ===
+
* All dynamic data should be explicitly bound to parametrized queries.
A general security principle which applies itself well to data validation is that of “deny by default” where data is rejected unless it specifically matches the criteria for known good data. This is also known as a “white list” approach and is the preferred method for performing data validation.  It allows one to define a restricted range for valid data and reject everything that does not fit this set.  The set of valid data should be constrained by:
+
* String concatenation should never be used to create dynamic SQL.
* Type – String, integer, unsigned integer, float etc;
+
 
* Length;
+
For more details, see the OWASP [[SQL Injection Prevention Cheat Sheet]].
* Set of character – for example, only alphabetic characters [a-zA-Z]*;
+
 
* Format – if appropriate the data could be further constrained by specifying a format, e.g.: \d\d\/\d\d\/\d\d
+
==Parameterized Queries==
* Reasonableness – where possible, values should be compared to expected ranges.  For example, a customer ordering 1000 televisions could be suspicious.
+
All data access techniques provide some means for escaping SQL meta-characters automatically. The following sections detail how to perform input validation and meta-character escaping using popular data access technologies.
It is essential that the data validation routines themselves can be trusted, therefore they must be performed on the server side.  Client side validation can be performed as a useful user interface feature, but it must be reinforced by server side validation.
+
 
Where input validation is performed on the server side will depend largely on the frameworks available.  JSF and Struts provide validation functions that are defined in the view layer, while Spring and EJB 3.0 allow validation to be defined in the model.<br>
+
===Prepared Statements===
Input validation provides the first line of defence in preventing dangerous characters from being processed by the application.
+
But even if data is constrained in this way it does not solve the meta-character problem: How should the application handle meta-characters that are defined as valid data, but cannot be used in certain processing contexts?  For example, the single quote (') character may be a valid character in a surname, but this character cannot simply be used in a string that is used to form an SQL statement.  The OWASP Guide project has more information on [[Data Validation]].
+
=== Escaping Meta-characters ===
+
All data access techniques provide some means for escaping SQL meta-characters automatically. The important thing to remember is to <b>never construct SQL statements using string concatenation of unchecked input values.</b>  The following sections detail how to perform input validation and meta-character escaping using popular data access technologies.
+
==Prepared Statements==
+
 
Variables passed as arguments to prepared statements will automatically be escaped by the JDBC driver.<br>
 
Variables passed as arguments to prepared statements will automatically be escaped by the JDBC driver.<br>
 
<b>Example: </b>ps.1<br>
 
<b>Example: </b>ps.1<br>
Line 54: Line 54:
 
PreparedStatement prepStmt = con.prepareStatement("SELECT * FROM user WHERE userId = '+strUserName+'");
 
PreparedStatement prepStmt = con.prepareStatement("SELECT * FROM user WHERE userId = '+strUserName+'");
 
</pre>
 
</pre>
It is highly recommended to use Bind Variables as mentioned in the example ps.1 above. Usage of PreparedStatement with Bind variables defends SQL Injection attacks and improves the performance.
 
  
==Stored Procedures==
+
===Stored Procedures===
==Hibernate==
+
 
According to [http://forum.hibernate.org/viewtopic.php?t=960817&start=0&postdays=0&postorder=asc this forum thread] hibernate uses prepared statements, so it is protected from sql injection.
+
TODO
 +
 
 +
===Hibernate===
 +
According to [http://forum.hibernate.org/viewtopic.php?t=960817&start=0&postdays=0&postorder=asc this forum thread] hibernate uses prepared statements, so it is protected from direct sql injection, but it could still be vulnerable to [[Interpreter_Injection#ORM_Injection| injecting HQL statements]].
 +
 
 +
==Variable Binding==
 +
 
 +
It is critical to use Bind Variables as mentioned in the example ps.1 above. Usage of PreparedStatement with Bind variables defends SQL Injection attacks and improves the performance.
 +
 
 +
 
 +
==Dynamic Queries via String Concatenation==
  
==Ibatis==
+
The important thing to remember is to <b>never construct SQL statements using string concatenation of unchecked input values.</b>  Creating of dynamic queries via the java.sql.Statement class leads to SQL Injection.
==Spring JDBC==
+
==EJB 3.0==
+
  
 
== References ==
 
== References ==

Latest revision as of 13:31, 27 May 2009

Contents

Status

Released 14 Jan 2008

Overview

As the name implies, SQL injection vulnerabilities allow an attacker to inject (or execute) SQL commands within an application. It is one of the most wide spread and dangerous application vulnerability. The CLASP project provides a good overview of SQL injection.

Example of SQL injection

The following Java servlet code, used to perform a login function, illustrates the vulnerability by accepting user input without performing adequate input validation or escaping meta-characters:

conn = pool.getConnection( );
String sql = "select * from user where username='" + username +"' and password='" + password + "'";
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
if (rs.next()) {
loggedIn = true;
	out.println("Successfully logged in");
} else {
	out.println("Username and/or password not recognized");
}

It is possible for attackers to provide a username containing SQL meta-characters that subvert the intended function of the SQL statement. For example, by providing a username of:

admin' OR '1'='1

and a blank password, the generated SQL statement becomes:

select * from user where username='admin' OR '1'='1' and password=' '

This allows an attacker to log in to the site without supplying a password, since the ‘OR’ expression is always true. Using the same technique attackers can inject other SQL commands which could extract, modify or delete data within the database.

Attack techniques

For more information on SQL injection attacks see:

Defense Strategy

To prevent SQL injection:

  • All queries should be parametrized.
  • All dynamic data should be explicitly bound to parametrized queries.
  • String concatenation should never be used to create dynamic SQL.

For more details, see the OWASP SQL Injection Prevention Cheat Sheet.

Parameterized Queries

All data access techniques provide some means for escaping SQL meta-characters automatically. The following sections detail how to perform input validation and meta-character escaping using popular data access technologies.

Prepared Statements

Variables passed as arguments to prepared statements will automatically be escaped by the JDBC driver.
Example: ps.1

String selectStatement = "SELECT * FROM User WHERE userId = ? ";
PreparedStatement prepStmt = con.prepareStatement(selectStatement);
prepStmt.setString(1, userId);
ResultSet rs = prepStmt.executeQuery();

Although Prepared Statements helps in defending against SQL Injection, there are possibilities of SQL Injection attacks through inappropriate usage of Prepared Statements. The example below explains such a scenario where the input variables are passed directly into the Prepared Statement and thereby paving way for SQL Injection attacks.
Example: ps.2

String strUserName = request.getParameter("Txt_UserName"); 
PreparedStatement prepStmt = con.prepareStatement("SELECT * FROM user WHERE userId = '+strUserName+'");

Stored Procedures

TODO

Hibernate

According to this forum thread hibernate uses prepared statements, so it is protected from direct sql injection, but it could still be vulnerable to injecting HQL statements.

Variable Binding

It is critical to use Bind Variables as mentioned in the example ps.1 above. Usage of PreparedStatement with Bind variables defends SQL Injection attacks and improves the performance.


Dynamic Queries via String Concatenation

The important thing to remember is to never construct SQL statements using string concatenation of unchecked input values. Creating of dynamic queries via the java.sql.Statement class leads to SQL Injection.

References