[FIXED] Python sqlite query based on logged username

Issue

I am struggling to understand why I cannot get the expected result from my query. I am using flask with SQLite and can easily return the username to the webpage with the "userlogin = session['username']" What i am trying to get is to query the database based on the username of the logged user in order to only show information related to this specific user. mytable is configured with username column used for the filter.

@app.route('/dashboard') 
@is_logged_in
def dashboard():
    from functions.sqlquery import sql_query
    userlogin = session['username']
    results = sql_query('''SELECT * FROM mytable WHERE username IS "userlogin"''')
    return render_template('dashboard.html', results=results)

sql_query.py

def sql_query(query):
   cur = conn.cursor()
   cur.execute(query)
   rows = cur.fetchall()
   return rows

Solution

The sql query isn't correct. You use should = instead of IS.

I would recommend making the following changes:

1) use a parameterised query to avoid sql injection attacks. So pass the parameters to sql_query() as a tuple:

def sql_query(query, params):
   cur = conn.cursor()
   cur.execute(query, params)
   rows = cur.fetchall()
   return rows

2) change the call to sql_query

results = sql_query("SELECT * FROM mytable WHERE username = ?", (userlogin,))

Answered By - jignatius