Input Validation Vulnerabilities (SQLIA) and Defenses in Web Applications Security

(1)

International Journal of Emerging Technology and Advanced Engineering

Website: www.ijetae.com (ISSN 2250-2459,ISO 9001:2008 Certified Journal, Volume 4, Issue 7, July 2014)

207

Input Validation Vulnerabilities (SQLIA) and Defenses in Web

Applications Security

Venkatramulu sunkari

1

, Dr. C. V. Guru Rao

2 1

Assoc. Prof. CSE Dept. KITS Warangal

2_{Professor and principal S.R Engineering College Warangal} Abstract--The internet has evolved into a critical delivery

pipeline for institutions to interact with Customers, partners and employees. Peoples use web sites to send and receive Information via Hypertext Markup Language (HTML) messages to web applications reside on web servers. Generally this information, expected as legitimate messages, can be used illegitimately by the unauthorized persons to compromise security vulnerabilities. Web application vulnerabilities provide the potential for an unauthorized party to gain access to critical and sensitive information, use resources inappropriately, interrupt web based business transactions or commit fraud. The cause for all inconvenience and fraud in web, improper input handling is one of the most common weaknesses identified across applications today. Poorly handled input is a leading cause behind critical vulnerabilities that exist in systems and applications. Generally, the term input handling is used to describe functions like validation, sanitization, filtering, encoding and/or decoding of input data. Applications receive input from various sources including human users, software agents, and network/peripheral devices etc. all input data should be considered as untrusted and potentially malicious. Applications which process untrusted input may become vulnerable to attacks such as Buffer Overflows, SQL Injection, XSS attacks, OS Commanding, Denial of Service and many more. Among all, SQLIA and XSS are most common and serious threats to web applications. This paper describes all aspects of SQL injection attacks and defenses.

Keywords-- input validation, SQLIA, XSS attacks, web applications, SQL injection Defense, untrusted data

I. INTRODUCTION

One of the key aspects of input handling is validating that the input satisfies a certain criteria [2]. For proper validation, it is important to identify the form and type of data that is acceptable and expected by the application. Defining an expected format and usage of each instance of untrusted input is required to accurately define restrictions. Validation can include checks for type safety and correct syntax. String input can be checked for length (min & max number of characters) and character set [1] validation while numeric input types like integers and decimals can be validated against acceptable upper and lower bound of values.

When combining input from multiple sources, validation should be performed during concatenation and not just against the individual data elements. This practice helps avoid situations where input validation may succeed when performed on individual data items but fails when done on a combined set from all the sources [11].

The OWASP Top 10 lists [3]&[7] Injection and Cross-Site Scripting (XSS) as the most common security risks to web applications. Indeed, they go hand in hand because XSS attacks are contingent on a successful Injection attack. While this is the most obvious partnership, Injection is not just limited to enabling XSS. Injection is an entire class of attacks that rely on injecting data into a web application in order to facilitate the execution or interpretation of malicious data in an unexpected manner. Examples of attacks within this class include Cross-Site Scripting (XSS), SQL Injection, Header Injection, Log Injection and Full Path Disclosure. I‘m scratching the surface here. This class of attacks is every programmer‘s bogeyman. They are the most common and successful attacks on the internet due to their numerous types, large attack surface, and the complexity sometimes needed to protect against them. All applications need data from somewhere in order to function. Injection flaws, such as SQL, OS, and LDAP injection, occur when untrusted data is sent to an interpreter as part of a command or query. The attacker‘s hostile data can trick the interpreter into executing unintended commands or accessing unauthorized data.

II.SQLINJECTION

(2)

International Journal of Emerging Technology and Advanced Engineering

208

Programmers will often trust data from their own database believing it to be completely safe without realizing that being safe for one particular usage does not mean it is safe for all other subsequent usages. Data from a database should be treated as untrusted unless proven otherwise, e.g. through validation processes. an SQL Injection can manipulate the SQL query being targeted to perform a database operation not intended by the programmer.

