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
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.