4

There are some types of software, such as ORMs and query builders, that has to concatenate column and table names into SQL queries. Since developers apparently put untrusted data in all sorts of places without seconds thoughts, a sane strategy for such a library would be to validate that the provided name is in fact a name, and not an SQLi attack.

A simple way to do that would be to match against a regex like /[a-zA-Z_]+/. However, this would limit what column names can be used. Many DBMS are quite liberal as to what column names they allow, and I don't want to introduce artificial restrictions.

So, what I am looking for is an approach to validate or sanitize database column and table names. It might be a tall order, but ideally it would:

  1. Work for all major databases - Postgres, MySQL, Oracle, MSSQL, etc.
  2. Limit what column names can be used as little as possible.
  3. Be immune to SQLi.

Compromises can be made with regards to #1 and #2, but not #3.

Improve this question
  • Not able to provide a full answer, but the start of an approach for me would be for the library to be able to evaluate semantically 1. the query it intends to make (before the input is expanded into the query) and 2. the query it is about to make once the data is expanded. My reasoning is that most (all?) of SQLi consists in hijacking the semantics of a query by adding extra conditionals or even escaping into a second statement. So what if you’re able to tell that the intention was to join on N known columns but you found that you’re about to join on more, or less, or there is extra stuff? – korrigan Mar 16 '18 at 2:17
  • Of course you didn’t say anything about performance :x – korrigan Mar 16 '18 at 2:18
1

I think your best bet is quoting. The SQL-99 standard specifies that double quote (") is used to delimit identifiers (but your mileage with various DBs will vary). This question has some more info on how this is handled across databases.

Oracle has a specific function for this DBMS_ASSERT.ENQUOTE_NAME - but if you're looking to go cross platform you will need to do this yourself. Double quote the name, but check for double quotes in the string and reject the string if they aren't escaped (paired).

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.