cannot insert datetime field with stored procedure into mysql database

Question

I am getting this syntax error when trying to insert a datetime into mysql database.

pymysql.err.ProgrammingError: (1064, "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 '00:00:01)' at line 1")

I am using pymysql and flask-mysql. This is my code:

  cursor.execute("""DROP TABLE IF EXISTS test_table_2;""")
  cursor.execute("""DROP PROCEDURE IF EXISTS updateTestProc2;""")
  testTable = """CREATE TABLE IF NOT EXISTS test_table_2 (
          time DATETIME,
          PRIMARY KEY (time)
          );
          """
  testProc = """
      CREATE PROCEDURE updateTestProc2(
        IN ptime DATETIME
      )
      BEGIN
        SET @queryStr = CONCAT('INSERT INTO test_table_2(time) VALUES ( ',
          ptime, 
          ')
          ;'
        );
        PREPARE query FROM @queryStr;
        EXECUTE query;
        DEALLOCATE PREPARE query;
      END
    """
  cursor.execute(testTable)
  cursor.execute(testProc)
  proc_input = ('1990-05-23 00:00:01',)
  cursor.callproc('updateTestProc2', proc_input)  

Answer 1

Do not use string concatenation to get values into statement. That's error prone and might make your program vulnerable to SQL injection attacks. Use parameters. Change your procedure's code to:

...
SET @queryStr = 'INSERT INTO test_table_2 (time) VALUES (?)';
PREPARE query FROM @queryStr;
EXECUTE query USING ptime;
DEALLOCATE PREPARE query;
...

? is a parameter placeholder that is replaced with the value of the expression you pass with USING when you do EXECUTE. That way don't need to care about escaping, quoting etc. and can't do it wrong.