Flask-MySQL How to effectively INSERT INTO table with parameterized queries

Question

I am trying to build a sign up form with Flask and trying to submit the user entries to MySQL database.

I am not having much success when using parameterized queries. Here is my app.py code:

from flask import Flask, render_template, request, json
from flaskext.mysql import MySQL
from werkzeug import generate_password_hash, check_password_hash

mysql = MySQL()
app = Flask(__name__)

with open('config.json', 'r') as f:
config = json.load(f)

app.config['MYSQL_DATABASE_USER'] = config['user']
app.config['MYSQL_DATABASE_PASSWORD'] = config['password']
app.config['MYSQL_DATABASE_DB'] = config['database']
app.config['MYSQL_DATABASE_HOST'] = config['host']
mysql.init_app(app)

@app.route("/")
def main():
  return render_template('index.html')

@app.route('/showSignUp')
def showSignUp():
  return render_template('signup.html')

@app.route('/signUp',methods=['POST','GET'])
def signUp():

  try:
    _name = request.form['inputName']
    _email = request.form['inputEmail']
    _password = request.form['inputPassword']
    _username = request.form['inputUsername']


    # validate the received values
    if _name and _email and _password and _username:

        conn = mysql.connect()
        cursor = conn.cursor()

        query = "select * from tbl_user where user_email = %s"
        cursor.execute(query, (_email))
        data = cursor.fetchall()


        if len(data) is 0:

            _hashed_password = generate_password_hash(_password)
            query = "insert into tbl_user (user_name,user_username,user_password,user_email) values (%s,%s,%s,%s)"

            cursor.execute(query, (_name, _username, _hashed_password, _email))
            conn.commit()

            return json.dumps({'message':'User created successfully !'})

        else:
            return json.dumps({'error':str(data[0]), 
                                'message':'An account associated with this email address already exists.'

                })
    else:
        return json.dumps({'html':'<span>Enter the required fields</span>'}) 

  except Exception as e:
    return json.dumps({'error':str(e)})

  finally:
    cursor.close() 
    conn.close()

if __name__ == "__main__":
   app.run(debug = True)

Here is what I have tried so far:

Hardcoded values work.The specific problem I am having is with this query. When I use parameters it does not work, but when I use harcoded values it does.

 query = "insert into tbl_user (user_name,user_username,user_password,user_email) values (%s,%s,%s,%s)"            
 cursor.execute(query, (_name, _username, _hashed_password, _email))
 conn.commit()

A simple query with 1 parameter: In the code below, works fine with the parameter and it returns the expected results, so I am confused why the other query is not working.
```
 query = "select * from tbl_user where user_email = %s"
 cursor.execute(query, (_email))
 data = cursor.fetchall()
```

Debugging the code with print statements in console: for what is worth, I've a also tried printing the values like shown below and nothing will print in my console...which seems very strange to me.

 if len(data) is 0:

   list_of_values = [_name, _username, _hashed_password, _email]
   print (list_of_values)

   _hashed_password = generate_password_hash(_password)
   query = "insert into tbl_user (user_name,user_username,user_password,user_email) values (%s,%s,%s,%s)"

    cursor.execute(query, (_name, _username, _hashed_password, _email))
    conn.commit()

Different python - mysql packages: in addition to Flask-MySQL I've also tried PyMySQL and had the same issue.

So what kind of issues are you getting? Error message, no queries, ... etc — Iron Fist, Jan 14 '17 at 15:56
@IronFist, apologies..I should have been more specific. The data is not being inserted into the mysql table. I am replying to your solution below. Thanks. — JSC, Jan 14 '17 at 19:23

Iron Fist · Answer 1 · 2017-01-14T16:16:40.287

1

Try to use a dictionary as parameter, which is more readable:

params = {
    '_name' : request.form['inputName']
    '_email' : request.form['inputEmail']
    '_password' : request.form['inputPassword']
    '_username' : request.form['inputUsername']
}

Then:

query = """insert into tbl_user (user_name, user_username, user_password, user_email) 
         values (%(_name)s, %(_username)s, %(_password)s, %(_email)s)"""

cursor.execute(query, params)

edited Jan 14 '17 at 16:16

answered Jan 14 '17 at 16:08

Iron Fist

10,739
2
18
34

1

Just in case, I'd like to emphasize how important it is to pass the parameters to the driver and not do the interpolation manually, to avoid SQL injection. – 9000 Jan 14 '17 at 17:47
@IronFist, thanks for the suggestion. You solution does indeed work and I agree, it is more readable. However, I found the problem. What is causing me issues is the -hasshed_password `_hashed_password = generate_password_hash(_password)` . After I removed this line and just used the _password variable, both your solution and mine worked. I am intrigue about why that line would mess everything else up. Thanks again for your help though. – JSC Jan 14 '17 at 19:25
@9000 - by passing the parameters to the driver do you mean doing: `params = (_name, _username, _password, _email)
query = """insert into tbl_user (user_name, user_username, user_password, user_email) values (%s, %s, %s, %s)"""
cursor.execute(query, params) `? Thanks – JSC Jan 14 '17 at 20:00
@JSC: Yes, this is it. (Unfortunately, the format is driver-specificr) – 9000 Jan 16 '17 at 01:43

score 1 · Answer 2 · answered Jan 14 '17 at 23:25

As I mentioned in one of the comments, what was creating the issue was the generate_hashed_password( ) function. By doing some research in StackOverflow I found that this function generates strings that are longer than the original password...so I ended up updating my MySQL table password column from VARCHAR(45) to VARCHAR(100) and that took care of the issue.

Flask-MySQL How to effectively INSERT INTO table with parameterized queries

2 Answers2