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

Source: Internet
Author: User
Tags sql injection


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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.