PreparedStatement with table name

Question

I have a vulnerability in my code, and I'm trying to fix it.

To do this, in the SQL query, I'm using PreparedStatements to inject the parameters safely as follows:

String runQuery(String value) { String myReturn; String query = "select VALUE from " + tableName + " where config = ?"; try(PreparedStatement ps = con.prepareStatement(query)){ ps.setString(1, value); try(ResultSet rs = ps.executeQuery()) { if (rs.next()) { myReturn = rs.getString(1); } else { throw new BusinessException("noSQLResultSet"); } } } catch (Exception sqlException) { throw new BusinessException("SQLException"); } return myReturn; }

My question is about the name of the table tableName, I am currently retrieving it from the properties file located on the server, but from what I see with PreparedStatements I cannot inject the name of the table since it is not allowed. How could you specify the table name in a safe way without concatenating the value in the query?

why is the table name variable? don't you know in which table the config is? — njzk2
– njzk2, Commented Feb 13, 2021 at 22:38
How about you check that the tableName has an appropriate value? Like you can check it is just an SQL identifier, not any kind of more complex expression; or you can check it is one of some known set of permitted table names. — khelwood
– khelwood, Commented Feb 13, 2021 at 22:44
Not quite sure how your system is configured. But! I would encrypt all the values, which the application would ingest. Then set it lose! — Bill Naylor
– Bill Naylor, Commented Jun 10 at 18:21

rzwitserloot · Accepted Answer · 2021-02-13 22:58:21Z

There is no particularly easy way; one solution is to whitelist the input: If it consists solely of letters and underscores, then it's okay.

Note that your exception handling is horrible, you're tossing away ALL useful info! Try throw new BusinessException("uncaught", e); - and if BusinessException is your code, make sure you add that constructor (you can just call super(msg, cause);).

Basil Bourque · Accepted Answer · 2021-02-16 02:31:19Z

1

A table name cannot be used as a parameter in a PreparedStatement. It must be hard coded.

edited Feb 16, 2021 at 2:31

Basil Bourque

347k130 gold badges950 silver badges1.3k bronze badges

answered Feb 13, 2021 at 22:44

lkatiforis

6,3252 gold badges21 silver badges38 bronze badges

Comments

Dragos Ionut · Accepted Answer · 2021-02-13 22:47:37Z

As i can see you already created your query containing the tableName. It's not ok to concatenate the query and the parameters on the fly as you are vulnerable to sql injection if you receive from frontend a parameter like "or 1=1", but it's not the case here since you are providing the parameters separately. Anyway, sounds a bit strange to have the table name configurable.

Bill Naylor · Accepted Answer · 2021-02-13 23:20:23Z

I'm guessing that the reason that you want to get the tableName from your properties is that your schema is in a state of flux (?) ... not unusual in a development environment. If that is the case getting it from your properties does not introduce a vulnerability, and if you are using Spring, I believe you can use anotations like:
@ConfigurationProperties(prefix = "schema.properties")
On your class, then include a field like:
private String tableName;
In the class. If this is the case and you have a field in the applications.properties file (or environment specific equivalences) called:
schema.properties.tableName = myTableName
I think you should be good to go.

Collectives™ on Stack Overflow

PreparedStatement with table name

4 Answers 4

Comments

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Related