Do the SelectLayerByAttribute or MakeFeatureLayer tools have any sort of internal checking for SQL Injection?

[christopherfricke2]

I'm making a GP tool for ArcGIS server to generate some standard reports with user input. I'm making a tool that allows for user input into a formatted query. I'd like to make sure I don't allow people to blow it up.

As an example:

parameter_1 = <USER INPUT>

def query(parameter_1):

query = "PIN = '{0}'".format(parameter_1)

arcpy.management.MakeFeatureLayer(source_fc, 'test_layer', query)

print query

Usually the operation will go as this:

parameter_1 = '110101010101'

def query(parameter_1):

query = "PIN = '{0}'".format(parameter_1)

arcpy.management.MakeFeatureLayer(source_fc, 'test_layer', query)

print query

> ExecuteTool()

PIN = '110101010101'

Theoretically the user could

parameter_1 = '110101010101; DROP TABLE pin'

def query(parameter_1):

query = "PIN = '{0}'".format(parameter_1)

arcpy.management.MakeFeatureLayer(source_fc, 'test_layer', query)

print query

> ExecuteTool()

??????

[JoshuaBixby]

Taking your question at face value, i.e., does SelectLayerByAttribute or similar tools have internal checking for SQL injection, I think the answer is pretty clearly no.  I just did a quick check using SelectLayerByAttribute, and I was able to drop a table in SQL Server by injecting extra SQL into the where_clause of the tool.  Since MS Access doesn't support multiple SQL statements, it didn't work on personal geodatabases.  It also didn't work on file geodatabases, which I am guessing is for the same reason.  Of course, I could use less invasive SQL injection with all three to return more records than intended.

Although I didn't check all DBMSes and all forms of SQL injection, the fact that I could successfully use some SQL injection with some DBMSes gives a strong indication the tools themselves are not doing any internal checks for SQL injection.  I think Esri would say these tools are simply passing SQL along, and that hardening against SQL injection should be taking place elsewhere.  Not only would programming internal checks get complicated and quickly, it would likely involve putting big constraints on how SQL is used with those tools.  Always a trade off.

The tools you reference might not be hardened against SQL injection, but that doesn't mean the floodgates are open.  There are still multiple layers in the application stack between these tools and the interface of ArcGIS Server that users will be interacting with.  One thing Esri introduced, I can't remember when exactly, is standardized queries for ArcGIS Server.  In terms of publishing GP tools, there may be extra precautions in place, I don't know.

I am a firm believer in seeing is believing, especially with ArcGIS.  Regardless of what the documentation does or doesn't say, I say test it and see for yourself.