SQL (Structured Query Language) flow limiting method and device in database, electronic equipment and storage medium

Abstract

The invention relates to an SQL flow limiting method and device in a database, electronic equipment and a storage medium, and the method comprises the steps that a flow limiting mode in flow limiting configuration is determined, the flow limiting mode comprises template flow limiting, token flow limiting and rule flow limiting, the template flow limiting is used for limiting the overall characteristics of an SQL statement, and the token flow limiting is used for limiting the overall characteristics of the SQL statement; the token current limit is used for limiting part of features of the SQL statement, and the rule current limit is used for limiting sub-operation of the SQL statement; and matching a target SQL statement obtained from a client based on the flow limiting mode, and preventing the target SQL statement from being executed by a database according to a matching success result. Stable and safe operation of the database is ensured.

Description

Technical Field

[0001] The present application relates to the field of database technology, and in particular to a method, device, electronic device and storage medium for limiting SQL flow in a database. Background Art

[0002] As the scale of enterprise business grows, distributed databases are increasingly used, and users have higher and higher requirements for database performance. In the process of using the database, various scenarios often occur, such as abnormal calls, which cause a sharp increase in the concurrency of a certain type of SQL, which will cause the performance of the database to deteriorate.

[0003] The SQL current limiting function means that users can set matching rules (generally called current limiting rules) for the SQL in question based on certain features of the SQL. Currently, the existing SQL current limiting methods are single and will affect the normal and safe operation of the database. Summary of the invention

[0004] The present application provides a method, device, electronic device and storage medium for limiting SQL flow in a database to solve the problem that the database operation is not safe and stable enough.

[0005] In a first aspect, the present application provides a method for limiting SQL flow in a database, the method comprising:

[0006] Determine a current limiting mode in the current limiting configuration, wherein the current limiting mode includes template current limiting, token current limiting and rule current limiting, the template current limiting is used to limit the overall characteristics of the SQL statement, the token current limiting is used to limit some characteristics of the SQL statement, and the rule current limiting is used to limit the sub-operations of the SQL statement;

[0007] The target SQL statement obtained from the client is matched based on the current limiting method, and the target SQL statement is prevented from being executed by the database according to a successful matching result.

[0008] Optionally, matching the target SQL statement obtained from the client based on the current limiting method, and preventing the target SQL statement from being executed by the database according to a successful matching result includes:

[0009] Determine the template SQL statement corresponding to the template current limiting, the token SQL statement corresponding to the token current limiting, and the rules in the rule current limiting;

[0010] Performing template matching on the target SQL statement and the template SQL statement by using a template matching method;

[0011] If the template matching fails, token matching is performed on the target SQL statement and the token SQL statement using a token matching method;

[0012] If the token matching fails, the target SQL statement is matched using the rule;

[0013] If the rule is matched successfully, the target SQL statement is prevented from being executed by the database.

[0014] Optionally, performing template matching on the target SQL statement and the template SQL statement in a template matching manner includes:

[0015] Fingerprinting the template SQL statement to generate a template SQL fingerprint and a template hash value;

[0016] The template hash value is used as the key, and the template SQL fingerprint is used as the value to store it in a Map data structure;

[0017] Parsing the target SQL statement to generate a target SQL fingerprint and a target hash value, and searching the Map data structure for the target SQL fingerprint corresponding to the target hash value;

[0018] If it can be found, it is determined that the template matching is successful; if it cannot be found, it is determined that the template matching fails.

[0019] Optionally, performing token matching on the target SQL statement and the token SQL statement in a token matching manner includes:

[0020] Parsing the target SQL statement to generate a target SQL fingerprint, and generating a target array according to the target SQL fingerprint;

[0021] Fingerprinting the token SQL statement to generate a token SQL fingerprint, and generating a token array according to the token SQL fingerprint;

[0022] If the length of the target array is greater than the length of the token array, matching the tokens in the target array with the tokens in the token array;

[0023] If all tokens in the token array are matched successfully, the output token matches successfully;

[0024] If not all tokens in the token array are matched successfully, the output token matching fails.

[0025] Optionally, the rules in the rule-based current limiting include: prohibiting full table query, prohibiting page query, limiting the number of JOINs, limiting deletion of table data, and limiting the number of EXISTS expressions.

[0026] Optionally, after performing template matching on the target SQL statement and the template SQL statement in a template matching manner, the method further includes:

[0027] If the template matches successfully, add one to the existing concurrency number to get the actual concurrency number;

[0028] If the existing concurrency reaches the maximum concurrency, the target SQL statement is prevented from being executed by the database;

[0029] If the existing concurrency does not reach the maximum concurrency, then add one to the existing concurrency.

[0030] Optionally, after token matching is performed on the target SQL statement and the token SQL statement in a token matching manner, the method further includes:

[0031] If the token matches successfully, add one to the existing concurrency number to get the actual concurrency number;

[0032] If the existing concurrency reaches the maximum concurrency, the target SQL statement is prevented from being executed by the database;

[0033] If the existing concurrency does not reach the maximum concurrency, then add one to the existing concurrency.

[0034] In a second aspect, the present application provides a device for limiting SQL flow in a database, wherein the device comprises:

[0035] A determination module, used to determine a current limiting mode in the current limiting configuration, wherein the current limiting mode includes template current limiting, token current limiting and rule current limiting, the template current limiting is used to limit the overall characteristics of the SQL statement, the token current limiting is used to limit some characteristics of the SQL statement, and the rule current limiting is used to limit the sub-operations of the SQL statement;

[0036] The matching module is used to match the target SQL statement obtained from the client based on the current limiting method, and prevent the target SQL statement from being executed by the database according to the successful matching result.

[0037] In a third aspect, the present application provides an electronic device comprising: at least one communication interface; at least one bus connected to the at least one communication interface; at least one processor connected to the at least one bus; and at least one memory connected to the at least one bus.

[0038] In a fourth aspect, the present application further provides a computer storage medium storing computer executable instructions, wherein the computer executable instructions are used to execute the method for SQL flow limiting in a database described in any one of the above items of the present application.

[0039] The above technical solution provided by the embodiment of the present application has the following advantages over the prior art: the present application provides three current limiting methods, which respectively limit the overall characteristics, partial characteristics and sub-operations of the SQL statement. The user can freely choose a specific current limiting method according to the actual scenario, and prevent the SQL statement from being executed by the database when the match is successful, thereby avoiding excessive database processing volume. By expanding the scope of the SQL statement current limiting method, the present application can limit multiple SQL types and ensure the stable and safe operation of the database. BRIEF DESCRIPTION OF THE DRAWINGS

[0040] The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the present application and, together with the description, serve to explain the principles of the present application.

[0041] In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, for ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative labor.

[0042] One or more embodiments are exemplarily described by pictures in the corresponding drawings, and these exemplified descriptions do not constitute limitations on the embodiments. Elements with the same reference numerals in the drawings represent similar elements, and unless otherwise stated, the figures in the drawings do not constitute proportional limitations.

[0043] Figure 1 A hardware structure diagram of SQL current limiting in a database provided in an embodiment of the present application;

[0044] Figure 2 A flow chart of a method for limiting SQL flow in a database provided in an embodiment of the present application;

[0045] Figure 3 A schematic diagram of the matching process of the template current limiting provided in the embodiment of the present application;

[0046] Figure 4 A schematic diagram of the matching process of token current limiting provided in an embodiment of the present application;

[0047] Figure 5 A schematic diagram of the matching process of the rule-based current limiting provided in the embodiment of the present application;

[0048] Figure 6 A schematic diagram of the overall process provided for the embodiment of the present application;

[0049] Figure 7 A schematic diagram of the structure of a SQL current limiting device in a database provided in an embodiment of the present application;

[0050] Figure 8 A schematic diagram of the structure of an electronic device provided in an embodiment of the present application. DETAILED DESCRIPTION

[0051] In order to make the purpose, technical solution and advantages of the embodiments of the present application clearer, the technical solution in the embodiments of the present application will be clearly and completely described below in conjunction with the drawings in the embodiments of the present application. Obviously, the described embodiments are part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without making creative work are within the scope of protection of this application.

[0052] The disclosure below provides many different embodiments or examples to realize the different structures of the present application. In order to simplify the disclosure of the present application, the parts and settings of specific examples are described below. Of course, they are only examples, and the purpose is not to limit the present application. In addition, the present application can repeat reference numbers and / or letters in different examples. This repetition is for the purpose of simplification and clarity, and does not itself indicate the relationship between the various embodiments and / or settings discussed.

[0053] In order to solve the problems mentioned in the background technology, according to one aspect of the embodiments of the present application, an embodiment of a method for limiting SQL flow in a database is provided.

[0054] Optionally, in the embodiment of the present application, the above-mentioned method of limiting SQL flow in the database can be applied to Figure 1 In the hardware environment shown in FIG. 1 , a client 101, a database middleware 103 and a server 105 are provided. Figure 1 As shown, the database middleware 103 is connected to the database server 105 via a proxy server. The database middleware 103 is connected to the client 101 through a network and can be used to limit the flow of SQL statements sent by the client. The above network includes but is not limited to: a wide area network, a metropolitan area network or a local area network.

[0055] A method for limiting SQL flow in a database in an embodiment of the present application can be executed by the database middleware 103.

[0056] An embodiment of the present application provides a method for limiting SQL flow in a database, which can be applied to database middleware to improve the stability and security of the database.

[0057] The following will be combined with a specific implementation method to describe in detail a method for limiting SQL flow in a database provided by an embodiment of the present application. Figure 2 As shown, the specific steps are as follows:

[0058] Step 201: Determine the current limiting method in the current limiting configuration, where the current limiting methods include template current limiting, token current limiting and rule current limiting. Template current limiting is used to limit the overall characteristics of SQL statements, token current limiting is used to limit partial characteristics of SQL statements, and rule current limiting is used to limit sub-operations of SQL statements.

[0059] Step 202: Match the target SQL statement obtained from the client based on the current limiting method, and prevent the target SQL statement from being executed by the database according to the successful matching result.

[0060] The user configures the current limiting information on the current limiting management page. The current limiting information includes the current limiting method, SQL statement, and the maximum concurrency of SQL. The current limiting method refers to the matching method used when judging whether the client SQL is a current limiting SQL. There are three types of current limiting methods: template current limiting, token current limiting, and rule current limiting.

