A method for dynamically generating SQL statements

Abstract

The invention discloses a method for dynamically generating SQL statements, which comprises the following steps: S1: establishing an extended statement of an SQL statement to form a DSQL statement; S2: adopting a parser to parse the DSQL statement to generate a DSQL syntax tree; S3: an application program sets the number of repetitions of a variable block element in the DSQL syntax tree; S4: an application program adopts an actual value to set a variable word element in the DSQL syntax tree; S5: an application program adopts an actual value to set a variable element in the DSQL syntax tree; S6: a generator sends a standard SQL statement to a relational database. The invention adds three syntax elements of "variable", "variable word" and "variable block" on the basis of a standard SQL statement, which can not only dynamically change the data in the SQL statement when the program is running, but also change the keywords, field names and operators in the SQL statement, so the method has strong flexibility and avoids repeated writing and debugging of similar SQL statements, which can reduce the workload and the error probability.

Description

Technical Field

[0001] The present invention relates to the technical field of data storage, and in particular to a method for dynamically generating SQL statements. Background Art

[0002] A large number of application software use relational databases to store structured data. Application software generally uses SQL statements to add, delete, modify, and query data stored in relational databases. Therefore, when application software accesses relational databases, it is mostly necessary to generate SQL statements first.

[0003] Since SQL statements are often related to changes in external data from users and the network, application software often needs to dynamically generate SQL statements at runtime. There are generally two ways to dynamically generate SQL statements. One is to concatenate strings together to generate SQL statements. Because the concatenated strings come from the outside world, there may be special characters, which will cause the concatenated SQL statements to have unexpected consequences. In serious cases, there may even be risks such as password leakage or data deletion. This is the "SQL injection" security risk. The other is to use the "parameterized SQL statement" method. This method avoids the security risk of "SQL injection", but can only dynamically change the data part of the SQL statement, and cannot change other parts of the SQL statement, nor can it change its structure. Therefore, it has low flexibility and often requires repeated writing and debugging of similar SQL statements, which increases the workload and error probability. Summary of the invention

[0004] In view of the above-mentioned deficiencies in the prior art, the present invention provides a method for dynamically generating SQL statements when an application dynamically generates SQL statements to access a database, which can maintain sufficient flexibility and avoid the security risk of "SQL" injection.

[0005] In order to achieve the above-mentioned object of the invention, the technical solution adopted by the present invention is:

[0006] A method for dynamically generating SQL statements is provided, comprising the following steps:

[0007] S1: Create an extended statement of an SQL statement to form a DSQL statement, wherein the DSQL statement includes a variable syntax element, a variable word syntax element, and a variable block syntax element;

[0008] The variable syntax element is a variable string or numeric type data in the SQL statement;

[0009] Variable word syntax elements are variable field names, function names, operators, and keywords in SQL statements;

[0010] The variable block syntax element is a repeatable or removable part of the SQL statement;

[0011] S2: Use a parser to parse the DSQL statement and generate a DSQL statement tree. If the DSQL syntax tree is successfully generated, proceed to step S3; otherwise, enter the error reporting program and end the process;

[0012] S3: The application sets the number of repetitions of the variable block syntax element; if it is found during the setting process that the variable block syntax element does not meet the requirements of the variable block syntax, the error reporting procedure is entered and the process ends; otherwise, the process goes to step S4;

[0013] S4: The application program sets the variable word syntax element with the actual value; if it is found during the setting process that the variable word syntax element does not meet the requirements of the variable word syntax, the error reporting procedure is entered and the process ends; otherwise, the process goes to step S5;

[0014] S5: The application program sets the variable syntax element using the actual value; if it is found during the setting process that the variable syntax element does not meet the variable syntax requirement, the error reporting procedure is entered and the process ends; otherwise, the process proceeds to step S6;

[0015] S6: The generator sends the standard SQL statement to the relational database.

[0016] Furthermore, the statement format of the variable syntax element is: ? [name1][:default1][ / *comment1* / ]; where ? is the variable start symbol, name1 is the name of the variable and is a legal identifier of the programming language; default1 is the default value of the variable and default1 matches its data type; / *comment1* / is the comment of the variable and is an arbitrary string.

[0017] Furthermore, the format of the variable syntax element referenced in the application is:

[0018] paraIndex1[name2|columnName_comparisonOperatorAbbreviation]

