How to prevent SQL injection when dealing with dynamic table/column names?

Question

I am using jdbc PreparedStatement for data insertion.

Statement stmt = conn.prepareStatement(
"INESRT INTO" + tablename+ "("+columnString+") VALUES (?,?,?)");

tablename and columnString are something that is dynamically generated.

I've tried to parameterise tablename and columnString but they will just resolve to something like 'tablename' which will violate the syntax.

I've found somewhere online that suggest me to lookup the database to check for valid tablename/columnString, and cache it somewhere(a Hashset perhaps) for another query, but I'm looking for better performance/ quick hack that will solve the issue, perhaps a string validator/ regex that will do the trick.

Have anyone came across this issue and how do you solve it?

Answer 1

I am not a java-guy, so, only a theory.

You can either format dynamically added identifiers or white-list them.

Second option is way better. Because

• most developers aren't familiar enough with identifiers to format them correctly. Say, to quote an identifier, which is offered in the first comment, won't make it protected at all.
• there could be another attack vector, not entirely an injection, but similar: imagine there is a column in your table, an ordinary user isn't allowed to - say, called "admin". With dynamically built columnString using data coming from the client side, it's piece of cake to forge a privilege escalation.

Thus, to list all the possible (and allowed) variants in your code beforehand, and then to verify entered value against it, would be the best.

As of columnString - is consists of separate column names. Thus, to protect it, one have to verify each separate column name against a white list, and then assemble a final columnString from them.