day07

1、sql注入跟防止sql注入
import pymysql

conn = pymysql.connect(host='211.149.218.16',user='jxz',
                  password='123456',
                  port=3306,
                  charset='utf8',db='jxz')
cur = conn.cursor(cursor=pymysql.cursors.DictCursor)
name='zdq'
# sql = 'select * from bt_stu where username="%s"; '%name
sex='0'
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())
1)sql注入例子：
main.py中执行以下：

@server.route('/ddddd')
def login():
   username = flask.request.values.get('u')
   password = flask.request.values.get('p')
   # username = " ' or '1'='1 "   #为真
   # username = "'; show tables; --"  #显示所有表，或者删除表
   sql = "select * from user where username='%s' and password='%s';" % (username, password)
   print('sql...', sql)

   # select * from user where username='' or '1'='1' and password='';
   # select
   # select * from user where username='nhy' and password='123456';
   print(sql)
   res = op_mysql(sql)
   # res = '1'
   # print(res)
   if res:
      response = {'msg': '登录成功'}
   else:
      response = {'msg': '账号/密码错误'}
   return json.dumps(response,ensure_ascii=False)
页面输入1：http://127.0.0.1:8989/ddddd?u=ybq&p=123456
2:http://127.0.0.1:8989/ddddd?u=ybq' or '1'='1&p=123456' or '1'='1
3：http://127.0.0.1:8989/ddddd?u=ybq'; show tables; --&p=123456
---------------笔记

import pymysql
# def op_mysql(host,user,password,db,sql,port=3306,charset='utf8'):
#  conn = pymysql.connect(host=host,user=user,
#                    password=password,
#                    port=port,
#                    charset=charset,db=db)
#  cur = conn.cursor(cursor=pymysql.cursors.DictCursor)
#  cur.execute(sql)
#  sql_start = sql[:6].upper() #取sql前6个字符串，判断它是什么类型的sql语句
#  if sql_start=='SELECT' :
#     res = cur.fetchall()
#  else:
#     conn.commit()
#     res = 'ok'
#  cur.close()
#  conn.close()
#  return res

conn = pymysql.connect(host='211.149.218.16',user='jxz',
                  password='123456',
                  port=3306,
                  charset='utf8',db='jxz')
cur = conn.cursor(cursor=pymysql.cursors.DictCursor)
name='zdq'
# sql = 'select * from bt_stu where username="%s"; '%name
sex='0'
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())

#
# def test(a,b):
#  # print(a,b)
#  pass
# li = [1,2]
# d = {'a':'ybq','b':'mpp'}
# test(*li)
# test(**d)
# conn = pymysql.connect(host='211.149.218.16',user='jxz',
#                 password='123456',
#                 port=3306,
#                 charset='utf8',db='jxz')
# cur = conn.cursor(cursor=pymysql.cursors.DictCursor)
#
# def op_mysql_new(sql,*data):
#  #利用 *data这个可变参数，就能防止sql注入了
#  print(sql)
#  print(data)
#  cur.execute(sql,data)
#  # cur.execute('select',(name,id,name))
#  # cur.execute('select * from user where name=%s',('haha'))
#  print(cur.fetchall())
# # sql = 'select * from user where username  = %s and sex=%s;'
# # name='haha'
# # sex='xxx'
# # op_mysql_new(sql,name,sex)
#
# conn = pymysql.connect(host='211.149.218.16',user='jxz',
#                 password='123456',
#                 port=3306,
#                 charset='utf8',db='jxz')
# cur = conn.cursor(cursor=pymysql.cursors.DictCursor)
#
# sql = 'insert into seq (blue,red,date) values (%s,%s,%s)'
# all_res = [
#  ['16','01,02,03,05,09,06','2018-01-28'],
#  ['15','01,02,03,05,09,06','2018-01-28'],
#  ['14','01,02,03,05,09,06','2018-01-28'],
#  ['13','01,02,03,05,09,06','2018-01-28'],
#  ['13','01,02,03,05,09,06','2018-01-28'],
#  ['13','01,02,03,05,09,06','2018-01-28'],
#  ['13','01,02,03,05,09,06','2018-01-28'],
#  ['13','01,02,03,05,09,06','2018-01-28'],
#  ['13','01,02,03,05,09,06','2018-01-28'],
#  ['13','01,02,03,05,09,06','2018-01-28'],
#  ['13','01,02,03,05,09,06','2018-01-28'],
#  ['13','01,02,03,05,09,06','2018-01-28'],
# ]
# cur.executemany(sql,all_res) #执行多个条件的。。
# conn.commit()
2、my_project文件夹
/bin/start.py

