By Atish Singh

Here a set of techniques is mentioned, which provide prevention from SQL Injection, one of the dangerous security vulnerability. This technique is beneficial for the technologies, such as Java, .net, php and so on.

Prevention from SQL Injection requires lot of defensive measures to be taken for your application. The basic defensive measures to be taken are considered as the primary defense that consists of some programming techniques, defined as follows:

Primary defenses:
1. Parameterization(Prepare statement)
2. Stored Procedure
3. Escaping all user supplied input

Although, applying the primary defense techniques, you can secure your application from the basic security vulnerabilities, if you want to secure the application a step ahead, then you need to use extra defensive measures defined as follows:

Extra Defenses:
1. Least Privilege
2. White List Input Validation

Let’s take an example that is unsafe and is vulnerable for SQL Injection.
The following (Java) example is UNSAFE, and would allow an attacker to inject code into the query that would be executed by the database.
 String query = “SELECT account_balance FROM user_data WHERE user_name = “+ request.getParameter(”customerName”);
 
 try {
  Statement statement = connection.createStatement();
  ResultSet results = statement.executeQuery(query);
     }

In the preceding example, the invalidated “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 common to be used among programmers.
Considering the preceding example, let’s now discuss the defensive measures that can be used to prevent SQL Injections.

Primary Defenses

Prepared Statement
     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 enters the userID of xyz’ or ‘1′=’1, the parameterized query would not be vulnerable and would instead look for a username which literally matched the entire string xyz’ or ‘1′=’1.

Language specific recommendations:
• Java EE – use PreparedStatement() with bind variables
• .NET – use parameterized queries like SqlCommand() or OleDbCommand() with bind variables
• PHP – use PDO with strongly typed parameterized queries (using bindParam())
• Hibernate - use createQuery() with bind variables (called named parameters in Hibernate)

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
 }

Hibernate Query Language (HQL) Prepared Statement (Named Parameters) Examples

 First is an unsafe HQL Statement
 
 Query unsafeHQLQuery = session.createQuery(”from Inventory where productID=’”+userSuppliedParameter+”‘”);
 
 Here is a safe version of the same query using named parameters
 
 Query safeHQLQuery = session.createQuery(”from Inventory where productID=:productid”);
 safeHQLQuery.setParameter(”productid”, userSuppliedParameter);

(to be continued…)

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.