python connects mysql clients

import pymysql # Import module
conn = pymysql.connect(
    host='127.0.0.1', # Host module
    port=3306, # Port number
    user='root',# User name
    password='123', # Password
    database='db', # Libraries that need to be connected
    charset='utf8' # Specify code utf8
)
cursor = conn.cursor() # Get cursor 
# cursor = conn.cursor(pymysql.cursors.DictCursor) # The query results obtained are more standardized and easy to distinguish.
sql = "select * from dep;"
ret = cursor.execute(sql) # Number of rows affected by ret
# res = cursor.executemany(sql,[(a,b),(a1,b1),(a2,b2)]) # When inserting multiline data 
print(cursor.fetchall())  # Take out all
print(cursor.fetchmany(3))# Take out more than one strip.
print(cursor.fetchone())  # Take out a single item.

cursor.scroll(3,'absolute') # Absolute movement, moving down three strips according to the starting position of the data
cursor.scroll(1,'relative') # Through the above data, cursor position, I now relative move a record, then next time, take out the third, I relative to the previous, move down one.
conn.commit()  # When adding, deleting and modifying operations, submission is required.

cursor.close() # Close cursor
conn.close() # Close connection

MySQL Injection Problem

Previously, we used to authenticate user name and password by saving them in a file, then by reading the contents of the file, we matched the user name and password sent by the client. Now we have learned the database, we can save these user data to the database, and then use the data in the database to carry out user name and password to the client. Password authentication.

Create a user information table, userinfo, which contains two fields, username and password, and then write two records in it

sql injection:Solution
    cursor.excute(sql,[Parameter 1,Parameter 2...])
# Use data to authenticate user names and passwords
import pymysql

conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    database='db',
    charset='utf8'
)

cursor = conn.cursor(pymysql.cursors.DictCursor)
usr = input('Please enter a user name:').strip()
pwd = input('Please input a password:').strip()
# When the user name is known, the decryption code is (feng'- dfadasdad)
# When the user name password is unknown, the decryption code is (fsdf'or 1 = 1 - fdsfsdfs)

sql = "select * from userinfo where username='%s' and password='%s';"%(usr,pwd)

res = cursor.execute(sql) # res We say the number of rows we get. If the number of rows is not zero, it means that the username and password entered by the user exists. If the number of rows is zero, you can imagine if it exists.

print(res) # If the user name and password are entered incorrectly, the result is 0, and if correct, the result is 1.
if res:
    print('Landing successfully')
else:
    print('Error username and password!')

# Through the above verification method, it is much more convenient than using files to save user name and password information for verification operation.

Solving Injection Problem

sql = "select * from userinfo where username = %s and password = %s;"%(usr,pwd)
res = cursor.execute(sql,[usr,pwd]) # The pymysql module automatically deletes special characters entered