1

Non-DDL-statements can and under all circumstances should be protected against SQL injection. In JDBC this is done with PreparedStatement and CallableStatement which allow parameter insertion, but can't be used for DDL statements.

The third is Statement which allows DDL statements, but doesn't offer parameter insertion. I need to do manual checks of the database name in order to protect against a database name like x"; DROP DATABASE "customer_data. This manual check feels both like reinventing the wheel and doing security myself which both are bad ideas afaik.

Am I missing a JDBC function? Is creating databases programmatically evil? Do stored procedures provide an additional level of protection? Are there well maintained and audited libraries for checking database names and similar variables passed to DDL statements.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Kalle Richter
  • 8,008
  • 26
  • 77
  • 177
  • While there are use cases where programmatically creating databases is legit, your last sentence leads me to believe you’re not sure whether you actually have such a use case…? – deceze Sep 18 '19 at 07:13
  • I'd like to separate customers in databases which can be moved between hosters. The customer (data) creation is done programmatically and thus is the database creation. – Kalle Richter Sep 18 '19 at 07:16
  • 1
    Well then, fundamentally prepared statements allow a separation between the structure of the query (what you want it to do on what tables and what columns), and the specific values, which provides protection against altering the query structure. If you’re programmatically generating the structure, then by definition there can be no real protection and you’re on your own. – deceze Sep 18 '19 at 07:20
  • A recent [related question](https://stackoverflow.com/q/57945247/285587) – Your Common Sense Sep 18 '19 at 07:54

1 Answers1

2

Even in databases that do support DDL as prepared statements (for example, MySQL does, see https://dev.mysql.com/doc/refman/8.0/en/sql-syntax-prepared-statements.html), it's still true that identifiers like schema names and column names can't be parameters.

You can use a query parameter only where you would otherwise use a constant value like a quoted string, quoted date/time, or a numeric literal. Not for identifiers.

So you need to be careful with identifiers. Whitelist them or do some kind of pattern-matching in your application code before interpolating them into your DDL statement. Don't just accept any input or untrusted content and use it verbatim in DDL without filtering it in some way.

Filtering isn't that hard. We do the similar safety-checks as normal work in application code, like checking to see if an object is null before assuming we can call methods on it. Or checking if a number is zero before using it as a divisor.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thanks for your input. Regarding "Filtering isn't that hard": my issue is not that it's hard to solve the problem. I want to do it right. There's a difference. I feel like I'm doing security myself which is not a good idea afaik when implementing such filters and checks. Are tools like jooq the solution? – Kalle Richter Sep 23 '19 at 07:10
  • Security _is_ the developer's responsibility. Frameworks like jOOQ have features to make it convenient quote identifiers (see https://www.jooq.org/doc/latest/manual/sql-building/names/) but it's up to you to use those features. I mean don't just blindly concatenate strings together without using the function provided to quote the identifiers. – Bill Karwin Sep 23 '19 at 14:15