Edit routine UI incorrectly removes too many escape slashes

[Donkie]

Describe the bug

When trying to edit a routine that contains a string with multiple consecutive backslashes (which is needed for for example regexp commands), one slash will be missing for each group of slashes.

For example, when exporting my routine, I get something like:

...
SELECT REGEXP_REPLACE('Test: (Item)', 'Test: \\(([\\w]+)\\)', '\\1')
...

As it should be, but when editing the routine via the UI, I get:

SELECT REGEXP_REPLACE('Test: (Item)', 'Test: \(([\w]+)\)', '\1')

Thus, when saving this the pattern string will become invalid and the routine breaks.

To Reproduce

1. Create a procedure with the first command above
2. Try to edit the procedure using the UI

Expected behavior

I expected the double backslashes to stick around

Server configuration

• Operating system: Ubuntu 18.04
• Web server: nginx/1.14.0
• Database version: 10.3.14-MariaDB-1:10.3.14+maria~trusty-log
• PHP version: 7.1.30-1+ubuntu18.04.1+deb.sury.org+1
• phpMyAdmin version: 4.9.0.1

Client configuration

• Browser: Chrome 75.0.3770.100
• Operating system: Windows 10

[Tithugues]

Using command line, here are some things I identified. (Please note that I added a \n at the end of the 3rd parameter of REGEXP_REPLACE to have both cases: single / double slashes.)

Using show create procedure command returns:

MariaDB [my_db]> show create procedure test_slash;
+------------+-------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure  | sql_mode                                                                                  | Create Procedure                                                                      | character_set_client | collation_connection | Database Collation |
+------------+-------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| test_slash | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`%` PROCEDURE `test_slash`()
    NO SQL
SELECT REGEXP_REPLACE('Test: (Item)', 'Test: \\(([\\w]+)\\)', '\\1 \n') | utf8mb4              | utf8mb4_unicode_ci   | latin1_swedish_ci  |
+------------+-------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.001 sec)

Using select in ROUTINES returns this (no double slash):

MariaDB [my_db]> select ROUTINE_DEFINITION from INFORMATION_SCHEMA.ROUTINES where SPECIFIC_NAME='test_slash';
+---------------------------------------------------------------------+
| ROUTINE_DEFINITION                                                  |
+---------------------------------------------------------------------+
| SELECT REGEXP_REPLACE('Test: (Item)', 'Test: \(([\w]+)\)', '\1 \n') |
+---------------------------------------------------------------------+
1 row in set (0.003 sec)

So unfortunately, I think that relying on ROUTINES table won't let us fix this bug.

Nevertheless, the whole definition is retrieved from:

$this->dbi->getDefinition(
                $db,
                $routine['ROUTINE_TYPE'],
                $routine['SPECIFIC_NAME']
            )

So maybe we can extract the body from this.

@Team, if you know a way to do it, would it be please possible to share it?

Thank you.

[williamdes]

@Tithugues thank you for the research, I do not know how to fix it

[williamdes]

Again thank you so much @Tithugues for the debug details, I could fix this quite quickly with 42165e4

Could you please test this fix @Donkie ?