Parameter Substitution for a SQLite "In" Clause

Parameter substitution for a SQLite IN clause

You do need the right number of ?s, but that doesn't pose a sql injection risk:

>>> result_set = c.execute('SELECT * FROM distro WHERE id IN (%s)' %
                           ','.join('?'*len(desired_ids)), desired_ids)
>>> print result_set.fetchall()
[(1, u'Ubuntu'), (2, u'Fedora'), (5, u'SuSE')]

Parameter substitution for a SQLite with multiple “IN” clause

The number of ?s in the SQL query needs to match the number of items in the parameters tuple. In your first example there are two items in the tuple: names and ids. But there are four question marks. Try tuple(names) + tuple(ids), or define the variables as tuples using round brackets and then just use names + ids.

In the second example it's one tuple names containing two names, hence it works.

Properly parameterize sqlite query with IN operator

Provided your delete_list isn't overly long you can do something like:

place_holders = ",".join("?"*len(delete_list))
query_string = '''delete from strat where Id in ({})'''.format(place_holders)
cur.execute(query_string, delete_list)

SQLite parameter substitution problem

The Cursor.execute() method expects a sequence as second parameter. You are supplying a string which happens to be 8 characters long.

Use the following form instead:

self.cursor.execute("SELECT weight FROM Equipment WHERE name = ?", [item])

Python library reference: sqlite3 Cursor Objects.

sqlite3 IN clause

So the answer from Alex Martelli is essentially solving your problem since python support for the IN is quite unwieldy, and in essence you have to provide as many ? as you have values in your collection.

selection = (100, 200, 300)
questionmarks = '?' * len(selection)
formatted_query = 'SELECT value1, value3 FROM myTable
              WHERE value2>? AND pageNum IN ({})'.format(','.join(questionmarks))
query_args = [10]
query_args.extend(selection)
result_set = c.execute(formatted_query, query_args)

the above code should do, and heres bit of explanation on what it does:

It essentially prepares the sql statement with as many ? as you need and then executes the query with all your arguments supplied in a list so sqlite will take care of escaping the values.

If you convert your query to a string you will have to make sure that you sanitize the values yourself which is not recommended.

Python SQLite parameter substitution with wildcards in LIKE

The quotes protect either ? or :name from being taken as a place-holder -- they're taken literally. You need to place the percent signs around the string you're passing, and use the plain placeholder without quotes. I.e.:

self.cursor.execute(
  "select string from stringtable where string like ? and type = ?",
  ('%'+searchstr+'%', type))

Note that neither ? is in quotes -- and that's exactly as it should be for them to be taken as placeholders.

Using sqlite3 DB-API multiple parameter substitution in SELECT statements

Maybe this sample can help you.

import sqlite3
import string

def char_generator():
    for c in string.ascii_lowercase:
        yield (c,)

con = sqlite3.connect(":memory:")

def initdb():
    cur = con.cursor()
    cur.execute("create table characters(c)")

    cur.executemany("insert into characters(c) values (?)", char_generator())

def search(value):
    values = [c for c in value]
    cur = con.cursor()
    cur.execute('SELECT * FROM characters WHERE c IN ({0})'.format(','.join(['?' for c in values])), values)
    return cur.fetchall()

if __name__ == '__main__':
    initdb()
    print(search("abcde"))

This code uses parameters. So you do not need to worry about SQL Injection.

Bind parameter in FROM clause in SQLite

The ? should only be used for binding actual values into the query such in the VALUES section of an INSERT or values in a WHERE clause.

For dynamically building the column and table names you should simply use a string format to build the query string before sending it to FMDB.

- (BOOL)existsColumn:(NSString *)column inTable:(NSString *)table ofDatabase:(FMDatabase *)database {
    NSString *query = [NSString stringWithFormat:@"SELECT %@ FROM %@ LIMIT 0", column, table];
    return (BOOL)[database executeQuery:query];
}

Please note that you should not use the string format in place of using ? and proper value binding for actual values (which you don't have in this case).

SELECT ... WHERE ... IN with unknown number of parameters

That's your best option without using additional libraries. I certainly have advocated just that technique in the past, more than once in fact.

You could also switch to using SQLAlchemy, which generates SQL for you, but that requires you to climb its learning curve and rewrite most of your application.

---

Related Topics