| 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: | |
| 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 | |
[3 Dec 2015 14:05]
MySQL Verification Team
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]
MySQL Verification Team
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.
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)) {