Output is as follows: WELCOME TO THE LIBRARY SYSTEM 1.LOGIN 2.NEW USER 3.EXIT Enter your choice : 2 WELCOME, NEW USER, PLEASE ENTER A VALID USERNAME AND PASSWORD Enter username: ASHES Enter Password: MASON

And then, there is a traceback of the error:

Traceback (most recent call last): File "C:\Users\Rotten\AppData\Local\Programs\Python\Python310\lib\site-packages\mysql\connector\connection_cext.py", line 523, in cmd_query self._cmysql.query(query, _mysql_connector.MySQLInterfaceError: 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 '\xe9\xfbo\xcdrSy\xe9\x9f\xc2\xb7\xebs\x10\x01\xfc\xb6\xa4\xeb\xe6\xe1\xca\xfc\xe' at line 1

During handling of the above exception, another exception occurred:

Traceback (most recent call last): File "C:\Users\Rotten\Downloads\import mysql.connector as sqltor.py", line 139, in adduser(username,password) File "C:\Users\Rotten\Downloads\import mysql.connector as sqltor.py", line 71, in adduser cursor.execute("insert into users values('{}','{}','{}'".format(username,key,salt)) File "C:\Users\Rotten\AppData\Local\Programs\Python\Python310\lib\site-packages\mysql\connector\cursor_cext.py", line 269, in execute result = self._cnx.cmd_query(stmt, raw=self._raw, File "C:\Users\Rotten\AppData\Local\Programs\Python\Python310\lib\site-packages\mysql\connector\connection_cext.py", line 528, in cmd_query raise errors.get_mysql_exception(exc.errno, msg=exc.msg, mysql.connector.errors.ProgrammingError: 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 '\xe9\xfbo\xcdrSy\xe9\x9f\xc2\xb7\xebs\x10\x01\xfc\xb6\xa4\xeb\xe6\xe1\xca\xfc\xe' at line 1

mysql> use library;
Database changed
mysql> desc users;
 ---------- -------------- ------ ----- --------- ------- 
| Field    | Type         | Null | Key | Default | Extra |
 ---------- -------------- ------ ----- --------- ------- 
| username | varchar(20)  | YES  |     | NULL    |       |
| key      | varchar(100) | YES  |     | NULL    |       |
| salt     | varchar(100) | YES  |     | NULL    |       |
 ---------- -------------- ------ ----- --------- ------- 
3 rows in set (0.15 sec)

Minimal code:

import mysql.connector
import hashlib
import os
mycon=mysql.connector.connect(host="localhost",user="root",passwd="123456",database="library")
cursor=mycon.cursor()
stmt = "SHOW TABLES LIKE 'users'"
cursor.execute(stmt)
result = cursor.fetchone()
if result:
    pass
else:
    cursor.execute("create table users(username varchar(20),key varchar(100),salt varchar(100));")
    cursor.execute("create table userlist(username varchar(20),book varchar(200));")
def checkkey(usertest):
    cursor.execute("select count(username) from users where username='{}';".format(usertest))
    count = cursor.fetchone()[0]
    if count==1:
        return False
    elif count==0:
        return True
    else:
        print("error no valid value returned")
        return False
def adduser(username,password):
    salt = os.urandom(32)
    key = hashlib.pbkdf2_hmac('sha256', password.encode('utf-8'), salt, 100000)
    cursor.execute("insert into users values('{}','{}','{}'".format(username,key,salt))
    mycon.commit() 
while True:
        print("WELCOME, NEW USER, PLEASE ENTER A VALID USERNAME AND PASSWORD")
        usertest=input("Enter username: ")
        usertest2=usertest
        x=checkkey(usertest)
        if x==True:
            password=input("Enter Password: ")
            username=usertest2
            adduser(username,password)
            print("USER CREATED")
            break
        elif x==False:
            print("Username already exists, try again")
            continue
        else:
            print("Error, unknown exception in boolean")
            break

Why is this happening?

CodePudding user response：

For completeness sake here is the full answer:

there are several issues:

1. unbalanced parenthesis on this line cursor.execute("insert into users values('{}','{}','{}'".format(username,key,salt))
2. trying to insert byte data into varchar - this MAY work, it really depends on your sql engine
3. using string formatting for binary data

the solution to 1. and 3. is to use prepared statements:

cursor.execute("insert into users values(%s, %s, %s)", (username,key,salt))

If the issue persists then change the appropriate data types in your table from varchar to something like binary or varbinary