Hacker News Reader

Comment by etanol | Hacker News Reader

Probably most of the readers already know it. But it is worth remarking that these tips only work when there is an injection vulnerability in the application.

If you prepare SQL queries as all manuals recommend nowadays, you're 99,9% safe (the other 0.01% beign the probability that your database driver is doing it wrong).

I just thought the title might raise some unnecessary alarms.

View on HN

And to be fair, SQLite makes it extremely trivial to use prepared statements and protect yourself from SQL injection.

Table names, and field names are not possible via prepared statements in sqlite. Some language wrappers do not expose the required functions to escape them either.

Please see this, just posted: http://news.ycombinator.com/item?id=4061387

You're probably doing something wrong if you're using user input to construct table names and field names.

I can see why this might be necessary in some cases (e.g. year and month in the name of the table), but such cases can be handled relatively easily by using a whitelist and/or validating & sanitizing strictly. If user input needs to be escaped rather than validated & sanitized, you're still doing something wrong. Why would you even have a table name or field name that doesn't match /[a-z0-9_]+/i ?

You're probably doing something wrong if you're using user input to construct table names and field names. This is possibly true.

Far more common is to offer the user a drop-down list of field name choices. In that case, the server side should whitelist valid field names before building the sql statement.

Better yet, hard-code the list on the server-side, and have the form only submit an index value to the list (and protect against overflows if necessary in your language/framework of choice). Forcing a translation between the integer and the field name protects against screwing up the white-list somehow and allowing arbitrary input.

I sure hope you're not accepting table and field names from an untrusted user. If you can't trust the individual who is administrating the application you might want to rethink some things.

Best approach is to not build queries by concatenating strings. Use parameters if at all possible.

I don't know about other data providers, but with the ADO.NET adapter, there is a huge performance penalty for non-parameterized, non-transactional queries. So if you string together a query you get clued in pretty quick that there's a problem.

Reply on news.ycombinator.com