0

I created a query in MySQL workbench to test out using MySQL in python, it runs fine in workbench, but when I run it in python, it tells me the part after the WHERE is invalid syntax

I have looked around for this issue and cannot find it, python MySQL works for most queries I have run but this one is failing and I do not know why

code:

import math
import mysql.connector

config = {'user': 'allazzanni', 'password': 'not posting that here', 'host': 'also no', 'database': 'i could, but i wont'}


def queryDatabase (query):

    cnx = mysql.connector.connect(**config)

    cursor = cnx.cursor()

    cursor.execute(query) #this is where the error is do stuff with the result

query = ("SELECT g.Grade FROM Grade = g, Topic = t WHERE g.idTopic = t.idTopic and t.nameTopic = %s")

print queryDatabase (query, 'math')

Expected:
the query to execute properly

Actual:
mysql.connector.errors.ProgrammingError: 1064 (42000): 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 'g.idTopic = t.idTopic and t.nameTopic = 'math'' at line 1
user3837868
  • 917
  • 1
  • 12
  • 24
Allazzanni
  • 1
  • 1

3 Answers3

0

instead of doing this 

query = ("SELECT g.Grade FROM Grade = g, Topic = t WHERE g.idTopic = t.idTopic and t.nameTopic = %s")

print queryDatabase (query, 'math')

Can you do this ?

query = ("""SELECT g.Grade FROM Grade = g, Topic = t WHERE g.idTopic = t.idTopic and t.nameTopic = '{0}'""".format('math'))

print queryDatabase (query)

What you are trying to do is sending the query as is, and sending the query param as a separate param to your queryDatabase function, which is only expecting one param (query). Hence you need to perform string interpolation before you pass the query to your function.

Hope this resolves your query.

Update:

It seems that it is happening because strings are not being escaped properly. Can you please follow some of the answers that are mentioned here : Escape string Python for MySQL

Thanks

Gagan
  • 5,416
  • 13
  • 58
  • 86
  • tried that but it still does the same error, I don't think the issue is with how I am passing it, but rather with the syntax of the SQL itself, but I have no idea why it is giving me these problems since it works in workbench – Allazzanni Apr 07 '19 at 22:25
  • Just updated the query - can you please try it ? I have used triple quotes to escape strings – Gagan Apr 07 '19 at 22:34
0

Never use string interpolation or concatenation to put parameter values into query strings that you send to your database. Not only does that introduce a vulnerability to SQL injection attacks, it may also require awkward quotes and quote escapes in the SQL string, as you have experienced yourself.
Every DB API 2 compliant driver package supports parameter binding and that's what you should always use to put parameters into your query.

You can pass the query parameters to your function using Python's arbitrary argument lists

def queryDatabase (query, *args):
    # every positional argument passed in after the 'query' argument will be collected in 'args'
    # in this example, 'args' will be ('math',). This is a valid format for the 'params' 
    # argument of the 'execute' method
    cnx = mysql.connector.connect(**config)    
    cursor = cnx.cursor()
    # passing the tuple containing the positional arguments as 'params' argument to 'execute' 
    cursor.execute(query, args)
    return cursor.fetchall()

query = "SELECT g.Grade FROM Grade = g, Topic = t WHERE g.idTopic = t.idTopic and t.nameTopic = %s"
# the parameters must be provided in the order matching the markers in your query string
print queryDatabase (query, 'math')

Disclaimer: I do not have a MySQL DB to test this. I tested with sqlite3 and Sybase ASE via pypyodbc. The recipe worked for both engines.
Using a query without parameter markers and omitting any positional arguments worked as well, e.g.:

query = "SELECT * FROM Grade"
print queryDatabase (query)

I'm fairly positive that this will work the same way for MySQL.

shmee
  • 4,721
  • 2
  • 18
  • 27
0

Another option is to use Bind Variables, Try to write it like that:

query = ("""SELECT g.Grade FROM Grade = g, Topic = t WHERE g.idTopic = t.idTopic and t.nameTopic = :s""")

print queryDatabase (query, {s='math'})

The triple dock-strings is instead of using "text" which allows you to use bind variables. Now you can add add many as you want to the dictionary, notice the syntax - you need to add ":" before the argument.

ohaim
  • 1