Search code examples
sqlasp-classicquotesparameterized

Classic ASP SQL Parameterization - How Can I Remove Single Quotes From A Parameter?

I am using classic ASP with a parameterized SQL querystring as follows:

SQL = "SELECT * FROM content WHERE Category LIKE ? ORDER BY SubDate ?"

The SQL query string is being used in the following parameterized code:

Set cmd = Server.CreateObject("ADODB.Command")
Set rsView = Server.CreateObject("ADODB.Recordset")
cmd.ActiveConnection = MM_connContent_STRING
cmd.Prepared = true
cmd.CommandType = adCmdText
cmd.CommandText = SQL
cmd.CommandTimeout = 60

cmd.Parameters.Append(cmd.CreateParameter("Category", 202, adParamInput, 30, qFilter))
cmd.Parameters.Append(cmd.CreateParameter("SubDate", 202, adParamInput, 10, myDateSort))

rsView.CursorLocation = adUseClient
rsView.Open cmd, , adOpenForwardOnly, adLockReadOnly

The code above works great except for one huge problem. The "ORDER BY SubDate ?" part outputs "ORDER BY SubDate 'DESC'" where DESC has single quotes around it added due to the fact that it is a parameter. I get a bad syntax error when running the code above because of the single quotes around DESC (which can also be ASC depending on what the user selects and sends through the URL query string). It seems all SQL parameters are output with single quotes. How can I remove the single quotes from specific SQL parameters so they don't create syntax errors in the SQL string?

Solution

  • It is very uncommon to allow keywords (or identifiers for that matter) to be parameters. There is not a single framework that I know of that supports it.

    Since there are only 2 allowable values, why don't you simply write:

    SQL = "SELECT * FROM content WHERE Category LIKE ? ORDER BY SubDate "
If myDateSort = "ASC" Or myDateSort = "DESC" Then
    SQL = SQL & myDateSort
Else
    'whoops, value not allowed. raise error... 
End If
'...rest of your code...