day38 python操作mysql ,防注入问题,

青旅半醒 2023-08-17 17:48 65阅读 0赞

## 用python操作MySql ##

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

import pymysql
    
    conn = pymysql.connect(
        host = '127.0.0.1',
        port = 3306,
        user = 'root',
        password = '123',
        database = 'db4',
        charset = 'utf8',
    )
    cursor =conn.cursor(pymysql.cursors.DictCursor)
    # 产生一个游标对象 以字典的形式返回结果 键是表的字段 值是字段对应的信息
    sql = 'select * from emp'
    # 要执行的sql 语句
    cursor.execute(sql)
    # 提交信息
    print(cursor.fetchone())
    # 只获取一条数据
    print(cursor.fetchone())
    print(cursor.fetchone())
    # 只获取一条数据
    # print(cursor.fetchall())
    # 获取所有的数据  返回的结果是一个列表
    # print(cursor.fetchmany(6))
    # 获取多条数据  返回的结果是一个列表
    
    cursor.scroll(5,'absolute')
    # 控制光标移动   absolute相对于在起始位置 往后移动
    # cursor.scroll(5,'relative')
    # relative相对于从当前位置往后移动几位
    print(cursor.fetchall())

## sql注入问题 ##

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

import pymysql
    
    conn = pymysql.connect(
        host = 'localhost',
        user = 'root',
        password = '123',
        database = 'day58',
        charset = 'utf8',
        # autocommit = True
        # 这个参数配置完成后  增删改操作都不需要在手动加conn.commit了
    
    )
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    
    
    sql = 'select * from students'
    cursor.execute(sql)
    # print(cursor.fetchone())
    
    
    """
    增删改操作 都必须加一句
    conn.commit()操作
    """
    conn.commit()
    name = input('name>>>:').strip()
    class_id = input('class_id>>>:').strip()
    
    sql = "select * from students where name = '%s' and class_id  = '%s'"%(name,class_id)
    print(sql)
    res = cursor.execute(sql)
    # execute 能够自动识别sql语句中的%s 帮你做替换
    
    if res:
        print(cursor.fetchone())
    else:
        print('用户或班级错误')

![1659204-20190822195338475-1883277401.png][]

![1659204-20190822195353343-698176891.png][]

![1659204-20190822195402525-1997213105.png][]

## 解决问题 ##

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

import pymysql
    
    
    conn = pymysql.connect(
        host = 'localhost',
        user = 'root',
        password = '123',
        database = 'day58',
        charset = 'utf8',
        autocommit = True
    #   # 这个参数配置完成后  增删改操作都不需要在手动加conn.commit了
    )
    course = conn.cursor(pymysql.cursors.DictCursor)
    
    sql = 'select * from students'
    # 提交
    course.execute(sql)
    
    
    
    name = input('name')
    class_id = input('number')
    sql = 'select * from students where name = %s and class_id = %s'
    print(sql)
    res = course.execute(sql,(name,class_id))
    # 能够帮你自动过滤特殊符号 避免sql注入的问题
    if res:
        print(course.fetchone())
    else:
        print('用户或班级不对')
    """
    sql注入 就是利用注释等具有特殊意义的符号 来完成一些骚操作
    
    后续写sql语句  不要手动拼接关键性的数据
    而是让excute帮你去做拼接
    
    """

![1659204-20190822195442745-1733331469.png][]

![1659204-20190822195450447-555322844.png][]

## 增删改 ##

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

# 增
    sql = "insert into user(username,password) values(%s,%s)"
    rows = cursor.excute(sql,('jason','123'))
    
    # 修改
    sql = "update user set username='jasonDSB' where id=1"
    rows = cursor.excute(sql)
    
    """
    增和改单单执行excute并不会真正影响到数据，需要再执行conn.commit()才可以完成真正的增改
    """
    
    # 一次插入多行记录
    res = cursor,excutemany(sql,[(),(),()]

转载于:https://www.cnblogs.com/komorebi/p/11396459.html

[ContractedBlock.gif]: https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif
[ExpandedBlockStart.gif]: /images/20230808/6692c519a0e34364b4c19aaa5f4103d0.png
[1659204-20190822195338475-1883277401.png]: /images/20230808/136ac36ad1f54d74b7009943f75d230a.png
[1659204-20190822195353343-698176891.png]: /images/20230808/7854b14f3f98416e8487d7e81f7060a4.png
[1659204-20190822195402525-1997213105.png]: /images/20230808/af66e89cea514735914102139d5482a4.png
[1659204-20190822195442745-1733331469.png]: /images/20230808/6adc1d635f2445cfa34af006187eb9f1.png
[1659204-20190822195450447-555322844.png]: /images/20230808/d830029e153b4bb78c1f4c396e99b23a.png