[0019] Among them: para is a fixed string; Index1 is the serial number of the variable using this reference name format, and the variable serial number starts from 1; name2 is the explicitly specified variable name; columnName is the field name on the right side of the comparison expression; comparisonOperatorAbbreviation is the abbreviation of the comparison operator.

[0020] Furthermore, the variable word statement includes a variable field name, table name, view name, function name, comparison operator, logical operator and "keyword" used to identify the variable in the SQL statement.

[0021] Furthermore, the syntax format of the variable word syntax element is:

[0022] #[name3:]default2[ / *comment2* / ]

[0023] Among them, # is the starting symbol of the variable word statement, name3 is the name of the variable word and is a legal identifier of the programming language, default2 is the default value of the variable word, / *comment2* / is the comment of the variable word statement, and / *comment2* / is any Java constant string.

[0024] Furthermore, the reference name format of the variable word statement in the application is:

[0025] wordIndex2[name4|type]

[0026] Among them, word is a fixed string, Index2 is the serial number of the variable word statement, and the serial number of the variable word statement starts from 1, name4 is an explicitly specified variable word name, type is the type name of the variable word statement, and when the variable word name of the variable word statement is not explicitly specified, the variable word name of the variable word statement is replaced by the type name of the variable word statement.

[0027] Furthermore, the syntax format of the variable block syntax element is:

[0028] {content}[name5][ / *comment3* / ]

[0029] Among them, { in {content} is the beginning of the variable block statement,} is the end of the variable block statement content, and content is the content of the variable block statement; name5 is the name of the variable block statement, and the name of the variable block statement is a legal identifier of the programming language; comment3 is the comment of the variable block statement, and the comment of the variable block statement is an arbitrary string.

[0030] Furthermore, the reference name format of the variable block statement in the application is:

[0031] blockIndex3[name6]

[0032] Where: block is a fixed string; Index3 is the serial number of the variable block statement, and the serial number of the variable block statement starts from 1; name6 is the name of the explicitly specified variable block statement.

[0033] The beneficial effects of the present invention are as follows: the present invention adds three syntax elements, namely "variable", "variable word" and "variable block", on the basis of standard SQL statements. After the application sets these variable parts, the standard SQL statement can be generated to access the database. Since the three variable syntax elements can be syntax checked according to their respective definitions and illegal character strings can be filtered out, compared with the commonly used character string concatenation SQL statement, the present invention can greatly reduce the security risk of "SQL injection"; compared with parameterized SQL statements, parameterized SQL statements can only change the data in the SQL statement, while the present invention can not only dynamically change the data in the SQL statement when the program is running, but also change the keywords, field names and operators in the SQL statement, so it has strong flexibility and avoids repeated writing and debugging of similar SQL statements, which can not only reduce the workload but also reduce the probability of errors. BRIEF DESCRIPTION OF THE DRAWINGS

[0034] Figure 1 A flowchart of a method for dynamically generating SQL statements. DETAILED DESCRIPTION

[0035] The specific implementation modes of the present invention are described below to facilitate those skilled in the art to understand the present invention. However, it should be clear that the present invention is not limited to the scope of the specific implementation modes. For those of ordinary skill in the art, as long as various changes are within the spirit and scope of the present invention as defined and determined by the attached claims, these changes are obvious, and all inventions and creations using the concepts of the present invention are protected.

[0036] like Figure 1 As shown, the method for dynamically generating SQL statements in this solution includes the following steps:

[0037] S1: Create an extended statement of an SQL statement to form a DSQL statement, wherein the DSQL statement includes a variable syntax element, a variable word syntax element, and a variable block syntax element;

[0038] The variable syntax element is a variable string or numeric type data in the SQL statement;

[0039] Variable word syntax elements are variable field names, function names, operators, and keywords in SQL statements;

[0040] The variable block syntax element is a repeatable or removable part of the SQL statement;

[0041] S2: Use a parser to parse the DSQL statement and generate a DSQL statement tree. If the DSQL syntax tree is successfully generated, proceed to step S3; otherwise, enter the error reporting program and end the process;

[0042] S3: The application sets the number of repetitions of the variable block syntax element; if it is found during the setting process that the variable block syntax element does not meet the requirements of the variable block syntax, the error reporting procedure is entered and the process ends; otherwise, the process goes to step S4;

