python使用mysql，防止SQL注入

python使用mysql，防止SQL注入

import pymysql
# %s sql注入
from pymysql import escape_string


def read_data0():
    # name = "张三' or 1=1 #"
    gender = "woman' or 1=1 #"
    sql = """ select * from my_students where  ms_gender ='%s' """ % gender
    try:
        conn = pymysql.Connect(host="127.0.0.1", port=3306, user="root", password="admin123", db="my_test")
        cursor = conn.cursor()
        cursor.execute(sql)
        data = cursor.fetchall()
        print("data0", data)
        cursor.close()
        conn.close()
    except Exception as e:
        print(e.__str__())


# %s 防止sql注入
# 把直接拼接的条件变量放入集合再把集合带入执行SQL的方法，就可以避免被注入的风险
def read_data1():
    # name = "张三"
    # gender = "woman "
    gender = "woman 'or 1=1 #"
    params = []
    params.append(gender)
    sql = """ select * from my_students where  ms_gender =%s"""
    try:
        conn = pymysql.Connect(host="127.0.0.1", port=3306, user="root", password="admin123", db="my_test")
        cursor = conn.cursor()
        cursor.execute(sql, params)
        data = cursor.fetchall()
        print("data1", data)
        cursor.close()
        conn.close()
    except Exception as e:
        print(e.__str__())


# .format sql注入
def read_data2():
    # name = "张三' or 1=1 #"
    gender = "woman' or 1=1 #"
    sql = """ select * from my_students where  ms_gender ='{}' """
    try:
        conn = pymysql.Connect(host="127.0.0.1", port=3306, user="root", password="admin123", db="my_test")
        cursor = conn.cursor()
        cursor.execute(sql.format(gender))
        data = cursor.fetchall()
        print("data2", data)
        cursor.close()
        conn.close()
    except Exception as e:
        print(e.__str__())


# 防止sql注入
# 不要在SQL中拼接参数,字符转义只会针对参数args
# orm框架都使用pymysql/MySQL-python等库,并且都使用execute(query,args)调用,将sql和参数分开传

def read_data3():
    name = "张三' or 1=1 #"
    # name = "张三"
    gender = "man"
    params = []
    params.append(name)
    params.append(gender)
    # gender = "woman ' or 1=1 #"
    sql = """ select * from my_students where  ms_name =%s and ms_gender = %s"""
    print(sql)
    try:
        conn = pymysql.Connect(host="127.0.0.1", port=3306, user="root", password="admin123", db="my_test")
        cursor = conn.cursor()
        cursor.execute(sql, params)
        data = cursor.fetchall()
        print("data3", data)
        cursor.close()
        conn.close()
    except Exception as e:
        print(e.__str__())

read_data0()
read_data1()
read_data2()
read_data3()

运行结果：
data0 ((1, ‘h202201’, ‘man’, ‘张三’, ‘01班’, 1, datetime.datetime(2022, 6, 13, 8, 59, 55), datetime.datetime(2022, 4, 12, 8, 59, 55, 668494)), (2, ‘h202202’, ‘woman’, ‘李四’, ‘01班’, 1, datetime.datetime(2022, 6, 13, 9, 0, 12), datetime.datetime(2022, 4, 12, 9, 0, 12, 917042)), (3, ‘h202203’, ‘man’, ‘王五’, ‘01班’, 1, datetime.datetime(2022, 6, 14, 9, 0, 33), datetime.datetime(2022, 4, 12, 9, 0, 33, 384853)))
data1 ()
data2 ((1, ‘h202201’, ‘man’, ‘张三’, ‘01班’, 1, datetime.datetime(2022, 6, 13, 8, 59, 55), datetime.datetime(2022, 4, 12, 8, 59, 55, 668494)), (2, ‘h202202’, ‘woman’, ‘李四’, ‘01班’, 1, datetime.datetime(2022, 6, 13, 9, 0, 12), datetime.datetime(2022, 4, 12, 9, 0, 12, 917042)), (3, ‘h202203’, ‘man’, ‘王五’, ‘01班’, 1, datetime.datetime(2022, 6, 14, 9, 0, 33), datetime.datetime(2022, 4, 12, 9, 0, 33, 384853)))
select * from my_students where ms_name =%s and ms_gender = %s
data3 ()

Process finished with exit code 0