I need to fetch rows of a table called records in SQLite based on a conditional query that selects only records that match value of email column = current_user.email. Current_user is the proxy for current logged in user, so I want this table to filter records only showing the records applicable to this user.

rows = c.execute("select * from records where email = current_user.email").fetchall()

There is no problem is resolving value of current_user.email. I can print it and it shows me the email. The problem is in the query the way and the way I am implementing it, does not filter the records. However when I replace current_user.email with the actual email id in quotes, for example "[email protected]", it filters perfectly. So my question is how can I filter Sqlite column based on a value that is another variable (here: email = current_user.email), and not email = "[email protected]?

CodePudding user response：

Use a ? placeholder in the sql statement, then pass the specific value you're looking for as an extra argument.

rows = c.execute("select * from records where email = ?", [current_user.email]).fetchall()

CodePudding user response：

You need to add the content of the variable to your string. Right now, your SQL driver just sees "select * from records where email = user.email"

The code below should help.

rows = c.execute("select * from records where email = %(username)", {"username": current_user.email}).fetchall()

Bad Code that is vulnerable to SQL injection.

rows = c.execute("select * from records where email = {}".format(current_user.email)).fetchall()

Thanks to John Gordon for pointing it out.