[0043] S4: The application program sets the variable word syntax element with the actual value; if it is found during the setting process that the variable word syntax element does not meet the requirements of the variable word syntax, the error reporting procedure is entered and the process ends; otherwise, the process goes to step S5;

[0044] S5: The application program sets the variable syntax element using the actual value; if it is found during the setting process that the variable syntax element does not meet the variable syntax requirement, the error reporting procedure is entered and the process ends; otherwise, the process proceeds to step S6;

[0045] S6: The generator sends the standard SQL statement to the relational database.

[0046] In this embodiment, the variable statement is used to identify variable character strings or numeric data in SQL statements, and can be used to replace constants in arithmetic expressions and comparison expressions, and can also be used to replace function parameters and field values.

[0047] The variable syntax element is a variable string or numeric type data in the SQL statement;

[0048] The statement format of the variable syntax element is:? [name1][:default1][ / *comment1* / ];

[0049] Among them, ? is the variable start symbol, name1 is the name of the variable and is a legal identifier of the programming language; default1 is the default value of the variable and default1 matches its data type; / *comment1* / is the comment of the variable and is an arbitrary string.

[0050] The meaning of each part of the variable syntax is shown in Table 1 below:

[0051] Table 1 Variable syntax format description table

[0052] symbol Optional illustrate ？ must Variable start symbol name1 Optional The name of the variable must be a legal identifier in the programming language :default1 Optional default1 represents the default value of the variable, which must match its data type / *comment* / Optional comment1 represents the comment of the variable, which can be any string End must Any illegal character represents the end of a variable

[0053] Notice:

[0054] The data type of the variable is obtained by parsing the database structure and SQL statements;

[0055] If the variable is a parameter of the in operator, its data type is array type.

[0056]

[0057] The variable reference name is the name used when referencing the variable in the program. If the variable is used as input data for the insert values ​​and update set clauses, its specified name is used as its reference name; if no name is specified, its corresponding field name is used as its reference name.

[0058] The format of variable syntax elements referenced in the application is:

[0059] paraIndex1[name2|columnName_comparisonOperatorAbbreviation]

[0060] Among them: para is a fixed string; Index1 is the serial number of the variable using this reference name format, and the serial number of the variable starts from 1; name2 is the explicitly specified variable name; when the name of the variable statement is not explicitly specified, and the variable statement is the parameter on the right side of the comparison operator, and the left side of the comparison operator is a field: columnName is the name of the field on the right side of the comparison expression; comparisonOperatorAbbreviation is the abbreviation of the comparison operator. The reference name of the variable statement is the name used when referencing the variable in the program.

[0061] The comparison operator abbreviations are shown in Table 2 below:

[0062] Table 2 Comparison operator abbreviations

[0063] Comparison Operators English name abbreviation Remark ＝ equal EQ equal <> not equal NEQ Not equal to ！＝ not equal NEQ Not equal to > greater than GT Greater than >＝ greater than or equal GTE Greater than or equal to ！> not greater than NGT No more than < less than LT Less than <＝ less than or equal LTE Less than or equal to ！< not less than NLT Not less than like like Like resemblance in in In in what between between and from BtFrom What to Start With between and between and to BtTo Until what

[0064] Notice:

[0065] Whether the two parameters of the between operator are included in the condition depends on the specific database management system. Therefore, the first parameter cannot be simply equated with GTE and the second parameter with LTE.

[0066]

[0067] The variable word statement includes the variable field name, table name, view name, function name, comparison operator, logical operator and "keyword" used to identify the variable in the SQL statement. The variable word statement can only replace a complete word in the SQL statement, and cannot replace part of a word or multiple words.

[0068] Variable word syntax elements are variable field names, function names, operators, and keywords in SQL statements;

[0069] The syntax format of the variable word syntax element is:

[0070] #[name3:]default2[ / *comment2* / ]

[0071] Among them, # is the starting symbol of the variable word statement. Note that the default value is not optional but required. name3 is the name of the variable word and is a legal identifier of the programming language. default2 is the default value of the variable word. / *comment2* / is the comment of the variable word statement, and / *comment2* / is an arbitrary constant string.

[0072] The meanings of the various parts of the variable word grammar are shown in Table 3 below:

[0073] Table 3 Variation word syntax format description table

[0074]

[0075] The following is an example of a SQL statement with a variable:

[0076]

[0077] The reference name format of the variable word statement is:

[0078] wordIndex2[name4|type]

