动态创建占位符以在 SQLite 表中为一行插入多个列值 [英] Dynamically creating a placeholder to insert many column values for a row in SQLite table

问题描述

我知道可以使用带有值元组的变量在 SQLite 数据库中插入许多列值 ('2006-03-28', 'BUY', 'IBM', 1000, 45.00) 和查询字符串中相应的占位符 (?, ?, ?, ?, ?).我正在我的程序中动态创建值元组，它们最多可容纳约 300 个值.我想知道是否有一种安全的(关于 SQL 注入攻击)方法来动态生成相应的占位符元组字符串 (?, ?, ?, ...) 为查询字符串?我要求这样做是为了避免在我的数据库结构和值元组在整个开发过程中发生变化时繁琐地计数、添加和删除 ? .谢谢你的想法.

I know that it's possible to insert many column values in a SQLite database using a variable with a tuple of values ('2006-03-28', 'BUY', 'IBM', 1000, 45.00) and a corresponding placeholder (?, ?, ?, ?, ?) in the query string. I am creating the value tuples dynamically in my program and they may hold up to ~300 values. I am wondering if there is a safe (with respect to SQL injection attacks) way to dynamically generate corresponding the placeholder tuple string (?, ?, ?, ...) for the query string? I ask this to avoid tediously counting, adding and deleting ?s as my database structure and value tuples change throughout development. Thanks for your thoughts.

推荐答案

根据 values 中项目的数量构建一个字符串，例如:

Build a string based on the number of items in your values, eg:

def place_holder(values):
    return '({})'.format(', '.join('?' * len(values)))

values = ['a', 'b', 'c']
ph = place_holder(values)
# (?, ?, ?)

然后是这样的:

your_cursor.execute('insert into your_table values {}'.format(ph), values)

如果它不符合您的架构，您就会遇到问题，但这是另一个问题...

If it doesn't meet your schema, you'll have issues, but that's another problem...

这篇关于动态创建占位符以在 SQLite 表中为一行插入多个列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！