Pymysql enables MySQL to interact with Python

Common MySQL operations required modules:

1 pip3  Install Pymysql

Query (Fetchone,fetchmany,fetchall):

ImportPymysql#Connectioncon = pymysql.connect (host='localhost', user='Root', passwd='Admin', db='DB1', charset='UTF8')#CursorsCur =con.cursor () SQL='SELECT * from USER'cur.execute (SQL)#get the first piece of dataRow_one=Cur.fetchone ()#gets any number of data, starting with the first data by defaultRow_many = Cur.fetchmany (3)#Get all dataRow_all =Cur.fetchall () cur.close () Con.close ( )Print(Row_one)Print(Row_many)Print(Row_all)

Insert, UPDATE, modify (last need to submit)

1 ImportPymysql2Conn=pymysql.connect (host='localhost', user='Root', password='Admin', database='DB1')3Cursor=conn.cursor ()4Sql='INSERT INTO User (Name,password) VALUES ("xxx", "123");'5Rows=cursor.execute (SQL)6 Print(CURSOR.LASTROWID)#View the latest record ID after inserting a statement7 #The Pymysql.connect class opens the transaction by default, so a transaction is required to be committed when modifying, updating, deleting, and inserting the table8 Conn.commit ()9 cursor.close ()TenConn.close ()

SQL injection noun Explanation:

SQL injection is a dynamic data check between Python and MySQL, and the user intentionally enters an illegal field, bypassing the behavior of the data check.

1 ImportPymysql2User=input ('User name:'). Strip ()3Pwd=input ('Password:'). Strip ()4 5 #links6Conn=pymysql.connect (host='localhost', user='Root', password='Admin', database='DB1', charset='UTF8')7 #Cursors8Cursor=conn.cursor ()#The result set returned by execution is displayed by default in tuples9 #Execute SQL statementTenSql='SELECT * from user where name= '%s ' and password= '%s ''% (USER,PWD)#Note%s needs to be quoted One Print(SQL) ARes=cursor.execute (SQL)#executes the SQL statement, returning the number of records for which the SQL query succeeded - cursor.close () - conn.close () the  - ifRes: -     Print('Login Successful') - Else: +     Print('Logon Failure')

The input process under normal circumstances:

Intentional bypass of illegal input of validation

When you know the user name:

Although know the password, but entered the password does not match but successfully landed.

When the user name and password are not known:

Although do not know the user name and password, but successfully landed.

The central idea of SQL injection is to bypass validation by artificially entering special strings in SQL statements. "--" in MySQL as the comment character, this method can block some code, thus bypassing the validation.

Workaround:

Use MySQL's built-in method to verify the legitimacy of the input string and improve security.

1 ImportPymysql2User=input ('User name:'). Strip ()3Pwd=input ('Password:'). Strip ()4 5 #links6Conn=pymysql.connect (host='localhost', user='Root', password='Admin', database='DB1', charset='UTF8')7 #Cursors8Cursor=conn.cursor ()#The result set returned by execution is displayed by default in tuples9 #Execute SQL statementTenSql='SELECT * from user where name=%s and password=%s'#注意%s is not quoted One Print(SQL) ARes=cursor.execute (Sql,[user,pwd])#executes the SQL statement, returning the number of records for which the SQL query succeeded - cursor.close () - conn.close () the ifRes: -     Print('Login Successful') - Else: -     Print('Logon Failure')

Checksum verification:

Successfully resolve SQL injection issues.

Pymysql enables MySQL to interact with Python