[0079] Among them, word is a fixed string, Index2 is the serial number of the variable word statement, and the serial number of the variable word statement starts from 1, name4 is an explicitly specified variable word name, type is the type name of the variable word statement, and when the variable word name of the variable word statement is not explicitly specified, the variable word name of the variable word statement is replaced by the type name of the variable word statement.

[0080] The type names of the variable characters are shown in Table 4 below:

[0081] Table 4 Variant Type Names

[0082] symbol illustrate Field Field Name Table Table or view name Comp Comparison Operators Logical Logical Operators Function Function Name Keyword Keywords

[0083] A variable must specify a default value. When a variable does not specify a name, the colon after the name must be removed. Examples of variable reference names are as follows:

[0084]

[0085] The variable block syntax element is a repeatable or removable part of an SQL statement; both the variable statement and the variable word statement only change one word in an SQL statement, while the variable block statement can change multiple words but this change can only be repeated multiple times or canceled.

[0086] The syntax format of the variable block syntax element is:

[0087] {content}[name5][ / *comment3* / ]

[0088] Among them, { in {content} is the beginning of the variable block statement,} is the end of the variable block statement content, and content is the content of the variable block statement; name5 is the name of the variable block statement, and the name of the variable block statement is a legal identifier of the programming language; comment3 is the comment of the variable block statement, and the comment of the variable block statement is an arbitrary string.

[0089] The identifier of a variable block is a pair of curly braces, where the part inside the curly braces represents the content of the variable block, followed by the right curly brace, which can be used to place the name and comments of the variable block. Note that a pair of curly braces is required.

[0090] The meaning of each part of the variable block syntax is shown in Table 5 below:

[0091] Table 5 Variable block syntax format description table

[0092]

[0093] The following is an example of a SQL statement with a variable block:

[0094]

[0095] The comments and terminators in the variable block syntax are exactly the same as those in the variable syntax and will not be repeated here.

[0096] The variable block content is between two curly braces and is a SQL statement fragment. In this SQL statement fragment, variables and variable word syntax in DSQL syntax can be included, but for the sake of simplicity, variable block syntax cannot be included, that is, variable block nesting is not supported. In addition, the sequence numbers in the variable and variable word reference names should be independently generated from 1 within the scope of the current variable block, and cannot be related to the variable and variable word sequence numbers outside the variable block or in other variable blocks.

[0097] The variable block content is repeatable, and the block is canceled when the number of repetitions is 0. Whether it is repeated multiple times or 0 times, in the process of generating a standard SQL statement from a DSQL statement, a check should be performed before and after the original variable block position to determine whether there are non-blank words that make the generated standard SQL statement illegal. If so, it should be deleted before outputting the final standard SQL statement. For example:

[0098]

[0099] Note: For variable blocks that do not have a set number of repetitions, the generator should directly report an error.

[0100] Predefined variable abbreviations

[0101] In order to simplify the definition of commonly used variable blocks, the following commonly used variable block abbreviations are predefined, as shown in Table 6:

[0102] Table 6 Predefined variable blocks

