(flask) python - mysql - using where clause in a select query with variable from URL

Question

@app.route('/select/<username>')
def select(username):

    db = MySQLdb.connect("localhost","myusername","mypassword","mydbname" )

    cursor = db.cursor()

    cursor.execute("SELECT * FROM p_shahr")

    data = cursor.fetchall()

    db.close()

    return render_template('select.html', data=data)

I want to edit the select query in this script in order to have

SELECT * FROm p_shahr WHERE os = username

How should I edit the query to include the where clause above to set os to username that is coming from URL?

Solution

Use placeholders in the query and pass the parameters as a tuple to execute.

@app.route('/select/<username>')
def select(username):

    db = MySQLdb.connect("localhost","myusername","mypassword","mydbname" )

    cursor = db.cursor()

    query_string = "SELECT * FROM p_shahr WHERE os = %s"
    cursor.execute(query_string, (username,))

    data = cursor.fetchall()

    db.close()

    return render_template('select.html', data=data)

But, be aware that this [passing data from URL directly to DB] is a very naive and attack prone approach. See

• https://stackoverflow.com/a/21734918/4183498
• https://stackoverflow.com/a/902417/4183498