mxODBC argv sql query

[BartlebyScrivener]

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

 

[Steve Holden]

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