CODEWITHSUNDEEP
mysqlcountgroup-by
user1720897
user1720897

Reputation: 1246

Inconsistent/Strange behavior of MySQL when using the group by clause and count() function

Due to a bug(?) in MySQL the COUNT() function along with the GROUP BY clause can cause MySQL to leak out db details like the following -

mysql> select count(*), floor(rand()*2)x from users group by x;

ERROR 1062 (23000): Duplicate entry '1' for key 'group_key'

Sensitive details can be revealed here with a well crafted query. This is unexpected behavior, maybe a bug?

mysql> select count(*), floor(rand()*2)x from users group by x;

+----------+---+  
| count(*) | x |  
+----------+---+  
| 8        | 0 |  
| 5        | 1 |  
+----------+---+

2 rows in set (0.00 sec) <-- Sometimes the query runs without any errors(Expected behavior)

Does anyone know what exactly causes the MySQL error.

The test bed that I am using is this excellent resource - https://github.com/Audi-1/sqli-labs

Upvotes: 1

Views: 306

Answers (1)

pala_
pala_

Reputation: 9010

This looks to be a reported (and old!) bug: http://bugs.mysql.com/bug.php?id=58081

Description: A GROUP BY query returns this error under certain circumstances:

Duplicate entry '107374182410737418241' for key 'group_key'

'group_key' is not a real column name in the table. It looks like a name for the grouping column in the temporary table.

How to repeat: set names latin1; drop table if exists t1; create table t1(a int) engine=myisam; insert into t1 values (0),(0),(1),(0),(0); select count(*) from t1, t1 t2 group by insert('', t2.a, t1.a,(@@global.max_binlog_size));

ERROR 1062 (23000): Duplicate entry '107374182410737418241' for key 'group_key'

Comments indicate a suggested work around is to increase the available heap and temp table size:

The workaround i found is to increase the size of the tmp_table:

SET SESSION max_heap_table_size=536870912; SET SESSION tmp_table_size=536870912;

now my request work !

Or to check your available disk space

Upvotes: 1

Related Questions