Why am I getting this sqlite3.ProgrammingError error?

Question

The error is:

sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 0, and there are 1 supplied.

And this is the code causing it:

data = c.execute('''SELECT * FROM job WHERE title LIKE "%?%"''', (user_input,)).fetchall()

For more context, I'm trying to create a functioning search bar with Python, Flask and SQlite3, where c.execute here is supposed to return the data from my database based on user input. But I'm having trouble configuring SELECT so it works with partial matches too, hence the use of LIKE. What am I doing wrong?

Thank you! Is it '''SELECT * FROM job WHERE title LIKE ''%' || ' ? || '%''''' or? Because this here is now throwing the sqlite3.OperationalError: near "?": syntax error. I tried using the """quotes""" for SELECT, as well. Same thing. — hemoglobin
– hemoglobin, Commented Nov 20, 2023 at 10:56
Does this answer your question? Python String Formats with SQL Wildcards and LIKE — slothrop
– slothrop, Commented Nov 20, 2023 at 12:16
The immediate problem is that ? doesn't create a binding. For that you want %s, but that's a little awkward here because of the % in the syntax of the LIKE operator. For ways to solve that, see: stackoverflow.com/a/3134756/765091. Don't use insecure string manipulation as suggested in your answer. — slothrop
– slothrop, Commented Nov 20, 2023 at 12:18

hemoglobin · Accepted Answer · 2023-11-20 11:02:21Z

0

If anyone else ends up having issues with this, please refer to this answer. In short, this can be done using format.

In my case it was:

data = c.execute("SELECT * FROM job WHERE title LIKE '%{}%'".format(user_input)).fetchall()

answered Nov 20, 2023 at 11:02

hemoglobin

8515 gold badges14 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

slothrop Over a year ago

That's vulnerable to SQL injection: consider what happens when a malicious user's input is x%'; DROP TABLE important_data; SELECT * FROM job WHERE title LIKE '%x. It's much safer to avoid string manipulation and use the approach here: stackoverflow.com/questions/902408/…. To handle the % character, one approach is shown in this answer: stackoverflow.com/a/62347841/765091

hemoglobin Over a year ago

Oh, got it. Thank you for the comment. However, the approach you linked and that Barmar is suggestion is still giving me a syntax error.

slothrop Over a year ago

You're right, there's a missing ) after the CONCAT in Barmar's answer there. So you could fix that or alternatively use the approach here that does the concatenation in Python: stackoverflow.com/a/3134756/765091

Collectives™ on Stack Overflow

Why am I getting this sqlite3.ProgrammingError error?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related