WHERE column LIKE '%?' returns 'number of parameter does not match number of columns in provided data'

[j9ac9k]

Hello,

I'm running into an issue, and while I'm not at all convinced that it's not an issue with my code/query setup, I'm out of ideas of what to try.

I have a query that is of the form

data = c("12345")
query <- "SELECT * FROM stuff left join stuff on more stuff = original stuff WHERE column LIKE '%?'"
res <- sqlExecute(channel=conn, query=query, data=data, fetch=TRUE)

I get the following result:

Error in : Number of parameters does not match number of columns in provided data

But if I change my query around a bit, and give it the full input (with no wildcard) such as follows, all is well

data = c("full_input_12345")
query <- "SELECT * FROM stuff left join stuff on more stuff = original stuff WHERE column = ?"
res <- sqlExecute(channel=conn, query=query, data=data, fetch=TRUE)

In case it makes a difference, I'm connecting to a 2014 SQL Server. I'm able to make the equivalent query work with the RODBC library in non-parametric form:

data = c("12345")
query <- "SELECT * FROM stuff left join stuff on more stuff = original stuff WHERE column LIKE '%'
query <- paste(query, data[1], "'", sep="")
res <- sqlQuery(conn, query)

I'm no expert with SQL, so I could be missing something very basic.

Any input would be appreciated.

[zozlak]

This is because the query parameter replaces the whole literal in the query. It is impossible to inject a part of the literal that way.

Try:

data = c("%12345")
query <- "SELECT * FROM stuff left join stuff on more stuff = original stuff WHERE column LIKE ?
res <- sqlExecute(channel=conn, query=query, data=data, fetch=TRUE)

[j9ac9k]

Brilliant, it worked, thanks so much!