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

Question

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

I am using flask with MySQLdb

vindex
– vindex

2017-01-04 13:06:55 +00:00
Commented Jan 4, 2017 at 13:06 — vindex
– vindex, Commented Jan 4, 2017 at 13:06

bruno desthuilliers · Accepted Answer · 2017-01-04 13:25:39Z

1

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.

edited Jan 4, 2017 at 13:25

answered Jan 4, 2017 at 13:07

bruno desthuilliers

78.3k6 gold badges102 silver badges129 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

vindex Over a year ago

it said "but the DB-API interpolation will automatically quote things and guard you from SQL injection attacks, to some extent. If you had a name value of "'; DELETE FROM coworkers;" in the first case, you’d be fine (as the single-quote character would be auto-quoted), but you might run into some slight data loss in the second case."

vindex Over a year ago

so, it is fine to use '%s' just like %s?

bruno desthuilliers Over a year ago

PLEASE for God's sake READ the code example, and compare to yours. To spell it loud: YOU MUST NOT DO THE INTERPOLATION YOURSELF BUT LEAVE IT TO THE DB-API !

user268396 Over a year ago

@vindex: No. You use the API as: cursor.execute(sqlWithPercentSGoesHere, tupleWithVariableValuesGoesHere)

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related