[0061] Template throttling is used to limit the overall features of SQL statements. It is a throttling method at the SQL syntax level. For example, given an SQL statement SQL1: "select id from db1.t1 where id=1", the corresponding generated SQL template is: "select`id`from`db1`.`t1`where`id`=?". When the SQL statements that need to be throttled are all point-check SQL statements such as SQL1, you can use an SQL template to throttle SQL statements of the SQL1 type.

[0062] Token limiting is used to limit some features of SQL statements. It is a limiting method at the SQL lexical level. Each part of the fingerprint SQL statement generated by token limiting is distinguished by "~" (or other symbols), and the generated template is: "select~`id`~from~`db1`~`t1`~where~`id`~=~?". In token limiting, tokens are composed of non-literal tokens recognized in the abstract syntax tree in SQL. As long as the client SQL is longer than the fingerprint SQL statement of token limiting and completely matches the fingerprint SQL statement of token limiting, it means that the limiting is successful. Token limiting has two relatively flexible advantages. One is that it can meet the demand of limiting only some values, such as a product, organization or a query condition; the other is that it can limit only some features of the SQL statement and ignore other parts.

[0063] Template current limiting and token current limiting can cope with high-concurrency types of SQL. High-concurrency types of SQL refer to situations where a large number of users simultaneously access the database and execute SQL operations within a specific time period. The characteristic of this type of SQL is that a large number of query requests are generated in a short period of time, which may cause a sharp increase in the database server load. This application can control the execution frequency and resource usage of this type of SQL through template current limiting and token current limiting.

[0064] Template current limiting and token current limiting only match on word strings, so it is difficult to limit the sub-operations of SQL statements. For example, if you want to limit SQL containing 5 join tables, template current limiting and token current limiting can only recognize the word join, and it is difficult to recognize the number of tables. Therefore, the embodiment of the present application also proposes a current limiting method of rule current limiting, which is used to perform current limiting SQL processing on SQL statements containing certain specific SQL sub-operations. Some categories of SQL sub-operations include: adding, deleting, and modifying tables, checking fields, expressions, functions, join tables, and exists (EXISTS is a statement in the SQL language used to check whether a subquery returns at least one row of data).

[0065] Rule-based flow control can handle high-resource-consuming SQL operations, which refer to SQL operations that consume a large amount of database resources during execution. Such SQL operations usually involve a large amount of computing, storage, and network transmission operations, such as full table scans, large-scale data export and import, and complex calculation expressions, which may have a significant impact on the overall performance of the database system. Rule-based flow control can manage the execution of such SQL operations by limiting the sub-operations of SQL.

[0066] The client sends the target SQL statement to the database middleware, and then uses the above-mentioned current limiting method to match the target SQL statement. If the match is successful, it indicates that the target SQL statement needs to be restricted and the target SQL statement cannot be transmitted to the database to perform the corresponding operation.

[0067] This application provides three current limiting methods, which limit the overall characteristics, partial characteristics and sub-operations of SQL statements respectively. Users can freely choose a specific current limiting method according to the actual scenario, and prevent the SQL statement from being executed by the database when the match is successful, so as to avoid excessive database processing volume. By expanding the scope of SQL statement current limiting methods, this application can limit multiple SQL types and ensure the stable and secure operation of the database.

[0068] In addition, the SQL current limiting function of the present application is deployed in the database middleware, which can access the database more flexibly and reduce the pressure on the database.

[0069] As an optional implementation method, the target SQL statement obtained from the client is matched based on the current limiting method, and the specific process of preventing the target SQL statement from being executed by the database according to the successful matching result is: determine the template SQL statement corresponding to the template current limiting, the token SQL statement corresponding to the token current limiting, and the rule in the rule current limiting; use the template matching method to perform template matching on the target SQL statement and the template SQL statement; if the template matching fails, use the token matching method to perform token matching on the target SQL statement and the token SQL statement; if the token matching fails, use the rule to perform rule matching on the target SQL statement; if the rule matching is successful, the target SQL statement is prevented from being executed by the database.

[0070] Specifically, after the user configures the current limiting method on the current limiting management page, the template SQL statement corresponding to the template current limiting, the template SQL statement corresponding to the token current limiting, and the rules in the rule current limiting will be automatically generated, and the database middleware obtains the template SQL statement, template SQL statement, and multiple rules.

[0071] First, the target SQL statement and the template SQL statement are matched by template matching. If the template matching is successful, it is determined whether the current concurrency has reached the maximum concurrency. If the maximum concurrency has been reached, it indicates that the number of queries that the database system can process at the same time has reached the maximum value. At this time, even if there are unprocessed SQL statements, they cannot be executed any more. At this time, the database system will no longer execute new SQLs sent by the client (that comply with the current limiting rules), thereby ensuring the stability of database operation. At the same time, the database middleware returns an error message to the client, informing the client to suspend sending SQL statements. If the maximum concurrency has not been reached, one is added to the existing concurrency.

[0072] If the template matching fails, the target SQL statement and the token SQL statement are matched by token matching. If the token matching succeeds, it is determined whether the current concurrency has reached the maximum concurrency. If the maximum concurrency has been reached, an error message is returned to prevent the target SQL statement from being executed by the database. If the maximum concurrency has not been reached, one is added to the existing concurrency.

