Python操作sqlite3快速、安全插入數(shù)據(jù)（防注入）的實(shí)例

更新時(shí)間：2014年04月26日 09:24:07 作者：

這篇文章主要介紹了Python操作sqlite3快速、安全插入數(shù)據(jù)（防注入）的實(shí)例,通過在一個(gè)表格中進(jìn)行操作來論述如何使用Python快速安全地操作sqlite3,需要的朋友可以參考下

table通過使用下面語句創(chuàng)建：

復(fù)制代碼代碼如下:

create table userinfo(name text, email text)

更快地插入數(shù)據(jù)

在此用time.clock()來計(jì)時(shí)，看看以下三種方法的速度。

復(fù)制代碼代碼如下:

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'),
            ('yy','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'),
            ('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'
    create_tables(dbname)
    insert1()
    drop_tables(dbname)
    create_tables(dbname)
    insert2()
    drop_tables(dbname)
    create_tables(dbname)
    insert3()
    drop_tables(dbname)

某次運(yùn)行結(jié)果：

復(fù)制代碼代碼如下:

05223164501e-07 0.531585119557 0.531584714334
755963264089 0.867329935942 0.111366671854
0324360882 1.12175173111 0.0893156429109

另外一次運(yùn)行結(jié)果：

復(fù)制代碼代碼如下:

05223164501e-07 0.565988971446 0.565988566223
768132520942 0.843723660494 0.0755911395524
04367819446 1.13247636739 0.0887981729298

在運(yùn)行結(jié)果中，第三列表示插入數(shù)據(jù)使用的時(shí)間。綜合看來，方法insert1()的速度很慢，原因在于每次insert都commit()。

更安全地操作數(shù)據(jù)庫

先上代碼：

復(fù)制代碼代碼如下:

import sqlite3

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 from 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)

運(yùn)行結(jié)果：

復(fù)制代碼代碼如下:

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;--'

函數(shù)insecure_select(text)和secure_select(text)的本意都是根據(jù)email獲取對(duì)應(yīng)的用戶名信息。但是insecure_select(text)的實(shí)現(xiàn)容易引起sql注入。

insecure_select("' or 1=1;--")便是一個(gè)例子。在insecure_select()中cursor.execute()只有一個(gè)參數(shù)，即sql語句，這個(gè)生成的sql語句如果有問題，還是會(huì)照常執(zhí)行。

secure_select(text)的實(shí)現(xiàn)可以防止sql注入，cursor.execute()的第一個(gè)參數(shù)使用了占位符?表示要被替代的內(nèi)容，第二個(gè)參數(shù)指定每個(gè)占位符對(duì)應(yīng)的值，在底層實(shí)現(xiàn)上，這種方法（至少）轉(zhuǎn)義了特殊字符，可以防止sql注入。

您可能感興趣的文章: