MySQL数据库（触发器，注入 问题）

MySQL数据库

SQL注入问题

视图

触发器

下一篇第二篇

SQL注入问题

什么是SQL的注入？

SQL注入是发生在web程序中大户局库层的安全漏洞，是网站存在最多也是最简单的漏洞，攻击者会在事先定义好的SQL语句中添加额外的SQL语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务执行授权的任意查询，从而进一步获取到数据信息。

产生SQL注入的原因？

程序对用户输入的数据的合法没有判断和处理。

使用者在用户输入的时候在SQL语句中添加SQL语句，如果在设计程序的时候忽略检查，那么这些注入进去事物SQL语句就会被数据库服务器认为是正常的SQL语句而运行，使用这个漏洞的人就可以执行计划以外的命令或哦这访问为首权的数据。

产生SQL注入的条件

GET,POST,HTTP头部注入,Gookie注入

满足传递给后端的参数可以受用户控制且参数内容会直接被带到数据库语句中查询。

注入SQL的原理

1.恶意拼接SQL语句

	当我们在页面输入用户账户的时候，后台的数据可能执行的是：
    select *from 表名 where user = user_id  
语句，此时如果我们输入的user_id后拼接上 :
    delete from user;
将会实现将user表中的数据删除，类似的通过拼接其他SQL语句也可以执行相应的功能。

2. 添加额外的条件

当我们在需要账户密码进行登录时，如果在账号密码输入处添加额外的恒定成立条件，可在没有正的账户密码时仍让操作请求成功。因为在验证账号密码时，后台数据库可能执行：

select *from 表名 where name = 'Jason' --kasdjksajb 'and pwd=''

select *from 表名 where name= 'xxx' or 1=1 --aksdjasldj 'and pwd=''

如果在账号和密码后都添加or 1=1，由于or左右两端的条件只需有一个为真则结果为真，而1=1恒为真，所以在where语句进行判断时账号和密码处都为成真赋值，这将实现绕过账号密码登录

上面的SQL语句就是利用特殊的符号的组成产生了特殊的含义，从而避开正常的业务逻辑，针对这个问题，核心在于手动拼接了关键数据，过滤特殊符号对于无法确定固定格式的变量，对特殊符号进行过滤或转义处理。交给execute处理。

sql="select *from userinfo where name=%s and pwd =%s"
cursor.execute(sql,(username,password))

在添加多个数据的时候如果这样一个一个是的编写很麻烦，所以可以使用
executemany (sql,[(user,pwd),(user2,pwd2),....])

视图

视图就是通过查询得到一张虚拟表，然后保存下来，下次直接使用即可

create view 视图表名 as select *from 表名1 inner join 表名2 on 表1.主键 = 表2.外键;

温馨提示：

1.视图的表只能用来查询不能做其他增删改的操作

2.视图尽量少用，因为它会和真正的表产生混淆，从而干扰操作者

触发器

概念：

​ 触发器是一种特殊类型的存储过程，它不同于存储过程，主要是通过事件触发而被执行的，就是不是主动调动而执行，而存储过程则需要主动调用用其名字执行。

触发器（trigger）：是指事先为某张表绑定的一段代码，当表中的某些内容发生改变（增删改 之前，之后六种）的时候，系统就会自动触发代码执行。

触发器有什么作用呢？

1.可以在写数据之前，强制检验或者转换数据（保证数据安全）

2.触发器发生错误时，前面用户已经执行成功的操作会被撤销，有点类似于事物的回滚。

创建触发器：

delimiter # 自定义结束符号
create trigger 触发器名字 触发时间 触发事件 on 表 for each row begin
 ---触发器内容主体，每行用分号结尾
end
自定义的结束符号 
delimiter；

on 表名 for each：触发对象，触发器绑定的实质是表中的所有行，因此当每一行发生指定改变时，触发器就会发生

触发的时机：

当SQL指令发生时，会令行中数据发生变化，而每张表中对应的行有两种状态：操作之前和操作之后

• before：表中数据发生改变前的状态
• after：表中数据发生改变后的状态

PS：如果before触发器失败或者语句本身失败，将不执行after触发器（如果有的话）

