CODEWITHSUNDEEP
pythonmysqlsqlescaping
Carlos
Carlos

Reputation: 1

How can i escape this string for sql query?

trying to use this query with escaped variables:

"UPDATE table_name SET status = ?, status_by = ? WHERE (unique_id, page_id) IN (?)", [req.body.action, req.body.status_by, aSet]

but end up getting this error: 'Operand should contain 2 column(s)'

this is how aSet looks: (1234567890, 123),(1234567890, 123)

Upvotes: 0

Views: 54

Answers (1)

Barmar
Barmar

Reputation: 780724

You can't use a ? placeholder for an entire list, it can only be replaced with a single value.

You need to spread the all the elements of aSet into separate parameters, and put enough ? placeholders in the query for all of them.

qs = ",".join(["(?, ?)"]*len(aSet))
sql = """UPDATE table_name SET status = ?, status_by = ? 
    WHERE (unique_id, page_id) IN (%s)"""%(qs)
cur.execute(sql, [req.body.action, req.body.status_by] + list(sum(aSet, ()))

I got that list(sum(aSet, ()) expression from Transform "list of tuples" into a flat list or a matrix

Upvotes: 1

Related Questions