CODEWITHSUNDEEP
pythonmysql
Ollie Pugh
Ollie Pugh

Reputation: 446

MySQL prepared statements causing SQL syntax error

I am using prepared statements for my SQL insert query, and I am receiving the message that there is an error in the syntax.

I have tried using PHPMyAdmin and used the same query in that and substituted the placeholders for the real values and that query worked fine, therefore I am assuming it is something to do with my use of prepared statements.

def create_user(f_name, s_name, email, sex, dob, mobile=None):
    try:
        conn = mysql.connector.connect(host=host, user=user, passwd=password)  # create a connection to the database
        cursor = conn.cursor(prepared=True)  # Creates a cursor that is expecting prepared

        if mobile is not None:  # if the mobile number is specified
            sql_parameterized_query = ("""BEGIN;
                                          INSERT INTO users (FirstName, Surname, Email, Dob, Gender, Mobile)
                                          VALUES (%s, %s, %s, %s, %s, %s);
                                          INSERT INTO passwords (UserID, hashedPass)
                                          VALUES (LAST_INSERT_ID(),%s);
                                          COMMIT;""")
            query_array = (f_name, s_name, email, date_sql_format(dob), sex, mobile, hash_user_password)

        else:  # if the mobile number is not specified
            sql_parameterized_query = ("""BEGIN;
                                         INSERT INTO users (FirstName, Surname, Email, Dob, Gender)
                                         VALUES(%s, %s, %s, %s, %s);
                                         INSERT INTO passwords (UserID, hashedPass)
                                         VALUES(LAST_INSERT_ID(),%s);
                                         COMMIT;""")
            query_array = (f_name, s_name, email, date_sql_format(dob), sex, hash_user_password)  # Init array of values

        cursor.execute(sql_parameterized_query, query_array)  # Execute query
        conn.commit()

I would like it to insert the details for a new user into the database all fields are required excluding the mobile phone number, that is why I have used the if statement to separate them, if this is poor practice then please guide me in the correct direction for that too as I could not find a more elegant way of solving that issue, anyway, when calling the function like so create_user("Ollie", "Pugh", "[email protected]", "M", "22-04-2001")

The variable query_array has the value of ('Ollie', 'Pugh', '[email protected]', '2001-04-22', 'M', '$2b$12$RU9FRcNjgHlC78kjZA5OIeqT1s1K2LHndC2iDK8mcqkadGc8d9XO2')

The message I receive is: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INSERT INTO users (FirstName, Surname, Email, Dob, Gender)' at line 2

The structure of the table Users is:

CREATE TABLE `users` (
 `UserID` int(11) NOT NULL AUTO_INCREMENT,
 `FirstName` varchar(255) NOT NULL,
 `Surname` varchar(255) NOT NULL,
 `Email` varchar(255) NOT NULL,
 `Dob` date NOT NULL,
 `Gender` char(1) NOT NULL,
 `Mobile` varchar(11) DEFAULT NULL,
 `timeOfCreation` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `Authority` varchar(255) NOT NULL DEFAULT 'User',
 PRIMARY KEY (`UserID`)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=latin1

Upvotes: 1

Views: 120

Answers (1)

Ollie Pugh
Ollie Pugh

Reputation: 446

The solution to my problem was to create a procedure, I named it CreateUser and consisted of:

BEGIN

START TRANSACTION;
    IF mobile = "NULL" THEN 
        SET mobile = null;
    END IF;

    INSERT INTO `cl43-flexfit`.users (FirstName, Surname, Email, Dob, Gender,Mobile)
    VALUES (f_name, s_name, email, dob, gender, mobile);

    INSERT INTO `cl43-flexfit`.passwords (UserID, hashedPass)
    VALUES (LAST_INSERT_ID(), passhash);
COMMIT;
END

And I have modified the python script to have two cursors as I could not use a USE statement within the procedure nor could I use one with a cursor that was configured for prepared statements.

    try:
        conn = mysql.connector.connect(host=host, user=user, passwd=password)  # create a connection to the database
        cursor = conn.cursor()  # Have to have two as you can not select database with a prepared cursor
        prep_cursor = conn.cursor(prepared=True)  # Creates a cursor that is expecting prepared

        if mobile is None:  # if the mobile number is not specified
            mobile = "NULL"  # This is recognised as null in the procedure

        sql_parameterized_query = "CALL CreateUser(%s, %s, %s, %s, %s, %s, %s)"  # Calls the create user procedure

        query_array = (f_name, s_name, email, date_sql_format(dob), sex, mobile, hash_user_password)

        cursor.execute("USE `cl43-flexfit`;")
        prep_cursor.execute(sql_parameterized_query, query_array)  # Execute query
        conn.commit()

I'm sure there are still better ways of doing this, but this does the job for now.

Upvotes: 1

Related Questions