一些了解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()
mit()
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循环