CODEWITHSUNDEEP
pythonmysqlmysql-python
vindex
vindex

Reputation: 341

Flask Python: MySQLdb, %s unable to access database (to prevent SQL injection)

I am referring this post https://ianhowson.com/blog/a-quick-guide-to-using-mysql-in-python/ that %s is prefer as it prevent SQL injection while '%s' is not. However, I try to convert a working code which used '%s' to %s, the code is not working anymore. It return error 500 with internal server error.

@app.route('/KTM/search', methods=['GET'])
def KTM():
    db = MySQLdb.connect(host='xxx.mysql.pythonanywhere-services.com',user='xxx',passwd='xxx',db='jackie$default',cursorclass=MySQLdb.cursors.DictCursor)
    curs = db.cursor()
    name = request.args['name']
    valueMin = request.args['valueMin']
    valueMax = request.args['valueMax']
    volumeMin = request.args['volumeMin']
    volumeMax = request.args['volumeMax']
    macd = request.args['macd']
    stoch = request.args['stoch']
    bollinger = request.args['bollinger']
    rsi = request.args['rsi']
    atr = request.args['atr']
    trade = request.args['trade']
    limit = request.args['limit']
    offSet = request.args['offSet']
    query0 = "SELECT * FROM KTM WHERE Stock LIKE %s%% AND "
    query1 = "(Value BETWEEN (IF(%s='_',-5000,%s)) AND (IF(%s='_',5000,%s))) AND "
    query2 = "(Volume_changes_pc BETWEEN (IF(%s='_',-5000,%s)) AND (IF(%s='_',5000,%s))) AND "
    query3 = "MACD LIKE %s AND "
    query4 = "STOCH LIKE %s AND "
    query5 = "BOLLINGER LIKE %s AND "
    query6 = "RSI LIKE %s AND "
    query7 = "ATR LIKE %s AND "
    query8 = "TRADE LIKE %s LIMIT %s OFFSET %s"
    query = query0+query1+query2+query3+query4+query5+query6+query7+query8
    input = name,float(valueMin),float(valueMin),float(valueMax),float(valueMax),float(volumeMin),float(volumeMin),float(volumeMax),float(volumeMax),macd,stoch,bollinger,rsi,atr,trade,int(limit),int(offSet)
    try:
        curs.execute(query%(input))
        g = curs.fetchall()
    except Exception:
        return 'Error: unable to fetch items'

    return jsonify({'Stock': g})

I did change the input as float or int when it is numbers. I also check that the MySQL seems only taking SELECT * FROM KTM WHERE Stock LIKE 'G%'but not SELECT * FROM KTM WHERE Stock LIKE G%. How to make my code roust to prevent SQL injection with %S?I am using Python 3.4 now

Upvotes: 0

Views: 2254

Answers (1)

bruno desthuilliers
bruno desthuilliers

Reputation: 77892

What about actually reading the post you mention ? It comes with a very explicit example of how to use the db-api placeholders:

cursor.execute("SELECT name, phone_number FROM coworkers WHERE name=%s AND clue > %s LIMIT 5", (name, clue_threshold))

As you can see, the variables are passed as the second argument to cursor.execute(), not used for string formatting against the SQL query.

Edit: with a shorter example it migh be easier to read ?

Good : 

sql = "SELECT name, phone FROM WHERE name=%s AND clue > %s"
args = (name, clue_threshold)
# NO string interpolation
cursor.execute(sql, args)

Bad:

sql = "SELECT name, phone FROM WHERE name=%s AND clue > %s"
args = (name, clue_threshold)
# BAD string interpolation: 
#  - does not respect types so may generate invalid SQL
#  - also this is plainly a SQL injection vulnerability by itself
#  - don't do this!
cursor.execute(sql % args)

Can you see the difference ?

Also, you may want to use triple quoted strings for readability:

query = """
SELECT * FROM KTM WHERE 
    Stock LIKE %s
    AND (Value BETWEEN (IF(%s='_',-5000,%s)) AND (IF(%s='_',5000,%s))) 
    AND (Volume_changes_pc BETWEEN (IF(%s='_',-5000,%s)) AND (IF(%s='_',5000,%s))) 
    AND MACD LIKE %s 
    AND STOCH LIKE %s 
    AND BOLLINGER LIKE %s 
    AND RSI LIKE %s 
    AND ATR LIKE %s 
    AND TRADE LIKE %s 
    LIMIT %s OFFSET %s
"""

And - for like clauses, you will have to add the wildcards to your arguments, ie:

sql = "SELECT * FROM workers WHERE name like %s"
arg = "%{name}%".format(name)
cursor.execute(sql, (args,))

And finally, your catch-all except clause will only prevent you from debugging your code. You should at least log the exception and traceback.

Upvotes: 1

Related Questions