Querying MySQL database using Python

Question

I have a table named 'staff', with several columns, but the two I'm interested in are 'username' and 'password'. I am trying to create a prompt which asks the user for their username (and later I will do password) and checks the table in the database to see if that username exists. I am a bit clueless about how to do this, but this is what I have coded so far.

import MySQLdb


db = MySQLdb.connect(host="127.0.0.1", user="root", passwd="", db="sakila")

cur = db.cursor()

search = raw_input("Enter username: ")

query = ('SELECT username, password FROM staff')

cur.execute(query)


results = cur.fetchall()

if search in results:
    print("username found")
else:
    print("invalid username")

db.close()

I think you want a `WHERE` clause in the SQL statement to select the user. — Gordon Linoff, Jun 01 '15 at 10:56
Oh, so how exactly would that work? (I literally just started SQL yesterday) — Programmer, Jun 01 '15 at 10:58
I would look at SQLAlchemy if I was you. Trust me, your life will be better for it — Mantis, Jun 01 '15 at 11:32

NDevox · Accepted Answer · 2015-06-01T11:27:46.683

2

EDIT: Make sure to check the comment from @FallenAngel at the bottom and check for security risks in your code!

As Gordon mentioned, you should use a WHERE clause to make things easier. Like so:

import re

import MySQLdb

db = MySQLdb.connect(host="127.0.0.1", user="root", passwd="", db="sakila")

cur = db.cursor()

search = raw_input("Enter username: ")

user_regex =  # input a regex here to make sure the username matches a format which avoids SQL injection.

if re.search(user_regex, search):

    query = ("SELECT password FROM staff WHERE username = %s", (search,)) #  add a WHERE clause

    cur.execute(query)

    results = cur.fetchall()

else:
    results = None

if results:  # Now we just need to check if the queryset is empty or not.
    print("username found")
else:
    print("invalid username")

db.close()

General rule of thumb is to try and make SQL do the searching for things, it's built for it so will be faster than Python.

Make sure your username column is a primary key (or a unique one at least) so you don't have duplicates.

EDIT: Based on @FallenAngels point, you shouldn't directly inject user input into an SQL query as it would expose you to SQL injection.

EDIT2:

First note that we are no longer using "%s" % var formatting for this solution as it is unsafe! Instead we are using "%s", (var,) formatting which is for db queries.

%s (it can also be %d, %n and a few other letters) in a string is a form of string formatting used to insert variable content into a string.

Essentially if you say:

"some stuff here: %s" % var

Anything that is in var will replace the %s in the string. There are several intricacies so it's worth reading more here: https://docs.python.org/2/library/string.html

edited Jun 01 '15 at 11:27

answered Jun 01 '15 at 11:05

NDevox

4,056
4
21
36

for anyone who downvotes - Let me know why please so I can improve my answer! – NDevox Jun 01 '15 at 11:08
1

Never use string operations for database queries since they are vulnerable to SQL injections... – Mp0int Jun 01 '15 at 11:08
1

Thank you very much @Scironic for your help :) – Programmer Jun 01 '15 at 11:10
@SakshamYadav, make sure you take into account what FallenAngel has said, I'm updating my answer atm. – NDevox Jun 01 '15 at 11:11
2

`("SELECT password FROM staff WHERE username = %s", (search,))` you must use `%s` for all placeholders and pass the values with a list or tuple. – Mp0int Jun 01 '15 at 11:12
alright, will do, thanks :). By the way, why are we selecting password? – Programmer Jun 01 '15 at 11:12
@SakshamYadav. Under the assumption that username is unique, and you already have it from the user input, you should only need to retrieve the password for use. Add username back in if it suits you better. – NDevox Jun 01 '15 at 11:16
Assuming this answers your question, don't forget to mark it as correct to help users looking at it in the future. – NDevox Jun 01 '15 at 11:17
That is still insecure and wrong! Check http://stackoverflow.com/questions/775296/python-mysql-with-variables – Mp0int Jun 01 '15 at 11:17
Oh, um..I'm just in high school, developing this to improve my skills. It's not an actual system, sorry if that caused some confusion about SQL injections :) – Programmer Jun 01 '15 at 11:18
@FallenAngel Sorry! Updated to your solution now. Deleted comment and updated. – NDevox Jun 01 '15 at 11:19
@SakshamYadav It's ok, you should still take it into account even if it's for personal use. Best to form good habits early. More to the point if anyone else ever looks at this who *is* making a live app, it would be a bad idea to give them unsafe information. – NDevox Jun 01 '15 at 11:21
So, before I head out, I would just like to know what this part means exactly - 'username = '%s'" % search'. Once again, thank you both for helping me, really appreciate it. @Scironic – Programmer Jun 01 '15 at 11:21
@SakshamYadav no, it is quite important to point such things out so lerners would not do it in the wrong and dangerous way. – Mp0int Jun 01 '15 at 11:21
and yes, I will consider those things about preventing SQL injections. – Programmer Jun 01 '15 at 11:22

score 1 · Answer 2 · answered Jun 01 '15 at 11:27

Perhaps you need to distribute this in two files

In first file you please build the form and through app.route you link it to the def in python file.

This way you can have your presentation and business model completely separate and that will remain more maintainable as well.

Please let me know if you need more simplification along with the code.

Querying MySQL database using Python

2 Answers2