SQL注入，外键使用，E-R模型及表间关系，三范式

「爱情、让人受尽委屈。」 2023-10-13 20:39 15阅读 0赞

### SQL注入 ###

####  ####

学习目标

*  能够说出如何避免 SQL 注入问题

####  ####

1. 什么是SQL注入

*  SQL注入:

简单说sql注入是一种可以使得数据库中的数据泄露的方式. 如果有一些人有恶意的目的, 可以利用sql注入完成盗取数据.

*  产生原因:

后台将用户提交的带有恶意的数据和SQL进行字符串方式的拼接，从而影响了SQL语句的语义，最终产生数据泄露的现象.

> 简单的说, 就是利用各种方式提交数据给程序并让这些数据和SQL语句产生结合, 进而让新产生的SQL语句和之前的原始的SQL语句变成不同的意思, 至此就可以利用新产生的SQL语句获取想要的数据了.

*  防止SQL注入

SQL语句的参数化可以防止SQL注入的产生. 将SQL语句的所有数据参数存在一个列表中传递给execute函数的第二个参数进行执行, 就是SQL语句参数化.

python \# 把sql语句需要的参数放到一个列表中 my\_list = \[xxx,xxx,xxx\] \# 把列表作为execute方法的第二个参数传递 cursor.execute(sql,my\_list)

####  ####

2. 非安全方式的SQL语句

注意:

在输入商品名称的时候输入

\# 双引号也要输入  
" or 1=1 or "

这样就完成了一个简单的SQL注入

\# 原始的sql语句  
sql = 'select \* from goods where name="%s"' % find\_name  
\# 输入 " or 1=1 or " 后的sql语句  
sql = 'select \* from goods where name="" or 1=1 or ""'

这里 name= "" or 1=1 or "" 这个条件是一定成立的,因为or是或的意思多个条件只要有一个条件成立整体就成立,而1=1是一定成立的. 这就造成这个sql语句的意思发生里改变.

####  ####

3. 安全方式的SQL语句

from pymysql import connect  
  
\# 创建Connection连接  
conn = connect(host='localhost', port=3306, user='root', password='mysql', database='jing\_dong', charset='utf8')  
  
\# 获得Cursor对象  
cur = conn.cursor()  
  
\# 获取用户想要查询的物品名称  
find\_name = input("请输入物品名称：")  
  
\# sql语句  
\# 注意:  
\# 此处不同于python的字符串格式化，必须全部使用%s占位  
\# 所有参数所需占位符外不需要加引号  
sql = 'select \* from goods where name=%s'  
  
\# 安全的方式  
\# 构造参数列表  
params = \[find\_name\]  
\# 执行select语句  
\# 注意:  
\# 如果要是有多个参数，需要进行参数化  
\# 那么params = \[数值1, 数值2....\]，此时sql语句中有多个%s即可  
count = cur.execute(sql, params)  
  
\# 获取查询的结果  
result = cur.fetchall()  
  
\# 打印查询的结果  
print(result)  
  
\# 关闭Cursor对象  
cur.close()  
  
\# 关闭Connection对象  
conn.close()

利用参数化列表就可以完成防止SQL注入.

####  ####

总结

SQL注入问题

*  将SQL语句的所有数据参数存在一个列表中传递给execute函数的第二个参数

####  ####

### 外键使用 ###

####  ####

学习目标：

*  知道数据库表外键约束的作用
 *  能够为已经出存在的表添加外键约束
 *  能够创建表时增加外键约束

####  ####

1. 外键

![图片][b18244536f6a3502819d653bafe180b0.png]

下面咱们开始来验证外键的作用

*  分别在 goods\_cates 和 goods\_brands表中插入记录

insert into goods\_cates(name) values ('路由器'),('交换机'),('网卡');  
insert into goods\_brands(name) values ('海尔'),('清华同方'),('神舟');

*  在 goods 数据表中写入任意记录

![图片][dbe7d707a5c656d279cdf963b44d8fe8.png]

insert into goods (name,cate\_id,brand\_id,price)  
values('LaserJet Pro P1606dn 黑白激光打印机', 12, 4,'1849');

> 问题: SQL语句中的12代表什么意义 ? 没错 是cate\_id 请问: goods\_cates表中有id=12的记录吗 显然没有

