Don’t Get Injected – Fix Your Code

May 15, 2012, by | Start Discussion

When I began doing security review for web applications, one common issue that I encountered was ‘SQL Injection’.  Developers used to pose several questions at me saying that their software is secure as they had followed several measures to mitigate this insidious issue.

The main mitigation adopted was to use Stored Procedures or input validation. While this does reduce certain type of Injections, It doesn’t prevent all. In this article, I will explain what SQL Injection is and what one can do to prevent it.

SQL Injection: 

SQL Injection attacks occur in all database driven web applications. There is a risk in every web application that accepts an end user’s input and uses it to send database queries to an underlying database. A hacker can manipulate the user input and send malicious queries to the database. The impact could range from stealing user’s information, taking control of the server to complete wipe out of the database.

So, the onus is on the developer to ensure that the application’s integrity and reliability is preserved.
SQL Injection: An Example

Consider the below login page which accepts a username and password and lets the user log in.

Let’s assume that the below query is executed when one tries to log on to the database.

In this case, the query would look like:-

SELECT * FROM USERS WHERE USERNAME=’celia’ AND PASSWORD =’password’;

While a naïve user would only provide the correct password and proceed to access the business functionality of the application, a hacker wouldn’t. Now, consider the same form but with input shown as below.

This is how the query will take shape now.

SELECT *  FROM USERS WHERE USERNAME=’1’ or 1=1--’ AND PASSWORD =’password’

As you would see, this will let the user login even when he doesn’t know the username and password. This is a very simple case of SQL Injection.

Mitigation:

The steps suggested here are absolutely needed if you want to mitigate SQL Injection. They are not just recommendation.

 

  • Always validate your input for the right size, format, type and range.
  • Use SQL parameterized Queries
  • Use Stored Procedures
  • Give the least minimum privilege to the database user account that is executing the queries.


Input Validation:

It is very important for your application that it should know what input to expect, what data type it can contain, the format of its input and the minimum and maximum lengths. Though it is bit difficult/time consuming to implement these validations for all input fields, it is a fool proof approach if you want your application to be reliable for a long time.

SQL Parameterized Queries:

Never use string concatenation to build your queries dynamically. Always use place holders or parameterized statements to build your queries. An example is given below.

String query = "SELECT * FROM USERS WHERE username=? And password=? ";
PreparedStatement prepStmt = con.prepareStatement(query);
prepStmt.setString(1, username);
prepStmt.setString(2, password);
ResultSet rs = prepStmt.executeQuery();

An argument when passed through the above statement, will be automatically escaped by the JDBC driver.

Stored Procedures:

Stored procedures by themselves do not help in mitigating SQL Injection. By using a stored procedure, type checking is automatically available for the parameters. Hence, when one uses this method in combinating with parameterized statements, one can minimize SQL injection to a great level. Consider the same SQL written as a procedure call.

CallableStatement stmt = conn.prepareCall("{call SELECT_USER (?,?)}");
stmt.setString(1, username);
stmt.setString(2, password);
stmt.execute();

The procedure that executes in the back end might look similar to below.

create or replace procedure

SELECT_USER( user IN varchar2, pass IN  varchar2,  userid OUT NUMBER,tablename IN varchar2) IS
BEGIN
SELECT USERID from users where username =user and password=pass;
Commit;
END;

One point to note here is to not use exec @sql or dynamic sql inside a stored procedure. If one does that, the advantage of using stored procedure is reduced and SQL Injection will be possible. Check out the below vulnerable code. This code does make the use of Stored Procedures but uses dynamic SQL. This code is still vulnerable to SQL Injection.

create or replace procedure SELECT_USER( user IN varchar2, pass IN  varchar2,  userid OUT NUMBER,tablename IN varchar2) IS

BEGIN
@query= ' SELECT * FROM USERS WHERE ' ||
'username = '''|| user ||
'AND password = ''' || password || '''';
Exec @query;
Commit;
END;

Likewise, Stored Procedures should be used in conjunction with input validation. Just because type checking is done, it doesn’t mean that one can get away without validating their user input.

Minimum Privilege:

Last but not the least, always ensure that the database user executing the queries has only SELECT or the minimum required privilege to use the application. This will prevent the database getting corrupted or wiped out should an attack occur.

So, Start following these simple requirements in your applications and you can be sure that you wouldn’t have a security consultant coming to you and asking you to fix your code.

 

Celia

Celia has been with Infosys for the past 5 years and has been associated with Internet Application Security since August 2010. Her expertise includes Product Development, Secure Code Development, Penetration Testing and Secure Code Analysis. She is a Certified Ethical Hacker and is currently engaged in application security consulting.

 

 

Author bio not avialable

Leave a Reply