How to prevent SQL injection when the statement has a dynamic table name?

Question

I have code like this.

final PreparedStatement stmt = connection .prepareStatement("delete from " + fullTableName + " where name= ?"); stmt.setString(1, addressName); 

The fullTableName calculation looks something like this:

  public String getFullTableName(final String table) { if (this.schemaDB != null) { return this.schemaDB + "." + table; } return table; } 

Here, schemaDB is the name of the environment (which can be changed over time), and table is the name of the table (which will be fixed).

The value for schemaDB comes from an XML file, which makes the query vulnerable to SQL injection.

Query: I'm not sure how the table name can be used as a prepared statement (for example, name used in this example), which is a 100% security measure against SQL injection.

Can someone please suggest me what could be a possible approach to handle this?

Note. We may be redirected to DB2 in the future so that the solution is compatible with Oracle and DB2 (and, if possible, database independent).

Answer 1

JDBC, unfortunately, does not seem to allow you to make a table name a related variable inside statements. (This has its own reasons for this).

Thus, you cannot write or perform such a function:

 connection.prepareStatement("SELECT * FROM ? where id=?", "TUSERS", 123); 

And bind TUSER to the statement table name.

Therefore, your only safe way forward is to verify user input. However, the safest way is not to check it and allow the user to enter data through the database, because from a security point of view, you can always count on the user being smarter than your check. Never trust a dynamic, user-created String, merged inside your statement.

So what is a safe validation pattern?

Sample 1: Secure Query Advanced Add-Ins

1) Create all your virtual statements once and for all, in code.

 Map<String, String> statementByTableName = new HashMap<>(); statementByTableName.put("table_1", "DELETE FROM table_1 where name= ?"); statementByTableName.put("table_2", "DELETE FROM table_2 where name= ?"); 

If necessary, this creation itself can be made dynamic, with the expression select * from ALL_TABLES; . ALL_TABLES will return all the tables that the SQL user has access to, and you can also get the table name and schema name from this.

2) Select the operator inside the map

 String unsafeUserContent = ... String safeStatement = statementByTableName.get(usafeUserContent); conn.prepareStatement(safeStatement, name); 

See how the variable unsafeUserContent never reaches the database.

3) Make some kind of policy or unit test that checks that all your statementByTableName are valid for your schemas for its future evolutions and that there is no table.

Sample 2: double check

You can: 1) verify that user input is indeed the name of the table using a query without injections (I type pseudo sql code here, you will have to adapt it to make it work, because I don't have an Oracle instance to check if it works this is):

 select * FROM (select schema_name || '.' || table_name as fullName FROM all_tables) WHERE fullName = ? 

And bind your full name as a prepared statement variable here. If you have a result, then this is a valid table name. You can then use this result to create a secure query.

Sample 3

This is a kind of mixture between 1 and 2. You create a table with the name, for example, "TABLES_ALLOWED_FOR_DELETION", and you statically populate it with all the tables suitable for deletion.

Then you take the verification step

 conn.prepareStatement(SELECT safe_table_name FROM TABLES_ALLOWED_FOR_DELETION WHERE table_name = ?", unsafeDynamicString); 

If this is the result, you execute the name safe_table_name. For added security, this table should not be writable by the standard user of the application.

I somehow feel that the first sample is better.

Answer 2

You can avoid the attack by checking the name of your table with a regular expression:

 if (fullTableName.matches("[_a-zA-Z0-9\\.]+")) { final PreparedStatement stmt = connection .prepareStatement("delete from " + fullTableName + " where name= ?"); stmt.setString(1, addressName); } 

It is not possible to inject SQL using such a limited character set.

In addition, we can avoid any quotation marks from the table name and safely add it to our query:

 fullTableName = StringEscapeUtils.escapeSql(fullTableName); final PreparedStatement stmt = connection .prepareStatement("delete from " + fullTableName + " where name= ?"); stmt.setString(1, addressName); 

StringEscapeUtils comes with the Apocal commons-lang library.

Answer 3

 create table MYTAB(n number); insert into MYTAB values(10); commit; select * from mytab; N 10 create table TABS2DEL(tname varchar2(32)); insert into TABS2DEL values('MYTAB'); commit; select * from TABS2DEL; TNAME MYTAB create or replace procedure deltab(v in varchar2) is LvSQL varchar2(32767); LvChk number; begin LvChk := 0; begin select count(1) into LvChk from TABS2DEL where tname = v; if LvChk = 0 then raise_application_error(-20001, 'Input table name '||v||' is not a valid table name'); end if; exception when others then raise; end; LvSQL := 'delete from '||v||' where n = 10'; execute immediate LvSQL; commit; end deltab; begin deltab('MYTAB'); end; select * from mytab; 

no rows found

 begin deltab('InvalidTableName'); end; ORA-20001: Input table name InvalidTableName is not a valid table name ORA-06512: at "SQL_PHOYNSAMOMWLFRCCFWUMTBQWC.DELTAB", line 21 ORA-06512: at "SQL_PHOYNSAMOMWLFRCCFWUMTBQWC.DELTAB", line 16 ORA-06512: at line 2 ORA-06512: at "SYS.DBMS_SQL", line 1721 

Answer 4

I think the best approach is to create a set of possible table names and check for the presence of that table before creating the query.

 Set<String> validTables=.... // prepare this set yourself if(validTables.contains(fullTableName)) { final PreparedStatement stmt = connection .prepareStatement("delete from " + fullTableName + " where name= ?"); //and so on }else{ // ooooh you nasty haker! }