Oh no! SQL Injection!

As most of you know by now, we’ve been hard at work getting our reporting/visualization product out the door (infomaptic). It’s super close; we are doing some testing internally and expect to be asking for beta testers soon. But this is a quick diversion about a little bug that I found just the other day involving SQL injection and how to prevent it when SQL parameters aren’t an option.

Wait, SQL Injection?!

SQL Injection is a way of attacking an application in order to force it to do something that your user doesn’t have permission to do or otherwise break the application. Mostly it comes down to getting the application to return any information you are not supposed to have. There is a pretty straightforward breakdown of SQLI in Wikipedia but in summary, the idea is to alter the SQL used by the application to provide additional information.

In our case, you might specify the number 1 and get the where clause OBJECTID = 1 or if you specified 1 or PARKNAME='Aniakchak' and get the where clause OBJECTID = 1 or PARKNAME='Aniakchak'; which would change what the user was able to return.

The Web Viewer

Infomaptic lets you design a template and then publish it. When you do, you get a URL to that report. Then you add a “key” value to the end as a query parameter. Let’s say that you want to use the; ObjectId;field as your identifier (you can use any field you wish to as long as it’s unique, but more on that later), so to use the feature with an; ObjectId of 1 in your report, the URL would look something like https://app.infomaptic.com/viewer/your-report-uuid-here?key=1

The Where Clause

To show different features, you change the key in the URL, and the report changes to show the corresponding feature – easy-peasy! Now, I mentioned using other fields, not just  ObjectId. To do this, you specify a where clause when building your report, which looks like this:

The SQL Injection

Here the were clause is: 'OBJECTID =' + data.key.value. That is a simple Atelerix command that builds a where clause from the value of key from the URL. The injection issue might seem obvious to you already, but it wasn’t to me until I started embedding report URLs into web maps for testing.

Give up? The issue is that the value specified as the key in the URL is passed directly into the where clause. For example:

1. Infomaptic passes the where clause directly to the feature service as the WhereClause parameter, so ArcGIS isn’t going to let you escape out of that and cause real database damage (1)
2. Infomaptic forces users to authenticate if the data or template is secured, so ArcGIS still limits access to layers and records.
   • If the user already has access to the entire layer, we do not reveal any data they could not already see through other means.
   • If the layer has additional protections, like row-level security, ArcGIS still enforces those and prevents users from loading records they could not otherwise see.

The Fix

Okay, so it’s not that bad, but we should still fix it. To do that, we’ve changed infomaptic to cram whatever you set as the key into a single value. If you specify a numeric value like 5, that’s fine, the where clause will just have a 5 appended to it. But if you specify a string, we encapsulate it in quotes, escape anything dangerous inside, and append that. Taking a look again at the three examples above, they now become:

Basically, that means that we are doubling all single quotes because they are escaped in Esri’s Standardized SQL (and most SQL). Here is a very simple chunk of code that does this

public static string EscapeString(string input)
    {
        // Replace all single quotes with double quotes
        var step = input.Replace("'", "''");
        // Wrap it in single quotes
        step = $"'{step}'";
        // and done
        return step;
    }

This isn’t perfect, but we think it is sufficient given that Esri is still protecting us from terrible things.

But SQL Parameters

I know, I know, the state of the art in preventing SQL injection is using SQL Parameters, but the ArcGIS platform doesn’t support parameters for their where clauses.

Maybe in the future, we’ll build some parameterization into Atelerix so that building where clauses will always go through some escape processes before being built. We also support SQL Databases in our core reporting library, and those should also use parameters from infomaptic, but we don’t support those yet!

1. But we shouldn’t rely on others to clean up for us. We should do our best to be good internet citizens and make things as safe as we can. But for those that are curious, Esri provides “Standardized SQL” queries, which you should always have turned on. I don’t know how this works, but if I were to make it, I would create my own SQL parser with limited abilities, basically only enough to generate where clauses. That way, it could generate appropriate SQL for any database and prevent SQL injection! For more see: https://enterprise.arcgis.com/en/server/latest/administer/linux/about-standardized-queries.htm

Image attribution: SQL Injection by Ian Rahmadi Kurniawan from the Noun Project