MySQL ProgrammingError on simple SELECT query with user-provided input

Question

def search():
    searc = input("Enter the name you want to search: ")
    s = (searc)
    mycursor.execute("SELECT * FROM studen WHERE Name = {}".format(s))
    for i in mycursor:
        print(i)

I'm trying to fetch data from the studen table where name is entered by the user.

Here is the error that I'm getting:

ERROR: mysql.connector.errors.ProgrammingError: 1054 (42S22): Unknown column 'ayush' in 'where clause'

Chris · Accepted Answer · 2021-01-01 19:24:18Z

1

The immediate problem is that your query is missing quotes. The string you get with .format() looks like this, assuming ayush is your input:

SELECT * FROM studen WHERE Name = ayush

But it needs to look like this:

SELECT * FROM studen WHERE Name = 'ayush'

However, you shouldn't be using .format() at all since it leaves you wide open to SQL injection.

This is the proper way to parametrize a query using mysql.connector:

mycursor.execute("SELECT * FROM studen WHERE Name = %s", (s,))

Note also that I have used (s,) instead of (s) here to get a tuple of one element.

answered Jan 1, 2021 at 19:24

Chris

139k139 gold badges316 silver badges293 bronze badges

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

1 Comment

connerito Over a year ago

Yes it works. I actually tried your code a day ago but instead of writing this (s ,) I wrote (s) without comma. Thank you for helping out.

Collectives™ on Stack Overflow

MySQL ProgrammingError on simple SELECT query with user-provided input

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related