Bug #79496 mysqldump fails for VIEWs which has double quote (") in their name
Submitted: 2 Dec 2015 16:13 Modified: 7 Dec 2015 7:28
Reporter: Teodor Milkov Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S3 (Non-critical)
Version:5.6.27, 5.6.29, 5.7.11 OS:Any
Assigned to: CPU Architecture:Any

[2 Dec 2015 16:13] Teodor Milkov
Description:
mysqldump fails to work for VIEWs with names like: a"b

How to repeat:
Create view named a"b

Suggested fix:
--- client/mysqldump.c.orig  2015-11-03 06:14:08.000000000 -0500
+++ client/mysqldump.c       2015-12-02 07:05:48.000000000 -0500
@@ -6088,6 +6088,8 @@
   char       *result_table, *opt_quoted_table;
   char       table_buff[NAME_LEN*2+3];
   char       table_buff2[NAME_LEN*2+3];
+  char       table_name_buff[NAME_LEN*2+3];
+  char       db_name_buff[NAME_LEN*2+3];
   char       query[QUERY_LENGTH];
   FILE       *sql_file= md_result_file;
   DBUG_ENTER("get_view_structure");
@@ -6141,11 +6143,14 @@
   verbose_msg("-- Dropping the temporary view structure created\n");
   fprintf(sql_file, "/*!50001 DROP VIEW IF EXISTS %s*/;\n", opt_quoted_table);

+  mysql_real_escape_string(mysql, table_name_buff, table, strlen(table));
+  mysql_real_escape_string(mysql, db_name_buff, db, strlen(db));
+
   my_snprintf(query, sizeof(query),
               "SELECT CHECK_OPTION, DEFINER, SECURITY_TYPE, "
               "       CHARACTER_SET_CLIENT, COLLATION_CONNECTION "
               "FROM information_schema.views "
-              "WHERE table_name=\"%s\" AND table_schema=\"%s\"", table, db);
+              "WHERE table_name='%s' AND table_schema='%s'", table_name_buff, db_name_buff);

   if (mysql_query(mysql, query))
   {
[3 Dec 2015 14:05] Miguel Solorzano
Thank you for the bug report. Please provide the create view script and print here the mysqldump error. Thanks.
[4 Dec 2015 14:39] Teodor Milkov
Sorry, I was in a hurry. So, there are two incarnations of this bug:

# 1. The more apparent variant:

CREATE VIEW `v6-=~!@#$%^&*()_+[]{}'\"|,./<>?` AS SELECT `c1` AS `id` FROM `t1`;

SHOW CREATE VIEW `v6-=~!@#$%^&*()_+[]{}'\"|,./<>?`\G
*************************** 1. row ***************************
                View: v6-=~!@#$%^&*()_+[]{}'\"|,./<>?
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`zimage`@`localhost` SQL SECURITY DEFINER VIEW `v6-=~!@#$%^&*()_+[]{}'\"|,./<>?` AS select `t1`.`c1` AS `id` from `t1`

mysqldump zimage_db 'v6-=~!@#$%^&*()_+[]{}'\''\"|,./<>?' >/dev/null
mysqldump: Got error: 0:  when trying to save the result of SHOW CREATE TABLE in ds_view.

Here at least we know something went wrong.

# 2. The more subtle variant:

CREATE TABLE t1 (c1 INT);
CREATE VIEW `a"b` AS SELECT `c1` AS `id` FROM `t1`;

SHOW CREATE VIEW `a"b`\G
                View: a"b
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `a"b` AS select `t1`.`c1` AS `id` from `t1`

mysqldump zimage_db 'v6-=~!@#$%^&*()_+[]{}'\''\"|,./<>?' >/dev/null

No errors reported. But dumped text is shorter than it should be. If we run through strace we discover why:

recv(3, "\272\0\0\1\377(\4#42000You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'b\" AND table_schema=\"zimage_db\"' at line 1", 16384, 0) = 190

Please let me know if you need additional information.
[7 Dec 2015 7:28] Umesh Shastry
Thank you for the feedback.
Verified as described with 5.7.11/5.6.29.

-- 
-- 5.7.11, 5.6.29
.
/*!50001 DROP VIEW IF EXISTS `v6-=~!@#$%^&*()_+[]{}'\"|,./<>?`*/;
mysqldump: Got error: 0:  when trying to save the result of SHOW CREATE TABLE in ds_view.