Python操作sqlite3快速、安全插入数据（防注入）的实例

table通过使用下面语句创建：

复制代码 代码如下:create table userinfo(name text, email text)

更快地插入数据

在此用time.clock()来计时，看看以下三种方法的速度。

复制代码 代码如下:

import sqlite3

import time

def create_tables(dbname): 

    conn = sqlite3.connect(dbname)

    cursor = conn.cursor()

    cursor.execute('''create table userinfo(name text, email text)''')

    conn.commit()

    cursor.close()

    conn.close()

def drop_tables(dbname):

    conn = sqlite3.connect(dbname)

    cursor = conn.cursor()

    cursor.execute('''drop table userinfo''')

    conn.commit()

    cursor.close()

    conn.close()

def insert1():

    users = [('qq','qq@example.com'),

            ('ww','ww@example.com'),

            ('ee','ee@example.com'),

            ('rr','rr@example.com'),

            ('tt','tt@example.com'),

  &www.devze.comnbsp;         ('y编程客栈y','yy@example.com'),

            ('uu','uu@example.com')

            ]

    start = time.clock()

    conn = sqlite3.connect(dbname)

    cursor = conn.cursor()

    for user in users:

        cursor.execute("insert into userinfo(name, email) values(?, ?)", user)

        conn.commit()

    cursor.close()

    conn.close()

    end = time.clock()

    print start, end, end-start

def insert2():

    users = [('qq','qq@example.com'),

            ('ww','ww@example.com'),

            ('ee','ee@example.com'),

            ('rr','rr@example.com'),

            ('tt','tt@example.com'),

            ('yy','yy@example.com'),

          &www.devze.comnbsp; ('uu','uu@example.com')

            ]

    start = time.clock()

    conn = sqlite3.connect(dbname)

    cursor = conn.cursor()

    for user in users:

        cursor.execute("insert into userinfo(name, email) values(?, ?)", user)

    conn.commit()

    cursor.close()

    conn.close()

    end = time.clock()

    print start, end, end-start

def insert3():

    users = [('qq','qq@example.com'),

            ('ww','ww@example.com'),

            ('ee','ee@example.com'),

            ('rr','rr@example.com'),

            ('tt','tt@example.com'),

            ('yy','yy@example.com'),

            ('uu','uu@example.com')

            ]

    start = time.clock()

    conn = sqlite3.connect(dbname)

    cursor = conn.cursor()

    cursor.executemany("insert into userinfo(name, email) values(?, ?)", users)

    conn.commit()

    cursor.close()

    conn.close()

    end = time.clock()

    print start, end, end-start

if __name__ == '__main__':

    dbname = 'test.db'www.de开发者_MongoDBvze.com

    create_tables(dbname)

    insert1()

    drop_tables(dbname)

    create_tables(dbname)

    insert2()

    drop_tables(dbname)

    create_tables(dbname)

    insert3()

    drop_tables(dbname)

某次运行结果：

复制代码 代码如下:

4.05223164501e-07 0.531585119557 0.531584714334

0.755963264089 0.867329935942 0.111366671854

1.0324360882 1.12175173111 0.0893156429109

另外一次运行结果：

复制代码 代码如下:

4.05223164501e-07 0.565988971446 0.565988566223

0.768132520942 0.843723660494 0.0755911395524

1.04367819446 1.13247636739 0.0887981729298

在运行结果中，第三列表示插入数据使用的时间。综合看来，方法insert1()的速度很慢，原因在于每次insert都commit()。

更安全地操作数据库

先上代码：

复制代码 代码如下:

import sqlite3

def create_tables(dbname): 

    conn = sqlite3.connect(dbname)

    cursor = conn.cursor()

    cursor.execute('''create table userinfo(name text, email text)''')

    conn.commit()

    cursor.close()

    conn.close()

def drop_tables(dbname):

    conn = sqlite3.connect(dbname)

    cursor = conn.cursor()

    cursor.execute('''drop table userinfo''')

    conn.commit()

    cursor.close()

    conn.close()

def insert():

    users = [('qq','qq@example.com'),

            ('ww','ww@example.com'),

            ('ee','ee@example.com'),

            ('rr','rr@example.com'),

            ('tt','tt@example.com'),

            ('yy','yy@example.com'),

            ('uu','uu@example.com')

            ]

    conn = sqlite3.connect(dbname)

    cursor = conn.cursor()

    cursor.executemany("insert into userinfo(name, email) values(?, ?)", users)

    conn.commit()

    cursor.close()

    conn.close()

def insecure_select(text):

    conn = sqlite3.connect(dbname)

    cursor = conn.cursor()

    print "select name from userinfo where email='%s'" % text

    for row in cursor.execute("select name from userinfo where email='%s'" % text):

        print row

def secure_select(text):

    conn = sqlite3.connect(dbname)

    cursor = conn.cursor()

    print "select name from userinfo where email='%s'" % text

    for row in cursor.execute("select name fromhttp://www.devze.com userinfo where email= ? ", (text,)):

        print row

if __name__ == '__main__':

    dbname = 'test.db'

    create_tables(dbname)

    insert()

    insecure_select("uu@example.com")

    insecure_select("' or 1=1;--")

    secure_select("uu@example.com")

    secure_select("' or 1=1;--")

    drop_tables(dbname)

运行结果：

复制代码 代码如下:

select name from userinfo where email='uu@example.com'

(u'uu',)

select name from userinfo where email='' or 1=1;--'

(u'qq',)

(u'ww',)

(u'ee',)

(u'rr',)

(u'tt',)

(u'yy',)

(u'uu',)

select name from userinfo where email='uu@example.com'

(u'uu',)

select name from userinfo where email='' or 1=1;--'

函数insecure_select(text)和secure_select(text)的本意都是根据email获取对应的用户名信息。但是insecure_select(text)的实现容易引起sql注入。

insecure_select("' or 1=1;--")便是一个例子。在insecure_select()中cursor.execute()只有一个参数，即sql语句，这个生成的sql语句如果有问题，还是会照常执行。

secure_select(text)的实现可以防止sql注入，cursor.execute()的第一个参数使用了占位符?表示要被替代的内容，第二个参数指定每个占位符对应的值，在底层实现上，这种方法（至少）转义了特殊字符，可以防止sql注入。