[0103] Predefined variable abbreviations Represented variable block illustrate {CONDITION_LIST} {#Field:field#Comp#=? #Logical:and} Condition List {SORT_LIST} {#Field:field#Direction:asc,} Sorting List {FIELD_LIST} {#Field:field,} Field List

[0104] The reference name format of the variable block statement is:

[0105] blockIndex3[name6]

[0106] Among them: block is a fixed string; Index3 is the sequence number of the variable block statement, and the sequence number of the variable block statement starts from 1; name6 is the name of the explicitly specified variable block statement. As shown below:

[0107]

[0108] Whether repeated multiple times or 0 times, in the process of generating a standard SQL statement from a DSQL statement, a check should be performed before and after the original variable block position to determine whether there are non-blank words that make the generated standard SQL statement illegal. If so, they should be deleted before outputting the final standard SQL statement.

[0109] The present invention adds three syntax elements, namely "variable", "variable word" and "variable block", on the basis of standard SQL statements. After the application sets these variable parts, the standard SQL statement can be generated to access the database. Since the three variable syntax elements can be syntax checked according to their respective definitions and illegal character strings can be filtered out, compared with the commonly used character string splicing SQL statement, the present invention can greatly reduce the security risk of "SQL injection"; compared with parameterized SQL statements, parameterized SQL statements can only change the data in the SQL statement, while the present invention can not only dynamically change the data in the SQL statement when the program is running, but also change the keywords, field names and operators in the SQL statement, so it has strong flexibility and avoids repeated writing and debugging of similar SQL statements, which can not only reduce the workload but also reduce the probability of errors.

Claims

1. A method for dynamically generating SQL statements, characterized in that: The following steps are involved: S1: Create an extended statement of an SQL statement to form a DSQL statement, wherein the DSQL statement includes a variable syntax element, a variable word syntax element, and a variable block syntax element; The variable syntax element is a variable string or numeric type data in the SQL statement; The variable word grammar elements are variable field names, function names, operators and keywords in SQL statements; The variable block syntax element is a repeatable or removable part of the SQL statement; S2: Use a parser to parse the DSQL statement and generate a DSQL statement tree. If the DSQL syntax tree is successfully generated, proceed to step S3; otherwise, enter the error reporting program and end the process; S3: The application sets the number of repetitions of the variable block syntax element; If it is found during the setting process that the variable block syntax element does not meet the requirements of the variable block syntax, the error reporting procedure will be entered and the process will end; Otherwise, proceed to step S4; S4: The application sets the variadic syntax element with the actual value; If it is found during the setting process that the variable word grammar element does not meet the requirements of the variable word grammar, the error reporting procedure is entered and the process ends; Otherwise, proceed to step S5; S5: The application sets the variable syntax element with the actual value; If it is found during the setting process that the variable syntax element does not meet the variable syntax requirements, the error reporting procedure is entered and the process ends; otherwise, the process goes to step S6; S6: The generator sends the standard SQL statement to the relational database.

2. The method for dynamically generating SQL statements according to claim 1, characterized in that: The statement format of the variable syntax element is:? [ name1 ][: default1 ][ / * comment1 * / ]; where? is the variable start symbol, name1 is the name of the variable and is a legal identifier in the programming language; default1 is the default value of the variable, and default1 Match its data type; / * comment1 * / is the comment of the variable and can be any string.

3. The method for dynamically generating SQL statements according to claim 2, characterized in that: The format of the variable syntax element referenced in the application is: to Index1 [ name2 | columnName _ comparisonOperatorAbbreviation ] Among them: para is a fixed string; Index1 The serial number of the variable using this reference name format, the serial number of the variable starts from 1; name2 is an explicitly specified variable name; columnName The name of the field on the right side of the comparison expression; comparisonOperatorAbbreviation An abbreviation for the comparison operator.

4. The method for dynamically generating SQL statements according to claim 1, characterized in that: The syntax format of the variable word syntax element is: #[ name3 :] default2 [ / * comment2 * / ] Among them, # is the starting symbol of the variable word statement. name3 is the name of a variable and is a legal identifier in the programming language, default2 The default value of the variable word, / * comment2 * / is a comment for the variable word statement, and / * comment2 * / is any constant string.

5. The method for dynamically generating SQL statements according to claim 4, characterized in that: The variable word statement includes a variable field name, table name, view name, function name, comparison operator, logical operator and keyword used to identify the variable field name, table name, view name, function name, comparison operator, logical operator and keyword in the SQL statement.

6. The method for dynamically generating SQL statements according to claim 5, characterized in that: The reference name format of the variable word statement is: word Index2 [ name4 | type ] Among them, word is a fixed string, Index2 is the sequence number of the variable word statement, and the sequence number of the variable word statement starts from 1. name4 is the variant name of the variant grammar element, type It is the type name of the variable statement. When the variable name of the variable statement is not explicitly specified, the variable name of the variable statement is replaced by the type name of the variable statement.

7. The method for dynamically generating SQL statements according to claim 1, characterized in that: The syntax format of the variable block syntax element is: { content}[ name5 ][ / * comment3 * / ] in,{ content The { in} is the beginning of the variable block statement, and} is the end of the variable block statement content. content The content of the variable block statement; name5 is the name of a variable block statement, and the name of a variable block statement is a legal identifier of the programming language; comment3 It is the comment of the variable block statement. The comment of the variable block statement is any string.

8. The method for dynamically generating SQL statements according to claim 7, characterized in that: The reference name format of the variable block statement is: block Index3 [ name6 ] Among them: block is a fixed string; Index3 It is the sequence number of the variable block statement, and the sequence number of the variable block statement starts from 1; name6 The name of the variable block statement is explicitly specified.

Images