Flask-Mysql type error when update data

Question

I'm having a strange bug when I write a website with flask and package flask-mysql.

Here is the code of the bug function:

@app.route('/calendar/editeventtitle',methods=['POST'])
def editeventtitle():
    if not session.get('logged_in'):
      abort(401)

    try:
      id = request.form.get('id',type=int)
      title = request.form['title']
      color = request.form['color']
      delete = request.form.get('delete')
    except:
      pass

    conn = mysql.connect()
    cursor = conn.cursor()
    print(id,type(id))
    # try:
    #   print(delete,type(delete))
    # except:
    #   pass

    if id and delete:
        cursor.execute('delete from events where id = %d',id)
        conn.commit()
        flash('Event canceled!')
        return redirect(url_for('calendar'))
    elif id and title and color:
        cursor.execute('update events set title = %s, color = %s where id = %d',(title,color,id))
        conn.commit()
        flash('Event updated!')
        return redirect(url_for('calendar'))

When I post the four variables to this page. I succesfully get them. And the result of print(id,type(id)) is like:

6 <class 'int'>

We see it's really an integer, but when the code starts to update or delete data from db, here is the error message:

TypeError: %d format: a number is required, not str

Really don't know the reason =-=, anyone can help me? Thank you.

PS: Python3.6.1, Flask 0.12.2, Flask-Mysql 1.4.0

score 3 · Accepted Answer · answered Sep 07 '17 at 11:09

You should use %s for all parameters in a SQL query which you execute like this. The reason is that flask-mysql (or whatever MySQL Python library it depends on) will convert the parameters title, color, id that you give it to a string, before doing string interpolation.

The reason it does this is because it also checks that whatever parameters you used in the query are something actually reasonable parameters of expected types like integer, SQL identifier, and not pieces of SQL code which might break your query. (This is used to gain unauthorized access to databases, and is called SQL injection). For each value it will examine the type and decide how to convert it to a SQL representation.

This is why you don't need single quotes around the parameters title and color. The SQL library identifies the parameters as strings and adds the quote, after also escaping any single quotes contained in those strings. It's also why you can pass a Python object like a datetime in here. The object will be correctly converted to a literal representation of the corresponding SQL type.

Flask-Mysql type error when update data

1 Answers1