python - 具有可变数量占位符的安全 INSERT

Question

使用 Python 的 sqlite3 库，我可以在 SQL 语句中使用可变数量的占位符吗:

INSERT INTO table VALUES (?,?)` 

其中 ? 是占位符，这对于 SQL injection 是安全的。攻击？

我希望能够有一个通用函数(如下)来检查列数并将数据写入一行，但它可以适用于任何具有任意列数的表。

我看了:

• Python Sqlite3: INSERT INTO table VALUE(dictionary goes here)和

• PHP MYSQL 'INSERT INTO $table VALUES ......' variable number of fields

但我还是不确定。

def rowin(self, TableName, ColumnData=[]):
    # First check number columns in the table TableName to confirm ColumnData=[] fits
    check = "PRAGMA table_info(%s)"%TableName
    conn = sqlite3.connect(self.database_file)
    c = conn.cursor()
    c.execute(check)
    ColCount = len(c.fetchall())
    # Compare TableName Column count to len(ColumnData)

    if ColCount == len(ColumnData):
        # I want to be have the number of ? = ColCount
        c.executemany('''INSERT INTO {tn} VALUES (?,?)'''.format(tn=TableName), ColumnData)
        conn.commit()

    else:
        print("Input doesn't match number of columns")

Answer 1

def rowin(self,TableName,ColumnData=[]):
        #first count number columns in the table TableName
        check = "PRAGMA table_info(%s)"%TableName
        conn = sqlite3.connect(self.database_file)
        c = conn.cursor()
        c.execute(check)
        #assing number of columns to ColCount
        ColCount = len(c.fetchall())
        #compare TableName Column count to len(ColumnData)
        qmark = "?"
        #first create a place holder for each value going to each column
        for cols in range(1,len(ColumnData)):
            qmark += ",?"
        #then check that the columns in the table match the incomming number of data
        if ColCount == len(ColumnData):
            #now the qmark should have an equl number of "?" to match each item in the ColumnData list input
            c.execute('''INSERT INTO {tn} VALUES ({q})'''.format(tn=TableName, q=qmark),ColumnData)
            conn.commit()
            print "Database updated"
        else:
            print "input doesnt match number of columns"