% doesn't work either. The MySQLdb examples use ','.
I went back to some code that worked with MySQL 3.23.58, and now it doesn't work with 4.1.1.
I'm using MySQLdb 0.9.1. Does anyone know if the MySQL interface has changed in 0.9.2 or 0.9.3?
I need MySQL 4.1.1 for character set handling.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
string_literal(...)
string_literal(obj) -- converts object obj into a SQL string literal.
This means, any special SQL characters are escaped, and it is enclosed
within single quotes. In other words, it performs:
"'%s'" % escape_string(str(obj))
This works!
result = xcursor.execute("""DELETE FROM %s""" % MySQLdb.escape_string(str(tname, )))
Rick
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
result = xcursor.execute("""DELETE FROM %s""" % tname)
Normally for queries you would do this:
result = xcursor.execute("""SELECT * FROM foo WHERE x<%s""", (x,))
But you are not supplying column values in your case: You are trying to pass a table name as a column value. The latter method converts the values into literals automatically; this means for strings, the values get put inside single quotes, so you can't use this for things like table or column names.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Would you mind explaining your post a bit further? Is this the method I would use to escape any possible special characters my user might enter?
So the user can enter backslashes and lots of double quotes and it will all get 'literalized' and put into the database exactly as he entered it?
This is what I'm looking to do and so far I'm completely stuck. It seems to me that even with triple quotes as in your example, if the user happens to enter triple quotes then you're screwed, right?
Any help would be greatly appriciated.
-Greg
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
cursor.execute() will correctly quote any value that is passed to it so it will not break your SQL, but I reiterate: The values passed to cursor.execute() MUST be column values; they CANNOT be things like column names, table names, or arbitrary SQL snippets. Those things must be substituted directly into your query, and as a general rule, you should never obtain this information directly from the user, and if you do, you must take care that it is properly sanitized. 99.9% of the time you will only want to have column values from the user, and execute() handles that just fine.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I'm getting a 'SQL syntax error' from the following Python code:
def delrecDB(tname):
db = MySQLdb.connect(db='goldrush')
xcursor = db.cursor()
result = xcursor.execute("""DELETE FROM %s""", (tname, ))
db.close()
I also get the same message when I change the SQL statement to """TRUNCATE TABLE %s""", (tname,)
Both work fine if don't hardwire the table name, ie
"""DELETE FROM crawler"""
What am I missing?
Python 2.3.3
MySQLdb 0.9.1
MySQL 4.1.1-alpha-standard
Rick Barnich
You're not using the correct syntax for replacing the string parameter. You need '%', not a comma. Try:
result = xcursor.execute("""DELETE FROM %s""" % (tname, ))
% doesn't work either. The MySQLdb examples use ','.
I went back to some code that worked with MySQL 3.23.58, and now it doesn't work with 4.1.1.
I'm using MySQLdb 0.9.1. Does anyone know if the MySQL interface has changed in 0.9.2 or 0.9.3?
I need MySQL 4.1.1 for character set handling.
Found this by running help("MySQLdb")
string_literal(...)
string_literal(obj) -- converts object obj into a SQL string literal.
This means, any special SQL characters are escaped, and it is enclosed
within single quotes. In other words, it performs:
"'%s'" % escape_string(str(obj))
This works!
result = xcursor.execute("""DELETE FROM %s""" % MySQLdb.escape_string(str(tname, )))
Rick
What you really need is:
result = xcursor.execute("""DELETE FROM %s""" % tname)
Normally for queries you would do this:
result = xcursor.execute("""SELECT * FROM foo WHERE x<%s""", (x,))
But you are not supplying column values in your case: You are trying to pass a table name as a column value. The latter method converts the values into literals automatically; this means for strings, the values get put inside single quotes, so you can't use this for things like table or column names.
Thanks for the help. I understand now why it wasn't working. Now, on to character sets!
Rick
Mr. Dustman,
Would you mind explaining your post a bit further? Is this the method I would use to escape any possible special characters my user might enter?
So the user can enter backslashes and lots of double quotes and it will all get 'literalized' and put into the database exactly as he entered it?
This is what I'm looking to do and so far I'm completely stuck. It seems to me that even with triple quotes as in your example, if the user happens to enter triple quotes then you're screwed, right?
Any help would be greatly appriciated.
-Greg
cursor.execute() will correctly quote any value that is passed to it so it will not break your SQL, but I reiterate: The values passed to cursor.execute() MUST be column values; they CANNOT be things like column names, table names, or arbitrary SQL snippets. Those things must be substituted directly into your query, and as a general rule, you should never obtain this information directly from the user, and if you do, you must take care that it is properly sanitized. 99.9% of the time you will only want to have column values from the user, and execute() handles that just fine.