best way of avoiding SQL injection?
Hey,
I was wondering what you guys would recommend to prevent SQL injections. Now, I know that you can use the PreparedStatement and setString for cases like this:
String userName = request.getParameter("username");
String sqlString ="SELECT * FROM UserTable WHERE USERNAME='" + userName ;
but what can you do when you have cases like these:
String userdef_table = request.getParameter("userdef_table");
String userName = request.getParameter("username");
String sqlString ="SELECT * FROM "+ userdef_table +" WHERE USERNAME='" + userName ;
Note: userdef_table can be created by the admin so I wont know what tables are around.
thanks,
domet
[861 byte] By [
dometa] at [2007-10-2 6:51:31]
and one more addition:
I am trying to have the following preparedStatement to use for a LIKE sql statement
String sql = "SELECT info FROM some_table WHERE username LIKE '?%'";
//String sql = "SELECT info FROM some_table WHERE username LIKE ?%";//tried that too
stmt = conn.prepareStatement(sql);
stmt.setString(1, some_string);
results = stmt.executeQuery();
unfortunately, this wont work. any suggestions?
thanks!
dometa at 2007-7-16 20:18:29 >
For Your First Question
1) using PreparedStatement is the best way, but yes you will have to catch the Table Not Found Exception very well in case the passed table deso not exist... Thats The Only Logical Answer For your Query
For Your Second Query
1) Create a String variable which will have the required reflex string ie the text you would liketo have after LIKE keyword and then pass the variable to the prepare statement
this
?% will not work for its wrong SQL query
String likeThis = "%Bill%"; <or what ever>
the Query part will be like this ".......like ?"
setString(....,likeThis);
Hope This Works
Bhaskar
> String userdef_table = request.getParameter("userdef_table");
> String sqlString = "SELECT * FROM "+ userdef_table + ...
For this, I would store the list of allowed table names in configuration somewhere. Only allow exact matches against that list. That should prevent SQL injection attack as well as lexically valid but undesirable table names -- e.g. V$WHATEVER, USER_TABLES, etc system tables in Oracle.
> String userdef_table = request.getParameter("userdef_table");
> String sqlString = "SELECT * FROM "+ userdef_table + ...
...or maybe don't even show the real table name in the HTTP parameter. Make the parameter some keyword, and look up the table name from configuration. If the keyword->table name mapping does not exist, reject the request.
