SQL注入和防止注入

1.SQL注入：

用户提交带有恶意的数据与SQL语句进行字符串方式拼接，从而影响了SQL语句的语义，最终产生数据泄露的现象

#1.导包
import pymysql

if __name__ == '__main__':
    #2.创建连接对象
    conn = pymysql.connect(
        host = "localhost",
        port=3306,
        user="root",
        password = "mysql",
        database = "python11",
        charset = "utf8")
    #3.获取游标，目的是执行sql语句
    cursor = conn.cursor()
    #4.执行sql语句
    sql = "select * from students where name ='%s';" % "余温' or 1 = 1 or'" 
    print(sql)
    cursor.execute(sql)
    # 获取一条数据
    # row = cursor.fetchone()
    # print(row)
    # 获取所有数据
    alluser  = cursor.fetchall()
    print(alluser)
    for row in alluser:
        print(row)
    #5.关闭游标
    cursor.close()
    #6.关闭连接
    conn.close()

查询结果：

print(sql)

select * from students where name ='余温' or 1 = 1 or'';

2.防止SQL注入：

SQL语句参数化

SQL语言中的参数使用%s来占位，如果是多个参数就用多个%s

sql = "select * from students where name =%s;"

#1.导包
import pymysql

if __name__ == '__main__':
    #2.创建连接对象
    conn = pymysql.connect(
        host = "localhost",
        port=3306,
        user="root",
        password = "mysql",
        database = "python11",
        charset = "utf8")
    #3.获取游标，目的是执行sql语句
    cursor = conn.cursor()
    #4.执行sql语句
    # sql = "select * from students where name ='%s';" % "余温' or 1 = 1 or'" 
    # print(sql)
    # cursor.execute(sql)
    sql = "select * from students where name =%s;" 
    print(sql)
    # cursor.execute(sql,("余温' or 1 = 1 or'",))
    cursor.execute(sql,("余温",))
    # 获取一条数据
    # row = cursor.fetchone()
    # print(row)
    # 获取所有数据
    alluser  = cursor.fetchall()
    print(alluser)
    for row in alluser:
        print(row)
    #5.关闭游标
    cursor.close()
    #6.关闭连接
    conn.close()

执行结果：

cursor.execute(sql,("余温' or 1 = 1 or'",))

cursor.execute(sql,("余温",))