MySQL-Python code not working, generating various errors each time i attempt to fix

Question

I have a python script that interfaces with a mysql database i have. Each time i run it, it gives me a different error every time. I am becoming so confused as to what to do to fix it.

Basically, the code is meant for an user to input a search parameter for an account database, and the script searches a MySQL table and brings up data about the account.

As of now i get no errors, but it returns absolutely no search results.

I used to be able to make it search by an EXACT username, but i wanted it so you can search for a term within that username. Every attempt at the latter always results in some sort of error or i get no results back from MySQL.

import mysql.connector

users1 = mysql.connector.connect(
    host="localhost",
    user="python",
    passwd="HaHaYou'reNotGettingMyPassword",
    database="accounts"
)

cursor=users1.cursor()

usersearch = input("Please input the search term: ")

sql = ("SELECT user_id, username, date, status, description, gen FROM users1 WHERE username LIKE %s")
cursor.execute(sql, ('"' + usersearch + '"'))

result = cursor.fetchall()

for x in result:
    print(x)
print("In Order: User ID, Username, Account Creation Date, bla bla bla")

EDIT: i figured out i think my SQL syntax is incorrect. i'll try an fix that and see if that was my only problem.

Does the SQL you posted return rows as expected? Also, have you heard of "SQL Injection Attack" before? — mgrollins, Jul 17 '19 at 00:30
yes. i was hoping someone could answer a way to make it work, while making it attack-proof. — eastasdfasdfaserawfasdgffras, Jul 17 '19 at 01:01
OK, check my answer, I think it will get it working, and I added a link to a good resource here on SO for SQL Injection mitigation https://stackoverflow.com/questions/7929364/python-best-practice-and-securest-to-connect-to-mysql-and-execute-queries — mgrollins, Jul 17 '19 at 17:55

altunyurt · Answer 1 · 2019-07-17T01:09:46.200

0

Try :


sql = ("SELECT user_id, username, date, status, description, gen FROM users1 WHERE username LIKE %s")
cursor.execute(sql, [usersearch])

edited Jul 17 '19 at 01:09

answered Jul 17 '19 at 00:22

altunyurt

2,821
3
38
53

that just made it give me ValueError: Could Not Process Parameters – eastasdfasdfaserawfasdgffras Jul 17 '19 at 00:59
Edited, please check – altunyurt Jul 17 '19 at 01:10
when i search for an existing user, it gives me the user and it's data. it only searches for things with that specific name.. if there is no user with that name it returns no values. – eastasdfasdfaserawfasdgffras Jul 17 '19 at 01:14
I was thinking there might be something with the sql syntax that takes the input, checks for any values within the "username" column and if it has the input word in it at all, it displays it. is this something i need to fix in the sql syntax? – eastasdfasdfaserawfasdgffras Jul 17 '19 at 01:18

score 0 · Answer 2 · answered Jul 17 '19 at 17:54

If you get results when specifying a full username with the above, you probably need to add "wildcards" to your search term to "find within" existing strings. The wildcard "%" matches 0 or more characters.

E.g. change your string concatenation to:

cursor.execute(sql, ('"%' + usersearch + '%"'))

WARNING: This input style is wide open to SQL Injection Attack, as user input is directly used to create part of the SQL statement that is sent to the DB engine. Please see this answer for mitigation methods.

MySQL-Python code not working, generating various errors each time i attempt to fix

2 Answers2