触发事件：触发器是针对数据发生改变才会被触发，对应的操作只有

insert ,delete,update

注意事项：

• 在给触发器命名的时候也应该有一定的规律（见明知意）

  eg: tri_before_inner_t1

• 临时修改SQL语句的结束符，因为在有些操作的时候是需要使用分号的

• 在 MySQL 5 中，触发器名必须在每个表中唯一，但不是在每个数据库中唯一，即同一数据库中的两个表可能具有相同名字的触发器

• 每个表的每个事件每次只允许一个触发器，因此，每个表最多支持 6 个触发器，before/after insert、before/after delete、before/after update

• MySQL 的触发器中不能对本表进行 insert、update 和 delete 操作，否则会报错

old 和 new 不是所有触发器都有

触发器实际应用:

CREATE TABLE cmd (
    id INT PRIMARY KEY auto_increment,
    USER CHAR (32),
    priv CHAR (10),
    cmd CHAR (64),
    sub_time datetime, #提交时间
    success enum ('yes', 'no') #0代表执行失败
);

CREATE TABLE errlog (
    id INT PRIMARY KEY auto_increment,
    err_cmd CHAR (64),
    err_time datetime
);

delimiter $$  # 将mysql默认的结束符由;换成$$
create trigger tri_after_insert_cmd after insert on cmd for each row
begin
    if NEW.success = 'no' then  # 新记录都会被MySQL封装成NEW对象
        insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
    end if;
end $$
delimiter ;  # 结束之后记得再改回来，不然后面结束符就都是$$了

#往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志
INSERT INTO cmd (
    USER,
    priv,
    cmd,
    sub_time,
    success
)
VALUES
    ('kevin','0755','ls -l /etc',NOW(),'yes'),
    ('kevin','0755','cat /etc/passwd',NOW(),'no'),
    ('kevin','0755','useradd xxx',NOW(),'no'),
    ('kevin','0755','ps aux',NOW(),'yes');

# 查询errlog表记录
select * from errlog;
# 查看所有的触发器
show triggers;
# 删除触发器 （触发器不能修改，只能删除）
drop trigger tri_after_insert_cmd;
#查看触发器的创建语句
show create trigger 触发器名字;

触发器的优缺点：

优点：
	触发器可以通过数据库中的关联表实现级联更改，即一张表数据的改变会影响其他表的数据
    可以保证数据安全，并进行安全校验
    
缺点：
	过分依赖触发器，影响数据库的结构，增加数据库的维护成本

事物

事物的四大特性（ACID）
	A:原子性
    	事物中的各项操作是不可分割的整体，要么同时成功，要么同时失败。
    B：一致性
    	十数据库从一个一致性状态变到另一个一致性状态
    I：隔离型
    	多个事物之间彼此不干扰
    D：持久性
    	也称为永久性，只一个事物一旦提交，它对数据库中数据的改变就应该是永久性的

代码：

create table user(
id int primary key autho_increment,
    name char(32),
    balance int
);

insert into user(name,balance) values
('jason',1000),
('kevin',1000),
('andy',1000);

# 修改数据之前先开启事物操作
start transaction ;

# 修改操作
# 买东西付了100元
update user set balance= 900 where name='jason'
# 中介收了10元
update user set balance = 1010 where name='kevin';
#卖家收款90元
update user set balance=1090 where name='andy';

#回滚到上一个状态
rollback;
#开启事物之后，只要没有执行commit操作，数据其实都没有真正刷新到硬盘
commit;

事物的相关关键字：

start transaction; rollback commit savepoint

在SQL标准中定义了四种隔离级别，每一种几倍都规定了一个事物中所做的修改

InnoDB支持所有隔离级别

set transaction isolation level 级别

1.read uncommitted(未提交读)
事物中的修改即使没有提交，对其他事物也都是可见的，事物可以读取未提交的数据，这一现象也就被称为"脏读"

脏读：事务A读取到了事务已经修改但未提交的数据，这种数据就叫脏数据，是不正确的

2.read committed（提交读）
大多数数据库系统默认的隔离级别
一个事物从开始直到提交之前所做所谓的任何修改对其他事物都是不可见的，这种级别也叫做'不可重复读'