Consider the following query:

$db=newmysqli('localhost','username','password', 'storedb');

$result = $db->query(

'SELECT * FROM transactions WHERE user_id = '. $_POST['user_id']

);

The above has a number of things wrong with it. First of all, we haven‘t validated the contents of the POST data to ensure it is a valid user_id. Secondly, we are allowing an untrusted source to tell us which user_id to use - an attacker could set any valid user_id they wanted to. Perhaps the user_id was contained in a hidden form field that we believed safe because the web form would not let it be edited (forgetting that attackers can submit anything). Thirdly, we have not escaped the user_id or passed it to the query as a bound parameter which also allows the attacker to inject arbitrary strings that can manipulate the SQL query given we failed to validate it in the first place. The above three failings are remarkably common in web applications. As to trusting data from the database, imagine that we searched for transactions using a user_name field. Names are reasonably broad in scope and may include quotes. It‘s conceivable that an attacker could store an SQL Injection string inside a user name. When we reuse that string in a later query, it would then manipulate the query string if we considered the database a trusted source of data and failed to properly escape or bind it.

Another factor of SQL Injection[8] to pay attention to is that persistent storage need not always occurs on the server. HTML5 supports the use of client side databases which can be queried using SQL with the assistance of Javascript. There are two APIs facilitating this: WebSQL and IndexedDB. WebSQL was deprecated by the W3C in 2010[3] and is supported by WebKit browsers using SQLite in the backend. Its support in WebKit will likely continue for backwards compatibility purposes even though it is no longer recommended for use. As its name suggests, it accepts SQL queries may therefore be susceptible to SQL Injection attacks.

Indexed DB is the newer alternative but is a NOSQL database (i.e. does not require usage of SQL queries).

A. SQL Injection Examples

Attempting to manipulate SQL queries may have goals including:

 Information Leakage

 Disclosure of stored data

 Manipulation of stored data

 Bypassing authorization controls

 Client-side SQL Injection

B. Variants of SQLIA

Over the past several years, attackers have developed a wide array of sophisticated attack techniques that can be used to exploit SQL injection vulnerabilities. These techniques go beyond the commonly used tautology-based SQLIA examples and take advantage of esoteric and advanced SQL constructs. Ignoring the existence of these kinds of attacks leads to the development of solutions that address the SQLIA problem only partially. For example, SQLIA can be introduced into a program using several different types of input sources. Developers and researchers often assume that SQLIAs are only introduced via user input that is submitted as part of a web form or as a response to a prompt for input. This assumption misses the fact that any external string or input that is used to build a query string can be under the control of an attacker and represents a possible input channel for SQLIAs. It is common to see other external sources of input such as fields from an HTTP cookie or server variables being used to build a query. Since cookie values are under the control of the user's browser and server variables are often set via values from HTTP headers, these values represent external strings that can be manipulated by an attacker. In addition,

(3)

International Journal of Emerging Technology and Advanced Engineering

209

Depending on the type and extent of the vulnerability, the results of these attacks can include crashing the database, gathering information about the tables in the database schema, establishing covert channels, and open-ended injection of virtually any SQL command [10].

Tautologies.

The general goal of a tautology-based attack is to inject SQL tokens that cause the query's conditional statement to always evaluate to true. Although the results of this type of attack are application specific, the most common uses are to bypass authentication pages and extract data. In this type of injection, an attacker exploits a vulnerable input field that is used in the query's WHERE conditional. This conditional logic is evaluated as the database scans each row in the table. If the conditional represents a tautology, the database matches and returns all the rows in the table as opposed to matching only one row, as it would normally do in the absence of injection.

Malformed Queries.

This attack technique takes advantage of overly descriptive error messages that are returned by the database when a query is rejected. Database error messages often contain useful debugging information that also allows an attacker to accurately identify which parameters are vulnerable in an application and the complete schema of the underlying database. Attackers exploit this situation by injecting SQL tokens or garbage input that causes the query to contain syntax errors, type mismatches, or logical errors. Consider our example, an attacker could try to cause a type mismatch error by injecting the following text into the pin

