By Atish Singh

(Continued from SQL Injection Prevention Techniques (Series-I))

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. 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 effects in preventing SQL injection so it’s your choice, 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

There are some additional security and non-security benefits of stored procedures also 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 the database. Some non-security benefits include performance benefits (in most situations), and having all the SQL code in one location potentially simplifies maintenance of the code and keeps the SQL code out of the application developers’ hands, leaving it for the database developers to develop and maintain.

Escaping all User Supplied Input

Each DBMS supports a character escaping scheme using which you can escape special characters to indicate to the DBMS that the characters you are providing in the query are intended to be data, and not code. If you 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.

Additional Defenses

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 of the underlying table. Rarely, if ever, grant the 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.

While you are at it, you should minimize the privileges of the operating system account on which the DBMS runs. Don’t run your DBMS as root or system! Most DBMSs run out of the box with a very powerful system account. For example, MySQL runs as system on Windows by default! Change the DBMS’s OS account to something more appropriate, with restricted privileges.

White List Input Validation

It is always recommended to prevent attacks as early as possible in the processing of the user’s (attacker’s) 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. Moreover, these 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.

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…)

By Atish Singh

What is SQL injection?
SQL injection is a type of security exploit in which the attacker “injects” SQL code through a web form input box, to gain access to resources, or make changes to data. It is a technique of injecting SQL commands to exploit non- validated input vulnerabilities in a web application database backend. Programmers use sequential commands with user input, making it easier for attackers to inject commands. Attackers can execute arbitrary SQL commands through the web application.

It exploits web applications that use client-supplied SQL queries. It enables an attacker to execute unauthorized SQL commands. It also takes advantage of unsafe queries in web applications, and builds dynamic SQL queries. For example, when a user logs onto a web page by using a user name and password for validation, a SQL query is used. However, the attacker can use SQL injection to send specially crafted username and password fields that poison the original SQL query.

Where to look for SQL injection?

SQL injection is possible at any pages that allow a user to submit data, for example a log in page, search page, feedback, etc. HTML pages that use POST or GET commands. If POST is used, we cannot see the parameters in the URL. Then we should check the source code of the HTML to get information ,for example ,to check whether it is using POST or GET, look for the <form> tag in the source code

<Form action=search.asp method=post>
<input type=hidden name=X value=z>
</Form>
If input is not given, check for pages like ASP, JSP, CGI, or PHP and check the URL that takes the following parameters:
Example
http://www.xyz.com/index.asp?id=100

How to perform SQL Injection and how it works?
To check SQL injection is possible or not, first try a single quote as a input and wait for response from server if server respond SQL Server error like
Microsoft OLEDB provider for ODBC Drivers
Error ‘8004De14’
[Microsoft] [ODBC Microsoft Access Driver] extra )
In query expression ‘userid=’3306’)or(‘a’=’a’ and password=’”.
/_booking/login3.asp, line 49
the time is to celebrate.

Lets take an example for Login Page where username and password is stored in database and the SQL is fired for retrieve username and password for validation of user.

The original query is
strQry=”select count(*) from users where Username=’ ” +txtUser.text + “’ AND
password=’” + txtPassword.Text +”’”;
In the case of the user entering a valid user name of “abc” and a password of “password”, strQry becomes:
Select Count(*) from users where Username =’abc’ and password=’password’
But when the hackers enter ‘Or 1=1—the query now becomes:
Select count(*) from users where username=’’ or 1=1—‘ and password=’’
Because a pair of hyphens designates the beginning of commenting in SQL, the query becomes simply:
Select count(*) from users where username=’’ or 1=1
This works because the condition 1=1 is always true so interpreter of SQL is confused, and it validates to the injector as a user which is first user in database (may be Admin).

If the input is not taken directly by textbox then try another option from which data goes to query string like the URL
http://www.xyz.com/index.asp?id=100
Here temper the id value assigned single quote or some always true value
http://www.xyz.com/index.asp?id=aaa’ or 1=1—

There are different SQL Injection techniques:
1. Authorization bypass
Bypassing log on forms
2. Using the SELECT command
Used to retrieve data from the database
3. Using the INSERT command
Used to add information to the database
4. Using SQL server stored procedures

This are some inputs which are always true
•    abc’ or 1=1—
•    login:abc’ or 1=1—
•    password:abc’ or 1=1—
•    http://search/index.asp?id=abc’ or 1=1—
•    Depending on the query, try the following possibilities:
•    ‘ or 1=1—
•    “ or 1=1—
•    ‘or  ‘a’=’a
•    “ or “a”=”a
•    ‘) or (‘a’=’a)

How to avoid SQL Injection?

To protect from SQL Injection we should avoid the use of interpreter if possible otherwise use safe APIs, Strongly typed parameterized queries (ORM). They handle data escaping.  Validate all input data (length, type, syntax, business rules etc) validation is done before displaying or storing any data, Validation must be done server-side(JavaScript validation doesn’t bring any security) Enforce least privilege, Configure your DB such that the web account can’t do more than what is expected, avoid detailed error messages, Give access to versions numbers, Give access to parts of the code, Give access to configurations, Use stored procedures.