不可重复读：对于事务A多次读取同一个数据时，由于其他是事务也在访问这个数据，进行修改且提交，对于事务A，读取同一个数据时，有可能导致数据不一致，叫不可重复读

3.repeatable read（可重复读） 是mySQL默认隔离级别
能够解决'脏读'问题，但是无法解决"幻读"
所谓幻读指的是当某个事物在读取某个范围的记录时另一个事物又在该范围插入新的记录，当之前的事物再次读取范围的记录会产生幻行，InnoDB和XtraDB通过多版本并发控制（MVCC）及间隙锁策略解决该问题

MVCC只能在read committed(提交读)、repeatable read(可重复读)两种隔离级别下工作，其他两个不兼容(read uncommitted:总是读取最新 serializable:所有的行都加锁)

InnoDB的MVCC通过在每行记录后面保存两个隐藏的列来实现MVCC
一个列保存了行的创建时间
一个列保存了行的过期时间(或删除时间) 本质是系统版本号
每开始一个新的事务版本号都会自动递增，事务开始时刻的系统版本号会作为事务的版本号用来和查询到的每行记录版本号进行比较

幻读：原因：因为mysql数据库读取数据时，是将数据放入缓存中，当事务B对数据库进行操作：例如删除所有数据且提交时，事务A同样能访问到数据，这就产生了幻读。
问题：解决了可重复读，但是会产生一种问题，错误的读取数据，对于其他事务添加的数据也将访问不到

4.serializable（可串行读）
强制事物串行执行，很少使用该级别

串行化：事务A和事务B同时访问时，在事务A修改了数据，而没有提交数据时，此时事务B想增加或修改数据时，只能等待事务A的提交，事务B才能够执行。

问题：用户的体验十分的差，因为每次访问时都要等待其他事务的提交才能操作

事物的隔离等级：

1. 读未提交 read uncommitted
2. 读已提交 read committed
3. 可重复读 repeatable read
4. 串行化 serializable

（1）、读未提交：事物A和事物B，事物A未提交的数据，事物B可以读取到。 这种隔离级别最低，这种级别一般是在理论上存在，数据库隔离级别一般都高于该级别。 三种并发问题都没解决。

set global transaction isolation level read uncommitted;
#查看当前隔离级别
select @@global.tx_isolation,@@tx_isolation;

（2）、读已提交：事务A只能读取到事务B提交的数据，这种级别可以避免“脏数据” ，这种隔离级别会导致“不可重复读取” ，Oracle默认隔离级别

set global transaction isolation level read committed;
查看当前隔离级别
select @@global.tx_isolation,@@tx_isolation;

（3）、可重复读：- 事务A和事务B，事务A提交之后的数据，事务B读取不到 - 事务B是可重复读取数据 - 这种隔离级别高于读已提交 - 换句话说，对方提交之后的数据，我还是读取不到 - 这种隔离级别可以避免“不可重复读取”，达到可重复读取 - 比如1点和2点读到数据是同一个 - MySQL默认级别 - 虽然可以达到可重复读取，但是会导致“幻像读”

set global transaction isolation level repeatable read;
查看当前隔离级别
select @@global.tx_isolation,@@tx_isolation;

(4)、串行化：事务A和事务B，事务A在操作数据库时，事务B只能排队等待 这种隔离级别很少使用，吞吐量太低，用户体验差 这种级别可以避免“幻像读”，每一次读取的都是数据库中真实存在数据，事务A与事务B串行， 而不并发

set global transaction isolation level serializable;

查看当前隔离级别
select @@global.tx_isolation,@@tx_isolation;

事物相关代码：

开启事物：start transaction

事物的提交： commit

事物回滚：rollback （一旦事物提交失败，我们需要将数据回滚到之前的转态，需要用到rollback语句）

由此当我们查询一条记录的时候，只有满足以下两个条件的记录才会被显示出来：
   1.当前事务id要大于或者等于当前行的create_version值，这表示在事务开始前这行数据已经存在了。
   2.当前事务id要小于delete_version值，这表示在事务开始之后这行记录才被删除。