input field: " c o n v e r t ( i n t , ( s e l e c t top 1 name from s y s o b j e c ts where xtype = ' u ' ) ) ".The resulting query generated by the web application would be:

SELECT info FROM users WHERE login=" AND pin= convert (int,(select top 1 name from sysobjects where xtype='u'))

In the attack string, the injected select query extracts the name of the first user table (xtype=' u ' ) from the database's metadata table, s y s o b j e c t s , which contains information on the structure of the database. It then converts this table name to an integer. Because the name of the table is a string, the conversion is illegal, and the database returns an error. For example, an SQL Server may return the following error: "Microsoft OLE DB Provider for SQL Server (Ox80040E07) Error converting nvarchar value 'CreditCards' to a column of data type int." There are two useful pieces of information in this message that aid an attacker.

First, the attacker can see that the database is an SQL Server database, as the error message explicitly states this. Second, the error message reveals the string that caused the type conversion to occur (in this case, the name of the first user-defined table in the database, "CreditCards"). A similar strategy can be used to systematically extract the name and type of each column in the given table. Using this information about the schema of the database, an attacker can create more precise attacks that specifically target certain types of information. Attacks based on malformed queries are typically.

Used as a preliminary information-gathering step for other attacks.

Union Query.

The Union Query technique refers to injection attacks in which an attacker causes the application to return data from a table that is different from the one that was intended. To this end, attackers inject a statement of the form "UNION < i n j e c t ed query>". By suitably defining <in j e c t e d query>, attackers can retrieve information from a specified table. The outcome of this attack is that the database returns a dataset that is the union of the results of the original query with the results of the injected query. In our example, an attacker could perform a Union Query injection by injecting the text "' UNION SELECT cardNo from C r e d i t C a r ds 5 Detection and Prevention of SQL Injection Attacks 91 where acctNo=10032—" into the login field. The application would then produce the following query:

SELECT info FROM users WHERE login=" UNION SELECT cardNo from CreditCards where acctNo=10032 — AND pin=

Assuming that there is no login equal to "" (the empty string), the original query returns the null set, and the injected query returns data from the "CreditCards" table.In this case, the database returns field "cardNo" for account "10032." The database takes the results of these two queries, unions them together, and returns them to the application. In many applications, the effect of this attack would be that the value for "cardNo" is displayed with the account information.

Piggy-backed Queries.

(4)

International Journal of Emerging Technology and Advanced Engineering

210

In our example application, an attacker could inject the text " 0 ; drop table users " into the pin input field. The application would then generate the query:

SELECT info FROM users WHERE login='doe' AND pin=0; drop table users

The database treats this query string as two queries separated by the query delimiter,";", and executes both. The second, malicious query causes the database to drop the users table in the database, which would have the catastrophic consequence of deleting all of the database users. Other types of queries can be executed using this technique, such as insertion of new users into the database or execution of stored procedures. It is worth noting that many databases do not require a special character to separate distinct queries, so simply scanning for a special character is not an effective way to prevent this attack technique.

Stored Procedures.

In this technique, attackers focus on the stored procedures that are present on the database system. Stored procedures are code that is stored in the database and run directly by the database engine. Stored procedures enable a programmer to code database or business logic directly into the database and provide an extra layer of abstraction. It is a common misconception that the use of stored procedures protects an application from SQLIAs[18]. Stored procedures are just code and can be just as vulnerable as the application's code. Depending on the specific stored procedures that are available on a database, an attacker has different ways of exploiting a system. The following example demonstrates how a parameterized stored procedure can be exploited via an SQLIA. To perform an SQLIA that exploits this stored procedure, the attacker can simply inject the text"

CREATE PROCEDURE DBO.isAuthenticated @userName varchar2, @pin int AS

