Application security encompasses measures taken throughout the application's life-cycle to prevent exceptions in the security policy of an application or the underlying system (vulnerabilities) through flaws in the design, development, deployment, upgrade, or maintenance of the application.

Saturday, June 19, 2010

SQL Injection

SQL injection is a code injection technique that exploits a security vulnerability occurring in the database layer of an application. The vulnerability is present when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements.

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;
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.

Primary Defenses:

a) Use of Prepared Statements (Parameterized Queries)
b) Use of Stored Procedures
c) Escaping all User Supplied Input

Additional Defenses:

a) Also Enforce: Least Privilege
b) Also Perform: White List Input Validation

Unsafe Query:

JAVA – Here user_name is integer

String query = "SELECT account_balance FROM user_data WHERE user_name = "
+ request.getParameter("customerName");
try {
Statement statement = connection.createStatement( … );
ResultSet results = statement.executeQuery( query );
}
JAVA – Here user_name is varchar
String query = "SELECT account_balance FROM user_data WHERE user_name = ‘ “ + request.getParameter("customerName")+”’”;
try {
Statement statement = connection.createStatement( … );
ResultSet results = statement.executeQuery( query );
}
.NET – Here user_name is varchar
String query = "SELECT account_balance FROM user_data
WHERE user_name = '" & username.Text & "'" 

Prepared Statements (Parameterized 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

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
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
} 

Stored Procedures

Stored procedures have the same effect as the use of prepared statements when implemented safely*. 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.

*Note: 'Implemented safely' means the stored procedure does not include any unsafe dynamic SQL generation.

Safe Java Stored Procedure Example
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
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
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.

Escaping All User Supplied Input

This third technique is to escape user input before putting it in a query. 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 one or more character escaping schemes specific to certain kinds of queries. If you then escape all user supplied input using the proper escaping 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.

Escape quotes: Use two quotes for every one displayed. Examples
SQL> SELECT 'Frank''s Oracle site' AS text FROM DUAL;
TEXT
--------------------
Franks's Oracle site

Escape wildcard characters: The LIKE keyword allows for string searches. The '_' wild card character is used to match exactly one character, while '%' is used to match zero or more occurrences of any characters. These characters can be escaped in SQL. Examples
SELECT name FROM emp
WHERE id LIKE '%/_%' ESCAPE '/';
SELECT name FROM emp
WHERE id LIKE '%\%%' ESCAPE '\';

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.

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.

If you adopt a policy where you use stored procedures everywhere, and don’t allow application accounts to directly execute their own queries, and 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.

White List Input Validation

Input validation can be used to detect unauthorized input before it is passed to the SQL query.

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.
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
^(AA|AE|AP|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])
Referance: OWASP


PL post your queries.......

and any other suggestion to remediate SQL Injection........


No comments:

Post a Comment