Escaping apostrophe/single quote in parameterized sql in asp

Question

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

when you send ' as a part of the parameter value you don't need to escape it. — Zohar Peled
– Zohar Peled, Commented Sep 7, 2015 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
– Andomar, Commented Sep 7, 2015 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
– TimothyF, Commented Sep 7, 2015 at 16:00

TimothyF · Accepted Answer · 2015-09-07 15:54:25Z

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.

You could have saved 13 minutes by just reading the comments :-)
Ha, yes. I didn't see them, hadn't refreshed the page. But thank you :)

Collectives™ on Stack Overflow

Escaping apostrophe/single quote in parameterized sql in asp

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related