SQL Injection prevention

[Nik Raut]

I ran the Fortify tool on my code base and it pointed the SQL Injection vulnerability in the following code:

String sequenceName = (String) map.get(tableName); //tableName is the input to the method.
String SQL_SEQUENCE = "SELECT " + sequenceName + ".NEXTVAL FROM SOMETABLE";
final String SQL = SQL_SEQUENCE;
stmt = conn.prepareStatement(SQL); //<-- SQL Injection vulnerability here

Can someone please suggest how to remove this vulnerability?

[Chad Clites]

I have never been convinced of the ability of an automated tool to definitively find vulnerabilities. The tableName value that is being passed to the PreparedStatement could very well being retrieved from a drop down menu. If that happens to be the case, and the tableName is being pulled from a controlled vocabulary of some sort, then there is no "vulnerability".

In the case that you have a limited amount of possible table names, then one solution would be to kill the query if the tableName is not in the database before the SQL statement is executed. Just from your little snippet, you will know ahead of time whether or not the query is valid because the table name will be in the map. If it isn't, then you would want to display some other message instead.

Another means of preventing SQL injection is to have a custom error page that catches the SQL error message and displays a generic error message. SQL injection works by sending specially crafted queries, and by reading the error messages generated by these sort of queries. The error messages then give them enough information to fully attack the database. This isn't the case in your example though. If the table name is not in the database, the query will fail, so a simple "If the tablename exists in the map, then execute the query" should suffice.

It's also a good idea if you frequently work with SQL databases to maybe read a PDF or two on the subject just to gain some understanding. SQL injection is fundamentally easy to understand, and only marginally harder to implement.

EDIT: Just as a CMA, I am basing my answer only on the small snippet of code you have given and should be taken as an example only. Obviously I have no way of knowing what else you may have going on with your application as far as SQL access.
[ August 20, 2007: Message edited by: Chad Clites ]

[Jeanne Boyarsky]

"Anikanchan",
Please check your private messages.

Thanks,
Jeanne
JavaRanch Sheriff

[Stan James]

SQL injection problems arise when somebody puts a value into tablename that changes your SQL into something you didn't expect. For example, what if they put:

* FROM USER;DELETE FROM USER;SELECT USER 

making your whole statement

SELECT * FROM USER;DELETE FROM USER;SELECT USER.NEXTVAL FROM SOMETABLE

That might be bad, no?

It ought to help to use a prepared statement with a variable for tablename. When you set the variable the PS will escape any sql characters in the string so the modified statement won't work.

[Jeanne Boyarsky]

Originally posted by Stan James:
It ought to help to use a prepared statement with a variable for tablename.

It would if JDBC allowed that. Unfortunately, we can only use variables for values. Not tablenames, operators or the multiple values in an "in clause."

Which means that the problem likely has to be solved by validation. Fortify is unlikely to recognize this so you will just have to know you removed the vulnerability.

[prakash chandra]

Hi,
Here is the simple solution to avoid SQL injection. In SQL statements dont use the direct parameter name passed to a method. Assign that parameter to one local varible then use that local variable in SQL statements. For example

public void find(String username){
String u1 = username;
// Following will cause SQL injection.
// String statement = "select * from usertables where user='"+username+"'";

// Use following to avoid SQL injection.
String statement = "select * from usertables where user='"+u1+"'";
.........
.........
}

[Nik Raut]

Thanks everyone for useful tips. I am still not convinced if my code is vulnerable to SQL injection. The user input is not directly used in SQL statement instead, it is used to get the value out of map and that value is used in SQL statement. I am convinced that it is a false alarm by Fortify tool and I should ignore it.

[Jeanne Boyarsky]

Anikanchan,
As long as you control the contents of the map, your code is not vulnerable to SQL injection. Although you may want to add code to handle if null is returned from the map to save on the database call.