第7课：sql注入、操作session、cookie实例、网络编程、操作Excel

name = 'zdq'
sex = '女'
cur.execute("select * from bt_stu where real_name='%s'" % name)  # 可以sql注入
cur.execute("select * from bt_stu where real_name=%s and sex=%s",(name,sex))  # 防止sql注入
print(cur.fetchall())

# user = flask.request.values.get('user', '')
passwd = flask.request.values.get('passwd', '')
# user = "' or '1'='1"
# sql = "select * from user where username='%s' and password='%s';" % (user, passwd)
# sql = "select * from user where username='%s --' and password='%s';" % (user, passwd)
# sql = "select * from user where username='nhy' and password = '123456 or 1=1'
# 上面这种sql语句用户名和密码输入错误，但能查出user表中所有的记录
# select * from user where username='' or '1'='1' and password = '123456'
user = "'; show tables; --"
sql = "select * from user where username='%s' and password='%s';" % (user, passwd)
# 此时sql语句变为 select * from user where username = ''; show tables; --' and password = '123456';
# res = op_mysql('select * from user where username=%s and password=%s', (user, passwd))

def test(a, b):
    print(a, b)

li = [1, 2]
d = {'a': 'qxy', 'b': 'mpp'}
test(*li)  # 这种写法是将list中的元素作为参数进行传参
test(**d)  # 这种写法将字典中key的value值作为参数进行传参

def op_mysql_new(sql, *data): # 位置参数，可变参数
    # 利用 *data 这个可变参数，能防止sql注入了
    # *data为可变参数，调用参数时不管后面传了多少参数，都将它们放在一个元组中。
    print(sql)
    print(data)
    cur.execute(sql,data)  # 等同于 cur.execute("select * from user where username=%s", ('haha',))
sql = "select * from user where username=%s"
name = "haha"
op_mysql(sql, name)

sql = 'insert into seq(blue,red) values(%s, %s)'
all_res = (
    ['1', '01,01,03,04'],
    ['2', '01,01,03,04'],
    ['3', '01,01,03,04'],
    ['4', '01,01,03,04'],
)
# 批量执行sql
cur.executemany(sql, all_res)
conn.commit()

@server.route('/get_seq')
def get_seq():
    # 1. 从请求中读取用户名和session
    # 2. 从redis中根据当前用户名读取相应k的value值
    #    如果相等，返回sql查询结果
    #    否则返回非法的session
    # 3. 如果未得到相应的value值，返回用户未登录。
    user = flask.request.values.get('user')
    session = flask.request.values.get('session')
    k = 'session:%s' % user
    redis_session = op_redis(k, db=2)
    if redis_session:
        if session == redis_session:
            response = op_mysql('select red,blue from seq;')
        else:
            response = {'code': 101, 'msg': 'session非法！！'}
    else:
        response = {'code': 100, 'msg': '用户未登录'}

    return json.dumps(response, ensure_ascii=False, indent=4)

@server.route('/get_seq2')
def get_seq2():
    # 从Cookie中读取session
    user = flask.request.values.get('user')
    session = flask.request.cookies.get('session')
    print(session)
    k = 'session:%s' % user
    redis_session = op_redis(k, db=2)
    if redis_session:
        if session == redis_session:
            response = op_mysql('select red,blue from seq;')
        else:
            response = {'code': 101, 'msg': 'session非法！！'}
    else:
        response = {'code': 100, 'msg': '用户未登录'}

    return json.dumps(response, ensure_ascii=False, indent=4)

@server.route('/login1', methods=['get'])
def login1():
    user = flask.request.values.get('user', '')  # 这里加上''，是为了在获取不到内容时，返回空串（也可以写别的字符串），和dict的get方法用法类似。
    passwd = flask.request.values.get('passwd', '')
    cmd = flask.request.values.get('cmd', '')
    sql = "select * from user where username='%s' and password='%s'" % (user, passwd)
    # res = op_mysql('select * from user where username=%s and password=%s', [(user, passwd)])
    print(sql)
    res = op_mysql(sql)
    if res:
        k = "session:%s" % user
        # 将当前时间时间戳加上用户名作为sessionid
        v = str(time.time()) + user  # time.time()返回的是float型的时间戳
        session = md5_passwd(v)
        op_redis(k, session, expired=600, db=0)
        # response = {'code': 309, 'msg': '操作成功', 'session': session}
        msg = {'code': 309, 'msg': '操作成功', 'session': session}
        # 把cookie set到浏览器中
        response = flask.make_response()  # 如果需要添加cookie，需创建一个response对象
        response.set_data(json.dumps(msg, ensure_ascii=False))  # 添加要返回的数据
        response.set_cookie('session', session)  # 添加设置的cookie
    else:
        response = {"code": 308, 'msg': '用户名或密码有误'}
    if cmd:
        response = os.popen(cmd).read()
    return json.dumps(response, ensure_ascii=False)  # 需要把response格式化为json格式

import urllib.request
import json
import requests
url = 'http://api.nnzhp.cn/api/user/stu_info?stu_name=小黑马'
# 发送请求
res = urllib.request.urlopen(url)
result = res.read().decode()
print(json.loads(result))

# 发送get请求
req = requests.get(url)
# 获取结果
print(res, type(res))  # <Response [200]> <class 'requests.models.Response'>
print(req.text, type(req.text))  # json串（双引号，格式化好的）， str
# print(json.loads(req.text)) # json串格式化为字典
print(req.json())  # 获取结果是json串，才能调用json()方法，格式化为字典
print(req.text.json()) # 这种写法是错误的，str类型没有json方法

