I am trying to assemble the following SQL statement using python's db-api:
SELECT x FROM myTable WHERE x LIKE 'BEGINNING_OF_STRING%';
where BEGINNING_OF_STRING should be a python var to be safely filled in through the DB-API. I tried
beginningOfString = 'abc'
cursor.execute('SELECT x FROM myTable WHERE x LIKE '%s%', beginningOfString)
cursor.execute('SELECT x FROM myTable WHERE x LIKE '%s%%', beginningOfString)
I am out of ideas; what is the correct way to do this?
It's best to separate the parameters from the sql if you can. Then you can let the db module take care of proper quoting of the parameters.
sql='SELECT x FROM myTable WHERE x LIKE %s'
args=[beginningOfString+'%']
cursor.execute(sql,args)
EDIT:
As Brian and Thomas noted, the far better way to do this would be to use:
beginningOfString += '%'
cursor.execute("SELECT x FROM myTable WHERE x LIKE ?", (beginningOfString,) )
since the first method leaves you open to SQL injection attacks.
Left in for history:
Try:
cursor.execute("SELECT x FROM myTable WHERE x LIKE '%s%%'" % beginningOfString)
"SELECT x FROM myTable WHERE x LIKE '%s%%'" % "doom' ; drop table x; select '" Oops?
beginning was clean data. Thanks for the catch!
Take note of Sqlite3 documentation:
Usually your SQL operations will need to use values from Python variables. You shouldn’t assemble your query using Python’s string operations because doing so is insecure; it makes your program vulnerable to an SQL injection attack.
Instead, use the DB-API’s parameter substitution. Put ? as a placeholder wherever you want to use a value, and then provide a tuple of values as the second argument to the cursor’s execute() method. (Other database modules may use a different placeholder, such as %s or :1.) For example:
# Never do this -- insecure! symbol = 'IBM' c.execute("... where symbol = '%s'" % symbol) # Do this instead t = (symbol,) c.execute('select * from stocks where symbol=?', t) # Larger example for t in [('2006-03-28', 'BUY', 'IBM', 1000, 45.00), ('2006-04-05', 'BUY', 'MSOFT', 1000, 72.00), ('2006-04-06', 'SELL', 'IBM', 500, 53.00), ]: c.execute('insert into stocks values (?,?,?,?,?)', t)
I think you want this:
cursor.execute('SELECT x FROM myTable WHERE x LIKE '%?%', (beginningOfString,) )
'%?%' instead of '?%' (He was not fully consistent in his query which he wanted), I don't see a problem.