ASP and ADO: When the Table Name is a Parameter

I am particularly proud (probably unreasonably) of this little piece of work; after a long afternoon and evening of googling, I was unable to find a useful solution on the web – so I worked one out myself! The solution itself has the merit of simplicity, and (as far as I can tell) originality. It may also be obvious to anyone but a dolt…

I have been working on hardening some of our ASP/SQL Server applications against SQL injection attacks. Typically, this involves identifying SQL commands that are built by concatenation with user-supplied variables and replacing them with SQL commands that are "parameterized", and setting the parameters to the user-supplied variables via ADO. Parameters can never be interpreted as database commands, so there is no danger of command injection.

Note that the term "user-supplied" here means _any_ information that is included in a request from the browser, regardless of its initial source. For example, information sent from the server and returned by the browser in a "cookie" must be considered "user-supplied", because malicious client software (or, absent SSL, a "man-in-the-middle") could modify that information before it re-arrives at the server.

To use parameters in ADO, you must utilize the Command object, rather than just the Connection object's "Execute" method or the recordset's Open method. Here's some sample code for a simple case (the code assumes that the connection objConn is open):

set objCmd = Server.CreateObject("ADODB.Command")
objCmd.ActiveConnection = objDBConn
objCmd.CommandType = adCmdText
objCmd.NamedParameters = True
objCmd.CommandText = "select field1, field2 from tblSurveyList where strSurveyNumber= ? "
objCmd.Parameters.Append objCmd.CreateParameter("", adChar, adParamInput, Len(strSurveyNumber), strSurveyNumber)
on Error Resume Next
set rsSurveyIndex = objCmd.Execute
On Error GoTo 0
if objDBConn.Errors.count <> 0 then
call handleError (objDBConn.Errors, SQLQuery, "survey list query")
end if

The parameter value given in "CreateParameter" call effectively replaces the "?" in the command text when the SQL is executed; but the technique is _not_ concatenation. Instead, ADO creates an SQL stored procedure and passes the value as a parameter to that procedure.

But what happens when the parameter you need to pass is actually a table name? For example, in our Silhouette survey system, each survey has its own table to store responses. When we record responses, we need to use the survey number as a variable to update the appropriate table. I tried something like this:


set objCmd1 = Server.CreateObject("ADODB.Command")
objCmd1.ActiveConnection = objDBConn
objCmd1.CommandType = adCmdText
' passing table name as parameter (fails)
objCmd1.CommandText = "select * from ? where strRespID = ?"
objCmd1.Parameters.Append objCmd.CreateParameter("", adChar, adParamInput, Len(strRespTable), strRespTable)
objCmd1.Parameters.Append objCmd.CreateParameter("", adChar, adParamInput, Len(strRespID), strRespID)
...

What I got for my pains was an error message: "The tablename variable @P1 must be declared." Google searches didn't answer the question directly, but I did find numerous posts about problems with passing table names into stored procedures. The solutions all used "dynamic SQL" -- EXEC statements plus concatenation, our old enemy. What to do?

I already had an "existence check" in the code to make sure that the response table exists before calling it for update. This check uses the "INFORMATION_SCHEME.tables" table maintained by SQL Server, in which the table name is an ordinary variable with a value that can be passed as a parameter:


'test if the response table exists
objCmd.CommandType = adCmdText
objCmd.CommandText = "SELECT * FROM INFORMATION_SCHEMA.tables where table_name = ?"
objCmd.Parameters.Append objCmd.CreateParameter("", adChar, adParamInput, Len(strRespTable), strRespTable)

on Error Resume Next
set rsTableExists = objCmd.Execute
On Error GoTo 0
if objDBConn.Errors.count <> 0 then
call handleError (objDBConn.Errors, SQLQuery, "response table does not exist")
end if


If this check is successful, I reasoned, the value of the response table name must be valid, and can be included in the command text via concatenation without concern about injection.

The final query looks like:


objCmd1.ActiveConnection = objDBConn
objCmd1.CommandType = adCmdText
' cannot pass table name as parameter (apparently) -- but strSurveyResponse is a table that exists
' (per success of Response Table Existence query above) so concatenation is safe
objCmd1.CommandText = "select * from " & strRespTable & " where strRespID = ?"
objCmd1.Parameters.Append objCmd.CreateParameter("", adChar, adParamInput, Len(strRespTable), strRespTable)
objCmd1.Parameters.Append objCmd.CreateParameter("", adChar, adParamInput, Len(strRespID), strRespID)