SQL注入问题/触发器trigger/事务/事物隔离

SQL注入问题

本质：利用特殊符号的组合产生特殊的含义，从而避开正常的业务逻辑

select * from userinfo where name='jason' -- kasdjksajd' and pwd=''
select * from userinfo where name='xyz' or 1=1 -- aksdjasldj' and pwd='' 

比如这样：用户可以通过输入 sql符号从而避开正常业务逻辑

如何避免:
  
  针对上述的SQL注入问题 核心在于手动拼接了关键数据 交给execute处理即可
  
  sql = " select * from userinfo where name=%s and pwd=%s "
cursor.execute(sql, (username, password))
# 使用execute(sql,根据%号位置传参 ) 使用这个方法来传参执行sql

'在sql拼接方面要注意不能直接拼接，应该使用cursor.execute方法'

execute(执行)


该方法会自动过滤用户输入的特殊字符 

触发器trigger

达到某个条件之后自动触发执行
在Mysql中 更加详细的说明是 触发器 ：针对表的增 删 改操作能够自动触发

主要有6种情况触发情况：
增前 before insert   增后 after insert
删前 before delete   删后 after delete 
改前 before update   改后 after update


create trigger 定义一个触发器名字 定义触发情况 on 表名
for each row begin  sql语句  end

当达到触发条件是 执行 sql语句


1、触发器命名规律 一般都是以 tri_ + 触发条件 + 表名
tri_before_insert_t1
tri_after_update_t1

2. 临时修改sql语句的结束符 因为有些操作中可能要用到分号


触发器代码实操

create table cmd (
     id int primary key auto_increment,
     name char(32),
     cmd char(32),
     sub_time datetime,
     success enum('yes','no')
);

create table errlog(
 id int primary key auto_increment,
 err_cmd char(32),
 err_time datetime
);


delimiter $ 将mysql默认的结束符换为 $ 

delimiter 定义结束符

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 
     
end $$ 
delimiter ; # 结束之后记得再改回来，

# 生成一个触发器 触发器名字为 tri_after_insert_cmd 这个触发器的触发条件为 after insert 当有这个动作是会触发 begin 里面的sql语句
# on cmd for each row 生效与那个表格 每一行

#往表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');
    
    
# 查看所有触发器
show triggers;

# 删除触发器
drop trigger tri_after_insert_cmd;
drop trigger 触发器名称

事务

关键词： 
开启事务: start transaction
节点位置： savepoint   # 节点就是可以标记回滚的点
回滚: rollback
确认结束:commit

事务的四大特性(ACID)

A:原子性
  事务中的各项操作是不可以分割的整体，要么同时成功要么同时失败
C:一致性
  使数据库从一个一致性状态变到另一个 一致性状态
I:隔离性
  多个事务之间彼此不干扰
D:持久性
  也称为永久性，指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的
 
create table user(id int primary key,name char(32),balance int);

insert into user values
(1,'jason',1000),
(2,'kevin',1000),
(3,'tank',1000);

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

update user set balance = 500 where name = 'jason';
update user set balance = 100 where name = 'kevin';

rollback;
# 回滚，这个指令可以让数据回到事务开始之前的状态  

commit;
# 事务结束  这样就不能回滚了 因为事务已经确认结束了

SQL中四种事物隔离级别

1.innoDB引擎支持所有的隔离级别
set transaction isolation level 级别 
# 设置一个级别 默认为 repeatable read(可重复读)

1.read uncommitted (未提交读)
事务中的修改及时没有提交，没有确认，但是其他事务也都是可见的，事务可以读取未提交的数据，这一现象也称之为 '脏读'
事务未确认时数据其他事务读取 叫 脏读

2.read committed(提交读)
大多数数据库系统默认的隔离级别
一个事务从开始直到提交之前所做的任何修改 对其他事务都是不可见得，
这中界别叫'不可重复度'
在事务未确认前 其他事务不可以使用事务进行中的数据

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

4.serializable (可串行读)
强制事物串行执行，实际中很少使用该级别 效率很低

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


例如：
刚插入第一条数据的时候，我们默认事务id为1，实际是这样存储的
    username		create_version		delete_version
    jason						1					
可以看到，我们在content列插入了kobe这条数据，在create_version这列存储了1，1是这次插入操作的事务id。
然后我们将jason修改为jason01，实际存储是这样的
    username		create_version		delete_version
    jason						1									2
    jason01					2
可以看到，update的时候，会先将之前的数据delete_version标记为当前新的事务id，也就是2，然后将新数据写入，将新数据的create_version标记为新的事务id
当我们删除数据的时候，实际存储是这样的
		username		create_version		delete_version
    jason01					2									 3
"""
由此当我们查询一条记录的时候，只有满足以下两个条件的记录才会被显示出来：
   1.当前事务id要大于或者等于当前行的create_version值，这表示在事务开始前这行数据已经存在了。
   2.当前事务id要小于delete_version值，这表示在事务开始之后这行记录才被删除。
"""