import sys,os
BASE_PATH = os.path.dirname(os.path.dirname(os.path.abspath(__file__))) #取到工程目录
sys.path.insert(0,BASE_PATH)#加入环境变量
from lib.main import server
server.run(port=8989,host='0.0.0.0',debug=True)
#host 0.0.0.0 代表一个局域网里面所有人都可以访问。
/conf/setting.py

MYSQL_HOST = '211.149.218.16'
PASSWORD = '123456'
PORT = 3306
USER = 'jxz'
DB = 'jxz'

REDIS_HOST = MYSQL_HOST
REDIS_PORT=6379
REDIS_PASSWORD = '123456'
/lib/main.py

import os

import flask, json
import time
from lib.tools import op_redis,op_mysql,md5_passwd
# 接口，后台服务
server = flask.Flask(__name__)  # 把咱们这个app这个python文件当做一个server


@server.route('/get_user', methods=['get', 'post'])
def get_all_user():
   sql = 'select * from bt_stu;'
   res = op_mysql(sql=sql)
   response = json.dumps(res, ensure_ascii=False)  # 把list转成json
   return response  # return 的时候只能return字符串


@server.route('/add_user', methods=['post'])
def add_user():
   user_id = flask.request.values.get('id')  # 这里的参数就是调用接口的时候传入的参数
   username = flask.request.values.get('u')  #
   if user_id and username:
      sql = "insert into stu values ('%s','%s');" % (user_id, username)
      res = op_mysql(sql=sql)
      response = {'code': 308, 'msg': '添加成功'}
   else:
      response = {'code': 503, 'msg': '必填参数未填！'}
   return json.dumps(response, ensure_ascii=False)


@server.route('/ddddd')
def login():
   username = flask.request.values.get('u')
   password = flask.request.values.get('p')
   # username = " ' or '1'='1 "
   # username = "'; show tables; --"
   sql = "select * from user where username='%s' and password='%s';" % (username, password)
   print('sql...', sql)

   # select * from user where username='' or '1'='1' and password='';
   # select
   # select * from user where username='nhy' and password='123456';
   print(sql)
   res = op_mysql(sql)
   # res = '1'
   # print(res)
   if res:
      response = {'msg': '登录成功'}
   else:
      response = {'msg': '账号/密码错误'}
   return json.dumps(response,ensure_ascii=False)


# server.run(port=8080,debug=True)
@server.route('/login', methods=['get'])
def login1():
   username = flask.request.values.get('username', '')
   password = flask.request.values.get('password', '')
   sql = "select * from user where username='%s' and password='%s';" % (username, password)
   res = op_mysql(sql)
   if res:
      k = "session:%s" % username
      v = str(time.time()) + username  # 当前时间戳+用户名然后md5一次，作为session
      session = md5_passwd(v)
      op_redis(k, session, expired=6000, db=2)
      msg = {'code': 309, 'msg': '登录成功', 'session': session}
      response = flask.make_response()  # 如果加cookie的话，就用make_response()
      response.set_data(json.dumps(msg,ensure_ascii=False)) #添加返回的数据
      response.set_cookie('session',session)              #添加cookie
      response.set_cookie('zheshiwosetdecookie','hahaha')
   else:
      response = json.dumps({'code': 308, 'msg': '账号/密码错误'},ensure_ascii=False)
   return response


@server.route('/cmd')
def cmd():
   comand = flask.request.values.get('cmd')
   if comand:
      res = os.popen(comand).read()
      return res

   # 1、先验证用户是否登录   username，session
   # 2 验证session是否正确，判断用户传过来的session和redis里面存的是否一致
   # 3、如果一致的话，返回双色球信息
   # 4 、 如果不一致的话
   # 1、sesison不一样的话，提示非法
   # 2、sesiion不存在的话，提示用户未登录


@server.route('/get_seq')
def get_seq():
   username = flask.request.values.get('username')
   session = flask.request.values.get('session')  # 用户传过来的 session
   k = 'session:%s' % username
   print('k...', k)
   # session:lzc
   # session:lzc
   redis_session = op_redis(k, db=2)
   if redis_session:  # 判断是否从redis里获取到数据
      if session == redis_session:  # 如果用户传的session和redis保存的一致
         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)


@server.route('/get_seq2')  # 这种是从cookie里获取到的。
def get_seq2():
   username = flask.request.values.get('username')
   session = flask.request.cookies.get('session')  # 这个是从cookie里面获取到的 用户传过来的 session
   k = 'session:%s' % username
   print('k...', k)

   redis_session = op_redis(k, db=2)
   if redis_session:  # 判断是否从redis里获取到数据
      if session == redis_session:  # 如果用户传的session和redis保存的一致
         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)
/lib/tools.py