[0073] If the token matching fails, the target SQL statement is matched with the rule. If the rule matching succeeds, an error message is returned, thereby preventing the target SQL statement from being executed by the database. If the rule matching fails, the target SQL statement will be executed by the database.

[0074] Among them, each target SQL statement corresponds to a configuration condition. For different SQL statements, users can configure at least one of the current limiting methods among template current limiting, token current limiting and rule current limiting. For example, if the user does not configure the template current limiting of the target SQL statement, then the SQL statement will inevitably fail when the template is matched. Therefore, if the SQL statement fails to match, there are two situations, one is that the current limiting method has been configured but the SQL statement cannot be matched successfully, and the other is that the background does not match the current limiting method, but no matter what the reason is, the above three current limiting processes need to be executed.

[0075] This application proposes three flow limiting methods: template flow limiting, token flow limiting and rule flow limiting: Template flow limiting means that the program extracts a SQL template for subsequent SQL matching after fingerprinting (formatting) the input SQL; Token flow limiting means that the program identifies a token list from SQL, and the user can flexibly remove or add tokens as needed, and finally this token list will be used for subsequent SQL matching; Rule flow limiting means that the sub-grammar item type and the number of grammar items of SQL can be set to achieve the purpose of flow limiting. The following describes these three flow limiting methods in detail.

[0076] 1. Template current limiting.

[0077] As an optional implementation, matching the target SQL statement with the template SQL statement by template matching includes: fingerprinting the template SQL statement to generate a template SQL fingerprint and a template hash value; storing the fingerprint in a Map data structure with the template hash value as the key and the template SQL fingerprint as the value; parsing the target SQL statement to generate a target SQL fingerprint and a target hash value, and searching for the target SQL fingerprint corresponding to the target hash value in the Map data structure; if the fingerprint can be found, it is determined that the match is successful, and if the fingerprint cannot be found, it is determined that the match fails.

[0078] After the database middleware fingerprints (formats) the input SQL, it extracts a template SQL statement for subsequent SQL matching. The specific process is: first, the alias of the table in the SQL is restored to the original table name, then the SQL is fingerprinted, and then the literals in the SQL are converted into placeholders, replacing the literals in the EXISTS expression, etc. Finally, a unique template SQL fingerprint is generated, and the template hash value is calculated based on the template SQL fingerprint, and the template hash value is used as the key and the template SQL fingerprint is used as the value to store it in the Map data structure.

[0079] The database middleware parses the target SQL statement according to the template matching method to generate the target SQL fingerprint and target hash value. The database middleware searches for the target SQL fingerprint corresponding to the target hash value in the Map data structure. If a matching record is found, it means that the SQL meets the current limiting conditions; otherwise, SQL current limiting is not performed.

[0080] Figure 3 The schematic diagram of the matching process of template current limiting in this application includes the following steps.

[0081] Step 301: The template SQL fingerprint information and the calculated hash value are stored in sfTemplateMap, and the target SQL fingerprint and the calculated hash value are stored in sqIFlowMatchInfo.

[0082] Step 302: Matching is performed using the formula sfTemplateMap[SQLFlowMatchInfo.TemplateHash]. If the template is found, the match is successful, otherwise the match fails.

[0083] In the template current limiting, the application stores the current limiting SQL fingerprint and hash value in a Map, which can improve the efficiency and accuracy of the current limiting operation, and ensure fast and stable query performance by taking advantage of the Map data structure.

[0084] 2. Token current limiting

[0085] As an optional implementation, matching the target SQL statement and the token SQL statement by token matching includes: parsing the target SQL statement to generate a target SQL fingerprint, and generating a target array based on the target SQL fingerprint; fingerprinting the token SQL statement to generate a token SQL fingerprint, and generating a token array based on the token SQL fingerprint; if the length of the target array is greater than the length of the token array, matching the tokens in the target array with the tokens in the token array; if all the tokens in the token array are matched successfully, outputting a successful match; if not all the tokens in the token array are matched successfully, outputting a failed match.

[0086] The database middleware parses the target SQL statement according to the template matching method, generates a target SQL fingerprint, and then generates a target array based on the target SQL fingerprint. Similarly, the database middleware fingerprints the token SQL statement to generate a token SQL fingerprint, and generates a token array based on the token SQL fingerprint.

[0087] The database middleware compares the length of the target array and the length of the token array. If the length of the target array is longer, it indicates that the length of the target SQL statement is greater than the length of the token SQL statement. In this way, the token SQL statement can be matched as a SQL partial feature; if the length of the target array is shorter, a matching failure result is output.

[0088] The database middleware continues to match the tokens in the target array and the tokens in the token array. If all the tokens in the token array are matched successfully, it means that the target SQL statement covers all the contents of the token SQL statement, and the token SQL statement belongs to some features in the target SQL statement. At this time, the match success result is output; if all the tokens in the token array cannot be matched, it means that the target SQL statement and the token SQL statement cannot be completely matched, and the match failure result is output here.

[0089] Figure 4 The following is a flow chart of the token flow control in this application, which includes the following steps.

[0090] Step 401: The token array keywords generated by the current limiting management page matches the array tokens generated by the client SOL.

