MySQL数据库(六)-- SQL注入攻击、视图、事物、存储过程、流程控制
一、SQL注入攻击
1、什么是SQL注入攻击
一些了解sql语法的用户,可以输入一些关键字 或合法sql,来导致原始的sql逻辑发生变化,从而跳过登录验证 或者 删除数据库
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
import pymysql conn = pymysql.Connect( user="root", password="admin", host="localhost", database="day43", charset="utf8" ) cursor = conn.cursor(pymysql.cursors.DictCursor) sql = "select *from user where user = '%s' and pwd = '%s';" % (input("input userName"),input("input password")) conut=cursor.execute(sql) print(count) if count: print("login success") else: print("login error") cursor.close() conn.close()
# -- 用于mysql注释,意思是后面的内容忽略掉 # 当用户输入的用户名为字符串为yy' -- 时,密码是否正确都能登录成功 产生的sq: select *from user where user = 'yy' -- ' and pwd = '987657890'; # 当用户输入axxax' or 1=1-- 时,跳过用户名和密码,就可登录 产生的sql: "select *from user where user = 'axxax' or 1=1 -- ' and pwd='123';
2、解决办法:
(1)从客户端注入SQL攻击
# 原来是我们对sql进行字符串拼接 # sql="select * from userinfo where name='%s' and password='%s'" %(user,pwd) # print(sql) # res=cursor.execute(sql) #改写为(execute帮我们做字符串拼接,把你的slq(用户输入的)参数放execute函数的arg参数中,让pymysql自动帮你屏蔽注入攻击,我们无需且一定不能再为%s加引号了) sql="select * from userinfo where name=%s and password=%s" #!!!注意%s需要去掉引号,因为pymysql会自动为我们加上 res=cursor.execute(sql,[user,pwd]) #pymysql模块自动帮我们解决sql注入的问题,只要我们按照pymysql的规矩来。
(2)中间人攻击(在你的客户端和服务器中间加一个中转服务器)
# 这种攻击方式绕过了客户端的输入限制,这种攻击只能将SQL合法性验证放在服务端
二、视图(实际开发中用的不多)
1、什么是视图
视图是虚拟的表,用户在查询时会产生临时表,可以利用视图将临时表永久保存下来。这样用户使用时只需用视图即可。
2、视图的功能
# 功能1:隐藏部分数据,开放指定的数据,使原表安全 例如:员工只能看自己的工薪信息,不能看别人的 # 功能2:因为视图可以将查询结果保存特性,可以用视图来达到减少书写sql的次数 例如:select *from emp where dept_id = (select id from dept where name = "市场"); 要查询市场的人,将查询结果作为一个视图 以后在使用到这个需求 就直接查看视图
3、创建视图
create view test_view as select *from t1;
4、使用视图
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#修改视图,原始表也跟着改 mysql> select * from course; +-----+--------+------------+ | cid | cname | teacher_id | +-----+--------+------------+ | 1 | 生物 | 1 | | 2 | 物理 | 2 | | 3 | 体育 | 3 | | 4 | 美术 | 2 | +-----+--------+------------+ rows in set (0.00 sec) mysql> create view course_view as select * from course; #创建表course的视图 Query OK, 0 rows affected (0.52 sec) mysql> select * from course_view; +-----+--------+------------+ | cid | cname | teacher_id | +-----+--------+------------+ | 1 | 生物 | 1 | | 2 | 物理 | 2 | | 3 | 体育 | 3 | | 4 | 美术 | 2 | +-----+--------+------------+ rows in set (0.00 sec) mysql> update course_view set cname='xxx'; #更新视图中的数据 Query OK, 4 rows affected (0.04 sec) Rows matched: 4 Changed: 4 Warnings: 0 mysql> insert into course_view values(5,'yyy',2); #往视图中插入数据 Query OK, 1 row affected (0.03 sec) mysql> select * from course; #发现原始表的记录也跟着修改了 +-----+-------+------------+ | cid | cname | teacher_id | +-----+-------+------------+ | 1 | xxx | 1 | | 2 | xxx | 2 | | 3 | xxx | 3 | | 4 | xxx | 2 | | 5 | yyy | 2 | +-----+-------+------------+ rows in set (0.00 sec)
修改视图的记录,其实是更新了原表中的记录,然后视图重新运行 as 后面的select语句,从而获取视图中的数据。
5、修改视图
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#语法:ALTER VIEW 视图名称 AS SQL语句 mysql> alter view teacher_view as select * from course where cid>3; Query OK, 0 rows affected (0.04 sec) mysql> select * from teacher_view; +-----+-------+------------+ | cid | cname | teacher_id | +-----+-------+------------+ | 4 | xxx | 2 | | 5 | yyy | 2 | +-----+-------+------------+ rows in set (0.00 sec)
6、删除视图
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
# 语法:DROP VIEW 视图名称 DROP VIEW teacher_view
7、视图的特点
1.每次对视图进行的查询,其实都是再次执行了 as 后面的查询语句 2.可以对视图进行修改,修改会同步到原表 3.视图是永久存储的,存储的不是数据,而就是一条 as 后面的 查询语句
三、事务
1、什么是事务
事务就是一组sql语句的集合,它们是原子性的,要么全部执行,要么都不执行
mysql客户端,默认开启自动提交,默认不开启事务,一条sql语句就是一个单独的事务,自动提交,不能实现撤销
pymysql 默认是不自动提交,默认开启事务,需要手动commit,就是N条SQL语句表示一个事务,必须在这些语句的结尾手动提交,不提交就可以撤销(rollback)
2、事务的特性
#1.原子性 事务是一个整体,不可分割 #2.隔离性 事务之间要相互隔离,为了维护数据完整性,查询和修改不能同时进行 因为并发访问导致的一些问题 1.脏读 一个事物 读到了 另一个事务未提交的数据,查询之前要保证,所有的更新都已经完成 2.幻读 一个查询事务没有结束时,数据被另一个事务执行insert delete 3.不可重复读 一个事物在查询,另一个事务在 update 四种隔离级别 读未提交 读已提交 可重复读 默认 串行化 #3.一致性 当事务执行后,所有的数据都是完整的(外键约束、非空约束) #4.持久性 一旦事务提交,数据就永久保存
3、事务中应掌握点
开启事务以后,必须提交。提交以前,数据可以撤销;一旦提交,数据就无法撤销。
start transaction; 开启一个事物
commit 提交事物
rollback 回滚事务
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> select * from emp; +------+------+--------+ | id | name | salary | +------+------+--------+ | 1 | tom | 10000 | | 2 | tony | 12000 | | 3 | Jack | 13000 | | 4 | Sary | 9000 | +------+------+--------+ rows in set (0.20 sec) mysql> start transaction; # 开启事务 Query OK, 0 rows affected (0.00 sec) mysql> update emp set name ='TOM' where id=1; #更改数据 Query OK, 1 row affected (0.06 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from emp; # 查看更改后的数据 +------+------+--------+ | id | name | salary | +------+------+--------+ | 1 | TOM | 10000 | | 2 | tony | 12000 | | 3 | Jack | 13000 | | 4 | Sary | 9000 | +------+------+--------+ rows in set (0.00 sec) mysql> rollback; # 撤销更改 Query OK, 0 rows affected (0.12 sec) mysql> select * from emp; # 查看撤销后的数据,没有提交,可以完成撤销 +------+------+--------+ | id | name | salary | +------+------+--------+ | 1 | tom | 10000 | | 2 | tony | 12000 | | 3 | Jack | 13000 | | 4 | Sary | 9000 | +------+------+--------+ rows in set (0.00 sec) mysql> update emp set name ='TOM' where id=1; # 更改数据 Query OK, 1 row affected (0.13 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> commit; # 提交数据 Query OK, 0 rows affected (0.00 sec) mysql> rollback; # 提交数据后,撤销更改 Query OK, 0 rows affected (0.00 sec) mysql> select * from emp; # 查看数据,提交以后,数据无法撤销 +------+------+--------+ | id | name | salary | +------+------+--------+ | 1 | TOM | 10000 | | 2 | tony | 12000 | | 3 | Jack | 13000 | | 4 | Sary | 9000 | +------+------+--------+ rows in set (0.00 sec)
4、事务用于将某些操作的多个SQL作为原子性操作,一旦有某一个出现错误,即可回滚到原来的状态,从而保证数据库数据完整性。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
create table user( id int primary key auto_increment, name char(32), balance int ); insert into user(name,balance) values ('wsb',1000), ('egon',1000), ('ysb',1000); #原子操作 start transaction; update user set balance=900 where name='wsb'; #买支付100元 update user set balance=1010 where name='egon'; #中介拿走10元 update user set balance=1090 where name='ysb'; #卖家拿到90元 commit; #出现异常,回滚到初始状态 start transaction; update user set balance=900 where name='wsb'; #买支付100元 update user set balance=1010 where name='egon'; #中介拿走10元 uppdate user set balance=1090 where name='ysb'; #卖家拿到90元,出现异常没有拿到 rollback; commit; mysql> select * from user; +----+------+---------+ | id | name | balance | +----+------+---------+ | 1 | wsb | 1000 | | 2 | egon | 1000 | | 3 | ysb | 1000 | +----+------+---------+ rows in set (0.00 sec)
四、存储过程
1、什么是存储过程
存储过程相当于python中的一个函数,通过调用它的名字可以执行其内部的一堆sql
2、存储过程的作用
可以将你的程序业务逻辑,放到mysql中来处理
这样可以降低网络访问次数,从而提高你的程序效率
3、程序与数据库结合使用的三种方式
#方式一: MySQL:存储过程 程序:调用存储过程 #方式二: MySQL: 程序:纯SQL语句 #方式三: MySQL: 程序:类和对象,即ORM(本质还是纯SQL语句)
4、创建存储过程
delimiter // create procedure 过程的名称 ({in,out,inout} 参数名称 数据类型 ) begin 具体的sql代码 end // # 参数前面需要指定参数的作用 # in 表示该参数用于传入数据 # out 用于返回数据 # inout 即可传入 也可返回 # 参数类型是 mysql中的数据类型
(1)无参存储过程的创建和使用
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
delimiter // # 修改提交标志,以//结束表示提交 create procedure p1() BEGIN select * from blog; INSERT into blog(name,sub_time) values("xxx",now()); END // delimiter ; #在mysql中调用 call p1() # call 表示调用,调用方式 #在python中基于pymysql调用 cursor.callproc('p1') print(cursor.fetchall())
(2)有参存储过程的创建和使用
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
delimiter // create procedure p2(in n1 int,in n2 int) BEGIN select * from blog where id > n1; END // delimiter ; #在mysql中调用 call p2(3,2) #在python中基于pymysql调用 cursor.callproc('p2',(3,2)) print(cursor.fetchall())
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
delimiter // create procedure p3(in n1 int,out res int) BEGIN select * from blog where id > n1; set res = 1; END // delimiter ; #在mysql中调用 set @res=0; #0代表假(执行失败),1代表真(执行成功) call p3(3,@res); # @res表示这个参数时返回值 select @res; #在python中基于pymysql调用 cursor.callproc('p3',(3,0)) #0相当于set @res=0 print(cursor.fetchall()) #查询select的查询结果 cursor.execute('select @_p3_0,@_p3_1;') #@p3_0代表第一个参数,@p3_1代表第二个参数,即返回值 print(cursor.fetchall())
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
delimiter // create procedure p4( inout n1 int ) BEGIN select * from blog where id > n1; set n1 = 1; END // delimiter ; #在mysql中调用 set @x=3; call p4(@x); select @x; #在python中基于pymysql调用 cursor.callproc('p4',(3,)) print(cursor.fetchall()) #查询select的查询结果 cursor.execute('select @_p4_0;') print(cursor.fetchall())
5、执行存储过程
(1)在mysql中执行
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
-- 无参数 call proc_name() -- 有参数,全in call proc_name(1,2) -- 有参数,有in,out,inout set @t1=0; set @t2=3; call proc_name(1,2,@t1,@t2)
(2)在Python中基于pymysql执行存储过程
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#!/usr/bin/env python # -*- coding:utf-8 -*- import pymysql conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1') cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 执行存储过程 cursor.callproc('p1', args=(1, 22, 3, 4)) # 获取执行完存储的参数 cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3") result = cursor.fetchall() conn.commit() cursor.close() conn.close() print(result)
补充:
mysql中的变量分为三种 1.全局变量 两个@@代表系统变量 系统以及定好的变量 2.会话级变量 一个@代表用户变量 自己定义的变量 3.局部变量 查看当前隔离级别 select @@global.tx_isolation,@@tx_isolation; 设置隔离级别 分两种 全局 与 会话级 set global tx_isolation = "read-committed"; //全局 set tx_isolation = "read-committed"; //全局
五、流程控制
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
delimiter // CREATE PROCEDURE proc_if () BEGIN declare i int default 0; if i = 1 THEN SELECT 1; ELSEIF i = 2 THEN SELECT 2; ELSE SELECT 7; END IF; END // delimiter ;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
delimiter // CREATE PROCEDURE proc_while () BEGIN DECLARE num INT ; SET num = 0 ; WHILE num < 10 DO SELECT num ; SET num = num + 1 ; END WHILE ; END // delimiter ;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
delimiter // CREATE PROCEDURE proc_repeat () BEGIN DECLARE i INT ; SET i = 0 ; repeat select i; set i = i + 1; until i >= 5 end repeat; END // delimiter ;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
BEGIN declare i int default 0; loop_label: loop set i=i+1; if i<8 then iterate loop_label; end if; if i>=10 then leave loop_label; end if; select i; end loop loop_label; END