Saturday, May 23, 2009

SQL Injection: A primer II

In continuation with my earlier post, SQL Injection: A primer (, I will be covering the following in this post:

a. Blind SQL Injection
b. Remediation Measures

Blind SQL Injection is used when a web application is vulnerable to the SQL Injection but the results of the malicious input strings are restricted or not visible to the attacker.

Attacking using Blind Injection involves observing the variations in the page output after the malicious query input is feeded into the application. The page with the vulnerability may be different than the page which displays data. The output display is different depending on the results of the logical statement(s) included with the legitimate SQL statement for that page.

Since the output varies with the attack strings, this attack can be time-intensive.

Types of Blind Injection Attacks:
1. Conditional Responses
This attack involves observing the output from the database by appending different evaluative strings. For example:
select uname from employees where deptID = 'a500' AND 1=1;
will result in a normal output / page display. And if:
select uname from employees where deptID ='a500' AND 1=0;
results in a different result / page display, it would be clear that the page is vulnerable to SQL Injection.

The reason behind this inference is the the behavior of database for the appended query string -> AND 1=1 / AND 1=0. The output must not have varied & the queries would have resulted in the same output regardless of any appended strings as input, had the input validation been in place.

2. Conditional Errors
This attack differs from Conditional Responses by attempting to force the database to evaluate a statement which would eventually throw an error when the query returns TRUE. For example:
select 1/0 from employees where uname='Victor';
1/0 will be evaluated only when the record 'Victor' is found & would then result in an error. This would provide a confirmation to the attack of the existence of the uname 'Victor'.

3. Time Delays
Another important type of Blind SQL attacks is the Time duration taken by the database to execute a long running query or a time delaying statement depending upon the query. The attacker would observe & measure the time taken for the page to load & attempt to determine if the query executed successfully as TRUE.

Remediation / Preventive Measures Against SQL Injection Attacks:

1. Using Parameterized Statements
In this technique, SQL BIND variables are used as the placeholders for the user input. A BIND variable is a question mark for each input parameter. 

The SQL statement is created using these BIND variables, it is compiled (prepared) into an internal ready-to-use form, only awaiting the value of the placeholder now. Once the parameter value (placeholder) is received, this prepared statement is executed & the resultset is presented to the front-end.

For example, in Java:
PreparedStatement prep = conn.prepareStatement("SELECT * FROM USERS WHERE USERNAME=? AND PASSWORD=?");
prep.setString(1, username);
prep.setString(2, password);
Here, the value of the 'username' & 'password' is passed as positional parameter - the question (?) marks, referenced by 1 & 2, respectively. The content of these variables do not have any impact on the final query because it is treated as 'plain data.' Hence, the application is now greatly immune to SQL Injection as against a normal query.

2. Using Stored Procedures
Stored Procedures provide encapsulation to rules for specific actions - Select, Insert, Update, Delete, etc. - into a single procedure. Business rules can be enforced so that if cond. A is TRUE, Allow Action A, else Deny Action A. For example, if a customer is a VIP, then allow him/her to avail a discount, else, apply full amount.

Stored procedures help prevent SQL Injection attacks by limiting the type of SQL statements that can be passed to their parameters. However, they are not the complete solution against SQL Injection.

3. Input Validation
Every input parameter & the value must be validated before the input reaches the database. The input fields could be input box, drop down box, check box, hidden fields, a Button, etc.. The important thing is to understand and chose the validation scheme.

There are 2 validation schemes-
1. Reject known blacklist
2. Accept known whitelist only

Using Blacklist as the sole criterion for filtering & validating the input is not a good idea. This is because of the availability of different schemes available, to convert the form of input, - Unicode, hex, base, etc.. A developer cannot & must not base his validation checks based on the Blacklist sets for it is bound to be incomplete, always.

Instead, Using Whitelist is an effective solution. Whitelist specifies the characters only which would be allowed to pass through. Period.
This allows a developer to exercise better control in defining the positive validation checklist for the input. And it is good for the security tester too :)

Besides these 3 mitigating techniques, there are other methods as well which are helpful in controlling the attack surface available for exploitation.

4. Code Reviews
The Code Reviews checks the source code of the application for flaws with respect to security. A Security professional or a developer with good knowledge & experience on Secure coding practices should do a code review. This is a manual process & hence it is time-consuming process. There are automated tools now available for this purposes. Few of them are: Checkstyle (, JNorm (, Perl::Critic module, Parasoft ( etc.

5. Control Database Error Leakage
As important it is to use Stored Procedures or Parameterized Queries, equally important is to configure the error messages thrown by the database on receiving an expected or invalid input. Think of it - an attacker being able to gather table, database, column name info, or related 'joined' table info through error messages. All your SPs, Parameterized queries, code reviews are no longer effective. Hence, configure the database errors to restrict the details or the debugging information meant to help the developers. Put a generic custom messages, whereever possible.

6. Securing the Web Server
All the code reviews, automated assessments, & manual security assessments CANNOT ensure a 100% security. It is, therefore, essential to balance the web application security measures with a secure network design. Employ a defense-in-depth approach while planning for securing the server:
- place the server in a tier-ed architecture, or at least in a hardened DMZ.
- use Web Application Firewalls (WAF) - though OWASP 2009 showed us bypassing WAFs, it's still good enough. (I will have a post coming on WAF soon.)
- And, Log monitoring.

This concludes the second & final post on SQL Injection: A primer. Please note that this is not complete even now. SQL Injection is a vast topic in itself. I have tried to present the concepts here as clearly as possible, the attack methods, query strings, steps taken to recon & penetrate a target; all may vary from applications & their implementations.

I hope someone finds this useful.

Please feel free to share your feedback.

Thanks for your time.

No comments:

Post a Comment


The views, information & opinions expressed in this blog are my own and do not reflect the views of my current or former employers or employees or colleagues.