Using sqlite3 DB-API multiple parameter substitution in SELECT statements

Question

Given:

letters = list("abc")

I'd like to get all rows in characters that contain any of the letters in letters in their c column. I can do this, but only with python's string operations, which isn't suitable given it's vulnerabilities.

Ideally (my example is simplified) this would be using the GLOB clause.

E.g.

>>> cur.execute(**the statement here**)
>>> print(cur.fetchall())
>>> [('a',), ('b',), ('c',)]

Creation of the db:

import sqlite3
import string

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

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("create table characters(c)")

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

I don't think it's quite a dupe, but this question might help. — glibdud
– glibdud, Commented Mar 8, 2019 at 16:28

andercruzbr · Accepted Answer · 2019-03-08 17:03:15Z

3

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.

answered Mar 8, 2019 at 17:03

andercruzbr

4542 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using sqlite3 DB-API multiple parameter substitution in SELECT statements

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related