mysql 存储过程 注入_MySQL数据库（六）-- SQL注入攻击、视图、事物、存储过程、流程控制...

一些了解sql语法的用户,可以输入一些关键字 或合法sql,来导致原始的sql逻辑发生变化,从而跳过登录验证 或者 删除数据库

importpymysql

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)ifcount: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、使用视图

#修改视图，原始表也跟着改

mysql> select * fromcourse;+-----+--------+------------+

| cid | cname | teacher_id |

+-----+--------+------------+

| 1 | 生物 | 1 |

| 2 | 物理 | 2 |

| 3 | 体育 | 3 |

| 4 | 美术 | 2 |

+-----+--------+------------+rowsin set (0.00sec)

mysql> create view course_view as select * from course; #创建表course的视图

Query OK, 0 rows affected (0.52sec)

mysql> select * fromcourse_view;+-----+--------+------------+

| cid | cname | teacher_id |

+-----+--------+------------+

| 1 | 生物 | 1 |

| 2 | 物理 | 2 |

| 3 | 体育 | 3 |

| 4 | 美术 | 2 |

+-----+--------+------------+rowsin set (0.00sec)

mysql> update course_view set cname='xxx'; #更新视图中的数据

Query OK, 4 rows affected (0.04sec)

Rows matched:4 Changed: 4Warnings: 0

mysql> insert into course_view values(5,'yyy',2); #往视图中插入数据

Query OK, 1 row affected (0.03sec)

mysql> select * from course; #发现原始表的记录也跟着修改了

+-----+-------+------------+

| cid | cname | teacher_id |

+-----+-------+------------+

| 1 | xxx | 1 |

| 2 | xxx | 2 |

| 3 | xxx | 3 |

| 4 | xxx | 2 |

| 5 | yyy | 2 |

+-----+-------+------------+rowsin set (0.00 sec)

View Code

修改视图的记录，其实是更新了原表中的记录，然后视图重新运行 as 后面的select语句，从而获取视图中的数据。

5、修改视图

#语法：ALTER VIEW 视图名称 AS SQL语句

mysql> alter view teacher_view as select * from course where cid>3;

Query OK, 0 rows affected (0.04sec)

mysql> select * fromteacher_view;+-----+-------+------------+

| cid | cname | teacher_id |

+-----+-------+------------+

| 4 | xxx | 2 |

| 5 | yyy | 2 |

+-----+-------+------------+rowsin set (0.00 sec)

View Code

6、删除视图

#语法：DROP VIEW 视图名称

DROP VIEW teacher_view

View Code

7、视图的特点

1.每次对视图进行的查询，其实都是再次执行了 as 后面的查询语句2.可以对视图进行修改，修改会同步到原表3.视图是永久存储的，存储的不是数据，而就是一条 as 后面的 查询语句

三、事务

1、什么是事务

事务就是一组sql语句的集合，它们是原子性的，要么全部执行，要么都不执行

mysql客户端，默认开启自动提交，默认不开启事务，一条sql语句就是一个单独的事务，自动提交，不能实现撤销

pymysql 默认是不自动提交，默认开启事务，需要手动commit，就是N条SQL语句表示一个事务，必须在这些语句的结尾手动提交，不提交就可以撤销(rollback)

2、事务的特性

#1.原子性

事务是一个整体，不可分割#2.隔离性

事务之间要相互隔离，为了维护数据完整性，查询和修改不能同时进行

因为并发访问导致的一些问题1.脏读 一个事物 读到了 另一个事务未提交的数据，查询之前要保证，所有的更新都已经完成2.幻读 一个查询事务没有结束时，数据被另一个事务执行insert delete3.不可重复读 一个事物在查询，另一个事务在 update

四种隔离级别

读未提交

读已提交

可重复读 默认

串行化#3.一致性

当事务执行后，所有的数据都是完整的(外键约束、非空约束)#4.持久性

一旦事务提交，数据就永久保存

3、事务中应掌握点

开启事务以后，必须提交。提交以前，数据可以撤销；一旦提交，数据就无法撤销。

start transaction; 开启一个事物

commit 提交事物

rollback 回滚事务

mysql> select * fromemp;+------+------+--------+

| id | name | salary |

+------+------+--------+

| 1 | tom | 10000 |

| 2 | tony | 12000 |

| 3 | Jack | 13000 |

| 4 | Sary | 9000 |

+------+------+--------+rowsin set (0.20sec)

