Python operations Sqlite3 An instance of fast, safe insertion of data (anti-injection) _python

The table is created by using the following statement:

Copy Code code as follows:

CREATE table UserInfo (name text, email text)

Inserting data more quickly

Use the Time.clock () to time to see the speed of the following three methods.

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

One run Result:

Copy 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 Code code 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 into use. In general, the Method Insert1 () is slow because each insert is a commit ().

Working with databases more securely

First code:

Copy 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 R ow 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
 &NB sp;  for row in Cursor.execute ("select name from UserInfo where email=?", (text,)):
    &nbs p;   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)

Run Result:

Copy Code code 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 intent of the function Insecure_select (text) and Secure_select (text) is to obtain the corresponding username information according to the email. However, the implementation of Insecure_select (text) can easily cause SQL injection.

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

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