MySQL Bugs: #25679: Denial-of-Service against entire server if FEDERATED engine is compiled

Bug #25679	Denial-of-Service against entire server if FEDERATED engine is compiled
Submitted:	17 Jan 2007 14:16	Modified:	31 Jul 2007 19:55
Reporter:	Philip Stoev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Federated storage engine	Severity:	S1 (Critical)
Version:	5.0.36-BK, 5.0.27-max; 5.1.14-beta	OS:	Linux (Linux)
Assigned to:	Antony Curtis	CPU Architecture:	Any
Tags:	DoS

Description:
This statement

CREATE TABLE test (id int) ENGINE=FEDERATED CONNECTION='mysql://root@host:port/test/test

Will hang forever if a listening process on host:port just accepts the connection and does nothing else. Along with that, all other clients will be unable to issue any queries against tables. Future clients that request a database on connect will not be able to connect.

This may be similar to bug #18287 however no timeout occurs and no connection to the same mysql instance or to localhost is involved.

How to repeat:
1. Open a listening port with netcat:

 nc -l 1234

2. Execute the following SQL

CREATE TABLE test (id int) ENGINE=FEDERATED CONNECTION='mysql://root@127.0.0.1:1234/test/test

3. Attempt to connect with another client to the mysql server using a database name. Your connection will hang (possibly forever)

4. Attempt to connect with another client without a database name. You will succeed however only non-data-related SQL can be issued, such as SHOW PROCESSLIST and SELECT NOW(). USE database and any real SELECT statement will hang forever.

Suggested fix:
It appears that a system-wide lock is set before doing a CREATE TABLE, on the premise that every CREATE TABLE will be short operation and the lock will be released soon. However, this is not true for the FEDERATED engine, which attempts to connect the remote deatabase upon a CREATE TABLE.

Also, it does not appear that any timeout or connection reset is triggered -- even after the remote port has closed the connection, CREATE TABLE remains hanging.

The connect_timeout parameter to the mysql client also does not work -- new clients attempting to connect remain hanging forever.

The reason the mysql client with a database name hangs on connect is that it attempts to issue a SHOW TABLES command which is the kind of statement that this bug causes to block indefinitely. This stage of the connection is not covered by the connect_timeout command-line variable.

5.1.14-beta is also affected, however the 30 second timeout kicks in and CREATE TABLE fails after 30 seconds. This is still a DoS since no other queries can be issued during those 30 seconds and the attacker can simply re-issue the CREATE TABLE statement every 30 seconds to keep the server irresponsive. The timeout can probably be circumvented if one does not use netcat, but rather a tool that partially completes the connection before hanging it.

Thank you for a problem report. Verified just as described on my SuSE Linux 9.3 with latest 5.0.36-BK. In one shell I executed:

openxs@suse:~/dbs/5.0> netcat -l -p 1234

and it still waits for something on that port.

In another shell:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.36 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table testfed(id int) engine=federated connection='mysql://root@1
27.0.0.1:1234/test/test';

and it hanged. In 3rd shell:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.36 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select count(*) from test;

and it hanged. 

But after 50 seconds netcat exited and statements in both mysql sessions were executed, with the following error message for CREATE TABLE:

ERROR 1429 (HY000): Unable to connect to foreign data source: database: 'test'
username: 'root'  hostname: '127.0.0.1'

So, this is verified S1 bug now.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/19142

ChangeSet@1.2390, 2007-01-31 15:35:57-08:00, antony@ppcg5.local +10 -0
  Bug#25679
    "Deadlock entire server with self-referencing FEDERATED"
    Deadlock caused by LOCK_open being held by federated table creation.
    Ensure that federated table is not accessible until after storage
    engine create_table() method completes with new HTON_SLOW_CREATE flag.
    Includes test for bug.

Hmm... I'll rework this - no need for storage engine to fiddle with mysqld internal mutexes

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/19145

ChangeSet@1.2390, 2007-01-31 16:07:55-08:00, antony@ppcg5.local +10 -0
  Bug#25679
    "Deadlock entire server with self-referencing FEDERATED"
    Deadlock caused by LOCK_open being held by federated table creation.
    Ensure that federated table is not accessible until after storage
    engine create_table() method completes with new HTON_SLOW_CREATE flag.
    Includes test for bug.

Thanks for the patch. Please note that "self-referencing" would be misleading -- deadlock can occur even if the MySQL server being contacted has nothing to do with the MySQL server initiating the connection.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/22044

ChangeSet@1.2482, 2007-03-15 10:37:48-07:00, acurtis@xiphis.org +9 -0
  Bug #25679 Denial-of-Service against entire server if FEDERATED engine is compiled
  
  Bug fixed:
  * Denial of service caused by LOCK_open mutex held during table creation operation
    when table creation requires network communication and remote server is timing out.
    This prevents all other sessions from opening any tables.
  
  This patch provides:
  * Name lock is acquired when creating a non-temporary table.
  * LOCK_open mutex is released when calling storage engine's ::create()/::open() method
  * Flag to indicate that storage engine still requires the LOCK_open mutex to be held.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/23618

ChangeSet@1.2551, 2007-04-02 21:49:53-07:00, acurtis@xiphis.org +10 -0
  Bug #25679 Denial-of-Service against entire server if FEDERATED engine is compiled
    
    Bug fixed:
    * Denial of service caused by LOCK_open mutex held during table creation operation
      when table creation requires network communication and remote server is timing out.
      This prevents all other sessions from opening any tables.
    
    This patch provides:
    * Name lock is acquired when creating a non-temporary table.
    * LOCK_open mutex is released when calling storage engine's ::create()/::open() method

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/30583

ChangeSet@1.2511, 2007-07-09 21:54:16-07:00, antony@ppcg5.local +4 -0
  Bug#25679
    "Federated Denial of Service"
    Federated storage engine used to attempt to open connections within
    the ::create() and ::open() methods which are invoked while LOCK_open
    mutex is being held by mysqld. As a result, no other connection can
    open tables during the network processing. Long DNS lookup times
    would stall mysqld's operation and a rogue connection string which
    connects to a remote server which simply stalls during handshake can
    stall mysqld for much longer periods of time.
    This patch moves the connection-opening much later to when LOCK_open
    mutex is not being held.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/31563

ChangeSet@1.2482, 2007-07-25 11:18:18-07:00, acurtis@xiphis.org +4 -0
  Bug#25679
    "Federated Denial of Service"
    Federated storage engine used to attempt to open connections within
    the ::create() and ::open() methods which are invoked while LOCK_open
    mutex is being held by mysqld. As a result, no other client sessions
    can open tables while Federated is attempting to open a connection.
    Long DNS lookup times would stall mysqld's operation and a rogue
    connection string which connects to a remote server which simply
    stalls during handshake can stall mysqld for a much longer period of
    time.
    This patch moves the opening of the connection much later, when the
    federated actually issues queries, by which time the LOCK_open mutex is
    no longer being held.

pushed into 5.0-engines and 5.1-engines

Created report Bug#30051 for the change in behavior from the result of this patch.

Pushed into 5.1.21-beta

Pushed into 5.0.48

Noted in 5.0.48, 5.1.21 changelogs.

The server was blocked from opening other tables while the FEDERATED 
engine was attempting to open a remote table.

I think we need also to document the change in behavior - federated engine doesn't check the correctness of the definition at the CREATE TABLE time. It only does it when the table is accesses, similar to how MERGE tables behave.

This is noted as Bug#30051, and will be changed back, when the internal locking code will be able to support it.

Added note about behavior change.

Bug #18287 create federated table always times out, error 1159 ' ' was marked a duplicate of this bug.