[0091] Step 402: Before matching, first determine the lengths of the two arrays. If len(tokens)>len(keywordsMatch), then enter the matching process. Otherwise, directly return that the matching failed.

[0092] Step 403: Match the tokens in the keywordsMatch and tokens arrays. If all the tokens in keywordsMatch match, output a successful match, otherwise output a failed match.

[0093] This application proposes a more flexible token limiting method. Token limiting refers to identifying a token list from SQL. Users can flexibly remove or add tokens as needed. Ultimately, this token list will be used for subsequent SQL matching. On the one hand, token limiting can only limit some values, such as a product, organization, or a query condition; on the other hand, it can only target some features determined in the SQL statement, and the rest can be ignored.

[0094] 3. Rules to limit flow.

[0095] As an optional implementation method, the rules in the rule-based current limiting include: prohibiting full table query, prohibiting page query, limiting the number of JOINs, limiting deletion of table data, and limiting the number of EXISTS expressions.

[0096] The rules include the following.

[0097] 1) Disable full table query

[0098] Since full table query needs to read every row of data in the table, the performance is low, so it needs to be controlled.

[0099] Full table query is prohibited, that is, select * from t1; * means selecting all columns in the table.

[0100] Method: Detect that a SQL statement contains *, that is, full table query, and restrict it.

[0101] 2) Prohibit page turning query

[0102] Paging query refers to using OFFSET and LIMIT clauses to retrieve paging data in the database. However, as OFFSET increases, query performance will drop sharply, so paging query also needs to be restricted.

[0103] Page query, that is, select id from t1 offset 10limit 10; this query means skipping the 10 data records on page 1 (offset 10) and returning the next 10 (limit 10) id records.

[0104] Method: Keyword detection. If the limit or offset keyword is detected, it is considered as a page turning query.

[0105] 3) Limit the number of JOINs

[0106] In SQL statements, too many JOINs will make the query difficult to understand and maintain, and may also lead to poor performance, so the number of JOINs needs to be limited.

[0107] select t1.id from t1 join t2 on t1.id=t2.id; This query returns all id field values ​​in table t1 whose id field values ​​in tables t1 and t2 are equal.

[0108] Method: Detect that the SQL contains the join keyword, and then count the number of tables involved in the join. For example, in this SQL, t1 join t2 involves two tables, t1 and t2.

[0109] 4) DROP internal security

[0110] In the daily operation and maintenance of the database, directly deleting a table may lead to data loss, so this method sets three ways to protect the database. The first is the none level, that is, the table can be deleted directly; the second is the error level, when deleting a table, a deletion failure error is returned; the third is the rewrite level, which "hides" the table by renaming it, making the database more secure.

[0111] Database name: test

[0112] Table name: t1

[0113] drop test;

[0114] drop t1;

[0115] Method: drop means drop keyword detection.

[0116] 5) Limit the number of EXISTS expressions

[0117] To prevent the query from being too complex and degrading database performance, the number of EXISTS expressions is limited.

[0118] select name from t1 where exists(select 1from t2 where t2.id>5)andexists(select 1from t3 where t3.id>10);

[0119] This SQL statement indicates that when there is at least one record that meets the conditions in both tables t2 and t3, the record of the name field in t1 is returned.

[0120] Method: Detect the number of exists in a SQL statement. For example, this SQL statement contains two exists expressions.

[0121] This application allows users to form a rule by customizing a set of SQL sub-operation types. When the client's target SQL statement meets the rule, it is judged as a current-limited SQL. Some categories of SQL sub-operations: adding, deleting, and modifying tables, searching fields, expressions, functions, join tables, and exists. For example: when you want to limit the number of fields in the query table in SQL (the number is recorded as n), you can add a rule: {search fields, number of fields = n}, where n is configurable; after adding the rule, the database middleware will parse the SQL and collect the information of the SQL sub-operations, and then match this information with the set current-limiting rules. If the match is successful, it will be identified as the SQL to be limited. In addition to user customization, this application already supports the following rule entries, and each entry can be enabled and disabled through the corresponding switch (execute SQL: set x = [on|off] of SQL current limiting in the database; x of SQL current limiting in the database is the name of the corresponding switch).

[0122] Figure 5 The following is a schematic diagram of the matching process of the rule-based current limiting in this application, including the following steps.

[0123] Step 501: Obtain the target SQL statement sent by the client.

[0124] Step 502: Matching is performed according to rules such as prohibiting full table query, prohibiting page query, and limiting the number of JOINs.

[0125] Step 503: If there is a rule match, the match is successful and the next step of current limiting operation is performed; otherwise, the database is notified to execute SQL.

[0126] The rule-based flow limiting method provided in this application can edit the sub-grammar items and the number of grammar items of SQL to limit the flow of SQL that does not meet the data operation specifications, especially to limit sub-operations of high resource consumption types, to ensure the stability and security of the database.

[0127] Figure 6 It is a schematic diagram of the overall process in this application. It can be seen that it includes the following steps.

[0128] Step 1. The database middleware obtains the target SQL statement sent by the client.

[0129] Step 2: Parse the target SQL statement.

[0130] Step 3. Generate target SQL fingerprint and target hash value.

