Use Python to perform sqlite3 quick and secure data insertion (Anti-injection ).

Table is created using the following statement:
Copy codeThe Code is as follows: create table userinfo (name text, email text)

Insert data faster

Here we use time. clock () for timing and look at the speed of the following three methods.
Copy codeThe Code is as follows:
Import sqlite3
Import time

Def create_tables (dbname ):
Conn = sqlite3.connect (dbname)
Cursor = conn. cursor ()
Cursor.exe cute ('''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.exe cute ('''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.exe cute ("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.exe cute ("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.exe cute.pdf ("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)

Running result:
Copy codeThe Code is as follows:
4.0522000000001e-07 0.531585119557 0.531584714334
0.755963264089 0.867329935942 0.111366671854
1.0324360882 1.12175173111 0.0893156429109
Another running result:
Copy codeThe Code is as follows:
4.0522000000001e-07 0.565988971446 0.565988566223
0.768132520942 0.843723660494 0.0755911395524
1.04367819446 1.13247636739 0.0887981729298
In the running result, the third column indicates the time when data is inserted. In general, the speed of the insert1 () method is very slow because every insert operation commit ().

More secure database operations

First run the Code:
Copy codeThe Code is as follows:
Import sqlite3

Def create_tables (dbname ):
Conn = sqlite3.connect (dbname)
Cursor = conn. cursor ()
Cursor.exe cute ('''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.exe cute ('''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.exe cute.pdf ("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.exe cute ("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.exe cute ("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)

Running 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 insecure_select (text) and secure_select (text) functions are intended to obtain the corresponding username information by 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.exe cute (), there is only one parameter, that is, the SQL statement. If there is a problem with the generated SQL statement, it will still be executed as usual.

Secure_select(can the real-time display of textpattern prevent SQL Injection? Does the first parameter of cursor.exe cute () Use a placeholder? Indicates the content to be replaced. The second parameter specifies the value corresponding to each placeholder. At the underlying implementation level, this method (at least) escapes special characters to prevent SQL injection.