| Bug #110205 | add prohibit sql list option for mysql parse | ||
|---|---|---|---|
| Submitted: | 24 Feb 2023 10:36 | Modified: | 2 Mar 2023 14:06 |
| Reporter: | bin wang (OCA) | Email Updates: | |
| Status: | Analyzing | Impact on me: | |
| Category: | MySQL Server: Options | Severity: | S4 (Feature request) |
| Version: | Ver 8.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | Options | ||
[24 Feb 2023 10:37]
bin wang
suggested add patch (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: feature.patch (application/octet-stream, text), 2.37 KiB.
[24 Feb 2023 10:54]
bin wang
fix:
[mysqld]
prohibit_sqlstr="(.*)"
it can use regular expression math to filter SQLs with specific keywords not premitted run in MySQL .
1:
mysql> show global variables like '%prohibit%';
+-----------------+---------------------------+
| Variable_name | Value |
+-----------------+---------------------------+
| prohibit_sqlstr | (.*)\\s(join)\\s(.*){4,} |
+-----------------+---------------------------+
1 row in set (0.02 sec)
#trigger prohibit sql
mysql> select a.*,b.*,c.*,d.* from a left join b on a.id=b.id left join c on a.id=b.id left join d on b.id=c.id left join d on b.id=c.id
ERROR 2013 (HY000): Lost connection to MySQL server during query
when SQL with four "join", the SQL is not promitted run .
[1 Mar 2023 12:50]
MySQL Verification Team
Hi Mr. wang, Thank you for your feature request. We already have so many options on limiting resource spending for each query, that your request seems excessive. If your features were implemented it would definitely cost the execution time of all queries, regardless if they prohibit reserved words or keywords. And all this just because you want a better control of your developers.
[1 Mar 2023 13:35]
MySQL Verification Team
Hi, It would be also helpful if you would list all the reserved words and keywords that you would like to be optionally prohibited. Hence, we would welcome a full and comprehensive patch !!!! Do you know that in most of the cases, for example JOIN, you would case syntax errors. Then, somebody from your company could complain on the system error ........ Do you have answers for all those questions ????
[2 Mar 2023 1:09]
bin wang
Thank you for your warm reply. This feature actually slow down few performance . I can add a disable/enable this feature option. When regardless of performance and need a SQL control ,can enable this feature. The first question , I think the amount of all the reserved words and the keywords may be is so large. It is impossible to list all words, So I add regular expression to as far as possible cover this requirement; And the second one , I can improve the error message part, When case syntax errors ,it will trully reply syntax errors nothing with system error. When the prohibit SQL ,it will trully reply the SQL question is controlled by DBA.
[2 Mar 2023 13:36]
MySQL Verification Team
Hi Mr. wang, We would truly require a full patch, as this is a highly complex request that would generally slowdown every single DDL or DML.
[2 Mar 2023 14:06]
MySQL Verification Team
Hi Mr. wang, We had a thorough discussions with Development team and it was decided that we have to wait on getting sufficient reasons to accept or reject your feature request? Regarding this particular feature request, we are skeptical, but we might accept a new request if you, Mr. wang, able to provide us with the patch and performance analysis of the impact of that patch. Hence, take your time and come back to this report when you are ready.
Description: 1:It is difficult for dba to control SQL developer use some very complex SQL such as many join tables . These SQLs may run long time or cause mysql oom with server low configuration. 2: SQL with some keywords is not premitted run because of security requirements. Above two problems, MySQL has no options to prohibit some keywords such as join\column_name ..... How to repeat: add a options named prohibit_sqlstr(defulat (.*) filter noting ) in my.cnf [mysqld] prohibit_sqlstr="(.*)" it can use regular expression math to filter SQLs with specific keywords not premitted run in MySQL . 1: mysql> show global variables like '%prohibit%'; +-----------------+---------------------------+ | Variable_name | Value | +-----------------+---------------------------+ | prohibit_sqlstr | (.*)\\s(ac)\\s(.*){4,} | +-----------------+---------------------------+ 1 row in set (0.02 sec) #trigger prohibit sql mysql> select a.*,b.*,c.*,d.* from a left join b on a.id=b.id left join c on a.id=b.id left join d on b.id=c.id left join d on b.id=c.id ERROR 2013 (HY000): Lost connection to MySQL server during query when SQL with four "join", the SQL is not promitted run . 2: mysql> show global variables like '%prohibit%'; +-----------------+---------------------------+ | Variable_name | Value | +-----------------+---------------------------+ | prohibit_sqlstr | (.*)password(.*) | +-----------------+---------------------------+ 1 row in set (0.02 sec) #trigger prohibit sql mysql> select password from test.user; ERROR 2013 (HY000): Lost connection to MySQL server during query when SQL with keyword "password", the SQL is not promitted run . and other flexible filter mode with regular expression Suggested fix: @@ -5198,6 +5199,15 @@ DBUG_EXECUTE_IF("parser_debug", turn_parser_debug_on();); + std::string str_prohibit=opt_prohibit_sqlstr; + std::string prohibit_sql= thd->query().str; + bool ret = std::regex_match(str_prohibit, prohibit_sql); + if (ret ) + { + DBUG_PRINT("find prohibit sqlist",("query: '%s'", thd->query().str)); + return ; + } + mysql_reset_thd_for_next_command(thd); // It is possible that rewritten query may not be empty (in case of // multiqueries). So reset it.