sqlite中的参数化startswith

Question

我正在为sqlite3使用python绑定，并尝试执行如下查询

table1

col1 | col2 
------------
aaaaa|1
aaabb|2
bbbbb|3

test.py

def get_rows(db, ugc):
    # I want a startswith query.  but want to protect against potential sql injection
    # with the user-generated-content
    return db.execute(
        # Does not work :)
        "SELECT * FROM table1 WHERE col1 LIKE ? + '%'",
        [ugc],
    ).fetchall()

有办法安全地做到这一点吗？

预期行为：

>>> get_rows('aa')
[('aaaaa', 1), ('aaabb', 2)]

Answer 1

在SQL中，+用于添加数字。您的SQL以... WHERE col1 LIKE 0结束。

若要连接字符串，请使用||

db.execute(
    "SELECT * FROM table1 WHERE col1 LIKE ? || '%'",
    [ugc],
)