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:
- Work for all major databases - Postgres, MySQL, Oracle, MSSQL, etc.
- Limit what column names can be used as little as possible.
- Be immune to SQLi.
Compromises can be made with regards to #1 and #2, but not #3.