Menu

Programming Error 1064

Help
2004-05-14
2012-09-19
  • Richard G. Barnich

    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

     
    • Ed Leafe

      Ed Leafe - 2004-05-14

      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, ))

       
    • Richard G. Barnich

      % 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.

       
    • Richard G. Barnich

      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

       
    • Andy Dustman

      Andy Dustman - 2004-05-19

      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.

       
    • Richard G. Barnich

      Thanks for the help. I understand now why it wasn't working. Now, on to character sets!

      Rick

       
    • Juris Program

      Juris Program - 2004-09-17

      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

       
      • Andy Dustman

        Andy Dustman - 2004-09-17

        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.

         

Log in to post a comment.

Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.