Pymysql Preventing SQL injection

I. Introduction to SQL injection

SQL injection is one of the most common methods of network attack, it is not to exploit the bugs of the operating system to implement the attack, but to neglect the programmer's programming, to realize the login without account and even tamper with the database through SQL statements.

Second, the general idea of SQL injection attack

1. Find the location of the SQL injection

2. Determine server type and background database type

3. SQL injection attacks against non-server and database features

Iii. examples of SQL injection attacks

1, string splicing query, resulting in injection

ImportPymysqlconn= Pymysql.connect (host='127.0.0.1', port=3306, user='Root', passwd="', db='User') Cursor=conn.cursor () Username=input ()Password=input ()#condition of the normal construction statementsql ="Select user,pwd from user where user= '%s ' and pwd= '%s '"%(Username,password) Row_count=cursor.execute (SQL) Row_1=Cursor.fetchone ()Print(Row_count, Row_1) conn.commit () Cursor.close () conn.close ()

In fact, users can enter the implementation of free account login:

Username: ' or 1 = 1–-

Password

If no special treatment is done, then the illegal user is logged in directly.

When you enter the user name and password above, the SQL on the server becomes:

sql = "Select user,pwd from user where user= ' or 1 = 1–-' and pwd= '%s '"

Because the condition is followed by username= "or 1=1 username equals" or 1=1 then this condition will be successful; then add two-, which means that the comment, which will comment on the following statements, so that they do not work, so that the statement will always be executed correctly, the user easily fooled the system, to obtain legal status.

Workaround:

1. Use parameterized statements provided by PYMYSQL to prevent injection

#!/usr/bin/env python#-*-coding:utf-8-*-ImportPymysql Conn= Pymysql.connect (host='127.0.0.1', port=3306, user='Root', passwd="', db='User') Cursor=conn.cursor () Username=input () password=input ()#Executing parameterized queriesRow_count=cursor.execute ("Select user,pwd from user where user= '%s ' and pwd= '%s '", (Username,password))#The execute () function itself has the parameter bits that accept the SQL statement variables, as long as it is used correctly (it is straightforward to use a "comma" instead of a "percent sign") to correctly escape the incoming value, thus avoiding the occurrence of SQL injection. #internal execution of parameterized generated SQL statements, special characters are added \ Escaped, to avoid injection statement generation. #sql=cursor.mogrify ("Select user,pwd from user where user= '%s ' and pwd= '%s '", (Username,password))#print (SQL)Row_1=Cursor.fetchone ()Print(row_count,row_1) conn.commit () Cursor.close () conn.close ()

View Code

NOTE: When executing SQL statements, Excute must use a parameterized approach, or SQL injection vulnerabilities will inevitably occur.

2. dynamically Execute SQL anti-injection using stored MySQL stored process

Use MySQL stored procedures to automatically provide anti-injection, dynamic incoming SQL to the stored procedure execution statement.

 delimiter \drop PROCEDURE IF EXISTS proc_sql \create PROCEDURE proc_sql ( 
   
    in  
     Nid1 INT,  
    in  
     Nid2 INT,  
    in  callsql VARCHAR (255
     = Nid1;  Set @nid2  = Nid2;    Set @callsql  = Callsql; PREPARE Myprod from @callsql;  --PREPARE prod from   select * from TB2 where nid> ;? and nid<?     -- 
   

View Code

Set @nid1 =12; set @nid2=15'select * from User where nid>? and nid<? ' ; Call Proc_sql (@nid1, @nid2, @callsql)

Called in Pymsql

#!/usr/bin/env python#-*-coding:utf-8-*-ImportPymysql Conn= Pymysql.connect (host='127.0.0.1', port=3306, user='Root', passwd="', db='User') Cursor=conn.cursor () MySQL="SELECT * from User where nid>? and nid<?"Cursor.callproc ('Proc_sql', args= (11, 15, MySQL)) Rows=Cursor.fetchall ()Print(Rows#( (U1, ' U1pass ', 11111), (+, ' U2 ', ' U2pass ', 22222), (+, ' U3 ', ' U3pass ', 11113) )Conn.commit () cursor.close () Conn.close ( )

View Code

Pymysql Preventing SQL injection