Pass three parameters to MySQL using Flask REST API

Question

I need to build a route in flask using python that would allow 3 parameters to be passed via URI so I can use the 3 parameters in a MySQL query.

The database table has each of the 3 parameters in 3 columns and the end result query would be something like:

Select * from maintable where (field1= param1 and field2 = param2 and field3 = param3);

I’d like the URI to look like: http://my.api.com/search/123/345/DD5432

The python code looks like this

@app.route('/search/param1/param2/param3')
def get():
cur = mysql.connect().cursor()
cur.execute('''select * from maindb.maintable''')
r = [dict((cur.description[i][0], value)
          for i, value in enumerate(row)) for row in cur.fetchall()]
return jsonify({'results' : r})

I have so far been able to successfully pass 1 parameter and use that to query 1 column in my database.

Answer 1

First of all you need to change the route rule to be able to extract parts of it. And then you need to send these parameters within the SQL query. However, don't build your SQL query from the user input directly since it could introduce an SQL injection vulnerability. Use placeholders and then submit your params as a tuple to the DB cursor execute():

@app.route('/search/<param1>/<param2>/<param3>')
def get(param1, param2, param3):
    cur = mysql.connect().cursor()
    cur.execute('''select * from maindb.maintable where field1 = %s and field2 = %s and field3 = %s''', 
                (param1, param2, param3))
    r = [dict((cur.description[i][0], value)
         for i, value in enumerate(row)) for row in cur.fetchall()]
    return jsonify({'results': r})