EXEC("SELECT info FROM users

WHERE login='" +@userName+ "' and pin=" +@pin); GO

Stored procedure for checking credentials.‘ SHUTDOWN; —" into the userName field. This injection causes the stored procedure to generate the following query:

SELECT info FROM users WHERE login=' '; SHUTDOWN; — AND pin=

This attack works like a piggy-back attack. When the second query is executed, the database is shut down.

Inference.

Inference-based attacks create queries that cause an application or database to behave differently based on the results of the query. In this way, even if an application does not directly provide the results of the query to the attacker, it is possible to observe side effects caused by the query and deduce the results. These attacks allow an attacker to extract data from a database and detect vulnerable parameters. Researchers have reported that, using these techniques, they have been able to achieve a data extraction rate of one byte per second [9]. There are two well-known attack techniques that are based on inference: blind-injection and timing attacks. Blind Injection: In this variation, an attacker performs queries that have a Boolean result. The queries cause the application to behave correctly if they evaluate to true, whereas they cause an error if the result is false. Because error messages are easily distinguishable from normal results, this approach provides a way for an attacker to get an indirect response from the database. One possible use of blind-injection is to determine which parameters of an application are vulnerable to SQLIA. Two possible injections into the

login field

are " l e g a l U s e r ' and 1=0 —" and " l e g a l U s e r ' and 1=1 —". These

Injections result in the following two queries:

SELECT Info FROM users WHERE logln='legalUser' and 1=0 — ' AND pin=

SELECT info FROM users WHERE login='legalUser' and 1=1 — ' AND pin=

(5)

International Journal of Emerging Technology and Advanced Engineering

211

Timing Attacks: A timing attack lets an attacker gather information from a database by observing timing delays in the database's responses. This attack is similar to blind injection, but uses a different type of observable side effect. To perform a timing attack, attackers structure their injected query in the form of an if-then statement whose branch condition corresponds to a question about the contents of the database. The attacker then uses the WAITFOR keyword along one of the branches, which causes the database to delay its response by a specified time. By measuring the increase or decrease in the database response time, attackers can infer which branch was taken and the answer to the injected question. Using our example, we illustrate how to use a timing-based inference attack to extract a table name from the database. In this attack, the following text is injected into the login parameter:

legal User' and ASCII(SUBSTRING((select top 1 name from sysobjects) , 1, 1)) >

X WAITFOR 5 —

This injection produces the following query:

SELECT i n t o FROM u s e r s WHERE l o g l n = ' l e g a l U s e r ' and ASCII(SUBSTRING((select top

1 name from s y s o b j e c t s ) , 1 , 1 ) ) > X WAITFOR 5 — ' AND pin=

In this attack, the SUBSTRING function is used to extract the first character of the first table's name. The attacker can then ask a series of questions about this character. In this example, the attacker is asking if the ASCII value of the character is greater than or less-than-or-equal-to the value of X. If the value is greater, the attacker will be able to observe an additional five-second delay in the database response. The attacker can continue in this way and use a binary-search strategy to identify the value of the first character, then the second character, and so on.

Alternate Encodings.

Using alternate encoding techniques, attackers modify their injection strings in a way that avoids typical signature- and filter-based checks that developers put in their applications. Alternate encodings, such as hexadecimal, ASCII, and Unicode can be used in conjunction with other techniques to allow an attack to escape straightforward detection approaches that simply scan for certain known "bad characters." Even if developers account for alternative encodings, this technique can still be successful because alternate encodings can target different layers in the application. For example, a developer may scan for a Unicode or hexadecimal encoding of a single quote and not realize that the attacker can leverage a database function (e.g., c h a r (44)) to encode the same character.

An effective code-based defense against alternate encodings requires developers to be aware of all of the possible encodings that could affect a given query string as it passes through the different application layers. Because developing such a complete protection is very difficult in practice, attackers have been very successful in using alternate encodings to conceal attack strings. The following example attack (from [11]) shows the level of obfuscation that can be achieved using alternate encodings. In the attack, the pin field is injected with the following string: "0; exec (0x73587574 64 5f77 6e)," and the resulting query is:

SELECT info FROM users WHERE login=" AND pin=0; exec(char(0x73687574646f776e))

This example makes use of the c h a r () function and ASCII hexadecimal encoding. The c h a r {) function takes as a parameter an integer or hexadecimal encoding of one or more characters and replaces the function call with the actual character(s).The stream of numbers in the second part of the injection is the ASCII hexadecimal encoding of the attack string. This encoded string is inserted into a query using some other type of attack profile and, when it is executed by the database, translates into the shutdown command.

III. DEFENSES

This paper focused on providing clear, simple, actionable guidance for preventing SQL Injection flaws in applications. SQL Injection attacks are unfortunately very common, and this is due to two factors:

1. the significant prevalence of SQL Injection vulnerabilities, and

2. The attractiveness of the target (i.e., the database typically contains all the interesting/critical data for your application).

It‘s somewhat shameful that there are so many successful SQL Injection attacks occurring, because it is EXTREMELY simple to avoid SQL Injection vulnerabilities in our code.

SQL Injection flaws are introduced when software developers create dynamic database queries that include user supplied input. To avoid SQL injection flaws is simple [17]. Developers need to either: a) stop writing dynamic queries [13]; and/or b) prevent user supplied input which contains malicious SQL from affecting the logic of the executed query.

