1

I am making a username password program using sqlite and I want to check if a username is in the database (I've already done this) then I want to find the row id of said username. This will be a username that the user has input. I know how to find the rowid of a word in the database e.g 'Word'. How would I make is so I could replace the word with a variable?

def sign_in():
    usernameask = input("What is your username?")
    passwordask = input("What is your password?")
    c.execute("SELECT username FROM stuffToPlot")
    names = {name[0] for name in c.fetchall()} 
    if usernameask in names:
        print("Yes")
        c.execute("SELECT password FROM stuffToPlot")
        passs = {name[0] for name in c.fetchall()}
        if passwordask in passs:
            print("yes,pass")
            t = c.execute("SELECT rowid, FROM stuffToPlot WHERE username = 'usernameask' ")
            rowid = t.fetchall()
            for r in rowid:
                print(r)
        else:
            print("No,pass"

I am looking at where it says t = c.execute("SELECT rowid, FROM stuffToPlot WHERE username = 'usernameask' ") and want to replace the 'usernameask' which is currently looking for it as a word in the database to a variable. How would I do this? There is no error, it just finds the position of the word "usernameask" which isn't in the database.

Jason Aller
  • 3,541
  • 28
  • 38
  • 38
Samboy T
  • 43
  • 3
  • 8

1 Answers1

3

You want to use a parameterised query. You put placeholders in your query where your data has to go, and leave it to the database driver to put your data and the query together.

Parameterised queries let you avoid a common security pitfall, the SQL injection attack, where an attacker can 'augment' your database query by putting in more commands than you originally anticipated. Query parameters always make sure your data is only ever handled as data, not as commands.

A parameterised query us usually also faster, as it lets the database avoid having to parse your query every time if you use it more than once, and it can also reuse query plans.

The sqlite3 database library uses ? for positional parameters; put a ? where ever you need to use data from your code, and put the parameter values in a sequence (like a tuple or a list) in the second argument to cursor.execute():

t = c.execute("SELECT rowid, FROM stuffToPlot WHERE username = ?", (usernameask,))

Note that (usernameask,) is a tuple with one element. You could also use [usernameask].

This executes your SELECT query using the string value that usernameask references in the WHERE username = filter. The driver takes care of quoting your value properly.

You could also use named parameters, these take the form of :parametername, (where you can pick your own names), and then you use a dictionary for the second argument to cursor.execute(), mapping names to values:

t = c.execute(
    "SELECT rowid, FROM stuffToPlot WHERE username = :username",
    {'username': usernameask})

Here the placeholder is named username, and the dictionary maps that to the usernameask value.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
  • Thank you, these have worked, how ever i want it to just return the password and for some reason when you enter Sam its returning `('Sam', 'Sam', 'Sam')` note that the password and username are both Sam in the database i have tried fetchone() but that doesnt work, how would you make it only return the passoword, i also updated it a bit to make it more efficient. The updated are in the main body @Ghostly – Samboy T Oct 30 '17 at 10:59
  • @SamboyT: sorry, I had to roll that back, the question you asked was answered. Stack Overflow works by producing questions and answers for future visitors too. You now have a new question. – Martijn Pieters Oct 30 '17 at 11:14
  • 1
    @SamboyT: You selected `password, *`, so first the password column, then add *all* columns. Just select the password (drop the `*`), and take out the single element from the row: `password = c.fetchone()[0]`; this is fetching the first matching row, only one column, then taking that one column result out of the row and assigning it to a variable. – Martijn Pieters Oct 30 '17 at 11:15