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?
Best Answer
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).