在ASP中的参数化sql中转义撇号/单引号 [英] Escaping apostrophe/single quote in parameterized sql in asp

问题描述

我是参数化SQL的新手.我在.asp页中有一个查询，该查询从表单中获取一个或多个客户端名称.它们被保存在名为clientArr的数组中，然后作为参数传递到SQL Server.我转义了'as，但这似乎不起作用.如果我使用诸如 McDonald's 的客户端名称运行查询，则不会返回任何结果.

I'm new to parametrized SQL. I've got a query in an .asp page that's getting one or more client names from a form. These are held in an array called clientArr and then passed through to SQL server as parameters. I'm escaping the ' as '' but this doesn't appear to be working. If I run the query with a client name like McDonald's, it returns no results.

clientArr(y) = Replace(clientArr(y),"'","''"

...

if qsClient > "" Then
    dim booComma
    booComma = false
    if mySQLwhere > "" Then
        mySQLwhere = mySQLwhere& " AND "
    End if
    mySQLwhere = mySQLwhere & " (p.client IN ( "
    for y = 0 to Ubound(clientArr)
        if booComma = true Then 
            mySQLwhere = mySQLwhere & ","
        end if
        mySQLwhere = mySQLwhere & "?"
        booComma = true
    Next
    mySQLwhere = mySQLwhere & ")) "
end if

...

if qsClient > "" Then
    for y = 0 to Ubound(clientArr)
        Response.write clientArr(y)
        set prm = cmd.CreateParameter("@prm", 129, 1, 50, clientArr(y))
        cmd.Parameters.Append prm
    next
end if

如果我直接运行查询或通过连接字符串而不是使用参数来创建查询，则它可以正常工作.如果我使用不带撇号的客户端名称，它也可以正常工作.

If I run the query directly or create it by concatenating strings rather then use parameters, it works fine. It also works fine is I use a client name without an apostrophe.

任何帮助将不胜感激.如果可以的话，很高兴提供更多信息.

Any help would be much appreciated. Happy to provide more info if I can.

谢谢， 蒂姆

推荐答案

处理了太长时间之后，它就击中了我.像这样直接传递参数意味着我根本不需要转义它.如果删除该replace语句，则保留单引号就可以了.我肯定对此太想了.

After working on this for far too long, it just hit me. Passing the parameter straight through like this means that I don't need to escape it at all. If I remove that replace statement, it works just fine keeping the single quote. I was definitely over-thinking this.

这篇关于在ASP中的参数化sql中转义撇号/单引号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！