Validate database column and table names to prevent SQLi?

Question

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.

Answer 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).