4

I'm working with ESRI's arcpy module, and I need to specify a WHERE clause as a parameter to this function. Since the result will eventually be used to retrieve data from the database, the text of the WHERE clause is passed directly to the database at some point, but sadly, ESRI does not give me any options for parametrizing the query. Since I want to safeguard against potential SQL injection, I need to find some alternative means of protecting my database.

One option that comes to mind is limiting the input and throwing an error before this function call if it doesn't conform. In my case, callers will only ever need ASCII alphanumeric characters. No other kind of characters are allowed in the column to be used for filtering. Would limiting the user to only alphanumeric characters be sufficient to prevent SQL injection, especially since the text must be quoted as a string?

  • 1
    Which DBMS are you using? – a_horse_with_no_name Feb 13 '14 at 8:50
  • @a_horse_with_no_name I should've known someone would ask. It happens to be Oracle, but I don't really have all of Oracle's facilities available. The only library available on the server for communicating with the database is those that come with ArcGIS. Furthermore, I'm still interested in considerations for other databases, as I don't know if this problem may arise on future projects. – jpmc26 Mar 19 '14 at 3:44
2

If you really can limit to alphanumeric characters, then yes, that's fine, IF you are limiting to ANSI alphanumeric characters. In Unicode, because every character is more than one byte, many representations of alphanumeric characters are actually unsafe and could lead to injection.

You will need to sanitize the data server-side and make sure the encoding is one that is safe. I'd suggest you take a look at the ESAPI libraries by OWASP for how they do it (see third section on this page).

1

The normal way is to make sure that the various keywords from SQL aren't permitted in the string the user enters. However there are many cases to check.

Quotations can be avoided by something like this:

SELECT  CHAR(97) + CHAR(100) + CHAR(109) + CHAR(105) + CHAR(110)

Returns admin and the technique of adding characters together in that manner can be used in WHERE without having to use quotations.

So basically - you'll have to build your query, and right before sending it to the database, verify that it's not doing anything you don't want it to by matching it up against SQL Keywords, but there are a good number of things to check. So make sure stuff like select, exec (all variations), convert, union, drop, truncate and pretty much any dangerous keywords in any upper/lower case combination, so on are not in the user string. Also there shouldn't be allowed any byte streams (0x numbers) that can be converted to avoid many dictionary checks especially if the exec commands are forgotten from the dictionary.

And if it is a web application, make sure you check this server side and not client side.

It's pretty much the best advice I can give if you cannot use parameters.

1

If using Oracle -- and if you're able to run arbitrary statements against it -- then you could first sanitize the input using dbms_assert.enquote_literal (at least, it's my understanding that this makes sure that the literal is properly quoted -- even allowing for quotes embedded in strings).

See http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_assert.htm for more details.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.