0

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.

Thanks, Tim

TimothyF
  • 11
  • 5
  • 1
    when you send `'` as a part of the parameter value you don't need to escape it. – Zohar Peled Sep 07 '15 at 15:41
  • Your query isn't very clear, but it looks like you're creating parameters called `@prm` in a loop. That will just overwrite the previously created parameter. You'd have to create a SQL string with different parameters (like `@p1`, `@p2`, `@p3`) in order to use a variable number of parameters. – Andomar Sep 07 '15 at 15:42
  • It's using undeclared parameters so it will add them all as new parameters and then calls them in an IN statement eg "WHERE client IN (?, ?, ?)". That all works and wasn't the issue. It was just that I was unnecessarily escaping the quote. – TimothyF Sep 07 '15 at 16:00

1 Answers1

1

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.

TimothyF
  • 11
  • 5