
SQLite3 Injection Cheat Sheet  - wglb
https://sites.google.com/site/0x7674/home/sqlite3injectioncheatsheet
======
etanol
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.

~~~
illumen
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>

~~~
kijin
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 ?

~~~
wglb
_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.

~~~
pyre
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.

------
tommi
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.

~~~
DrJokepu
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.

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

[https://blogs.oracle.com/optimizer/entry/explain_adaptive_cu...](https://blogs.oracle.com/optimizer/entry/explain_adaptive_cursor_sharing_behavior_with_cursor_sharing_similar_and_force)

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

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

~~~
hippich
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.

~~~
ars
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.)

~~~
jeltz
> 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.

