On the Lambda

Programming, Technology, and Systems Administration

On the Lambda

Let’s Make a Single Statement Mode for ADO.NET

September 5th, 2022 · No Comments · .net, security, sql, Sql Server, stackoverflow

One of the issues I see often in code on Stack Overflow is SQL injection. I still typically answer multiple questions per week that include, along with the rest of answer, some form of the text, “This is scary-vulnerable to SQL injection issues.”

This is a “Really Big Deal”™. It’s one of about three things I believe you should not do even for practice, learning, testing, and proof of concept projects (another is plain-text or otherwise poorly protected passwords).  On the other hand, everyone has to learn about this somewhere. String manipulation is just too natural for programmers to expect us to just know about it.

With that in mind, I think we’re missing an easy security win in ADO.NET (and other libraries): there should be an option for a Single Statement Mode. This mode would only execute up to the first statement received in a batch. Anything afterwards is ignored.

Consider this bad example from a recent Stack Overflow question:

query = ("update items set name='" + txtName.Text + "',category='" + txtCategory.Text + "',price='" + txtPrice.Text + "where iid =" + id + "'");

If someone where to enter, say, ';DROP TABLE Items;-- for the txtName field, bad things are gonna happen (or not: one hopes the user account here would lack the required permissions. Hopes. But more subtle and invasive attacks are possible as well, and not even all that difficult).

Now instead consider if SQL Server and ADO.Net worked together so only the first statement is executed, and anything afterwards is ignored? Now we end up with executable code like this:

update items set name='';

This is still a broken query, but — importantly — it no longer attempts to execute the user-injected SQL command. Alternatively, the presence of multiple statements could cause the batch to fail.

Even more importantly, what if this were the default mode? Users have to discover parameterized queries, but if this is the default mode we have a measure of protection out-of-the-box. Personally, I’ve found the ability to execute multiple statements very useful, but it’s not (by far) the majority of the queries I send. I would gladly trade the need to explicitly turn that ability on for a command or connection object in order to know most queries have an added layer of injection protection.

Note I am explicitly NOT proposing this as a replacement for parameterized queries, but rather as a layer of depth in addition to them.

Adding this as an option to ADO.NET as it should be possible (even better if you can flag it on by default for a whole solution or project), but I understand making it the default would be a breaking change for a lot of code out there. It’s not something Microsoft can just do.

However, I also believe it’s past time for an ADO.NET v2. The current ADO.NET pre-dates nullable types and using blocks, both of which can potentially change the API in very positive ways. It also uses separate interfaces for a row in a DataTable than the current fields in DataReader. There are other improvements available, as well.

I believe there’s enough room for improvement on the table to consider a completely updated ADO.NET v2 library which includes a by-default single-statement mode. Such an updated library would make .NET programs invulnerable to the vast majority of SQL injection attacks out of the box, and that’s a security win too big to ignore.

So step 1: update the existing ADO.NET library to have this as an option. Step 2: Create a modernized ADO.NET v2 that uses the option by default.


No Comments so far ↓

There are no comments yet...Kick things off by filling out the form below.

Leave a Comment