Search and compare SQLite / Python database

Question

At the moment, I have a database that contains a username, password, etc. I want to look in the database to see if there are duplicates.

con = lite.connect('userInfo.db') with con: cur = con.cursor() cur.execute("SELECT * FROM Users WHERE LOWER(Username) = LOWER(?)", (newID,)) rows = cur.fetchall() if len(rows)!=0: return "Duplicate detected" 

Here is my code for now. newID is the new name, and I want to check if there are any existing entries in the database with the same name.

My question is - am I doing this in my code - a good idea? I am mostly concerned about my approach. Should I use something other than fetchall() ?

Thank you for your time! :) Postscript This is an app for the website.

Answer 1

Here you can do what you requested - to find out if this username exists:

 import sqlite3 conn = sqlite3.connect(":memory:") conn.execute (""" CREATE TABLE users ( uid INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT UNIQUE, email TEXT UNIQUE ); """) test_users = ( {'username':"Alice", 'email':" [e-mail] "}, {'username':"Billy", 'email':" [e-mail] "}, {'username':"Charles", 'email':" [e-mail] "}, {'username':"Dick", 'email':" [e-mail] "}, {'username':"Emily", 'email':" [e-mail] "}, {'username':"Faramir", 'email':" [e-mail] "}, ) for user in test_users: conn.execute("INSERT INTO users (username, email) VALUES (?,?)", (user['username'],user['email']) ) result = conn.execute("SELECT COUNT(*) FROM users WHERE username='Alice'") number_of_Alices = result.next()[0] # number_of_Alices will be 1 

Since all you need is COUNT , that's enough.

---

Indeed, you should not use the uniqueness of the usernames themselves. Let the database do this for you by specifying the field as UNIQUE or PRIMARY KEY .

If you try to insert "Alice", " [e-mail] " after creating the database as shown above, this will give you sqlite3.IntegrityError:

 >>> conn.execute("""INSERT INTO users (username, email) ... VALUES ("Alice", " [e-mail] ");""") Traceback (most recent call last): File "<stdin>", line 2, in <module> sqlite3.IntegrityError: column username is not unique 

To detect this, try running INSERT and determining if it will work.

 try: conn.execute("""INSERT INTO users (username, email) VALUES ("Alice", " [e-mail] ");""") except sqlite3.IntegrityError: print ("Username 'Alice' was already taken.") 

By the way, be very careful when using upper / lower case functions. " ".lower() means what you think it means?”

---

Since you mentioned this for webapp, I just remind you that you store your passwords as salted password hashes, using unique salts for each user (never like plain text!), And also to protect against SQL injection using (?,?,?,?,...) placeholders for SQL queries, not the method (%s,%s) % (var1, var2) for string interpolation.

To quote sqlite3 documentation:

Usually your SQL operations should use values ​​from Python variables. You should not collect your request using the Pythons string because it is unsafe; this makes your program vulnerable to SQL injection attack.

Use DB-API parameter substitution instead. Put it down? as a placeholder, wherever you want to use the value, and then provide a tuple of values ​​as the second argument to the execute () method of the cursors. (Other database modules may use a different placeholder, for example% s or: 1.) For example:

If you do not, someone may request the username Robert Menzies; DROP TABLE users; Robert Menzies; DROP TABLE users; with fun results .

Answer 2

Why not set the field in SQLite unique, which will prevent duplicate entries in the first place.