1 pymysql Module installation

pymysql install ：

pip3 install pymysql -i https://pypi.douban.com/simple

Installation successful ！
Check if the installation is successful , That is to say python In the environment import pymysql, Install successfully without error ！

2 Python Implement user login

Code implementation ：

import pymysql

username = input(" Enter your user name ：")
password = input(" Please enter your password ：")

conn = pymysql.connect(host="localhost", user='root', password='', database="pymysql")  #  Connect to database 
cursor = conn.cursor()    #  Open cursor 
sql = "select * from userinfo where username=%s and password=%s" 
cursor.execute(sql, [username, password])  #  The return value of this sentence is the number of rows of the operation 
result = cursor.fetchone()  # fetchone It means only take the first data 
cursor.close() #  Close cursor 
conn.close()   #  Close the database 

if result:
    print(" Login successful ")
else:
    print(" Incorrect username and password ")

If it contains Chinese , Should be in pymysql.connect Add charset=‘utf8’
as follows ：

3 SQL Inject

One 、SQL Introduction to injection

SQL Injection is one of the most common network attacks , It's not using the operating system BUG To achieve an attack , It's about the negligence of the programmer in programming , adopt SQL sentence , Realize no account login , Even tampering with the database .

Two 、SQL The general idea of injection attack

1. Look for SQL Injection location
2. Judge the server type and background database type
3. According to the characteristics of servers and databases that are not available SQL Injection attack

3、 ... and 、SQL Inject attack instance

import pymysql

username = input(" Enter your user name ：")
password = input(" Please enter your password ：")

conn = pymysql.connect(host="localhost", user='root', password='', database="pymysql")
cursor = conn.cursor()
sql = "select * from userinfo where username='%s' and password='%s'" % (username, password)
cursor.execute(sql)
result = cursor.fetchone()
cursor.close()
conn.close()

if result:
    print(" Login successful ")
else:
    print(" Incorrect username and password ")

When the user name is entered ：'or 1=1-- ( There is a space in front ) It will produce SQL Inject
This sentence SQL The sentence becomes :
select * from userinfo where username=''or 1=1 -- ' and password='%s'
explain ：or In the back 1=1 It's permanent then -- Express sql Comments in statements , Therefore, login will succeed

Four 、 resolvent

Use pymysql The parameterized statements provided prevent injection

import pymysql

username = input(" Enter your user name ：")
password = input(" Please enter your password ：")

conn = pymysql.connect(host="localhost", user='root', password='', database="pymysql")
cursor = conn.cursor()
sql = "select * from userinfo where username='%s' and password='%s'"
cursor.execute(sql, [username, password])        #  Write parameters to execute In the function 
# cursor.execute(sql,user,pwd) #  Write directly later 
# cursor.execute(sql,{'u':username,'p':password}) # Using dictionaries 
result = cursor.fetchone()
cursor.close()
conn.close()

if result:
    print(" Login successful ")
else:
    print(" Incorrect username and password ")

excute perform SQL At the time of statement , Must be parameterized , Otherwise, there must be SQL Inject holes .

4 pymysql Other functions

Addition, deletion and modification belong to one category , Check belongs to class I

#  Insert a single piece of data 
import pymysql

username = 'admin'
password = '123'

conn = pymysql.connect(host="localhost", user='root', password='', database="pymysql")  #  Connect to database 
cursor = conn.cursor()    #  Open cursor 
sql = "insert into userinfo(username,password) values(%s, %s)"
r = cursor.execute(sql, [username, password])  #  insert data ,execute The execution will have a return value . That is, the number of rows affected （ This can make r Equal to its ）
conn.commit() #  If you insert , Need to submit . Otherwise, the insertion will not succeed 
cursor.close() #  Close cursor 
conn.close()   #  Close the database 

#  Insert multiple data 
import pymysql

conn = pymysql.connect(host="localhost", user='root', password='', database="pymysql")  #  Connect to database 
cursor = conn.cursor()    #  Open cursor 
sql = "insert into userinfo(username,password) values(%s, %s)"
r = cursor.executemany(sql, [('laoyao','123'), ('egon','113')])  #  Insert multiple data ,executemany The execution will have a return value . That is, the number of rows affected （ This can make r Equal to its ）
conn.commit() #  If you insert , Need to submit . Otherwise, the insertion will not succeed 
cursor.close() #  Close cursor 
conn.close()   #  Close the database 

#  Check data 
# conn = pymysql.connect(host="localhost",user='root',password='',database="pymysql")
# cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # this sentence , The result of the operation will be returned to the dictionary , Not tuples 
# sql = "select * from userinfo"
# cursor.execute(sql)

# cursor.scroll(1,mode='relative') #  The cursor moves relative to the current position 
# cursor.scroll(2,mode='absolute') #  The cursor moves relative to the absolute position 
# result = cursor.fetchone() #  Get a row of data 
# print(result)
# result = cursor.fetchone()
# print(result)
# result = cursor.fetchone()
# print(result)
# result = cursor.fetchall() #  Get all the data 
# print(result)


# result = cursor.fetchmany(4) #  Get multiple rows of data 
# print(result)
# cursor.close()
# conn.close()

#  Autoincrement of newly inserted data ID: cursor.lastrowid
# import pymysql
#
# conn = pymysql.connect(host="localhost",user='root',password='123',database="pymsql")
# cursor = conn.cursor()
# sql = "insert into userinfo(username,password) values('asdfasdf','123123')"
# cursor.execute(sql)
# conn.commit()
# print(cursor.lastrowid) #  Get the self increase of new data ID, It is also the row number of the primary key of the previous operation 
# cursor.close()
# conn.close()

The tuple returned ：
(1, ‘alex’, ‘123’)
Return dictionary ：
{‘uid’: 1, ‘username’: ‘alex’, ‘password’: ‘123’}

Other reference ：
https://www.cnblogs.com/wupeiqi/articles/5713330.html