MySQL prepared statements causing SQL syntax error

Question

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

Have you thought of creating a stored procedure on the database? — Keith John Hutchison
– Keith John Hutchison, Commented Jun 22, 2019 at 10:00
I'm assuming I would still need to use prepared statements when calling the procedure, therefore meaning I may still be stuck in the same situation? — Ollie Pugh
– Ollie Pugh, Commented Jun 22, 2019 at 10:05
You cannot use multiple statements in execute, that's why MySQL is complaining about everything after the first statement, BEGIN;. See e.g. here. To (not) use transactions, set the autocommit values, see e.g. here or here. — Solarflare
– Solarflare, Commented Jun 22, 2019 at 10:14
Then when I break it down into just the first INSERT statement I am told that no database is selected, therefore I use the USE command, then I am shown the original error message displayed within the question. — Ollie Pugh
– Ollie Pugh, Commented Jun 22, 2019 at 10:39

Ollie Pugh · Accepted Answer · 2019-06-22 11:27:48Z

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.

Collectives™ on Stack Overflow

MySQL prepared statements causing SQL syntax error

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related