CODEWITHSUNDEEP
pythonsqlite
Aayla
Aayla

Reputation: 161

sqlite3 bindings in regex

I'm trying to search a SQLite database in Python based on the simple regex %value%. However, when trying to use a binding to prevent SQL Injection, sqlite3 doesn't recognize ? as a binding when surrounded in percentage symbols. How can I get sqlite3 to recognize my binding while being used in a regular expression?

SELECT * FROM opportunities WHERE LOWER(title) LIKE LOWER('%?%');

run with

print(sql, values)
cursor.execute(sql, ('somequery',))

returns

SELECT * FROM opportunities WHERE LOWER(title) LIKE LOWER('%?%'); ('somequery',)
Traceback (most recent call last):
  File "[REDACTED]", line 86, in <module>
    opportunitiesSearchResults = databaseFunctions.searchOpportunities(memCursor, currentFilter)
  File "[REDACTED]", line 102, in searchOpportunities
    cursor.execute(sql, values)
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 0, and there are 1 supplied.

Upvotes: 0

Views: 181

Answers (1)

Barmar
Barmar

Reputation: 780909

? is not a placeholder when it's quoted. You need to take it out of the quotes.

SELECT * FROM opportunities WHERE LOWER(title) LIKE '%' || LOWER(?) || '%'

Upvotes: 1

Related Questions