[MDEV-20466] SHOW PROCESSLIST truncates query text on \0 bytes Created: 2019-09-02  Updated: 2020-05-11  Resolved: 2019-10-15

Status: Closed
Project: MariaDB Server
Component/s: OTHER
Affects Version/s: 5.5, 10.0, 10.1, 10.3.17, 10.4.7, 10.2, 10.3, 10.4
Fix Version/s: 10.2.28, 5.5.66, 10.1.42, 10.3.19, 10.4.9

Type: Bug Priority: Critical
Reporter: Hartmut Holzgraefe Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Problem/Incident
causes MDEV-21462 main.processlist_notembedded fails to... Closed
Relates
relates to MDEV-22513 main.processlist_notembedded fails in... Closed

 Description   

When executing a query with a NUL byte ('\0') in it, SHOW PROCESSLIST will just show the query up to that byte, hiding the rest of the query. In the slow query log, on the other hand,
the full query is shown, and the NUL byte encoded as '^@'.

This can be used by a malicious user to try to hide SQL injection attacks.

<?php
 
$db = mysqli_connect("127.0.0.1", "root", "", "test");
 
$query = "SELECT user FROM mysql.user WHERE user = 'root\0' OR SLEEP(100)";
 
$db->query($query);
 
?>


> show full processlist;
 
+----+-------------+-----------------+------+---------+------+--------------------------+------------------------------------------------+----------+
 
| Id | User        | Host            | db   | Command | Time | State                    | Info                                           | Progress |
 
+----+-------------+-----------------+------+---------+------+--------------------------+------------------------------------------------+----------+
 
[...]
 
| 17 | root        | localhost:41928 | NULL | Query   |    0 | Init                     | show full processlist                          |    0.000 |
 
| 18 | root        | localhost:41930 | test | Query   |    2 | User sleep               | SELECT user FROM mysql.user WHERE user = 'root |    0.000 |
 
+----+-------------+-----------------+------+---------+------+--------------------------+------------------------------------------------+----------+


Time                Id Command  Argument
 
# Time: 190902 12:22:03
 
# User@Host: root[root] @ localhost [127.0.0.1]
 
# Thread_id: 18  Schema: test  QC_hit: No
 
# Query_time: 300.028394  Lock_time: 0.001831  Rows_sent: 0  Rows_examined: 3
 
# Rows_affected: 0  Bytes_sent: 70
 
use test;
 
SET timestamp=1567419723;
 
SELECT user FROM mysql.user WHERE user = 'root^@' OR SLEEP(100);



 Comments   
Comment by Sergei Golubchik [ 2019-09-02 ]

I wonder, whether it's really SHOW PROCESSLIST, that truncates or mysql client itself?

Comment by Sergei Golubchik [ 2019-09-02 ]

and whether I_S.PROCESSLIST does the same. One can use HEX() on the Info column there

Comment by Alice Sherepa [ 2019-09-02 ] 

MariaDB [(none)]> select * from information_schema.processlist;
 
+----+------+-----------+------+---------+------+----------------------+----------------------------------------------------------------+------------+-------+-----------+----------+
 
| ID | USER | HOST      | DB   | COMMAND | TIME | STATE                | INFO                                                           | TIME_MS    | STAGE | MAX_STAGE | PROGRESS |
 
+----+------+-----------+------+---------+------+----------------------+----------------------------------------------------------------+------------+-------+-----------+----------+
 
|  4 | root | localhost | NULL | Query   |    0 | Filling schema table | select * from information_schema.processlist                   |      0.459 |     0 |         0 |    0.000 |
 
|  2 | root | localhost | test | Query   |  440 | User sleep           | SELECT user FROM mysql.user WHERE user = 'root ' OR SLEEP(100) | 440611.672 |     0 |         0 |    0.000 |
 
+----+------+-----------+------+---------+------+----------------------+----------------------------------------------------------------+------------+-------+-----------+----------+
 
2 rows in set (0.00 sec)
 
 
 
MariaDB [(none)]> show full processlist\G
 
*************************** 1. row ***************************
 
      Id: 2
 
    User: root
 
    Host: localhost
 
      db: test
 
 Command: Query
 
    Time: 459
 
   State: User sleep
 
    Info: SELECT user FROM mysql.user WHERE user = 'root
 
Progress: 0.000
 
*************************** 2. row ***************************
 
      Id: 4
 
    User: root
 
    Host: localhost
 
      db: NULL
 
 Command: Query
 
    Time: 0
 
   State: NULL
 
    Info: show full processlist
 
Progress: 0.000
 
2 rows in set (0.00 sec)

Comment by Hartmut Holzgraefe [ 2019-09-02 ] 

select hex(info) from information_schema.processlist;
 
 
 
53454C45435420757365722046524F4D206D7973716C2E757365722057484552452075736572203D2027726F6F740027204F5220534C4545502831303029
 
                                                                                            ^^

So it may indeed be a client side problem

Comment by Hartmut Holzgraefe [ 2019-09-02 ]

No, wireshark clearly shows that the query string is terminated after "root" on the sending side already:

    000002AB  02 32 34 04 72 6f 6f 74  0f 6c 6f 63 61 6c 68 6f   .24.root .localho
 
    000002BB  73 74 3a 34 32 35 35 32  04 74 65 73 74 05 51 75   st:42552 .test.Qu
 
    000002CB  65 72 79 02 32 30 0a 55  73 65 72 20 73 6c 65 65   ery.20.U ser slee
 
    000002DB  70 2e 53 45 4c 45 43 54  20 75 73 65 72 20 46 52   p.SELECT  user FR
 
    000002EB  4f 4d 20 6d 79 73 71 6c  2e 75 73 65 72 20 57 48   OM mysql .user WH
 
    000002FB  45 52 45 20 75 73 65 72  20 3d 20 27 72 6f 6f 74   ERE user  = 'root
 
    0000030B  05 30 2e 30 30 30 3d 00  00 12 02 32 36 04 72 6f   .0.000=. ...26.ro
 
    0000031B  6f 74 0f 6c 6f 63 61 6c  68 6f 73 74 3a 34 32 35   ot.local host:425
 
    0000032B  35 38 fb 05 51 75 65 72  79 01 30 04 49 6e 69 74   58..Quer y.0.Init
 
    0000033B  10 53 48 4f 57 20 50 52  4f 43 45 53 53 4c 49 53   .SHOW PR OCESSLIS
 
    0000034B  54 05 30 2e 30 30 30 05  00 00 13 fe 00 00 02 00   T.0.000. ........

Comment by Oleksandr Byelkin [ 2019-10-14 ]

correct test case for the bug without waiting (sleep(3) actually do not cause waiting, connection will just be closed)

connect (con1,localhost,root,,);
 
 
 
#select * from information_schema.processlist;
 
connection con1;
 
 
 
let $q= `select CONCAT("SELECT user FROM mysql.user WHERE user ='some", CHAR(0), "' or sleep (3)")`;
 
 
 
SET DEBUG_SYNC= 'before_join_optimize SIGNAL in_sync';
 
--send_eval $q;
 
 
 
connection default;
 
 
 
SET DEBUG_SYNC= 'now WAIT_FOR in_sync';
 
 
 
SHOW PROCESSLIST;
 
 
 
disconnect con1;
 
 
 
SET DEBUG_SYNC = 'RESET';

Generated at Thu Feb 08 08:59:41 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.