SQLite3 Injection Cheat Sheet | Hacker News Reader

SQLite3 Injection Cheat Sheet

sites.google.com

139 pointswglb19 comments

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.

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.

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.

You would think that using prepared statements would be the norm by now, but I'm not so sure anymore. Today as I recovered my password from website of a multi-national gym, I got it in plain text. There are some crazy shit out there so treat every service like it will be hacked some day.

Keeping passwords in plain text has nothing to do with not using prepared statements. There may be a correlation between the two types of stupidity, though.

Report them to http://plaintextoffenders.com/ ;)

This is not relevant to SQLite, but depending on your table structure, the constraints, your data, your query and the phase of the moon, some database systems (Oracle and PostgreSQL come to mind) can generate much better query plans if all the values are available when the query is planned. I still wouldn't recommend anyone to concatenate the (escaped) query parameters right into the query text unless they really know what you're doing.

This problem seems to be fixed in recent versions of Oracle:

https://blogs.oracle.com/optimizer/entry/explain_adaptive_cu...

I like how PHP will interpret a SQLite database as code. (Yes, I know why this happens, no need to explain.)

As someone who does not know why this is the case, care to explain?

PHP will directly print everything till opening <?, i.e. DB header. Then run system command with passed parameters (<? system($_GET[‘cmd’]); ?>), then continue to print whatever SQLite put as a DB footer.

It is just kinda "wow". Would never think off top my head about something like this.

You can do it with images as well.

If the site lets you upload an image to a readable directory you may be able to trick the webserver into executing your image.

This and the SQLite issue are not limited to PHP BTW, PHP is just most common.

To prevent this make sure never to let the user control the on disk filename of an image they upload!

I would actually never let the webserver directly serve uploaded files to the user. Store the files in a directory not served by a webserver, and use a small bit of code to read the file and send it to the user.

I always run my code such that the webserver has read only access to any directory it will serve. This helps limit exploits since the attacker has no ability to write anything and then have it execute. (Not a panacea of course, if you have a large enough bug, the attacker could write to /tmp then exec the code.)

> I would actually never let the webserver directly serve uploaded files to the user. Store the files in a directory not served by a webserver, and use a small bit of code to read the file and send it to the user.

If you run nginx or lighthttpd you can use X-Sendfile/X-Accel-Redirect for this purpose.

Additionally I recommend never having your runnable scripts in the same directory as static content, and also preferrably run the scripts through proxied HTTP or FastCGI. Then you never have to worry about this problem and not either by people being able to download your source code.

Apache's default treatment of PHP is an example of bad design making for complex configuration and many possible security holes.

Reply on news.ycombinator.com