What character sanitation do I need for an MS Access Pass Through Query to SQL Server

Question

I am attempting to interface MS Access with SQL Server and I want to make sure I am not vulnerable to SQL Injection attacks.

I have seen suggestions to use ADO to create a paramaterized version of the queries, but I wanted to learn how to sanitize my input with a DAO pass through.

At the moment I am escaping single quotations and backslashes.

Are there any standard SQL Server injection sanitation methods in VBA?

Answer 1

Are there any standard SQL Server injection sanitation methods in VBA?

Nope. And I would be very worried if there was one. I don't do PHP, but I've read horror stories about its "standard SQL injection sanitation methods". Just peek at Code Review questions tagged with php and sql - quite a lot are asking a variant of "is this code secure?", and unless they're using prepared statements, the answer is invariably "no".

');--DROP TABLE Users;-- is a rather questionable value to see in a UserName database field (where do you think this very answer's text is being stored?), but if you've written your data access code properly, there's no reason for it to cause any mayhem, even if your login is authorized (server-side) to execute such a statement.

The problem isn't the user's input. The problem is concatenating user input into executable SQL and thus treating user input as executable code.

---

What you want is to execute a constant SQL statement. When you do this:

' *** DON'T DO THIS ***
Dim sql As String
sql = "SELECT Id FROM dbo.Users WHERE UserName = '" & pName & "';"

Then every time the SQL statement is sent to the server, it's a different query: the server doesn't even know a parameter is involved, all it sees is this:

SELECT Id FROM dbo.Users WHERE UserName = ''');--DROP TABLE Users;--';

Notice the extra single quote; if you're thinking hey, SQL injection was prevented, you're forgetting that it's not because YOU can't defeat your input sanitization, that NOBODY can. A lot of companies have world-facing websites using this kind of "injection prevention", and think their code is secure. And when they brag on Twitter that they "haven't been hacked in over a decade of being online with it", it takes a few minutes for script kiddies around the globe to take it down. Can't be bothered to dig up a link, but this exact story happened, rather recently.

When you send a parameterized query through ADO, this is what the server receives:

SELECT Id FROM dbo.Users WHERE UserName = ?;

Notice the query doesn't know or even need to care for the type of the parameter and its surrounding single quotes.

Notice it's the same identical statement regardless of what the user input is.

Notice the parameter values don't even come close to being involved anywhere in the execution plan of that query. Parameters are received separately, and handled by the database server in a way that's completely secure, written by people that do this for a living.

There is no sanitization you or anyone can come up with that will ever be secure, no matter how hard you try.

If user input is part of an executable SQL statement, it's not secure, period.

Don't pretend to do the server's job. Just don't.

---

Doing it right.

Learn to use ADODB Command and Parameter objects. They have a fairly simple API, and using them properly and consistently will beat any string-concatenated-pseudo-sanitized SQL string.

Here's a verbose example:

Const sql As String = "SELECT Id FROM dbo.Users WHERE UserName = ?;"

Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.ConnectionString = "{connection string}"
conn.Open

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
cmd.CommandType = adCommandText
cmd.CommandText = sql
cmd.ActiveConnection = conn

Dim userNameParam As ADODB.Parameter
Set userNameParam = New ADODB.Parameter
userNameParam.Type = adVarChar
userNameParam.Size = 60
userNameParam.Direction = adParamInput
userNameParam.Value = pUserName '<~ user input

cmd.Parameters.Append userNameParam

Dim result As ADODB.Recordset
Set result = cmd.Execute

MsgBox result("Id").Value
conn.Close

I say verbose, because that's how you do it when you explicitly specify everything. Personally I use my own ADODB wrapper classes, and with them this code is entirely equivalent to what's above:

Const sql As String = "SELECT Id FROM dbo.Users WHERE UserName = ?;"
Dim result As Long
result = SqlCommand.QuickSelectSingleValue(sql, pUserName)

MsgBox result

As you can see, with proper object-oriented code that encapsulates all the plumbing for you, sending a properly parameterized and entirely secure query to your database server isn't hard at all.

Here's how:

• Creating ADODB parameters on the fly
• Materializing any ADODB query

The classes, with some bugs fixed and other features, can be found on GitHub in the VBEX repository, which also happens to contain a truckload of other useful tools (licensed under GPLv3). Note, while I did author some of that code, or some code that inspired what's on there, I do not claim affiliation of any kind with the VBEX repository. I did upload the original versions of these classes to my own VBTools repository though, licensed under CC-by-SA, as is any Stack Exchange content.