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
– Iron Fist, Commented Jan 14, 2017 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
– JSC, Commented Jan 14, 2017 at 19:23

Iron Fist · Accepted Answer · 2017-01-14 16:16:40Z

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, 2017 at 16:16

answered Jan 14, 2017 at 16:08

Iron Fist

11k2 gold badges21 silver badges36 bronze badges

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

4 Comments

9000 Over a year ago

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.

JSC Over a year ago

@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 Over a year ago

@9000 - by passing the parameters to the driver do you mean doing:

params = (_name, _username, _password, _email)<br/> query = """insert into tbl_user (user_name, user_username, user_password, user_email) values (%s, %s, %s, %s)"""<br/> cursor.execute(query, params)

? Thanks

9000 Over a year ago

@JSC: Yes, this is it. (Unfortunately, the format is driver-specificr)

JSC · Accepted Answer · 2017-01-14 23:25:27Z

1

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.

answered Jan 14, 2017 at 23:25

JSC

212 silver badges7 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related