User Input to mySQL database

Question

I want a simple python program to prompt the user for a title and search a mySQL database. I have the database constructed but I can't figure out how to query it properly.

import MySQLdb
servername = "localhost";
username = "username";
password = "password";
conn = MySQLdb.connect(user=username,host=servername, passwd=password, db=db)
cursor = conn.cursor()
user_input = raw_input("What do you want to watch?:")
query= ("SELECT * from videos where title LIKE %s")
cursor.execute(query,user_input)

IF, I hardcode the query to say "where title LIKE '%HARDCODE%' ", then it works fine. I figure the solution is pretty simple but I for the life of me can't figure it out. Any help is much appreciated! I've tried every variation I could find online but to no avail. Some others I tried:

query= ("SELECT * from videos where title LIKE CONCAT('%',%s,'%')")

cursor.execute(query,(user_input,))

query= ("SELECT * from videos where title LIKE (search)"
        "VALUES (%s)", user_input)

... They all don't work.

Errors all seem to revolve around me passing my variable user_input through correctly.

dacuna · Accepted Answer · 2016-04-19 03:47:43Z

3

You can create your query like:

c.execute("SELECT * FROM videos WHERE title LIKE %s", ("%" + user_input + "%",))

answered Apr 19, 2016 at 3:47

dacuna

1,0961 gold badge11 silver badges22 bronze badges

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

1 Comment

Brian Sizemore Over a year ago

You should mention why calling the function in this way is the secure way to do it.

Strik3r · Accepted Answer · 2016-04-19 06:57:45Z

0

I Think the solution given by @ddsu should have solved your problem. in case if not, in this i am going to use the same line of code but i am posting a full code for ur problem

    import MySQLdb
    servername = "localhost"
    username = "username"
    password = "password"
    conn = MySQLdb.connect(user=username,host=servername, passwd=password, db=db)
    cursor = conn.cursor()
    user_input = raw_input("What do you want to watch?:")
    query= ("SELECT * from videos where title LIKE %"+user_input+"%")
    cursor.execute(query)

answered Apr 19, 2016 at 6:57

Strik3r

1,0578 silver badges15 bronze badges

1 Comment

Brian Sizemore Over a year ago

This answer is vulnerable to SQL injection. Dacuna's answer utilizes the sanitization built into the cursor.execute function, by passing the user input directly to the execute function whereas this function simply places it into the string with no sanitization.

JensV · Accepted Answer · 2020-04-21 08:57:10Z

0

The solution to your problem is putting your input in brackets ( ,) so the result would look something like this:

user_input = (raw_input("What do you want to watch?:"), )

Don't forget to put the comma ,

In full you have:

import MySQLdb
servername = "localhost";
username = "username";
password = "password";
conn = MySQLdb.connect(user=username,host=servername, passwd=password, db=db)
cursor = conn.cursor()
user_input = (raw_input("What do you want to watch?:"), )
query= ("SELECT * from videos where title LIKE %s")
cursor.execute(query,user_input)

edited Apr 21, 2020 at 8:57

JensV

4,6692 gold badges22 silver badges50 bronze badges

answered Apr 21, 2020 at 6:28

Stickman Dev.

316 bronze badges

Collectives™ on Stack Overflow

User Input to mySQL database

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related