1064 (42000): You have an error in your SQL syntax;-CodePudding

I am using MYSQL for storing username and their corresponding salted and hashed password it is giving me continuously this error.

1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$2b$12$0NwXT7hoHPBK.ywn/r5f3OWQKrF9o1/wUJt7u1eFtn3Se2XCmiXdm'')' at line 1

My Table Name is hassle_free_register and it table structure is as follows as :

 ---------- -------------- ------ ----- --------- ---------------- 
| Field    | Type         | Null | Key | Default | Extra          |
 ---------- -------------- ------ ----- --------- ---------------- 
| USER_ID  | int          | NO   | PRI | NULL    | auto_increment |
| USERNAME | varchar(255) | NO   | UNI | NULL    |                |
| PASSWORD | varchar(255) | NO   |     | NULL    |                |
 ---------- -------------- ------ ----- --------- ----------------

Code For Storing the password :

@app.route('/register' ,methods =['POST'])
def register():
   try:
      NAME = request.form['USER_NAME']
      PASSWORD = request.form['USER_PASSWORD']
      if(len(NAME)==0):
         raise ValueError("USERNAME CANNOT BE EMPTY")
      if(len(PASSWORD)<=8):
         raise ValueError("PASSWORD LENGTH TOO SHORT")
      if(len(PASSWORD)>=30):
         raise ValueError("PASSWORD LENGTH TOO LONG") 
      HASHEDPASS = bcrypt.hashpw(PASSWORD.encode('utf-8'),bcrypt.gensalt())    
      mycursor.execute("insert into Hassle_Free_Register(USERNAME,PASSWORD) values('{USER_NAME}','{USER_PASSWORD}');".format(USER_NAME = NAME,USER_PASSWORD = str(HASHEDPASS)))
      mydb.commit()
      return jsonify("REGISTERED SUCCESSFULLY") 
   except ValueError as error:
      return jsonify({"message":str(error)}),403
   except mysql.connector.Error as error:
      print(error)
      # return error
      return jsonify({"message":str(error)}), 404

Please help ! I am using Flask Framework.

CodePudding user response：

Do you see the problem? Your hashed password contains single quote marks. That screws up your quoting. So, let the database connector do it:

mycursor.execute("INSERT INTO HassleFree_Register(USERNAME,PASSWORD) VALUES(?,?);", (NAME, HASHEDPASS))

Depending on the database, you might need %s instead of ?.

As a stylistic note, it's not considered good form to use ALL CAPS for variable names. That's fine for global constants, but variables should be username or user_name.

CodePudding user response：

HASHEDPASS apparently contains a ' character, which is ending the string '{USER_PASSWORD}' early.

Use parameters to cursor.execute() rather than substituting variables directly into the SQL string.

mycursor.execute("insert into Hassle_Free_Register(USERNAME,PASSWORD) values(%s, %s);", (NAME, HASHEDPASS))