Automagic & Secure ADODB

VitoshAcademy has the pleasure to welcome its first guest author – Mr. Mathieu Guindon.  He is the project manager behind Rubberduck – an open-source COM add-in project written in C#, extending the glorious VBE with modern-day IDE features.  On Code Review and Stack Overflow he is closely monitoring the vba tag. Ex-moderator on Code Review  (2015-2018), Microsoft Excel MVP (2018). See this 5 question interview with Mathieu here.

---

One quite common mistake many developers do every day, often without even realizing, is to write code like this:

Where pFoo is some String value, and pBar is some number. Concatenating the WHERE clause values like this, regardless of the language, leaves the code wide open to an SQL injection attack. If you hand-wave the issue and pretend it doesn’t apply to you, then you are not understanding the problem.

This vulnerability will invariably cause issues at one point or another – if you’re doing PHP or server-side C# or VB.NET, you need to see this classic XKCD comic:

If you’re writing some in-house desktop application in Java, C#, or VBA, you’ll be writing code to work around the vulnerability (e.g. double up single quotes), and think you’re covered and Mrs. Null and Mr.O’Neil can safely use your application. Until something else breaks, or until you need to maintain these concatenated SQL monstrosities in any way.

Of course there’s a better way. People will tell you to use parameterized queries instead of concatenating your values and properly accounting for single quotes. You’ve done it once or twice, thought something along the lines of “boy that’s a lot of code for so little benefit”, and went back to your old ways. Or, you’re now a convert and use parameters all the time, and you copy/paste big chunks of boilerplate code every time.

What if there was a magic way to deal with all the boilerplate?

Generating ADODB Parameters on the fly

What we need is a class that’s able to take a value, determine its type, and spit out a properly-configured ADODB.Parameter object for us. Enter AdoValueConverter:

Pretty straightforward, boring boilerplate: to each type its own dedicated function. We have an option to make everything convert to a String parameter, and another to determine whether a GUID string should be converted into a Guid or a String parameter, and a Property Let member allows reconfiguring the mappings at run-time.

A more advanced implementation could perhaps use a keyed collection or a dictionary to store the mappings, instead of spelling them all out.

Next, we need another class – let’s call it SqlCommand, whose job is to take the SQL string and the parameter values, create the ADODB boilerplate, execute it, and return the results.

The magic happens in ToSqlInputParameter:

Using CallByName against the converter, we invoke the appropriate To[TypeName]Parameter and receive an ADODB parameter that's ready to use with an ADODB command - and the command itself is automatically created & wired-up too.

All public methods require an ADODB connection, so you can initiate a transaction and run multiple commands before you commit. The parameters are received with a ParamArray argument, so the usage would look like this:

You could then have additional methods that wire up the connection automatically, execute the query, iterate the recordset and return a dictionary of key-value pairs for each record (works great for small result sets), and then this eliminates the connection boilerplate, too, leaving the “client code” with nothing to worry about except passing the parameter values in the correct order, and working with the query results.

You’ll never need to worry about single quotes and SQL injection ever again… and the command & parameterization boilerplate is nicely tucked away in its own dedicated and reusable class.