[0131] Step 4: Determine whether the template current limiting match is successful. If successful, proceed to step 7; if failed, proceed to step 5.

[0132] Step 5: Determine whether the token current limit match is successful. If successful, proceed to step 7; if failed, proceed to step 6.

[0133] Step 6: Determine whether the rule current limiting match is successful. If successful, proceed to step 8; if failed, proceed to step 9.

[0134] Step 7. Determine whether the actual number of concurrent requests exceeds the maximum number of concurrent requests. If so, execute step 8; otherwise, update the number of concurrent requests cumulatively and execute step 9.

[0135] Step 8. Return error information to the client.

[0136] Step 9. Notify the database to execute SQL.

[0137] This application adopts a flexible current limiting method to meet the diverse SQL current limiting functions; implements multiple current limiting methods through a current limiting tool to save current limiting costs; improves the efficiency and accuracy of SQL current limiting matching to ensure safe and stable operation of the database.

[0138] The SQL statement current limiting in this application includes the following four steps.

[0139] In the first step, users configure the current limiting information on the current limiting management page, including: current limiting method, SQL statement, and maximum concurrent SQL.

[0140] The second step is to obtain the target SQL statement sent by the client.

[0141] The third step is to parse the target SQL statement and generate the target SQL fingerprint corresponding to the template current limiting method and the token current limiting method, as well as the hash value calculated by each fingerprint.

[0142] The fourth step is to compare the hash value of the target SQL fingerprint with the template SQL fingerprint. If the template matches successfully, it is determined whether the current SQL concurrency reaches the preset maximum concurrency. If not, the cumulative concurrency is updated. If so, the SQL is blocked from execution and an error message is returned. If the template matches unsuccessfully, token matching is performed. A token array is generated based on the target SQL fingerprint and the token SQL fingerprint. If all tokens configured with current limiting are matched successfully, it is determined whether the current SQL concurrency reaches the preset maximum concurrency. If not, the cumulative concurrency is updated. If so, the SQL is blocked from execution and an error message is returned. If the token matches unsuccessfully, rule matching is performed. If the match succeeds, the SQL is blocked from execution and an error message is returned. If the match fails, the SQL is executed.

[0143] Based on the same technical concept, the present application provides a device for limiting SQL flow in a database, such as Figure 7 As shown, the device comprises:

[0144] A determination module 701 is used to determine a current limiting mode in the current limiting configuration, wherein the current limiting mode includes template current limiting, token current limiting and rule current limiting, the template current limiting is used to limit the overall characteristics of the SQL statement, the token current limiting is used to limit some characteristics of the SQL statement, and the rule current limiting is used to limit the sub-operations of the SQL statement;

[0145] The matching module 702 is used to match the target SQL statement obtained from the client based on the current limiting method, and prevent the target SQL statement from being executed by the database according to the successful matching result.

[0146] Optionally, matching the target SQL statement obtained from the client based on the current limiting method, and preventing the target SQL statement from being executed by the database according to the matching success result includes:

[0147] Determine the template SQL statement corresponding to the template current limiting, the token SQL statement corresponding to the token current limiting, and the rules in the rule current limiting;

[0148] The template matching method is used to perform template matching between the target SQL statement and the template SQL statement;

[0149] If the template matching fails, the target SQL statement and the token SQL statement are token matched using the token matching method;

[0150] If the token matching fails, the target SQL statement is matched by rules;

[0151] If the rule matches successfully, the target SQL statement is prevented from being executed by the database.

[0152] Optionally, performing template matching on the target SQL statement and the template SQL statement in a template matching manner includes:

[0153] Fingerprint the template SQL statement to generate the template SQL fingerprint and template hash value;

[0154] The template hash value is used as the key and the template SQL fingerprint is used as the value to store it in the Map data structure;

[0155] Parse the target SQL statement to generate the target SQL fingerprint and target hash value, and search for the target SQL fingerprint corresponding to the target hash value in the Map data structure;

[0156] If it can be found, it is determined that the template matching is successful; if it cannot be found, it is determined that the template matching fails.

[0157] Optionally, performing token matching on the target SQL statement and the token SQL statement in a token matching manner includes:

[0158] Parse the target SQL statement to generate a target SQL fingerprint, and generate a target array based on the target SQL fingerprint;

[0159] Fingerprinting the token SQL statement generates a token SQL fingerprint, and generating a token array according to the token SQL fingerprint;

[0160] If the length of the target array is greater than the length of the token array, the tokens in the target array are matched with the tokens in the token array;

[0161] If all tokens in the token array are matched successfully, the output token matches successfully;

[0162] If not all tokens in the token array are matched successfully, the output token matching fails.

[0163] Optionally, the rules in the rule-based current limiting include: prohibiting full table query, prohibiting page query, limiting the number of JOINs, limiting deletion of table data, and limiting the number of EXISTS expressions.

[0164] Optionally, after performing template matching on the target SQL statement and the template SQL statement in a template matching manner, the method further includes:

[0165] If the template matches successfully, the current concurrent volume is obtained;

[0166] If the existing concurrency reaches the maximum concurrency, the target SQL statement is prevented from being executed by the database;

[0167] If the existing concurrency does not reach the maximum concurrency, add one to the existing concurrency.

