Problem with quotes on MySQL request

[PierreSoulier]

Hello World,

i've been searching for a while but i've found nothing so i post it here.

My problem is that I want to create a function that search for an id on a MySQL database which the row contains a certain word.

Here is my programm:

import MySQLdb
    
def rechercheMySQL(): 
    table=raw_input("Nom de la TABLE à explorer:")
    mot=raw_input("Mot à rechercher:")
    filtre=raw_input("Filtrer dans:")
    tri=raw_input("Trier par:")
    mydb = MySQLdb.connect(host='localhost', user='root', passwd='eisti0001', db='DATAtourisme')
    cursor = mydb.cursor()
    SQL="SELECT %s FROM %s WHERE %s LIKE %s"
    cursor.execute(SQL, (tri, table, filtre, mot))        
    
if __name__ == "__main__":
    rechercheMySQL()

and it returns:

python RechercheMYSQL.py
Nom de la TABLE à explorer:Theatre
Mot à rechercher:cerises
Filtrer dans:commentaire
Trier par:id
Traceback (most recent call last):
  File "RechercheMYSQL.py", line 21, in <module>
    rechercheMySQL()
  File "RechercheMYSQL.py", line 18, in rechercheMySQL
    cursor.execute(SQL, (tri, table, filtre, mot))        
  File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 250, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 50, in defaulterrorhandler
    raise errorvalue
_mysql_exceptions.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 ''Theatre' WHERE 'commentaire' LIKE 'cerises'' at line 1")

Basically it put quotes in the SQL syntax that i do not want and i don't know how to get rid of it. Do you have any idea?

Sorry for my English or if i did something wrong, first time posting on a forum

[cryomick]

As per the MySQLdb documentation, you can use placeholders only for column names, not table names etc. You might want to go through this link (look for paramstyle)
So you can take input from the user as to what column he/she wants to select, but not which table to select from.

[Larz60+]

is a semicolon required at end of SQL?
Late post, answered above

[PierreSoulier]

Thank you for your answer cryomick, i will try something different :)

[woooee]

Try

SQL="SELECT %s FROM %s WHERE %s LIKE %s" % (tri, table, filtre, mot)
cursor.execute(SQL) 

[ichabod801]

@woooee: You probably need a %r for mot so that it comes through with quotes, and that is vulnerable to SQL injection attacks.

[nilamo]

Try

SQL="SELECT %s FROM %s WHERE %s LIKE %s" % (tri, table, filtre, mot)
cursor.execute(SQL) 

Don't do this. Let the wrapper at least escape whatever's typed first:

escape = MySQLdb.escape_string

tri = escape(tri)
table = escape(table)
sql = "select %s from %s where %%s like %%s" % (tri, table)

cursor.execute(sql, (filtre, mot))

That way, everything is still escaped, and the engine handles escaping the where clause.

[woooee]

and that is vulnerable to SQL injection attacks

If you accept anything without any acceptance testing. People who don't check the values sent deserve what they get, as they will have a corrupted database at the least and an injection at the most. There is no injection boogey man just poor programming.