SQL syntax error in a Flask application

Question

I am developing a game display system, with user login necessary to add games to this list, but when I try to run the query, I get a syntax error.

It follows the code that prepares the database (has the main application of the web, yet to change, and the DAO of "Game" and "User" also.):

import pymysql
pymysql.install_as_MySQLdb()
print('Conectando...')
conn = pymysql.connect(user='root', passwd='admin', host='127.0.0.1', port=3306)

criar_tabelas = '''SET NAMES utf8;
    CREATE DATABASE jogoteca;
    USE jogoteca;
    CREATE TABLE jogo (
      id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
      nome varchar(50) COLLATE utf8_bin NOT NULL,
      categoria varchar(40) COLLATE utf8_bin NOT NULL,
      console varchar(20) NOT NULL
    ); ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
    CREATE TABLE usuario (
      id varchar(8) NOT NULL PRIMARY KEY COLLATE utf8_bin,
      nome varchar(20) COLLATE utf8_bin NOT NULL,
      senha varchar(8) COLLATE utf8_bin NOT NULL

    ); ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;'''

conn.cursor().execute(criar_tabelas)

# inserindo usuarios
cursor = conn.cursor()
cursor.executemany(
      'INSERT INTO jogoteca.usuario (id, nome, senha) VALUES (%s, %s, %s)',
      [
            ('andré', 'André Jurgen', 'flask'),
            ('miguel', 'Miguel de Cervantes', '12345'),
            ('danilo', 'Danilo Ravetti', 'python')
      ])

cursor.execute('select * from jogoteca.usuario')
print(' -------------  Usuários:  -------------')
for user in cursor.fetchall():
    print(user[1])

# inserindo jogos
cursor.executemany(
      'INSERT INTO jogoteca.jogo (nome, categoria, console) VALUES (%s, %s, %s)',
      [
            ('God of War 4', 'Ação', 'PS4'),
            ('NBA 2k18', 'Esporte', 'Xbox One'),
            ('Rayman Legends', 'Indie', 'PS4'),
            ('Super Mario RPG', 'RPG', 'SNES'),
            ('Super Mario Kart', 'Corrida', 'SNES'),
            ('Fire Emblem Echoes', 'Estratégia', '3DS'),
      ])

cursor.execute('select * from jogoteca.jogo')
print(' -------------  Jogos:  -------------')
for jogo in cursor.fetchall():
    print(jogo[1])

# commitando senão nada tem efeito
conn.commit()
cursor.close()

The mistake I have is:

"C:\Users\Windows 10\AppData\Local\Programs\Python\Python37-32\python.exe" "C:/Users/Windows 10/PycharmProjects/jogoteca/prepara_banco.py"
Conectando...
Traceback (most recent call last):
  File "C:/Users/Windows 10/PycharmProjects/jogoteca/prepara_banco.py", line 22, in <module>
    conn.cursor().execute(criar_tabelas)
  File "C:\Users\Windows 10\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pymysql\cursors.py", line 170, in execute
    result = self._query(query)
  File "C:\Users\Windows 10\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pymysql\cursors.py", line 328, in _query
    conn.query(q)
  File "C:\Users\Windows 10\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pymysql\connections.py", line 517, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "C:\Users\Windows 10\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pymysql\connections.py", line 732, in _read_query_result
    result.read()
  File "C:\Users\Windows 10\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pymysql\connections.py", line 1075, in read
    first_packet = self.connection._read_packet()
  File "C:\Users\Windows 10\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pymysql\connections.py", line 684, in _read_packet
    packet.check_error()
  File "C:\Users\Windows 10\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pymysql\protocol.py", line 220, in check_error
    err.raise_mysql_exception(self._data)
  File "C:\Users\Windows 10\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pymysql\err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
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 'CREATE DATABASE jogoteca;\n    USE jogoteca;\n    CREATE TABLE jogo (\n      id int' at line 2")

Process finished with exit code 1

Could someone help me solve this problem? Grateful.

Answer 1

The fact that you can create a string of arbitrary size in Python, with """, mean that you can put as many SQL commands in there and have them executed as if they had been typed right into the database prompt:

The call .execute() from any Python database connector runs a single command (statement) in SQL - even the ; at the end of the command.

Hence the mistake: the ;, when typed directly at the database prompt signals the end of the command. For programmatic calls to an SQL database (to any SQL_database, what signals the end of the command is the end of the string. (This feature alone already hinders SQL Injection attacks - although it doesn’t stop if other parts of the code break - but only attacks made as Subqueries, not as separate queries).

The solution in this case is to put each command you want to run in SQL into a separate string, and make a call to each one of them. Without changing almost anything in your program, this is easily solvable with a for:

criar_tabelas = [
    "SET NAMES utf8",
    "CREATE DATABASE jogoteca",
    "USE jogoteca",
    """CREATE TABLE jogo (
      id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
      nome varchar(50) COLLATE utf8_bin NOT NULL,
      categoria varchar(40) COLLATE utf8_bin NOT NULL,
      console varchar(20) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
    """,
    """CREATE TABLE usuario (
      id varchar(8) NOT NULL PRIMARY KEY COLLATE utf8_bin,
      nome varchar(20) COLLATE utf8_bin NOT NULL,
      senha varchar(8) COLLATE utf8_bin NOT NULL

    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
    """
]

cursor = conn.cursor()

for comando in criar_tabelas:
    cursor.execute(comando)

Answer 2

The error in the query is here:

); ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; ^

You have a semicolon where you shouldn’t.

Regardless of alerting you to the error location, I respectfully suggest that when you encounter new problems run the query directly in the bank and find out for yourself what is going wrong.

Answer 3

You are running multiple inserts so you need to use

cursor.executemany('select * from jogoteca.usuario')

I suggest you use it like this

SQL_COMMAND = "INSERT INTO TABELA (CAMPOVALORA, CAMPOVALORB, CAMPOVALORC) VALUES (%s, %s)"

VALORES = [
  ('VALORA', 'VALORA'),
  ('VALORB', 'VALORB'),
  ('VALORC', 'VALORC')
]
cursor.executemany(SQL_COMMAND, VALORES)