mysql> start transaction; #开启事务

Query OK, 0 rows affected (0.00sec)

mysql> update emp set name ='TOM' where id=1; #更改数据

Query OK, 1 row affected (0.06sec)

Rows matched:1 Changed: 1Warnings: 0

mysql> select * from emp; #查看更改后的数据

+------+------+--------+

| id | name | salary |

+------+------+--------+

| 1 | TOM | 10000 |

| 2 | tony | 12000 |

| 3 | Jack | 13000 |

| 4 | Sary | 9000 |

+------+------+--------+rowsin set (0.00sec)

mysql> rollback; #撤销更改

Query OK, 0 rows affected (0.12sec)

mysql> select * from emp; #查看撤销后的数据，没有提交，可以完成撤销

+------+------+--------+

| id | name | salary |

+------+------+--------+

| 1 | tom | 10000 |

| 2 | tony | 12000 |

| 3 | Jack | 13000 |

| 4 | Sary | 9000 |

+------+------+--------+rowsin set (0.00sec)

mysql> update emp set name ='TOM' where id=1; #更改数据

Query OK, 1 row affected (0.13sec)

Rows matched:1 Changed: 1Warnings: 0

mysql> commit; #提交数据

Query OK, 0 rows affected (0.00sec)

mysql> rollback; #提交数据后，撤销更改

Query OK, 0 rows affected (0.00sec)

mysql> select * from emp; #查看数据，提交以后，数据无法撤销

+------+------+--------+

| id | name | salary |

+------+------+--------+

| 1 | TOM | 10000 |

| 2 | tony | 12000 |

| 3 | Jack | 13000 |

| 4 | Sary | 9000 |

+------+------+--------+rowsin set (0.00 sec)

提交前后无法撤销数据

4、事务用于将某些操作的多个SQL作为原子性操作，一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性。

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 * fromuser;+----+------+---------+

| id | name | balance |

+----+------+---------+

| 1 | wsb | 1000 |

| 2 | egon | 1000 |

| 3 | ysb | 1000 |

+----+------+---------+rowsin set (0.00 sec)

View Code

四、存储过程

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)无参存储过程的创建和使用

delimiter // #修改提交标志，以//结束表示提交

create procedure p1()

BEGIN

select* fromblog;

INSERT into blog(name,sub_time) values("xxx",now());

END//delimiter ;#在mysql中调用

call p1() #call 表示调用，调用方式

#在python中基于pymysql调用

cursor.callproc('p1')print(cursor.fetchall())

无参存储过程

(2)有参存储过程的创建和使用

delimiter //create procedure p2(in n1 int,inn2 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())

in：传入参数

delimiter //create procedure p3(inn1 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())

out：返回值

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())

inout

5、执行存储过程

(1)在mysql中执行

--无参数

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)

mysql中执行存储过程

(2)在Python中基于pymysql执行存储过程

#!/usr/bin/env python#-*- coding:utf-8 -*-

importpymysql

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)

基于pymysql执行存储过程

补充：

mysql中的变量分为三种1.全局变量 两个@@代表系统变量 系统以及定好的变量2.会话级变量 一个@代表用户变量 自己定义的变量3.局部变量

查看当前隔离级别

select @@global.tx_isolation,@@tx_isolation;

设置隔离级别 分两种

全局 与 会话级

setglobal tx_isolation = "read-committed"; //全局

set tx_isolation= "read-committed"; //全局

五、流程控制

delimiter //CREATE PROCEDURE proc_if ()

BEGIN

declare i int default 0;if i = 1THEN

SELECT1;

ELSEIF i= 2THEN

SELECT2;

ELSE

SELECT7;

END IF;

END//delimiter ;

if条件

delimiter //CREATE PROCEDURE proc_while ()

BEGIN

DECLARE num INT ;

SET num=0 ;

WHILE num< 10DO

SELECT

num ;

SET num= num + 1;

END WHILE ;

END//delimiter ;

while循环

delimiter //CREATE PROCEDURE proc_repeat ()

BEGIN

DECLARE i INT ;

SET i=0 ;

repeat

select i;

set i= i + 1;

until i>= 5end repeat;

END//delimiter ;

repeat循环

BEGIN

declare i int default 0;

loop_label: loop

set i=i+1;if i<8then

iterate loop_label;

endif;if i>=10then

leave loop_label;

endif;

select i;

end loop loop_label;

END

loop循环