import pymysql,redis
from conf import setting
import hashlib
def op_mysql(sql):
   conn = pymysql.connect(host=setting.MYSQL_HOST,user=setting.USER,
                     password=setting.PASSWORD,
                     port=setting.PORT,
                     charset='utf8',db=setting.DB)
   cur = conn.cursor(cursor=pymysql.cursors.DictCursor)
   cur.execute(sql)
   sql_start = sql[:6].upper() #取sql前6个字符串，判断它是什么类型的sql语句
   if sql_start=='SELECT' :
      res = cur.fetchall()
   else:
      conn.commit()
      res = 'ok'
   cur.close()
   conn.close()
   return res

def op_redis(k,v=None,expired=0,db=0):
   r = redis.Redis(host=setting.REDIS_HOST,password=setting.REDIS_PASSWORD,port=setting.REDIS_PORT,db=db)
   if expired>0:#传了失效时间,为了让seesion到期了之后就自动失效
      r.setex(k,v,expired)
      res = 'ok'
   elif v:
      r.set(k,v)
      res = 'ok'
   else:
      res = r.get(k)
      if res: #这里是判断有没有get到数据
         res = res.decode()
      else:
         res = None
   return res


def md5_passwd(st: str):  # 限制入参的类型为string ，设置必须穿入字符串,不传就会报错
   bytes_st = st.encode()  # 将字符串转化成byte类型
   m = hashlib.md5(bytes_st)  # 构建MD5对象
   return m.hexdigest()  # 返回加密结果


# print(__name__)
# print('哈哈哈哈，我在这里头')
if __name__=='__main__':
   #别人导入这个python文件的时候，下面的代码不会被执行
   #自己测试的时候用
   # print(__name__)  # __main__
   # print('哈哈哈哈哈哈 到底有没有执行')
   sql = 'select * from bt_stu limit 5;'
   sql2 = 'update bt_stu set class="天蝎座3" where id=503;'
   res = op_mysql(
      host='211.149.218.16',
      user='jxz',password='123456',#port这里一定要写int类型
      port=3306,db='jxz',charset='utf8',sql=sql2)
   print(res)
3、网络编程

import urllib.request
import json,requests

#发送get请求
# url = 'http://api.nnzhp.cn/api/user/stu_info?stu_name=小黑马'
# req = requests.get(url) #发送get请求
# print(req.text) #获取结果
# print(req.json()) #获取结果直接就是字典，必须返回的是json串，才能用.json方法。

#发送post请求
# url = 'http://api.nnzhp.cn/api/user/login'
# data =  {'username':'niuhanyang','passwd':'aA123456'}
# req = requests.post(url,data) #发送post请求，第一个参数是url，第二个参数是请求的数据
# print(req.json())

#入参是json的
# url = 'http://api.nnzhp.cn/api/user/add_stu'
# data =  {'name':'丁飞','grade':'巨蟹座','phone':31971891223}
# req = requests.post(url,json=data) #发送post请求，第一个参数是url，第二个参数是请求的数据
# print(req.json())
#添加cookie

# url = 'http://api.nnzhp.cn/api/user/gold_add'
# data =  {'stu_id':231,'gold':1000}
# cookie = {'niuhanyang':'6d195100b95a43046d2e385835c6e2c2'}
# req = requests.post(url,data,cookies=cookie)
# print(req.json())

#添加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://api.nnzhp.cn/api/file/file_upload'
# f = open(r'C:\Users\bjniuhanyang\Desktop\ad.cpm.schedulingInfo.v1.json','rb')
# r = requests.post(url,files={'file':f})
# print(r.json())

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

#保存网页
url = 'http://www.nnzhp.cn/archives/630'
r = requests.get(url)
f = open('nnzhp.html','wb')
f.write(r.content)
f.close()
4、操作excel

import xlwt
# book = xlwt.Workbook()  #新建一个excel
# sheet = book.add_sheet('sheet1')  #添加一个sheet页
# sheet.write(0,0,'姓名')
# sheet.write(0,1,'性别')
# sheet.write(0,2,'年龄')
# book.save('stu.xls') #微软的office不能用xlsx结尾的，wps随意


stus = [
      ['姓名','年龄','性别','分数'],
       ['mary', 20, '女', 89.9],
      ['mary', 20, '女', 89.9],
      ['mary', 20, '女', 89.9],
      ['mary', 20, '女', 89.9]
]
book = xlwt.Workbook()  #新建一个excel
sheet = book.add_sheet('sheet1')  #添加一个sheet页
raw = 0#控制行的
for stu in stus:
   col = 0 #控制列
   for s in stu:
      sheet.write(raw,col,s)
      col+=1
   raw+=1

book.save('kkk.xls')

5、读取excel

