1. 简单讲一些sql注入的内容
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))
2. 1）调用函数参数前加*或**
def test(a, b):
print(a, b)
li = [1, 2]
d = {'a': 'qxy', 'b': 'mpp'}
test(*li) # 这种写法是将list中的元素作为参数进行传参
test(**d) # 这种写法将字典中key的value值作为参数进行传参
2）可变参数
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)
3. 批量执行sql
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()
4. 1）从redis中读取session
@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)
2） 从cookie中读取session
@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)
3） 将cookie set到浏览器中
@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格式
5. 网络编程：主要靠requests模块实现
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()
6. 1）读取Excel，用xlrd模块实现
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']
2）写入Excel，用xlwt模块实现
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')
3）修改Excel，用xlutils模块实现
# 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, '小黑')