current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

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?

share|improve this question
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

3 Answers 3

up vote 1 down vote accepted

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

share|improve this answer
up vote 1 down vote

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.

share|improve this answer
up vote 0 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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