Question 20818 in Mysql Question posted in
The official MySQL documentation can be found here.

Error executing cursor.execute when quotes are required – Mysql

fairly new to SQL in general. I’m currently trying to bolster my general understanding of how to pass commands via cursor.execute(). I’m currently trying to grab a column from a table and rename it to something different.

import mysql.connector

user = 'root'
pw = 'test!*'
host =  'localhost'
db =  'test1'

conn = mysql.connector.connect(user=user, password=pw, host=host, database=db)
cursor = conn.cursor(prepared=True)
new_name = 'Company Name'
query = f'SELECT company_name AS {new_name} from company_directory'
cursor.execute(query)
fetch = cursor.fetchall()

I’ve also tried it like this:

query = 'SELECT company_name AS %s from company_directory'
cursor.execute(query, ('Company Name'),)
fetch = cursor.fetchall()

but that returns the following error:

stmt = self._cmysql.stmt_prepare(statement)
_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 '? from company_directory' at line 1

I’m using python and mySQL. I keep reading about database injection and not using string concatenation but every time I try to use %s I get an error similar to the one below where. I’ve tried switching to ? syntax but i get the same error.

If someone could ELI5 what the difference is and what exactly database injection is and if what I’m doing in the first attempt qualifies as string concatenation that I should be trying to avoid.

Thank you so much!

Tags:

2

Answers


  1. 0

    If a column name or alias contains spaces, you need to put it in backticks.

    query = f'SELECT company_name AS `{new_name}` from company_directory'

    You can’t use a placeholder for identifiers like table and column names or aliases, only where expressions are allowed.

    Login or Signup to reply.
  2. 0

    You can’t make a query parameter in place of a column alias. The rules for column aliases are the same as column identifiers, and they must be fixed in the query before you pass the query string.

    So you could do this:

    query = f"SELECT company_name AS `{'Company Name'}` from company_directory'
cursor.execute(query)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search