Thursday, October 16, 2008

What is SQL Injection? How can it be handled?

What is SQL Injection? How can it be handled?

SQL Injection - what's it?

It's an attack where some malicious piece of code is added to a SQL statement which is later passed to a DBMS to be parsed and executed. If the overall SQL statement string is syntactically correct (and of course if the user has rights to fire those SQL commands) then the SQL gets executed by the SQL Engine and consequently makes the database inconsistent (to say the least otherwise such an effort may completely spoil the database as well).

Example: suppose you have a SQL statement string as specified below:

String parm = request.getParamater("parameterName");
String queryString = "SELECT * FROM table_name WHERE column_name = '" + parm + "'";

Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(queryString);

Now, if the end user enters say "value'; DROP TABLE table_name --" as the parameter then the SQL query string will ultimately become "SELECT * FROM table_name WHERE column_name = 'value'; DROP TABLE table_name --'" which is valid SQL statement and it'll first fetch the results of the query and immediately after it it'll drop the table until you actually realize it. Notice the usage of '--' at the end of the parameter which mischievously makes the last "'" of the queryString ineffective. Anything following '--' is considered as comment in SQL ... right? Needless to mention here that ';' is an SQL statement terminator and it gives the attacker a luxury of terminating the expected SQL statement and fire his/her own malicious SQL statement.

Thus we see how easily one can add malicious but valid code to SQL statements and how terrible results can that produce for your application.

How can SQL Injection be handled?

Next obvious questions which comes to mind is - how can we effectively handle it? Can we really restrict it to happen altogether? I really doubt that we can claim 100% avoidance, but we can follow several measures to catch it beforehand and then accordingly we can deal with the situation.

As we can easily understand that as long as a SQL query is valid (and the user has right set of privileges) the execution of SQL queries can hardly be stopped without programmatic intervention both at the middleware level as well as the DBMS level. So how can we actually deal with such a situation? By following the same old rule of not relying on what comes as an input and by ensuring that all the user inputs go through a strict set of validations before they reach the DBMS only in the case they pass all the stages. Now deciding on how many validations the input should go through depends upon the foresight of the DBA/Developer and the success of SQL Injection will then depend upon how far can the DBA/Developer think ahead of the attacker.

Some Typical Validations for SQL Injection avoidance

All Inputs should be strictly validated without making any assumption about their

  • datatype
  • length
  • format
  • range

Strictly checking and rejecting inputs having avoidable characters like

  • ; : the SQL query delimeter (we saw the impact above)
  • -- : the SQL comment
  • ' : data string specifier (it should be appended by the code)
  • /*..*/ : comment delimeters can be used to fool the app to a great extent

Checking the SQL Parameters - as we know that Parameters collection inherently checks the type and length checks so it's better to use them while passing the parameters to the parametrized SQL statements whenever possible.

Checking Injection caused by Truncation - pay attention to the maximum length a variable can hold as rest of the characters in the assigned value will be trunctaed silently and that may cause severe damage in certain cases.

These are just few basic validations. Depending upon the actual DBMS you're using you can have various other validations before the SQL statement is permitted to be parsed and executed.

Liked the article? You may like to Subscribe to this blog for regular updates. You may also like to follow the blog to manage the bookmark easily and to tell the world that you enjoy GeekExplains. You can find the 'Followers' widget in the rightmost sidebar.



Anonymous said...

hi geek,
ur article sql injection found to
be very informative to me


now i have a problem related to abstract we know object of abstract class can not be created. but when we create an obj
of subclass of abstract class then the constructer of super class must be run and since constucter run obj must be created then how can we say obj of abst class can not be created.


Geek said...

Hi Ranvijay,

You've been quite regular here and most of your questions have been really nice. Thanks!

Regarding the Abstract Class problem, it's not that an object of an abstract class is never created. By saying that an object of an abstract classes can't be created we mean that they can't be created explicitly and independently. That means you can't have any code creating them independently.

But, as you have mentioned that while creating a subclass object the object of superclass is always created first and abstract classes are also no exceptions in this case. Otherwise you can't access the accessible members of an abstract superclass in an object of any of its subclasses. All right?

You might have noticed that any code trying to instantiate an abstract class explicitly throws a compile-time error, which means the compiler checks the corresponding classes for every direct instantiation and reports errors whenever it finds any of them being anything other than a concrete class. The error may be thrown if it's an abstract class or an interface or something of that sort. Right?

The moral is that the objects of abstract classes are certainly created (otherwise the whole concept of superclass-subclass object creation would go awry), but only implicitly :-)

What I understand is that this check is only at the compiler level, so what'll happen if somebody manages to temper the bytecodes somehow with the addition of direct instantiation code of an abstract class without disturbing the allowed bytecodes format (otherwise the Verifier component of Linker would throw an error)? I'm not sure if that's possible at all. The Verifier is quite conservative in nature and I believe it should be able to recognize even a slight bytecode tempering.