(6)

International Journal of Emerging Technology and Advanced Engineering

212

There are other types of databases, like XML databases, which can have similar problems (e.g., XPath and XQuery injection) and these techniques can be used to protect them as well.

Primary Defenses:

 Option #1: Use of Prepared Statements (Parameterized Queries)

 Option #2: Use of Stored Procedures

 Option #3: Escaping all User Supplied Input

Additional Defenses:

 Also Enforce: Least Privilege

 Also Perform: White List Input Validation SQL injection flaws typically look like this:

The following (Java) example is UNSAFE, and would allow an attacker to inject code into the query that would be executed by the database. The unvalidated ―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 all too common.

String query = "SELECT account_balance FROM user_data WHERE user_name = "

+ request.getParameter("customerName");

try {

Statement statement =

connection.createStatement( … );

ResultSet results = statement.executeQuery( query );

}

A. Primary Defenses

Defense Option 1: Prepared Statements (Parameterized Queries)

The use of prepared statements (parameterized queries) is how all developers should first be taught how to write database queries. They are simple to write, and easier to understand than dynamic 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.

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)

 SQLite - use sqlite3_prepare() to create a statement object

In rare circumstances, prepared statements can harm performance. When confronted with this situation, it is best to escape all user supplied input using an escaping routine specific to your database vendor as is described below, rather than using a prepared statement. Another option which might solve your performance issue is to use a stored procedure instead.

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

(7)

International Journal of Emerging Technology and Advanced Engineering

213

OleDbDataReader reader =

command.ExecuteReader();

