CODEWITHSUNDEEP
flaskmysql-python
Bhargav
Bhargav

Reputation: 484

Error 1064 sql syntax error

enter image description hereI am trying to work on registration using WTF forms and I am facing a sql syntax error when I am trying to inject data through flask execution. But I can able to insert the data using normal sql query through mysql command line.

from wtforms import Form, BooleanField, StringField, PasswordField, validators
from MySQLdb import escape_string as thwart

class RegistrationForm(Form):
    username = StringField('Username', [validators.Length(min=4, max=25)])
    email = StringField('Email Address', [validators.Length(min=6, max=35)])
    password = PasswordField('New Password', [validators.DataRequired(), validators.EqualTo('confirm', message='Passwords must match')])
    confirm = PasswordField('Repeat Password')
    accept_tos = BooleanField('I accept the TOS', [validators.DataRequired()])
# for registering the user
@app.route('/register/', methods = ['GET', 'POST'])
def register_page():
    try:
        form = RegistrationForm(request.form)
        if request.method == 'POST' and form.validate():
            username = form.username.data
            email = form.email.data
            password = sha256_crypt.encrypt(str(form.password.data))

            c, conn = connection()
            x = c.execute("SELECT * FROM users WHERE username = '(%s)'" %(thwart(username),))
            #x = c.fetchone()
            if int(x) > 0:
                flash ("that username already taken, please take another")
                return render_template("register.html", form =form)
            else:
                c.execute("INSERT INTO users (username, password, email, tracking) VALUES (%s, %s, %s, %s)" %(thwart(username), thwart(password), thwart(email), thwart('/home/')))
                c.commit()
                flash("Thanks for registering")
                c.close()
                conn.close()
                gc.collect()

                session['logged_in'] = True
                session['username'] = username
                return redirect(url_for('dashboard'))


        return render_template("register.html", form = form)
    except Exception as e:
        return render_template("register.html", error = e, form = form)

The error can be found below After entering the password and matching it with confirm and submitting it. I am getting an error. Can anyone please help me on this.

Upvotes: 1

Views: 458

Answers (2)

Bhargav
Bhargav

Reputation: 484

query = "SELECT * FROM users WHERE username = %s"
x = c.execute(query, (thwart(username),))

similarly 

query2 = "INSERT INTO users (username, password, email, tracking) VALUES (%s, %s, %s, %s)"

c.execute(query2, (thwart(username), thwart(password), thwart(email), thwart('/home/'))

worked!

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

Your SQLite statements look wrong.

x = c.execute("SELECT * FROM users WHERE username = '(%s)'" %(thwart(username),))

The single quotes are already being handled as far as I know, but in any case you can just use a prepared statement:

x = c.execute("SELECT * FROM users WHERE username = ?", (thwart(username)))

The same is true regarding your INSERT statement:

c.execute("INSERT INTO users (username, password, email, tracking) VALUES (?, ?, ?, ?)" (thwart(username), thwart(password), thwart(email), thwart('/home/')))
            c.

Upvotes: 1

Related Questions