Python operation Sqlite3 Fast, secure insertion of data (anti-injection) instances

Table is created by using the following statement:
Copy CodeThe code is as follows:

CREATE table UserInfo (name text, email text)

Insert Data faster

Use Time.clock () to take a look at the speed of the following three ways.
Copy the Code code as follows:

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)

A running result:
Copy the Code code as follows:

4.05223164501e-07 0.531585119557 0.531584714334
0.755963264089 0.867329935942 0.111366671854
1.0324360882 1.12175173111 0.0893156429109

Another run result:
Copy CodeThe code is as follows:

4.05223164501e-07 0.565988971446 0.565988566223
0.768132520942 0.843723660494 0.0755911395524
1.04367819446 1.13247636739 0.0887981729298

In the run result, the third column represents the time that the data was inserted for use. In general, the Method Insert1 () is slow because each insert commits ().

Working with databases more securely

First on the code:
Copy the Code code as follows:

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

Operation Result:
Copy CodeThe code is as follows:

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

The purpose of the function Insecure_select (text) and Secure_select (text) is to obtain the corresponding user name information according to the email. But the implementation of Insecure_select (text) is prone to SQL injection.

Insecure_select ("' or 1=1;--") is an example. In Insecure_select (), Cursor.execute () has only one parameter, the SQL statement, that will execute as usual if there is a problem with the generated SQL statement.

The implementation of Secure_select (text) prevents SQL injection, and the first parameter of Cursor.execute () uses a placeholder? Represents the content to be substituted, the second parameter specifies the value corresponding to each placeholder, and at the bottom implementation, this method (at least) escapes the special character , you can prevent SQL injection.

