参数化查询中的ASP经典命名参数：必须声明标量变量

I'm trying to write a parameterized query in ASP Classic, and it's starting to feel like i'm beating my head against a wall. I'm getting the following error:

我正在尝试在ASP Classic中编写一个参数化查询,它开始觉得我正在撞墙。我收到以下错误:

Must declare the scalar variable "@something".

必须声明标量变量“@something”。

I would swear that is what the hello line does, but maybe i'm missing something...

我发誓这就是你好的线,但也许我错过了一些东西......

<% OPTION EXPLICIT %>
<!-- #include file="../common/adovbs.inc" -->
<%

    Response.Buffer=false

    dim conn,connectionString,cmd,sql,rs,parm

    connectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Data Source=.\sqlexpress;Initial Catalog=stuff"
    set conn = server.CreateObject("adodb.connection")
    conn.Open(connectionString)

    set cmd = server.CreateObject("adodb.command")
    set cmd.ActiveConnection = conn
    cmd.CommandType = adCmdText
    cmd.CommandText = "select @something"
    cmd.NamedParameters = true
    cmd.Prepared = true
    set parm = cmd.CreateParameter("@something",advarchar,adParamInput,255,"Hello")
    call cmd.Parameters.append(parm)
    set rs = cmd.Execute
    if not rs.eof then
        Response.Write rs(0)
    end if


%>

4 个解决方案

#1

Here's some sample code from an MSDN Library article on preventing SQL injection attacks. I cannot find the original URL, but googling the title keywords (Preventing SQL Injections in ASP) should get you there quick enough. Hope this real-world example helps.

以下是有关防止SQL注入攻击的MSDN Library文章中的一些示例代码。我找不到原始的网址,但谷歌搜索标题关键字(在ASP中防止SQL注入)应该足够快。希望这个真实世界的例子有所帮助。

strCmd = "select title, description from books where author_name = ?"
Set objCommand.ActiveConnection = objConn
objCommand.CommandText = strCmd
objCommand.CommandType = adCmdText
Set param1 = objCommand.CreateParameter ("author", adWChar, adParamInput, 50)
param1.value = strAuthor
objCommand.Parameters.Append param1
Set objRS = objCommand.Execute()

See the following page on MSDN, near the bottom, referring specifically to named parameters.

请参阅底部附近的MSDN上的以下页面,特别是指命名参数。

MSDN example

#2

ADO is going to expect question marks instead of actual parameter names in this case. Right now, the SQL "select @something" is not actually parameterized: it sees the "@something" as an (undeclared) SQL variable, not as a parameter. Change your CommandText line to this:

在这种情况下,ADO将期望问号而不是实际的参数名称。现在,SQL“select @something”实际上并未参数化:它将“@something”视为(未声明的)SQL变量,而不是参数。将CommandText行更改为:

cmd.CommandText = "select ?"

And I think you will get the result you are looking for.

我想你会得到你想要的结果。

Good luck!

#3

with server.createobject("adodb.command")
  .activeConnection = application("connection_string")
  .commandText = "update sometable set some_col=? where id=?"
  .execute , array(some_value, the_id)
end with

#4

I'm not sure what your query is intended to accomplish. I'm also not sure that parameters are allowed in the select list. MSDN used to have (many years ago, probably) a decent article on where parameters were allowed in a query, but I can't seem to find it now.

我不确定您的查询要完成什么。我也不确定选择列表中是否允许参数。 MSDN曾经(许多年前,可能)有一篇关于查询中允许参数的文章,但我现在似乎无法找到它。

OTTOMH, your attempts to supply the parameter values to ADO look correct. Does your query execute if you do something like this?

OTTOMH,您尝试向ADO提供参数值看起来是正确的。如果您执行此类操作,您的查询是否会执行?

SELECT 1 FROM sometable WHERE somefield = @something

#1

strCmd = "select title, description from books where author_name = ?"
Set objCommand.ActiveConnection = objConn
objCommand.CommandText = strCmd
objCommand.CommandType = adCmdText
Set param1 = objCommand.CreateParameter ("author", adWChar, adParamInput, 50)
param1.value = strAuthor
objCommand.Parameters.Append param1
Set objRS = objCommand.Execute()