group by 用于结合合计函数,根据一个或多个列对结果集进行分组。

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
mysql> select id,username,password from demo_member;
+------+----------+----------------------------------+
| id | username | password |
+------+----------+----------------------------------+
| 1 | admin | 7fef6171469e80d32c0559f88b377245 |
| 2 | guest | e10adc3949ba59abbe56e057f20f883e |
+------+----------+----------------------------------+
2 rows in set (0.00 sec)

mysql> select id,username,password,count(*) as sum from demo_member group by password;
+------+----------+----------------------------------+-----+
| id | username | password | sum |
+------+----------+----------------------------------+-----+
| 1 | admin | 7fef6171469e80d32c0559f88b377245 | 1 |
| 2 | guest | e10adc3949ba59abbe56e057f20f883e | 1 |
+------+----------+----------------------------------+-----+
2 rows in set (0.00 sec)

mysql> insert into demo_member
-> values(3,"test","7fef6171469e80d32c0559f88b377245");
Query OK, 1 row affected (0.00 sec)

mysql> select id,username,password,count(*) as sum from demo_member group by password;
+------+----------+----------------------------------+-----+
| id | username | password | sum |
+------+----------+----------------------------------+-----+
| 1 | admin | 7fef6171469e80d32c0559f88b377245 | 2 |
| 2 | guest | e10adc3949ba59abbe56e057f20f883e | 1 |
+------+----------+----------------------------------+-----+
2 rows in set (0.00 sec)

mysql>

with rollup 对group by字段的汇总,会在group by 的字段上返回一个NULL的数据

1
2
3
4
5
6
7
8
9
10
11
mysql> select id,username,password from demo_member group by password with rollup;
+------+----------+----------------------------------+
| id | username | password |
+------+----------+----------------------------------+
| 1 | admin | 7fef6171469e80d32c0559f88b377245 |
| 2 | guest | e10adc3949ba59abbe56e057f20f883e |
| 2 | guest | NULL |
+------+----------+----------------------------------+
2 rows in set (0.00 sec)

mysql>

group by with rollup 示例

PHP Demo

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
<?php
header("Content-Type:text/html;charset=utf-8");
ini_set("display_errors","OFF");
$username=$_POST['username'];
$password=$_POST['password'];
$conn=mysqli_connect("localhost","root","root");
mysqli_query($conn,'use demo_group_by_with_rollup');
$sql = "SELECT * FROM demo_member WHERE username='{$username}'";
$query = mysqli_query($conn, $sql);
if(mysqli_num_rows($query)==1){
$result = mysqli_fetch_array($query);
if ($result['password'] == $password){
print_r("Success");
}else{
print_r("Password Error");
}
}else{
print_r("Error");
}
print_r('UserName:'.$username."<br />");
print_r("PassWord:".$password."<br />");
print_r('SQL语句:'.$sql."<br />");
?>

在不知道Password的情况下,进行注入。

首先SELECT * FROM member WHERE username=’admin’ group by password with rollup会产生一个 password 字段为 NULL 的数据
然后通过limit将这行 password 为 NULL 的数据取出来,if($result[$password]==$password) 及 NULL = NULL 结果为True。