mxODBC argv sql query

[Steve Holden]

BartlebyScrivener wrote:
> This can't be the most elegant way to get a command line parameter into
> an sql query. It works but I can't explain why. Is there another, more
> correct way? Here sys.argv[1] is a topic like "laugher" or "technology"
> 
> import mx.ODBC.Windows as odbc
> import sys
> 
> driv='DRIVER={Microsoft Access Driver (*.mdb)};DBQ=d:/Access
> Databases/Quotations2005'
> 
> conn = odbc.DriverConnect(driv)
> c = conn.cursor()
> 
> c.execute ("SELECT Author, Topics.Topic1, Topic2, Quote FROM QUOTES7
> WHERE Topics.Topic1 LIKE '%%%s%%'"  % sys.argv[1])
> 
> rows = c.fetchall()
> 

Try

c.execute ("""SELECT Author, Topics.Topic1, Topic2, Quote FROM QUOTES7
               WHERE Topics.Topic1 LIKE ?""", ("%"+sys.argv[1],)

First, note that mx.ODBC uses paramstyle qmark bydefault. If you look in 
the DB API manual you'll see that the cursor execute() method can take a 
second argument which is a tuple of data values to replace the parameter 
marks in the statement.

The parameterised query is the best way to avoid the potential for "SQL 
injection" exploits against your program.

regards
  Steve
-- 
Steve Holden       +44 150 684 7255  +1 800 494 3119
Holden Web LLC                     www.holdenweb.com
PyCon TX 2006                  www.python.org/pycon/