20

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?

MPelletier
  • 16,256
  • 15
  • 86
  • 137
laramichaels
  • 1,515
  • 5
  • 18
  • 30

3 Answers3

26

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)
unutbu
  • 842,883
  • 184
  • 1,785
  • 1,677
  • @~unutbu: thanks, that nails it. Hadn't crossed my mind to simply append the % to the string itself *before* passing it as an arg through the db-api. – laramichaels Jan 20 '10 at 10:17
3

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)
Sean Vieira
  • 155,703
  • 32
  • 311
  • 293
  • 4
    -1 `"SELECT x FROM myTable WHERE x LIKE '%s%%'" % "doom' ; drop table x; select '"` **Oops?** – Brian Jan 19 '10 at 22:11
  • @Brian; very valid point! I was focusing on the syntax error; I should not have assumed that `beginning` was clean data. Thanks for the catch! – Sean Vieira Jan 19 '10 at 22:24
  • -1 removed. You still have a syntax error, though. Use double quotes for your string and you won't need to escape your single quotes. – Brian Jan 19 '10 at 22:49
  • Edited to fix the syntax error and the bug that Thomas pointed out. – Sean Vieira Jan 19 '10 at 23:10
  • I tried using ? instead of %s With mysql-connector-python and it did not – Schäfer Sep 12 '21 at 10:28
-1

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,) )
Brian
  • 25,523
  • 18
  • 82
  • 173
  • @Thomas: Why? Other than the fact that I used `'%?%'` instead of `'?%'` (He was not fully consistent in his query which he wanted), I don't see a problem. – Brian Jan 19 '10 at 22:50
  • 5
    The problem is that the ? gets replaced by the *quoted* version of the actual argument you pass. So given, for instance "';DROP TABLE x;SELECT '" (notice the single *and* double quotes there), you end up with this obviously bad query: SELECT x FROM myTable WHERE x LIKE '%';DROP TABLE x; SELECT '%' – Thomas Wouters Jan 19 '10 at 22:58
  • I don't follow. Doesn't DB-API's parameter substitution fix that? – Brian Jan 20 '10 at 01:04