![图片][ffc20a5cd58e56e385a483ab5720c632.png]

*  查询所有商品的详细信息 (通过左连接 将左表未显示数据添加到最终结果)

select \* from goods left join goods\_cates on goods.cate\_id = goods\_cates.id;

> 发现问题: cate\_id = 12的SQL语句名称插入的数据是有问题的。

![图片][ab0e6a5cc84dbc5f57951347551ded6f.png]

> 如何防止无效信息的插入,就是可以在插入前判断类型或者品牌名称是否存在呢? 可以使用之前讲过的外键来解决

*  外键约束:对外键字段的值 在更新和插入时进行和引用的表中字段数据进行对比
 *  关键字: foreign key,只有 innodb数据库引擎 支持外键约束

####  ####

2. 对于已经存在的字段添加外键约束

\-- 给brand\_id 添加外键约束和goods\_brands的id建立外键关联  
alter table goods add foreign key (brand\_id) references goods\_brands(id);  
\-- 给cate\_id 添加外键约束和goods\_cates的id建立外键关联  
alter table goods add foreign key (cate\_id) references goods\_cates(id);  
  
\-- ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (\`jing\_dong\`.\`\#sql-403\_5\`, CONSTRAINT \`\#sql-403\_5\_ibfk\_2\` FOREIGN KEY (\`cate\_id\`) REFERENCES \`goods\_cates\` (\`id\`))  
  
\-- 给cate\_id 添加外键失败  
\-- 会出现1452错误  
\-- 错误原因:已经添加了一个不存在的cate\_id值12,因此需要先删除  
  
delete from goods where cate\_id = 12;  
alter table goods add foreign key (cate\_id) references goods\_cates(id);

![图片][1ad69e19cce7bc5eda445d0ff9906bae.png]

*  此时如果再次插入一个不存在的品牌（cate\_id=12）的产品，会报错的

insert into goods (name,cate\_id,brand\_id,price) values('LaserJet Pro P1606dn 黑白激光打印机', 12, 4,'1849');  
  
插入失败，报错如下：  
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (\`jing\_dong\`.\`goods\`, CONSTRAINT \`goods\_ibfk\_2\` FOREIGN KEY (\`cate\_id\`) REFERENCES \`goods\_cates\` (\`id\`))

![图片][90637abccca65f6bc54bb2662d549820.png]

####  ####

3. 在创建数据表的时候设置外键约束

*  注意: goods 中的 cate\_id 的类型一定要和 goods\_cates 表中的 id 类型一致

create table goods(  
id int primary key auto\_increment not null,  
name varchar(40) default '',  
price decimal(5,2),  
cate\_id int unsigned,  
brand\_id int unsigned,  
is\_show bit default 1,  
is\_saleoff bit default 0,  
foreign key(cate\_id) references goods\_cates(id),  
foreign key(brand\_id) references goods\_brands(id)  
);

####  ####

4. 删除外键约束

\-- 需要先获取外键约束名称,该名称系统会自动生成,可以通过查看表创建语句来获取名称  
show create table goods;  
  
\-- 获取名称之后就可以根据名称来删除外键约束  
alter table goods drop foreign key 外键名称;

*  使用到外键约束会极大的降低表更新的效率, 所以在追求读写效率优先的场景下一般很少使用外键。

####  ####

总结

*  将查询的数据直接插入表中

insert into xxx (字段名) select 语句  
将select语句的结果集插入到一个表中

*  连表更新

update 表1 join 表2 on 连接条件  
set 某表.字段=值

*  外键约束作用

子表中的外键字段在插入和更新 新值的时候 新值必须 在主表中相应字段出现过。

####  ####

### E-R模型及表间关系 ###

####  ####

学习目标

*  了解E-R模型的组成部分
 *  能够举例说出生活中 1对1 1对多 多对多关系的例子

####  ####

1. E-R模型

*  E-R模型简介

E-R模型即E-R图。

E-R图即实体-联系图(Entity Relationship Diagram)，是指提供了表示实体型、属性和联系的方法，用来描述现实世界的概念模型。由美籍华裔计算机科学家陈品山(Peter Chen)发明。

*  E-R模型的使用场景
 *  关系型数据库 关系模型的基础上，我们需要根据产品经理的设计策划，抽取出来模型与关系，制定出表结构，这是项目开始的第一步
 *  在设计阶段一般使用E-R模型进行建模。有很多设计数据库的软件，常用的如power designer，db desinger等，这些软件可以直观的看到实体及实体间的关系
 *  设计数据库，可能是由专门的数据库设计人员完成，也可能是由开发组成员完成，一般是项目经理带领组员来完成
 *  待设计完成E-R模型会将其转化为关系模型

![图片][205df47052cedafda20abd8dd4e72417.png]

*  E-R模型组成元素

![图片][1f974f6b9e522859bb3c0799c36923af.png]

E-R图用 实体、联系和属性这3个概念来描述现实问题, 有以下三种元素:

*  实体型(Entity)：具有相同属性的实体具有相同的特征和性质，用实体名及其属性名集合来抽象和刻画同类实体;在E-R图中用矩形表示，矩形框内写明实体名；比如 电商购物系统中用户、购物车、订单等都是实体。
 *  属性(Attribute)：实体所具有的某一特性，一个实体可由若干个属性来刻画。在E-R图中用椭圆形表示，并用无向边将其与相应的实体连接起来；比如用户的ID、用户名、密码、昵称、身份证号码 都是属性。
 *  联系(Relationship)： 实体彼此之间相互连接的方式称为联系，也称为关系。

实体: 用矩形表示，并标注实体名称  
  
属性: 用椭圆表示，并标注属性名称  
  
关系: 用菱形表示，并标注关系名称

联系可分为以下 3 种类型：一对一、一对多、多对多:

*  关系也是一种数据，需要通过一个字段存储在表中
 *  实体A对实体B为1对1，则在表A或表B中创建一个字段，存储另一个表的主键值

![图片][120013747ade7bb4e92d78155bb186e5.png]

*  实体A对实体B为1对多：在表B中创建一个字段，存储表A的主键值

![图片][8a12ddf4092e50557227ee1f5b3b3456.png]

*  实体A对实体B为多对多：新建一张表C，这个表只有两个字段，一个用于存储A的主键值，一个用于存储B的主键值

![图片][5dc03eb0e9cf4a0e499e07d5b247d91b.png]

####  ####

总结

*  范式就是设计数据库的通用规范。
 *  E-R图由 实体、属性、实体之间的联系构成，主要用来描述 数据库中表结构。

[b18244536f6a3502819d653bafe180b0.png]: https://img-blog.csdnimg.cn/img_convert/b18244536f6a3502819d653bafe180b0.png
[dbe7d707a5c656d279cdf963b44d8fe8.png]: https://img-blog.csdnimg.cn/img_convert/dbe7d707a5c656d279cdf963b44d8fe8.png
[ffc20a5cd58e56e385a483ab5720c632.png]: https://img-blog.csdnimg.cn/img_convert/ffc20a5cd58e56e385a483ab5720c632.png
[ab0e6a5cc84dbc5f57951347551ded6f.png]: https://img-blog.csdnimg.cn/img_convert/ab0e6a5cc84dbc5f57951347551ded6f.png
[1ad69e19cce7bc5eda445d0ff9906bae.png]: https://img-blog.csdnimg.cn/img_convert/1ad69e19cce7bc5eda445d0ff9906bae.png
[90637abccca65f6bc54bb2662d549820.png]: https://img-blog.csdnimg.cn/img_convert/90637abccca65f6bc54bb2662d549820.png
[205df47052cedafda20abd8dd4e72417.png]: https://img-blog.csdnimg.cn/img_convert/205df47052cedafda20abd8dd4e72417.png
[1f974f6b9e522859bb3c0799c36923af.png]: https://img-blog.csdnimg.cn/img_convert/1f974f6b9e522859bb3c0799c36923af.png
[120013747ade7bb4e92d78155bb186e5.png]: https://img-blog.csdnimg.cn/img_convert/120013747ade7bb4e92d78155bb186e5.png
[8a12ddf4092e50557227ee1f5b3b3456.png]: https://img-blog.csdnimg.cn/img_convert/8a12ddf4092e50557227ee1f5b3b3456.png
[5dc03eb0e9cf4a0e499e07d5b247d91b.png]: https://img-blog.csdnimg.cn/img_convert/5dc03eb0e9cf4a0e499e07d5b247d91b.png