How to prevent SQL injection if I don't have option to use "PreparedStatement" in Java/J2EE

I have one application In which I can’t user “PreparedStatement” on some of places.

Most of SQL queries are like…. String sql = "delete from " + tableName;

So I like to know how to fix “SQL Injection” problem in my code.

Regards, Sanjay Singh

=======================Edited After getting answer and like to verify solution========== According开发者_StackOverflow to provided suggestion I have identified one strategy to prevent SQL injection in my case …. Like to know views, I am working on the VeraCode Certificate for our application…

Filter Data so it does not content any space and escape SQL character (so if there is any injected code, it’ll not going to part of my dynamic SQL, so my column name and table name can’t use to inject SQL query).

public static String getTabColName(String tabColName)

{

if(tabColName == null || "".equals(tabColName.trim()))

return "";

String tempStr = StringEscapeUtils.escapeSql(tabColName.trim());

//If this value content space that means it is not a valid table

// or column name, so don’t use it in dynamic generated SQL

//use space so it create an invalid SQL query

return tempStr.indexOf(' ') == -1 ? tempStr : "";

}

Parameterised queries are a major step towards preventing SQL injection attacks. If you cannot use them, you have an equally major setback in your hands. You can somewhat mitigate the danger by:

• input string validation. And I mean validation with all the bells and whistles, which can sometimes reach the level of a full-blown parser, not just a few checks.

• input manipulation (e.g. quoting and string escaping). Again, you have to do this right, which can be harder than it seems.

Both techniques are problematic - you have to let valid input through unchanged, in order to maintain compatibility with your current codebase, while still effectively protecting your system. Good luck with that...

From my experience, refactoring - or even rewriting - your code to use prepared statements will save you a lot of time and tears in the long run.

If you don't have a peer-reviewed library of string-escaping functions, at the very least you should white-list characters that you know are safe to embed in strings. For instance, ensure your strings are composed only of letters, digits and underscores, and nothing else. Black-listing known "bad characters" is poised to get you in trouble.

Making sure that the input contains only allowed characters is just an important first step. Your sample statement is a good example for the value of the strategy "find input in a list of all good values" (you surely know the set of tables in your database and the subset of tables users are allowed to zap). "compare input against plausible range" (salary shouldn't be increased by millions or half cents), or "match input against a regex to reveal structural violations" are further examples.

To get confidence in your defenses, you may consider using a QuickCheck-like testing library to attack your validation functions by (suitably biased) random strings. This article lists implementations for languages other than Haskell.