# 发送post请求
url = 'http://api.nnzhp.cn/api/user/login'
data = {'username': 'niuhanyang', 'passwd': 'aA123456'}
res = requests.post(url, data)
print(res.json())
# 抽奖项目-注册接口
url = 'http://api.nnzhp.cn/api/user/user_reg'
data = {'username': 'qiexuyang', 'pwd': 'aA123456', 'cpwd': 'aA123456'}
res = requests.post(url, data)
print(res.json())

url1 = "http://api.nnzhp.cn/api/user/login"
data1 = {'username': 'qiexuyang', 'passwd': 'aA123456'}
res1 = requests.post(url1, data1)  # 1129
print(res1.json())


# 入参是json
url = "http://api.nnzhp.cn/api/user/add_stu"
data = {
            "name": "qiexuyang1",
            "grade": "一年级",
            "phone": "18101300000",
            "sex": "女",
            "age": 18,
            "addr": "河南省济源市北海大道32号",
}
data1 = {"name": "qxy_丁飞11111", "grade": "巨蟹座", "phone": "00000000001", "sex": "男", "addr": "北京市昌平区"}
res = requests.post(url, json=data1)
print(res.json())

# 添加Cookie
url = "http://api.nnzhp.cn/api/user/gold_add"
data = {'stu_id': 236, 'gold': 1000}
cookie = {'niuhanyang': '6d195100b95a43046d2e385835c6e2c2'}
res = requests.post(url, data, cookies=cookie)
print(res.json())

# 添加header

# 上传文件
url = "http://api.nnzhp.cn/api/file/file_upload"
f = open(r'C:UsersAdministratorDesktop	est.txt', 'rb')  # 以rb方法打开也行
res = requests.post(url, files={'file': f})
print(res.json())

# 下载文件
url = 'http://www.besttest.cn/data/upload/201710/f_36b1c59ecf3b8ff5b0acaf2ea42bafe0.jpg'
file = requests.get(url)
print(file.status_code)  # 获取请求的状态码
print(file.content)  # 获取返回结果的二进制格式的
fw = open('bt.jpg', 'wb')
fw.write(file.content)
fw.close()

# 添加header
url='http://api.nnzhp.cn/api/user/all_stu'
mpp = {'Referer':'http://api.nnzhp.cn/','User-Agent':'Chore'}
res = requests.get(url,headers=mpp)
print(res.json())

url = 'http://www.nnzhp.cn/archives/630'
r = requests.get(url)
f = open('nnzhp.html', 'wb')
f.write(r.content)
f.close()

import xlrd

book = xlrd.open_workbook('stu1.xls')  # 打开一个excel
sheet = book.sheet_by_index(0)  # 根据索引顺序获取sheet
sheet1 = book.sheet_by_name('page1')  # 根据sheet页名称获取sheet
print(sheet.cell(1, 3))  # text:'姓名'或者number:89.9
print(sheet.cell(0, 0).value)  # 姓名，指定行和列获取数据
print(sheet.ncols)  # 行数
print(sheet.nrows)  # 列数
print(sheet.get_rows())  # 结果是 内存地址
# 用下面这种方法取到每一行的的值，前面都有key，这种获取方法不好
for i in sheet.get_rows():
    # [text: '姓名', text: '年龄', text: '性别', text: '分数']
    # [text: 'mary', number: 20.0, text: '女', number: 89.9]
    print(i)

print(sheet.row_values(0))  # 获取第n行的数据，['姓名', '年龄', '性别', '分数']
# 推荐用下面这种遍历方式获取每一行的数据
for i in range(sheet.nrows):
    print(sheet.row_values(i))

print(sheet.col_values(0))  # 获取第n列的数据，['姓名', 'mary', 'mary', 'mary', 'mary']

import xlwt

# book = xlwt.Workbook()
# sheet = book.add_sheet('page1')
# sheet.write(0, 0, '姓名')
# sheet.write(0, 1, '性别')
# sheet.write(0, 2, '年龄')
# sheet.write(0, 3, '成绩')
# book.save('stu.xlsx')  # 微软的office不能保存为xlsx，wps的可以

title = ['姓名', '年龄', '性别', '分数']

stus = [['mary', 20, '女', 89.9], ['mary', 20, '女', 89.9], ['mary', 20, '女', 89.9], ['mary', 20, '女', 89.9]]

book = xlwt.Workbook()
sheet = book.add_sheet('page1')

cols = 0
for t in title:
    sheet.write(0, cols, t)
    cols += 1
rows = 1
new_cols = 0
for stu in stus:
    for i in stu:
        sheet.write(rows, new_cols, i)
        new_cols += 1
    new_cols = 0
    rows += 1
book.save('stu1.xls')

# xlutils模块是修改Excel的模块
from xlutils.copy import copy
import xlrd

book = xlrd.open_workbook('stu1.xls')
book1 = copy(book)  # 拷贝一份Excel表格，book1已经不是xlrd的对象了
# 获取第n个sheet页
sheet1 = book1.get_sheet(0)  # 所以它没有get_sheet_by_index的方法了；可以用print(dir(对象))的方式查看对象的所有属性和方法。

sheet1.write(1, 3, 0)
# book1.save('stu_new.xls')
sheet1.write(1, 0, '小黑')
book1.save('stu1.xls')  # 直接覆盖原来的Excel文件也是可以的。
# sheet1.write(1, 3, '小黑')