// …

} catch (OleDbException se) { // error handling }

We have shown examples in Java and .NET but practically all other languages, including Cold Fusion, and Classic ASP, support parameterized query interfaces. Even SQL abstraction layers, like the Hibernate Query Language (HQL) have the same type of injection problems (which we call HQL Injection). HQL supports parameterized queries as well, so we can avoid this problem:

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

Developers tend to like the Prepared Statement approach because all the SQL code stays within the application. This makes your application relatively database independent. However, other options allow you to store all the SQL code in the database itself, which has both security and non-security advantages. That approach, called Stored Procedures, is described next.

Defense Option 2: 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. Both of these techniques have the same effectiveness in preventing SQL injection so your organization should choose which approach makes the most sense for you.

*Note: 'Implemented safely' means the stored procedure does not include any unsafe dynamic SQL generation. Developers do not usually generate dynamic SQL inside stored procedures. However, it can be done, but should be avoided. If it can't be avoided, the stored procedure must use input validation or proper escaping as described in this article to make sure that all user supplied input to the stored procedure can't be used to inject SQL code into the dynamically generated query. Auditors should always look for uses of sp_execute, execute or exec within SQL Server stored procedures. Similar audit guidelines are necessary for similar functions for other vendors.

There are also several cases where stored procedures can increase risk. For example, on MS SQL server, we have 3 main default roles: db_datareader, db_datawriter and db_owner. Before stored procedures came into use, DBA's would give db_datareader or db_datawriter rights to the webservice's user, depending on the requirements. However, stored procedures require execute rights, a role that is not available by default. Some setups where the user management has been centralized, but is limited to those 3 roles, cause all web apps to run under db_owner rights so stored procedures can work. Naturally, that means that if a server is breached the attacker has full rights to the database, where previously they might only have had read-access. More on this topic here. http://www.sqldbatips.com/showarticle.asp?ID=8

Safe Java Stored Procedure Example

The following code example uses a Callable Statement, 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) {

(8)

International Journal of Emerging Technology and Advanced Engineering

214

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

We have shown examples in Java and .NET but practically all other languages, including Cold Fusion, and Classic ASP, support the ability to invoke stored procedures. For organizations that already make significant or even exclusive use of stored procedures, it is far less likely that they have SQL injection flaws in the first place. However, you still need to be careful with stored procedures because it is possible, although relatively rare, to create a dynamic query inside of a stored procedure that is subject to SQL injection. If dynamic queries in stored procedures can‘t be avoided, then validate or properly escape all user supplied input to the dynamic query, before we construct it.There are also some additional security and non-security benefits of stored procedures 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 that database. Some non-security benefits include performance benefits (in most situations), and having all the SQL code in one location, potentially simplifying maintenance of the code and keeping the SQL code out of the application developers' hands, leaving it for the database developers to develop and maintain.

Defense Option 3: Escaping All User Supplied Input

This third technique is to escape user input before putting it in a query. If we concerned that rewriting dynamic queries as prepared statements or stored procedures might break our application or adversely affect performance, then this might be the best approach. However, this methodology is frail compared to using parameterized queries and we cannot guarantee it will prevent all SQL Injection in all situations. This technique should only be used, with caution, to retrofit legacy code in a cost effective way. Applications built from scratch, or applications requiring low risk tolerance should be built or re-written using parameterized queries. This technique works like this. Each DBMS supports one or more character escaping schemes specific to certain kinds of queries. If escape all user supplied input using the proper escaping scheme for the database is using, the DBMS will not confuse that input with SQL code written by the developer, thus avoiding any possible SQL injection vulnerabilities[15].

 Full details on ESAPI are available here on OWASP.

 The javadoc for ESAPI is available here at its Google Code repository.

 You can also directly browse the source at Google, which is frequently helpful if the javadoc isn't perfectly clear.

To find the javadoc specifically for the database encoders, click on the ‗Codec‘ class on the left hand side. There are lots of Codecs implemented. The two Database specific codecs are OracleCodec, and MySQLCodec.

Just click on their names in the ‗All Known Implementing Classes:‘ at the top of the Interface Codec page.

At this time, ESAPI currently has database encoders for:

 Oracle

 MySQL (Both ANSI and native modes are supported)

Database encoders for:  SQL Server

 PostgreSQL

Are forthcoming. If database encoder is missing, please let us know.

Database Specific Escaping Details

(9)

International Journal of Emerging Technology and Advanced Engineering

215

Oracle Escaping

This information is based on the Oracle Escape character

information found here:

http://www.orafaq.com/wiki/SQL_FAQ#How_does_one_e scape_special_characters_when_writing_SQL_queries.3F

Escaping Dynamic Queries

To use an ESAPI database codec is pretty simple. An Oracle example looks something like:

ESAPI.encoder().encodeForSQL( new OracleCodec(), queryparam );

So, if you had an existing Dynamic query being generated in your code that was going to Oracle that looked like this:

String query = "SELECT user_id FROM user_data WHERE user_name = '" + req.getParameter("userID") + "' and user_password = '" + req.getParameter("pwd") +"'";

try {

Statement statement = connection.createStatement( … ); ResultSet results = statement.executeQuery( query ); }

You would rewrite the first line to look like this:

Codec ORACLE_CODEC = new OracleCodec(); String query = "SELECT user_id FROM user_data WHERE user_name = '" +

ESAPI.encoder().encodeForSQL( ORACLE_CODEC, req.getParameter("userID")) + "' and user_password = '" + ESAPI.encoder().encodeForSQL( ORACLE_CODEC, req.getParameter("pwd")) +"'";

And it would now be safe from SQL injection, regardless of the input supplied.

For maximum code readability, you could also construct your own OracleEncoder.

Encoder oe = new OracleEncoder();

String query = "SELECT user_id FROM user_data WHERE user_name = '"

+ oe.encode( req.getParameter("userID")) + "' and user_password = '"

+ oe.encode( req.getParameter("pwd")) +"'";

With this type of solution, all your developers would have to do is wrap each user supplied parameter being passed in into an ESAPI.encoder().encodeForOracle( ) call or whatever you named it, and you would be done.

Turn off character replacement

Use SET DEFINE OFF or SET SCAN OFF to ensure that automatic character replacement is turned off. If this character replacement is turned on, the & character will be treated like a SQLPlus variable prefix that could allow an attacker to retrieve private data.

See

http://download.oracle.com/docs/cd/B19306_01/server.102 /b14357/ch12040.htm#i2698854 and http://stackoverflow.com/questions/152837/how-to-insert-a-string-which-contains-an for more information

Escaping Wildcard characters in like Clauses

The LIKE keyword allows for text scanning searches. In Oracle, the underscore '_' character matches only one character, while the ampersand '%' is used to match zero or more occurrences of any characters. These characters must be escaped in LIKE clause criteria. For example:

SELECT name FROM emp

WHERE id LIKE '%/_%' ESCAPE '/'; SELECT name FROM emp

WHERE id LIKE '%\%%' ESCAPE '\';

Oracle 10g escaping

An alternative for Oracle 10g and later is to place { and } around the string to escape the entire string. However, you have to be careful that there isn't a } character already in the string. You must search for these and if there is one, then you must replace it with }}. Otherwise that character will end the escaping early, and may introduce a vulnerability.

MySQL Escaping

MySQL supports two escaping modes:

1. ANSI_QUOTES SQL mode, and a mode with this off, which we call

2. MySQL mode.

ANSI SQL mode: Simply encode all ' (single tick) characters with '' (two single ticks)

MySQL mode, do the following:

NUL (0x00) --> \0 [This is a zero, not the letter O] BS (0x08) --> \b

(10)

International Journal of Emerging Technology and Advanced Engineering

216

' (0x27) --> \'

\ (0x5c) --> \\ _ (0x5f) --> \_

all other non-alphanumeric characters with ASCII values less than 256 --> \c

where 'c' is the original non-alphanumeric character.

This information is based on the MySQL Escape character information found here: http://mirror.yandex.ru/mirrors/ftp.mysql.com/doc/refman/ 5.0/en/string-syntax.html

SQL Server Escaping

We have not implemented the SQL Server escaping routine yet, but the following has good pointers to articles describing how to prevent SQL injection attacks on SQL server

 http://blogs.msdn.com/raulga/archive/2007/01/04/ dynamic-sql-sql-injection.aspx

DB2 Escaping

This information is based on DB2 WebQuery special characters found here: https://www-304.ibm.com/support/docview.wss?uid=nas14488c61e322 3e8a78625744f00782983 as well as some information from Oracle's JDBC DB2 driver found here: http://docs.oracle.com/cd/E12840_01/wls/docs103/jdbc_dri vers/sqlescape.html

Information in regards to differences between several DB2 Universal drivers can be found here: http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.j sp?topic=/com.ibm.db2.udb.doc/ad/rjvjcsqc.htm

B. Additional Defenses

Beyond adopting one of the three primary defenses, we also recommend adopting all of these additional defenses in order to provide defense in depth. These additional defenses are:

 Least Privilege

 White List Input Validation

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, rather than the underlying table. Rarely, if ever, grant creates 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 that the DBMS runs under. 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.

IV. CONCLUTION

(11)

International Journal of Emerging Technology and Advanced Engineering

217

These are useful for upcoming researchers to motivate in a right way and software developers to learn about different types of attacks and defenses in order to defend the applications from attacks.

REFERENCES

Character encodings in HTML

[1] http://en.wikipedia.org/wiki/Character_encodings_in_HTML

Secure input and output handling

[2] http://en.wikipedia.org/wiki/Secure_input_and_output_handling

OWASP Enterprise Security API

[3] http://www.owasp.org/index.php/Category:OWASP_Enterprise_Sec

urity_API

W3C XML Schema

[4] http:// www.w3.org/XML/Schema

SQL Cheat Sheet

[5] http://ha.ckers.org/sqlinjection/

SQL Injection

[6] http://projects.webappsec.org/SQL-Injection

[7] http://www.owasp.org/index.php/Top_10_2010-A1-Injection,

retrieve on 13/01/2010

[8] C. Anley, ―Advanced SQL Injection In SQL Server

Applications,‖white paper, Next Generation Security Software, 2002.

[9] S.W. Boyd and A.D. Keromytis, ―SQLrand: Preventing SQL

Injection Attacks,‖ Proc. Second Int‘l Conf. Applied Cryptography and Network Security, pp. 292-302, June 2004.

[10] W. Halfond, A. Orso, and P. Manolios, ―Using Positive Tainting and

Syntax-Aware Evaluation to Counter SQL Injection Attacks,‖Proc. ACM SIGSOFT Symp. Foundations of Software Eng., pp. 175-185, Nov. 2006.

[11] W.G. Halfond and A. Orso, ―AMNESIA: Analysis and Monitoring

for NEutralizing SQL-Injection Attacks,‖ Proc. 20th IEEE and ACM Int‘l Conf. Automated Software Eng., pp. 174-183, Nov. 2005. [12] D. Balzarotti, M. Cova, V. Felmetsger, N. Jovanovic, E. Kirda,C.

Kr¨ugel, and G. Vigna. Saner: composing static and dynamic analysis to validate sanitization in web applications.In Proceedings of the IEEE Symposium on Security and Privacy, Oakland, CA, USA, May 2008.

[13] Halfond W. G., Viegas, J., and Orso, A., AClassification of SQL-Injection Attacks and Countermeasures. In Proc. of the Intl. Symposium on Secure Software Engineering, Mar. 2006.

[14] Thomas, S., Williams, L., and Xie, T., on automated prepared

statement generation to remove SQL injection vulnerabilities. Information and Software Technology, Volume 51 Issue 3, March 2009, pp. 589–598.

[15] Junjin, M., An Approach for SQL Injection Vulnerability Detection.

Proc. of the 6th International Conference on Information Technology: New Generations, Las Vegas, Nevada, April 2009, pp. 1411-1414.

[16] Amirtahmasebi, K., Jalalinia, S.R., and Khadem, S., A survey of SQL injection defense mechanisms. International Conference for Internet Technology and Secured Transactions (ICITST 2009), 9-12 Nov.(2009), pp. 1-8.

[17] Ke Wei, M. Muthuprasanna, Suraj Kothari. Preventing SQL

Injection Attacks in Stored Procedures.IEEE Software Engineering Conference, 2006. Australian.

[18] Pietraszek, T. Berghe, C. V. 2006. Defending against injection