#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    8
    Rep Power
    0

    Python-mysql question concerning %s


    I've done up the beginning of a script that connects to MySQL. What I'm trying to figure out is in the ("SELECT * FROM table") I want the user prompted to supply the table name. I've tried messing with the syntax but always get:

    Traceback (most recent call last):
    File "mysql.py", line 22, in <module>
    cur.execute("SELECT * FROM %r") % (table)
    File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 174, in execute
    self.errorhandler(self, exc, value)
    File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, 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 '%s' at line 1")

    here is a screenshot:
    [IMG]file:///home/michael/Pictures/Screenshot%20from%202013-07-29%2020:51:53.png[/IMG]

    Am I doing something simple wrong?
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    8
    Rep Power
    0
    Originally Posted by MTGailey
    I've done up the beginning of a script that connects to MySQL. What I'm trying to figure out is in the ("SELECT * FROM table") I want the user prompted to supply the table name. I've tried messing with the syntax but always get:

    Traceback (most recent call last):
    File "mysql.py", line 22, in <module>
    cur.execute("SELECT * FROM %r") % (table)
    File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 174, in execute
    self.errorhandler(self, exc, value)
    File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, 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 '%s' at line 1")

    here is a screenshot:
    [IMG]file:///home/michael/Pictures/Screenshot%20from%202013-07-29%2020:51:53.png[/IMG]

    Am I doing something simple wrong?
    I guess I am unaware as to how to add the image.

    Sooooo....here

    #!/usr/bin/python
    #filename: datademo.py
    #a simple script to pull some data from a mysql table

    import MySQLdb
    import sys
    import datetime
    import time

    #login




    db = MySQLdb.Connect (host='localhost', user="root", passwd="brightleaf001", db="wastewater" )
    cur = db.cursor()



    table = raw_input("Enter a table name and press enter:\n")

    cur.execute("SELECT * FROM %s") % (table)
    for row in cur:
    print(row)

    #loop to iterate



    cur.close()
    db.close()
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2010
    Posts
    153
    Rep Power
    5
    You're closing the parenthesis before the % operator, so the method is receiving the literal string without any replacement going on.

    So first, you need to move the "% (table)" bit inside the parenthesis.

    Second, if you care at all about the data in this database, you need to make sure you are sanitizing the "table" variable, such as checking it against a list of valid table names. Otherwise you've just created a whopper of a SQL injection vulnerability.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    8
    Rep Power
    0
    Thank you for the info. Im just learning all of this, so I will definatly look into your observations.

IMN logo majestic logo threadwatch logo seochat tools logo