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.

Answer 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.

Answer 2

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)