[0168] Optionally, after token matching is performed on the target SQL statement and the token SQL statement in a token matching manner, the method further includes:

[0169] If the token matches successfully, add one to the existing concurrency number to get the actual concurrency number;

[0170] If the existing concurrency reaches the maximum concurrency, the target SQL statement is prevented from being executed by the database;

[0171] If the existing concurrency does not reach the maximum concurrency, add one to the existing concurrency.

[0172] like Figure 8 As shown, an embodiment of the present application provides an electronic device, including a processor 801, a communication interface 802, a memory 803 and a communication bus 804, wherein the processor 801, the communication interface 802, and the memory 803 communicate with each other through the communication bus 804.

[0173] The memory 803 is used to store computer programs.

[0174] In one embodiment of the present application, the processor 801 is used to execute the program stored in the memory 803 to implement the method for limiting SQL flow in the database provided by any of the aforementioned method embodiments, including:

[0175] Determine the current limiting mode in the current limiting configuration, where the current limiting modes include template current limiting, token current limiting and rule current limiting. Template current limiting is used to limit the overall characteristics of SQL statements, token current limiting is used to limit partial characteristics of SQL statements, and rule current limiting is used to limit sub-operations of SQL statements;

[0176] The target SQL statement obtained from the client is matched based on the current limiting method, and the target SQL statement is prevented from being executed by the database according to the matching success result.

[0177] Optionally, matching the target SQL statement obtained from the client based on the current limiting method, and preventing the target SQL statement from being executed by the database according to the matching success result includes:

[0178] Determine the template SQL statement corresponding to the template current limiting, the token SQL statement corresponding to the token current limiting, and the rules in the rule current limiting;

[0179] The template matching method is used to perform template matching between the target SQL statement and the template SQL statement;

[0180] If the template matching fails, the target SQL statement and the token SQL statement are token matched using the token matching method;

[0181] If the token matching fails, the target SQL statement is matched by rules;

[0182] If the rule matches successfully, the target SQL statement is prevented from being executed by the database.

[0183] Optionally, performing template matching on the target SQL statement and the template SQL statement in a template matching manner includes:

[0184] Fingerprint the template SQL statement to generate the template SQL fingerprint and template hash value;

[0185] The template hash value is used as the key and the template SQL fingerprint is used as the value to store it in the Map data structure;

[0186] Parse the target SQL statement to generate the target SQL fingerprint and target hash value, and search for the target SQL fingerprint corresponding to the target hash value in the Map data structure;

[0187] If it can be found, it is determined that the template matching is successful; if it cannot be found, it is determined that the template matching fails.

[0188] Optionally, performing token matching on the target SQL statement and the token SQL statement in a token matching manner includes:

[0189] Parse the target SQL statement to generate a target SQL fingerprint, and generate a target array based on the target SQL fingerprint;

[0190] Fingerprinting the token SQL statement generates a token SQL fingerprint, and generating a token array according to the token SQL fingerprint;

[0191] If the length of the target array is greater than the length of the token array, the tokens in the target array are matched with the tokens in the token array;

[0192] If all tokens in the token array are matched successfully, the output token matches successfully;

[0193] If not all tokens in the token array are matched successfully, the output token matching fails.

[0194] Optionally, the rules in the rule-based current limiting include: prohibiting full table query, prohibiting page query, limiting the number of JOINs, limiting deletion of table data, and limiting the number of EXISTS expressions.

[0195] Optionally, after performing template matching on the target SQL statement and the template SQL statement in a template matching manner, the method further includes:

[0196] If the template matches successfully, add one to the existing concurrency number to get the actual concurrency number;

[0197] If the existing concurrency reaches the maximum concurrency, the target SQL statement is prevented from being executed by the database;

[0198] If the existing concurrency does not reach the maximum concurrency, add one to the existing concurrency.

[0199] Optionally, after token matching is performed on the target SQL statement and the token SQL statement in a token matching manner, the method further includes:

[0200] If the token matches successfully, add one to the existing concurrency number to get the actual concurrency number;

[0201] If the existing concurrency reaches the maximum concurrency, the target SQL statement is prevented from being executed by the database;

[0202] If the existing concurrency does not reach the maximum concurrency, add one to the existing concurrency.

[0203] An embodiment of the present application further provides a computer-readable storage medium having a computer program stored thereon. When the computer program is executed by a processor, the steps of the method for limiting SQL flow in a database provided in any of the aforementioned method embodiments are implemented.

[0204] The device embodiments described above are merely illustrative, wherein the units described as separate components may or may not be physically separated, and the components shown as units may or may not be physical units, that is, they may be located in one place or distributed on multiple network units. Some or all of the modules may be selected according to actual needs to achieve the purpose of the solution of this embodiment.

[0205] Through the description of the above implementation methods, those skilled in the art can clearly understand that each implementation method can be implemented by means of software plus a general hardware platform, and of course, by hardware. Based on this understanding, the above technical solution is essentially or the part that contributes to the relevant technology can be embodied in the form of a software product, and the computer software product can be stored in a computer-readable storage medium, such as ROM / RAM, a disk, an optical disk, etc., including a number of instructions for a computer device (which can be a personal computer, a server, or a network device, etc.) to execute the methods described in each embodiment or some parts of the embodiments.

