Take the 2-minute tour ×

I'm exposing a more or less public API that allows the user to query datasets from a database. Since the user will need to filter out specific datasets I'm tempted to accept the WHERE-part of the SELECT statement as an API parameter. Thus the user could perform queries as complex as she'd like without worrying about a cluttered API interface.

I'm aware of the fact that I would have to catch SQL-injection attempts.

Do you think that this would circumvent the purpose of an API wrapping a database too much or would you consider this a sane approach?

share|improve this question
WHERE 1=1; DROP bobby_tables; –  MatBailie Nov 2 '11 at 16:26
@Dems +1 for an XKCD Reference –  Purplegoldfish Nov 2 '11 at 16:30
Even if you force me to only be able to modify the where clause, I can just DoS you with table scans. :| –  StrangeWill Nov 2 '11 at 16:44
XKCD: "Exploits of a Mom": xkcd.com/327 –  Kevin Bedell Nov 2 '11 at 16:47
@Dems I fail to understand why Db Connectivity API (or RDBMS) doesn't throw an error when running executeQuery("select 1 from customer; drop table customer;") Better example is UNION injection. –  Tegiri Nenashi Nov 2 '11 at 22:11
show 2 more comments

4 Answers

up vote 4 down vote accepted

In general, I'd recommend against letting them embed actual sql in their requests

You can allow them to submit where conditions in their request pretty easily:

    <condition "field"="name" "operator"="equal" "value"="Fred"/>

or something similar.

The value of doing this is muli-fold:

  1. You parse each condition and make sure they're correct before running them
  2. You can create 'fake' fields, such as "full_name" that may not exist.
  3. You can limit the columns they can put conditions on
  4. You can isolate the users from actual changes in your underlying database.

I think the last point is actually most important. The day will come when you'll need to make changes to the underlying schema of the database. Eventually, it will happen. At that point you'll appreciate having some 'translation' layer between what the users send in and the queries. It will allow you to isolate the users from actual changes in the underlying database.

The API should present an 'abstracted' version of the actual tables themselves that meet the users needs and isolate them from changes to the actual underlying database.

share|improve this answer
+1 - This would be the equivalent (and should probably actually use, anyways) of using a view in the database layer itself. –  Clockwork-Muse Nov 2 '11 at 16:52
Great answer. You caught the reasons for my vague doubts towards this approach. Thank you. –  h0b0 Nov 3 '11 at 10:56
add comment

I would recommend limitting your users account by modifying the permissions to only allow the user to SELECT from tables. Don't allow updating, inserting, or deleting recordsets. Lock down the user as much as possibile, possibly at a table level.

share|improve this answer
add comment

If the WHERE clause is limited to only a few columns and the comparator is limited to >, = or < then perhaps you could just have the user pass in some extra parameters to represent columns and comparators. You then build the WHERE safely on your server side.

If this is too messy then by all means let them pass a full WHERE clause - it's not too hard to sanitise and if you combine that with running the query under a locked-down account (SELECT only), then any potential damage is limited.

share|improve this answer
add comment

Personally I would not want to allow users to be able to pass in SQL directly to my database, the risks are too great.

If you fail to catch all injection attempts you risk either data theft, someone just destroying your database or hijacking it for some other use that you really dont want.

share|improve this answer
add comment

Your Answer