import xlrd
book = xlrd.open_workbook('stu.xls') #打开一个excel
sheet = book.sheet_by_index(0) #根据顺序获取sheet
# sheet2 = book.sheet_by_name('sheet1') #根据sheet页名字获取sheet
# print(sheet.cell(0,0).value)  #指定行和列获取数据
# print(sheet.ncols) #获取excel里面有多少列
# print(sheet.nrows) #获取excel里面有多少行
sheet.row_values(1)#取第几行的数据
print(sheet.col_values(1)) #取第几列的数据
for i in range(sheet.nrows): # 0 1 2 3 4 5
   print(sheet.row_values(i)) #取第几行的数据
6、修改excel

from xlutils.copy import copy
import xlrd
book1 = xlrd.open_workbook('stu.xls')
book2 = copy(book1)  #拷贝一份原来的excel
sheet = book2.get_sheet(0) #获取第几个sheet页
sheet.write(1,3,0)
sheet.write(1,0,'小黑')
book2.save('stu.xls')
7、笔记加作业

1、上周回顾
    1、怎么开发mock接口
        1、
        2、辅助测试，代替第三方接口
        3、查看数据
        flask #轻量级的，web开发框架，开发后台服务

    2、操作数据库、redis
        1、连上 pymysql
           c= py.connect(**mysqlinfo)
           cur = c.course(course=pymysql.cours.DicCour)
           cur.execute(sql)
           cur.fetchall()  #获取所有数据      #二维数组
           cur.fetone()    #一次只获取一条    #一维
           c.coomit()
           cur.close()
           c.close()
         2、r = redis.Redis(**redis)
          string
            r.set(k,v)
            r.get(k)
            r.delete(k)
            r.setex(k,v,time)
          hash
            r.hset(name,k,v)
            r.hgetall(name)  #字典
            r.hget(name,k)
          所有的key
            r.keys('')
            r.type(k).decode()
          redis里面查出来的数据都是 bytes

    3、写程序的分目录
        为了我们的程序看起来更有条理
        互相导入

#sql注入 安全测试
    sql注入的原理是利用了引号

作业：
    1、 http://doc.nnzhp.cn/index.php?s=/6&page_id=14
        这个接口  获取所有学生信息
     先调用这个接口，然后把学生信息写到excel
    2、读data这个目录下excel的数据，然后用excel里面所有的用户信息，调用
        添加学生信息的接口
        #手机号码可以是包含字母。
        #写入10条就ok了

#-*-coding:utf-8-*-
import json,requests
import xlwt
import random,string
import xlrd
from xlutils.copy import copy
#添加header#获取所有学生的信息
url='http://api.nnzhp.cn/api/user/all_stu'
header = {'Referer':'http://api.nnzhp.cn/','User-Agent':'Chore'}
res = requests.get(url,headers=header)
stu_all = res.json()

stus = [
      'id','name','sex','age','addr','grade','phone','gold'
]
stu_key = stus[1]
book = xlwt.Workbook()  #新建一个excel
sheet = book.add_sheet('sheet1')  #添加一个sheet页
col = 0
for stu in stus:
    sheet.write(0,col,stu)
    col+=1

i = 0
for i in range(len(stu_all['stu_info'])):
    j = 0 #控制列
    for st in stus:
        sheet.write(i+1,j,stu_all['stu_info'][i][st])
        j+=1

book.save('kkk.xls')
print('保存成功')

book1 = xlrd.open_workbook('kkk.xls')
book2 = copy(book1)  #拷贝一份原来的excel
sheet = book2.get_sheet(0) #获取第几个sheet页
i = 0
for i in range(len(stu_all['stu_info'])):
    j = 0 #控制列
    for st in stus:
        lower = random.sample(string.ascii_lowercase, 1)
        upper = random.sample(string.ascii_uppercase, 1)
        num = random.sample(string.digits, 1)
        other = random.sample(string.ascii_letters + string.digits, 4)
        res = lower + upper + num + other
        random.shuffle(res)
        new_res = ''.join(res)
        new_res = str(new_res)
        sheet.write(i+1,1,'yzf_'+stu_all['stu_info'][i]['name'])
        sheet.write(i+1,6,'yzf_'+new_res)
        j+=1
book2.save('kkk.xls')
print('修改成功')


book = xlrd.open_workbook('kkk.xls') #打开一个excel
sheet = book.sheet_by_index(0) #根据顺序获取sheet

for i in range(1,11): # 0 1 2 3 4 5
    data = sheet.row_values(i)  #取第几行的数据
    url = 'http://api.nnzhp.cn/api/user/add_stu'
    data =  {'name':data[1],'sex':data[2],'age':data[3],'addr':data[4],'grade':data[5],'phone':data[6]}
    req = requests.post(url,json=data) #发送post请求，第一个参数是url，第二个参数是请求的数据
    print(req.json())

#python操作excel详解
http://www.cnblogs.com/python2016/p/5840520.html