[0206] It should be understood that the terms used herein are only for the purpose of describing specific example embodiments and are not intended to be limiting. Unless the context clearly indicates otherwise, the singular forms "one", "an" and "said" as used herein may also be meant to include plural forms. The terms "include", "comprise", "contain", and "have" are inclusive, and therefore specify the existence of stated features, steps, operations, elements and / or parts, but do not exclude the existence or addition of one or more other features, steps, operations, elements, parts, and / or combinations thereof. The method steps, processes, and operations described herein are not interpreted as necessarily requiring them to be performed in the specific order described or illustrated, unless the execution order is clearly indicated. It should also be understood that additional or alternative steps may be used.

[0207] The above description is only a specific implementation of the present application, so that those skilled in the art can understand or implement the present application. Various modifications to these embodiments will be apparent to those skilled in the art, and the general principles defined herein can be implemented in other embodiments without departing from the spirit or scope of the present application. Therefore, the present application will not be limited to the embodiments shown herein, but will conform to the widest range consistent with the principles and novel features applied for herein.

Claims

1. A method for limiting SQL flow in a database, characterized in that: The method comprises: Determine a current limiting mode in the current limiting configuration, wherein the current limiting mode includes template current limiting, token current limiting and rule current limiting, the template current limiting is used to limit the overall characteristics of the SQL statement, the token current limiting is used to limit some characteristics of the SQL statement, and the rule current limiting is used to limit the sub-operations of the SQL statement; The target SQL statement obtained from the client is matched based on the current limiting method, and the target SQL statement is prevented from being executed by the database according to a successful matching result.

2. The method according to claim 1, characterized in that: Matching the target SQL statement obtained from the client based on the current limiting method, and preventing the target SQL statement from being executed by the database according to the successful matching result includes: Determine the template SQL statement corresponding to the template current limiting, the token SQL statement corresponding to the token current limiting, and the rules in the rule current limiting; Performing template matching on the target SQL statement and the template SQL statement by using a template matching method; If the template matching fails, token matching is performed on the target SQL statement and the token SQL statement using a token matching method; If the token matching fails, the target SQL statement is matched using the rule; If the rule is matched successfully, the target SQL statement is prevented from being executed by the database.

3. The method according to claim 2, characterized in that Performing template matching on the target SQL statement and the template SQL statement in a template matching manner includes: Fingerprinting the template SQL statement to generate a template SQL fingerprint and a template hash value; The template hash value is used as the key, and the template SQL fingerprint is used as the value to store it in a Map data structure; Parsing the target SQL statement to generate a target SQL fingerprint and a target hash value, and searching the Map data structure for the target SQL fingerprint corresponding to the target hash value; If it can be found, it is determined that the template matching is successful; if it cannot be found, it is determined that the template matching fails.

4. The method according to claim 2, characterized in that: Performing token matching on the target SQL statement and the token SQL statement in a token matching manner includes: Parsing the target SQL statement to generate a target SQL fingerprint, and generating a target array according to the target SQL fingerprint; Fingerprinting the token SQL statement to generate a token SQL fingerprint, and generating a token array according to the token SQL fingerprint; If the length of the target array is greater than the length of the token array, matching the tokens in the target array with the tokens in the token array; If all tokens in the token array are matched successfully, the output token matches successfully; If not all tokens in the token array are matched successfully, the output token matching fails.

5. The method according to claim 2, characterized in that: The rules in the rule-based current limiting include: prohibiting full table query, prohibiting page query, limiting the number of JOINs, limiting the deletion of table data, and limiting the number of EXISTS expressions.

6. The method according to claim 2, characterized in that After performing template matching on the target SQL statement and the template SQL statement in a template matching manner, the method further includes: If the template matches successfully, the current concurrent volume is obtained; If the existing concurrency reaches the maximum concurrency, the target SQL statement is prevented from being executed by the database; If the existing concurrency does not reach the maximum concurrency, then add one to the existing concurrency.

7. The method according to claim 2, characterized in that After token matching is performed on the target SQL statement and the token SQL statement in a token matching manner, the method further includes: If the token matches successfully, add one to the existing concurrency number to get the actual concurrency number; If the existing concurrency reaches the maximum concurrency, the target SQL statement is prevented from being executed by the database; If the existing concurrency does not reach the maximum concurrency, then add one to the existing concurrency.

8. A device for limiting SQL flow in a database, characterized in that: The device comprises: A determination module, used to determine a current limiting mode in the current limiting configuration, wherein the current limiting mode includes template current limiting, token current limiting and rule current limiting, the template current limiting is used to limit the overall characteristics of the SQL statement, the token current limiting is used to limit some characteristics of the SQL statement, and the rule current limiting is used to limit the sub-operations of the SQL statement; The matching module is used to match the target SQL statement obtained from the client based on the current limiting method, and prevent the target SQL statement from being executed by the database according to the successful matching result.

9. An electronic device, characterized in that: It includes a processor, a communication interface, a memory and a communication bus, wherein the processor, the communication interface and the memory communicate with each other through the communication bus; Memory, used to store computer programs; A processor, for implementing any of the methods described in claims 1-7 when executing a program stored in a memory.

10. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the method according to any one of claims 1 to 7 is implemented.

Images