JSC
JSC

Reputation: 21

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

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:

Upvotes: 0

Views: 6345

Answers (2)

JSC
JSC

Reputation: 21

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.

Upvotes: 1

Iron Fist
Iron Fist

Reputation: 10961

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)

Upvotes: 1

Related Questions