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