经典ASP SQL参数化 - 我怎样才能从参数中取出单引号？ [英] Classic ASP SQL Parameterization - How Can I Remove Single Quotes From A Parameter?

问题描述

我用传统的ASP与参数化的SQL查询字符串如下：

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

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

SQL查询字符串在下面的参数code使用：

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

上面的code ++工程，除了一个巨大的问题很大。该ORDER BY SUBDATE？部分输出ORDER BY SUBDATE'DESC'，其中有DESC单引号周围添加由于这样的事实，这是一个参数。运行，因为周围的DESC单引号（也可以是ASC这取决于用户选择，并通过URL查询字符串发送）以上的code时，我得到一个坏的语法错误。似乎所有的SQL参数与单引号输出。我怎样才能把特定的SQL参数的单引号，使他们不会在SQL字符串创建语法错误？

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?

推荐答案

这是非常罕见的，让关键字（或标识为此事）为参数。没有一个单一的框架，我知道支持它。

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.

由于只有2个允许值，你为什么不干脆写：

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... 

这篇关于经典ASP SQL参数化 - 我怎样才能从参数中取出单引号？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！