CODEWITHSUNDEEP
javasqljdbcsql-injection
Mc Kevin
Mc Kevin

Reputation: 972

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

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?

Upvotes: 3

Views: 3945

Answers (5)

vinod samudrala
vinod samudrala

Reputation: 11

There are multiple solutions we can apply.

1) White List Input Validation

String tableName;
switch(PARAM):
   case "Value1": tableName = "fooTable";
                  break;
   case "Value2": tableName = "barTable";
              break;
   ...

   default      : throw new InputValidationException("unexpected value provided for table name");
  • By doing this input validation on tableName, will allows only specified tables in the query, so it will prevents sql injection attack.

2) Bind your dynamic columnName(s) or tableName(s) with special characters as shown below

eg:

  • For Mysql : use back codes (`)

    Select `columnName ` from `tableName `;

  • For MSSQL : Use double codes(" or [ ] )

    select "columnName" from "tableName";
    or
    select [columnName] from [tableName];

Note: Before doing this you should sanitize your data with this special characters ( `, " , [ , ] )

Upvotes: 0

emiliollbb
emiliollbb

Reputation: 95

I think, the best approach is to get table and columns names from database or other non user input, and use parameters in prepared statement for the rest.

Upvotes: 0

jwenting
jwenting

Reputation: 5663

As an elaboration on @Anders' answer, don't use the input parameter as the name directly, but keep a properties file (or database table) that maps a set of allowed inputs to actual table names.

That way any invalid name will not lead to valid SQL (and can be caught before any SQL is generated) AND the actual names are never known outside the application, thus making it far harder to guess what would be valid SQL statements.

Upvotes: 0

anders
anders

Reputation: 824

Create a method that generates the sql string for you:

private static final String template = "insert into %s (%s) values (%s)";

private String buildStmt(String tblName, String ... colNames) {
    StringJoiner colNamesJoiner = new StringJoiner(",");
    StringJoiner paramsJoiner = new StringJoiner(",");
    Arrays.stream(colNames).forEach(colName -> {
        colNamesJoiner.add(colName);
        paramsJoiner.add("?");
    });
    return String.format(template, tblName, colNamesJoiner.toString(), paramsJoiner.toString());
}

Then use it...

Statement stmt = conn.prepareStatement(buildStmt(tablename, [your column names]));

Upvotes: 0

Your Common Sense
Your Common Sense

Reputation: 157839

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.

Upvotes: 1

Related Questions