CN110795450A

CN110795450A - SQL processing method and device

Info

Publication number: CN110795450A
Application number: CN201910863939.3A
Authority: CN
Inventors: 宋城; 孔万群
Original assignee: LIANLIANYINTONG ELECTRONIC PAYMENT CO Ltd
Current assignee: LIANLIANYINTONG ELECTRONIC PAYMENT CO Ltd
Priority date: 2019-09-12
Filing date: 2019-09-12
Publication date: 2020-02-14

Abstract

The SQL processing method and the device provided by the invention are used for acquiring at least one SQL statement in an SQL text, extracting the characteristic information of the SQL statement aiming at each SQL statement, judging whether each SQL statement meets the preset auditing standard or not according to the characteristic information of the SQL statement, judging whether the grammar of each SQL statement is correct or not in a test environment if all the SQL statements meet the auditing standard, and executing the SQL text in a database if all the grammar of the SQL statements are correct. According to the method provided by the invention, the SQL sentence is checked through the preset checking standard, and after the SQL sentence is determined to be in accordance with the checking standard, the grammar accuracy is further judged in the test environment, so that the accuracy of checking the normalization of the SQL sentence is improved, and finally the accuracy of database change is improved.

Description

SQL processing method and device

Technical Field

The present application relates to the field of database technologies, and in particular, to a method and an apparatus for SQL processing.

Background

With the increase of business volume of enterprises, the number of databases of application systems of the enterprises is correspondingly increased, and data in the databases needs to be queried, updated or otherwise operated every day, and the operations are generally realized by using a Structured Query Language (SQL).

In the process of operating the database by using the SQL statement, if the SQL statement is not standardized, an operation error of the database may be caused, so before the SQL statement is executed, the standardization of the SQL statement needs to be checked.

The normalization work of the SQL statements is usually realized manually, but some nonstandard SQL statements pass the verification, so the accuracy of the verification result is low, and the operation error rate of the database is high.

Disclosure of Invention

The application provides an SQL processing method and an SQL processing device, and aims to solve the problem of improving accuracy of examining and verifying normalization of SQL sentences so as to improve accuracy of database data change.

In order to achieve the above object, the present application provides the following technical solutions:

an SQL processing method comprises the following steps:

acquiring at least one SQL statement in the SQL text;

extracting the characteristic information of the SQL statement aiming at each SQL statement;

judging whether each SQL statement meets a preset auditing standard or not according to the characteristic information of the SQL statement;

if all the SQL sentences accord with the auditing standard, judging whether the grammar of each SQL sentence is correct or not in a test environment;

and if the syntax of all the SQL sentences is correct, executing the SQL text in a database.

Optionally, the method for acquiring at least one SQL statement in an SQL text includes:

acquiring an SQL work order, wherein the SQL work order at least comprises an SQL text and a database identity mark corresponding to the SQL text;

judging whether the SQL text in the SQL work order meets the requirement of no-danger fields and the requirement that the text capacity is smaller than a capacity threshold value;

and if the SQL sentences are satisfied, segmenting the SQL text to obtain the at least one SQL sentence.

Optionally, the method for executing the SQL text in the database includes:

determining a database corresponding to the SQL text according to the database identity corresponding to the SQL text;

executing each SQL statement in a database corresponding to the SQL text one by one;

if the current SQL statement fails to be executed, rolling back the successfully executed SQL statement;

and after each SQL statement is successfully executed, backing up the changed data in the database.

Optionally, the method for determining whether each SQL statement meets a preset auditing standard according to the feature information of the SQL statement includes:

judging whether the characteristic information of each SQL statement conforms to preset auditing content, wherein the auditing content at least comprises statement end number auditing, SQL type auditing, database type auditing and update operation auditing;

if the characteristic information of the SQL statement conforms to the preset auditing content, determining that the SQL statement conforms to the auditing standard;

and if the characteristic information of the SQL statement does not accord with any item in the preset auditing content, determining that the SQL statement does not accord with the auditing standard.

Optionally, in the method, if all the SQL statements meet the auditing standard, determining whether the syntax of each SQL statement is correct in a test environment includes:

analyzing the SQL statement by adopting a syntax analyzer in a database;

and judging whether the syntax of the SQL statement is correct or not according to the analysis result.

The above method, optionally, further includes:

and if any one of the SQL sentences does not accord with the auditing standard, or if the syntax of any one of the SQL sentences is wrong, outputting prompt information that the SQL sentences do not accord with the auditing standard.

Optionally, in the method, before the SQL text is executed in the database, prompt information for checking the SQL statement is sent to an account of a checker having a checking right.

An SQL processing apparatus comprising:

the acquiring unit is used for acquiring at least one SQL statement in the SQL text;

the extraction unit is used for extracting the characteristic information of the SQL sentences aiming at each SQL sentence;

the first judgment unit is used for judging whether each SQL statement meets a preset auditing standard or not according to the characteristic information of the SQL statement;

a second judging unit, configured to judge, in a test environment, whether syntax of each SQL statement is correct if all the SQL statements meet the audit standard;

and the execution unit is used for executing the SQL text in a database if the syntax of all the SQL statements is correct.

Optionally, the above apparatus, where the obtaining unit is configured to obtain at least one SQL statement in an SQL text, and includes: the acquiring unit is specifically configured to acquire an SQL work order, where the SQL work order at least includes an SQL text and a database identity identifier corresponding to the SQL text; judging whether the SQL text in the SQL work order meets the requirement of no-danger fields and the requirement that the text capacity is smaller than a capacity threshold value; and if the SQL sentences are satisfied, segmenting the SQL text to obtain the at least one SQL sentence.

Optionally, the above apparatus, where the execution unit is configured to execute the SQL text in a database, includes: the execution unit is specifically configured to determine a database corresponding to the SQL text according to the database identity corresponding to the SQL text; executing each SQL statement in a database corresponding to the SQL text one by one; if the current SQL statement fails to be executed, rolling back the successfully executed SQL statement; and after each SQL statement is successfully executed, backing up the changed data in the database.

Drawings

In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the drawings used in the description of the embodiments or the prior art will be briefly described below, it is obvious that the drawings in the following description are only some embodiments of the present application, and for those skilled in the art, other drawings can be obtained according to the drawings without creative efforts.

Fig. 1 is a flowchart of an SQL processing method disclosed in an embodiment of the present application;

FIG. 2 is a flow chart of the execution of SQL text in a database disclosed in the embodiments of the present application;

FIG. 3 is a flow chart of another SQL processing method disclosed in the embodiments of the present application;

fig. 4 is a schematic structural diagram of an SQL processing apparatus disclosed in an embodiment of the present application;

fig. 5 is a schematic structural diagram of another SQL processing apparatus disclosed in the embodiment of the present application.

Detailed Description

The SQL processing method and the SQL processing device disclosed by the embodiment of the application are used for checking the normalization of the SQL statement and executing the SQL statement meeting the normalization in data.

The technical solutions in the embodiments of the present application will be clearly and completely described below with reference to the drawings in the embodiments of the present application, and it is obvious that the described embodiments are only a part of the embodiments of the present application, and not all of the embodiments. All other embodiments, which can be derived by a person skilled in the art from the embodiments given herein without making any creative effort, shall fall within the protection scope of the present application.

Fig. 1 is a diagram of an SQL processing method disclosed in an embodiment of the present application, including the following steps:

s101, acquiring at least one SQL statement in the SQL text.

The SQL statement is used for changing the database, and the specific process for acquiring the SQL statement to be processed is as follows: and acquiring the SQL text, and judging whether the SQL text contains a danger field, wherein the danger field can be drop, truncate, delete and the like. And ensuring that the SQL text does not contain dangerous fields so as to ensure the data security of the changed database.

If the SQL text contains the dangerous fields, returning to the SQL work order editing interface, and outputting prompt information that the SQL text contains the dangerous fields. And if the SQL text does not contain the dangerous fields, calculating the capacity of the SQL text, and judging whether the capacity of the SQL text is larger than a capacity threshold value. If the capacity of the SQL text is larger than the capacity threshold, carrying out asynchronous processing, wherein the asynchronous processing comprises the following steps: and after outputting the prompt information that the current SQL text is in the auditing state, segmenting the SQL text to obtain at least one SQL statement. And if the capacity of the SQL text is less than or equal to the preset capacity threshold, directly segmenting the SQL text to obtain at least one SQL statement. One way to segment SQL text may be: split the SQL text using SQL split segmentation tool.

It should be noted that the SQL text is the text in the SQL work order created by the developer. The SQL work order at least includes an SQL text and information such as a database identity corresponding to the SQL text, where the database identity may be a database connection address, a database name, and the like.

S102, extracting the characteristic information of the SQL statement aiming at each SQL statement.

And analyzing the SQL sentences aiming at each SQL sentence to acquire the characteristic information contained in the SQL sentences. The characteristic information of the SQL statement may be table name information, SQL type information, database name information, sentence end symbols, statement connection characters, and primary key fields included in the SQL statement.

S103, judging whether each SQL statement meets a preset auditing standard or not according to the characteristic information of the SQL statement. If yes, S104 is performed, and if no, S106 is performed.

One way of judging whether each SQL statement meets the preset auditing standard according to the feature information of the SQL statement may be: and judging whether the characteristic information of each SQL statement conforms to preset audit content, wherein the audit content at least comprises statement end number audit, SQL type audit, database type audit and update operation audit.

The statement end number auditing can be used for auditing whether the end of the SQL statement is a semicolon. The SQL type audit may be to audit whether the statement type of the SQL statement is the statement type specified in the SQL work order, where the SQL work order may be a ddl type or a dml type specified in advance in the SQL statement type. The database type examination may be to examine whether the database type corresponding to the SQL statement is the database type specified in the SQL work order, and the database type may be a mysql type or an oracle type.

The update operation audit is used for auditing the condition that the SQL statement is of the update operation type, and whether the where condition of the SQL statement contains illegal connection characters, whether the main key field in the where condition contains comparison characters, whether the where condition contains set fields and the like can be audited in the update operation audit.

If the characteristic information of the SQL statement conforms to the preset auditing content, the SQL statement is determined to conform to the auditing standard, and S104 is executed. And if the characteristic information of the SQL statement does not accord with any item in the audit content, determining that the SQL statement does not accord with the audit standard, and executing S107.

And S104, judging whether the grammar of each SQL statement is correct or not in the test environment. If yes, go to step S105, otherwise, go to step S106.

For each SQL statement, in a test environment, whether the syntax of the SQL statement is correct is determined, and specifically, one way of determining whether the syntax of the SQL statement is correct may be: and adopting a grammar parser in the database to parse the SQL sentence, and judging whether the grammar of the SQL sentence is correct or not according to a parsing result. Because the syntax of the SQL statement is usually complex, in a test environment, the syntax parser in the database is used for parsing the syntax of the SQL statement by connecting the database to judge the correctness of the syntax, so that the accuracy of judging the correctness of the syntax can be improved.

The syntax of the SQL statement is a predefined fixed statement form of the SQL statement, for example, an arrangement order of each word in the SQL statement is predefined, and if the arrangement order of each word of the SQL statement obtained by parsing by the syntax parser is different from a preset arrangement order, it is determined that the syntax of the SQL statement is incorrect.

And S105, executing the SQL text in the database.

And after the grammar of each SQL statement to be processed is determined to be correct, executing the SQL text in the database to change the data of the database. Specifically, the process of executing each SQL statement to be processed in the database may refer to the flow illustrated in fig. 2.

And S106, outputting prompt information that the SQL statement does not meet the auditing standard.

It should be noted that, if any SQL statement to be processed does not meet the currently executed auditing standard or syntax error, the SQL statement editing interface returns to the first embodiment, and prompt information indicating that the SQL statement does not meet the auditing standard is output to prompt the developer about the non-normalization of the SQL statement, so that the developer can re-edit the SQL statement in the SQL statement editing interface according to the prompt information.

In this embodiment, optionally, in order to enable the developer to timely know the information that the SQL statement does not meet the audit standard, a prompt mail that the SQL statement does not meet the audit standard may be sent to the developer after it is checked that the SQL statement does not meet the audit standard.

According to the SQL processing method provided by the embodiment, the SQL sentence is checked according to the preset checking standard, and after the SQL sentence is determined to meet the checking standard, the accuracy of the grammar is further judged in the test environment, so that the accuracy of checking the normalization of the SQL sentence is improved, and finally the accuracy of database change is improved.

Fig. 2 is an embodiment of S105 in fig. 1, which executes SQL text in the database, and includes the following steps:

s201 to S206 are executed under the condition that the execution environment is the production environment.

S201, determining a database corresponding to the SQL text according to the database identity corresponding to the SQL text.

In order to implement execution of the SQL text in the specified data, the SQL work order is preset with a database identity identifier corresponding to the SQL text, as described in the foregoing embodiment, the database identity identifier may be a database name or a database connection address, and since each database identity identifier is different, the database may be determined according to the database identity identifier.

And S202, backing up data of the database corresponding to the SQL text.

Before the SQL statement is executed, the data of the database corresponding to the SQL text is backed up, so that the data of the database before change can be retrieved under the condition that the data change has errors, and the safety of the database is improved.

S203, executing each SQL statement in the database corresponding to the SQL text one by one, and rolling back the successfully executed SQL statement if the current SQL statement fails to be executed.

And executing each SQL statement in a database corresponding to the SQL text one by one, and rolling back all SQL statements which are successfully executed before if the currently executed SQL statement fails to be executed.

And S204, judging whether all SQL sentences are successfully executed. If so, go to S205, otherwise, go to S206.

And S205, backing up the changed data in the database.

In this embodiment, if it is determined that the SQL statement in each row is successfully executed, the changed data in the database is backed up to improve the security of the data.

S206, finishing the execution of the SQL statement.

And if the currently executed SQL statement fails to be executed, ending the execution task.

In the method provided by this embodiment, the database identity identifier corresponding to the SQL text is preset in the SQL work order, so that when the SQL statement in the SQL text is executed, the SQL statement is determined to be executed in the database according to the database identity identifier, thereby avoiding the situation that the SQL statement is executed in the wrong database.

Fig. 3 is another SQL processing method provided in the embodiment of the present application, in which an auditor with an audit authority is allowed to audit an SQL statement in the method provided in the present application, the method may include the following steps:

s301, at least one SQL statement in the SQL text is obtained.

In this embodiment, reference may be made to S101 in fig. 1 for a specific implementation of S301.

S302, extracting the characteristic information of the SQL statement aiming at each SQL statement.

In this embodiment, reference may be made to S102 in fig. 1 for a specific implementation of S302.

S303, judging whether each SQL statement accords with a preset auditing standard or not according to the characteristic information of the SQL statement, if so, executing S304, and if not, executing S308.

In this embodiment, the manner of determining whether each SQL statement meets the preset auditing standard may refer to S103 in fig. 1.

S304, in a test environment, judging whether the grammar of each SQL statement is correct, if so, executing S305, and if not, executing S308.

S305, sending prompt information for checking the SQL statement to an account of a checker with the checking authority.

One implementation way of sending the prompt information for auditing the SQL statement to the auditor with the auditing authority may be as follows: and sending prompt information for checking the SQL statements to account numbers of the auditors of different levels, so that the auditors of all levels check the SQL statements, wherein the account numbers of the auditors are account numbers pre-stored in the SQL work order. For example: and sending prompt information for checking the SQL statement to an account number of the first-level auditor, and sending the prompt information for checking the SQL statement to an account number of the second-level auditor after the result of checking the SQL statement by the first-level auditor is determined to be passed. Wherein, the auditors of different levels can only audit partial fields in the SQL statement according to the actual situation.

Another embodiment may also be: the method comprises the steps of presetting SQL (structured query language) contents needing to be manually checked, and sending prompt information for checking the SQL statements to an account of a checker in charge of checking the SQL contents so that the checker checks the SQL statements. For example, it is predetermined that the key service table information of the SQL statement needs to be checked, and after determining that the feature information of the SQL statement contains the key service table information, sending a prompt message for checking the SQL statement to a checker responsible for checking the key service table information, so that the checker can check the key service table information in the SQL statement.

Another embodiment may also be: and sending prompt information for checking the SQL statement to auditors of different levels and accounts of the auditors in charge of checking the corresponding SQL content by combining the two methods. For example, sending a prompt message for checking the SQL statement to an account of a first-level auditor, determining whether the checking result of the first-level auditor on the SQL statement passes, determining whether the SQL statement includes the key business table information, if so, sending a prompt message for checking the SQL statement to an account of an auditor in charge of checking the key business table information, and after determining that the key business table information of the SQL statement is correct, sending a prompt message for checking the SQL statement to a second-level auditor.

It should be noted that, since the SQL statements that are audited by the auditor with the audit authority all conform to the preset audit standard and the statements have correct syntax, the auditors of different levels or the auditors responsible for auditing the corresponding SQL content may not need to judge whether the SQL statements conform to the audit standard or judge whether the syntax is correct again, and only need to perform the audit on part of the fields in the SQL statements, thereby improving the audit efficiency.

And S306, judging whether the result of the SQL statement verification by the verifier is passed, if so, executing S307, and if not, executing S308.

And judging whether the result of the SQL statement audit by the auditor passes according to the result of the SQL statement audit by the auditor. For example, when the review result submitted by the first reviewer is "statement correct", the review result of the SQL statement is confirmed to be that the review is passed.

And S307, executing the SQL text in the database.

In this embodiment, S105 in fig. 1 may be referred to in the implementation of executing each SQL statement in the SQL text in the database.

And S308, outputting prompt information that the SQL statement does not meet the auditing standard.

If the result that the auditor does not pass the SQL statement is determined, outputting prompt information that the SQL statement does not meet the audit standard, and prompting the developer of the non-standardization of the SQL statement, so that the developer can re-edit the SQL statement in an SQL statement editing interface according to the prompt information.

According to the method provided by the embodiment of the invention, after all SQL sentences are determined to meet the auditing standard and the grammar is correct, the prompt information for auditing the SQL sentences is sent to the auditor, so that the auditor with the auditing authority can audit the SQL sentences, and the SQL sentences meet the auditing standard and have correct grammar before the developers audit the SQL sentences, so that the auditor can not audit the SQL sentences comprehensively and in detail, and only needs to audit part of fields in the SQL sentences again.

The SQL processing method provided by the present application may be applied to an electronic device with an SQL processing function, and the following takes the electronic device as a server with the SQL processing function as an example to exemplify the technical solution of the present embodiment.

The functional architecture of the server with the SQL processing function comprises a database information management module, an SQL analysis module, an SQL execution module and an SQL work order flow module. The technical solution of the present embodiment will be described below with respect to the mutual cooperation between the modules.

And the database information management module is used for maintaining the changed database instance address which is input by the database management administrator DBA and needs to be changed and the connection account password of the database, and simultaneously recording the main key information of each table in the database and the association relation between each table and the authorized user.

Specifically, the database information management module may implement its functions through the following steps S401 to S405, including the following steps:

s401, receiving a database example.

The database instance inputs data to the database information management module by the DBA.

S402, determining a database to be changed and maintaining a connection account password of the database.

And determining the database to be changed according to the database instance address. The connection account password of the database is encrypted to maintain the connection account password of the database, and the specific encryption mode is as follows: the connection account password is encrypted by AES (advanced encryption standard) which is the most common algorithm in symmetric key encryption and then stored in a database. The key part is stored in a professional encryption machine through RSA, and is decrypted through a decryption method each time the key part is used. The security of the database is ensured through twice encryption, and the database connection password cannot be acquired even if the server or the database storing the password is broken.

And S403, scanning all tables in the database.

A database contains a plurality of tables, each table containing table information such as a table name and a table primary key.

S404, creating roles, and associating the created roles with authorized users.

The roles created may be a technical director, a technical head manager, and a database administrator. And after the role is created, associating the role with the authorized user so as to determine the identity of the authorized user, wherein the authorized user is a developer with the authority of managing the database.

S405, associating the scanned table with the created role.

And associating each table of the scanned database with the created role, thereby determining the corresponding relation between the table and the role.

And the SQL auditing module is used for analyzing the SQL sentences and auditing whether the SQL sentences meet the preset auditing standards.

Specifically, the SQL auditing module can implement its functions through the flow of auditing SQL statements shown in S501 to S518, which specifically includes:

s501, obtaining SQL texts in the SQL work order

It should be noted that the SQL work order is created by a developer, wherein an SQL statement in the SQL work order is written by the developer in combination with database information stored in the database information management module.

S502, judging whether the SQL text comprises a dangerous operation field. If yes, go to S518, if no, go to S503.

S503, judging whether the SQL capacity is larger than a preset capacity value; if yes, go to S504, if no, go to S505.

Specifically, the determining whether the SQL volume is greater than the preset volume value may be determining whether the size of the bytes of the SQL text is greater than the preset size of the bytes.

And S504, returning to the front end to display that the current SQL text is in a checking state.

And S505, adopting a Sqlparse split tool to split the SQL text to obtain a plurality of SQL sentences.

S506, judging whether the total number of the SQL statements exceeds the maximum number limit, if so, executing S518, and if not, executing S507.

And S507, judging whether the tail of all SQL sentences does not have a semicolon, if not, executing S518, and if so, executing S508.

And S508, acquiring the table name and the database connection name in each SQL statement.

S509, judging whether the SQL type corresponding to each SQL statement is consistent with the SQL type set by the SQL work order. If so, go to S510, if not, go to S518,

it should be noted that, the determining of the SQL type corresponding to the SQL statement may be determining whether an update or insert field occurs in the SQL statement, and if so, determining that the SQL type of the SQL statement is the dml type.

S510, judging whether the database connection name of each SQL statement is consistent with the database connection name specified in the work order; if so, S511 is performed, and if not, S518 is performed,

s511, judging whether the statement of update operation exists in all SQL statements, if so, executing S512, and if not, executing S518.

S512, analyzing fields in the set and fields in the where condition in the SQL statement aiming at the SQL statement of update operation, and acquiring the main key fields of all tables in the database.

One way to obtain the primary key fields of all tables in the database may be to determine each database in the database instance according to the database instance address in the SQL work order, access the database according to the connection name of the database, determine each table in the database according to the table name of each table in the database, and finally obtain the primary key fields of all tables in the database.

S513, judging whether the where condition field has illegal connectors in and or. If not, S514 is performed, and if yes, S518 is performed.

S514, judging whether the key field comparator in the where condition field is > or <. If not, S515 is performed, and if yes, S518 is performed.

S515, judging whether the where condition field contains the primary key fields of all tables. If so, S516 is performed, and if not, S518 is performed.

S516, judging whether a set field exists in the Where condition field. If not, S517 is performed, and if yes, S518 is performed.

And S517, finishing the verification, outputting prompt information of successful verification, and submitting the SQL text to the SQL execution module.

And S518, refusing to submit the SQL text, outputting prompt information of failure of the examination, and sending a mail to inform developers who create the SQL work order.

The SQL execution module is used for receiving the SQL sentences submitted by the SQL examination module, verifying the correctness of the syntax of the SQL sentences in the test environment and executing the SQL sentences in the production environment.

It should be noted that, in this embodiment, the SQL execution module may connect the mysql database through mysql and connect the oracle database through cxoracle.

And the SQL execution module is used for sequentially verifying the correctness of the grammar of the SQL sentences in a serial mode in the test environment and recording whether the grammar of each SQL sentence is correct or not.

And the SQL execution module submits all SQL statements in a transaction mode in a production environment, when one SQL statement has an error, all SQL statements successfully executed before are rolled back, and if all SQL statements successfully executed, the SQL statements are submitted in a unified way. If the SQL statement is update operation, the following procedures need to be executed additionally:

a1, analyzing the SQL statement, obtaining the update field name, the table name and the Where clause, obtaining the main key field of the table through the table name, and simultaneously spelling the backup statement select { columns }, { primary _ keys } from { table _ part } { Where _ part }.

And A2, executing a select statement, and recording an execution result to a database.

A3, execute update statement in production environment.

And A4, executing the select statement again, and recording the execution result to the database.

A5, generating a rollback statement, analyzing the original SQL statement, finding the table name and set part in the statement, obtaining each record for executing the select statement, converting into an update table _ name set column na, and converting into a column key id.

The SQL work order flow module is used for enabling the SQL examination module, the SQL execution module and the examiner to carry out examination on the SQL statements and other flows to be carried out according to a specified flow sequence, and comprises the following steps:

s601, creating an SQL work order.

And S602, sending the SQL work order to the SQL examination and verification module.

And S603, judging whether the checking result of the SQL checking module passes, if not, returning to execute S601, and if so, executing S604.

S604, the SQL execution module judges whether the syntax of all SQL sentences is correct.

S605, if the syntax of all SQL sentences is correct, executing S606, if not, executing S601.

And S606, sending information for prompting the technical supervisor to check the SQL statement.

S607, judging whether the auditing result submitted by the technical director is approved, if not, executing S608, and if so, executing S609.

And S608, outputting prompt information that the SQL text does not meet the specification.

And S609, judging whether the SQL statement relates to a key business table, if so, executing S6010, and if not, executing S611.

S610, sending information for prompting a technical manager to check the SQL statement.

S611, sending information for prompting a database administrator to check the SQL statement.

And S612, judging whether the auditing result submitted by the technical manager is approved, if not, executing S608, and if so, executing S611.

And S612, judging whether the audit submitted by the database administrator is approved, if not, returning to the step S608, and if so, executing the step S614.

And S614, submitting the SQL statement to the SQL execution module, and enabling the SQL execution module to execute the SQL statement in the production environment.

In summary, the method provided by the embodiment of the present invention realizes multi-layer checking of the normalization of the SQL statement to be processed, thereby improving the accuracy of checking the normalization of the SQL statement, and finally improving the accuracy of database modification. Meanwhile, mysql database can be linked through mysql and oracle database can be linked through cxoracle. The problem that the method and the device can not be simultaneously applied to the mysql database and the oracle database in the prior art is solved.

Corresponding to the method in fig. 1, an embodiment of the present application further provides an SQL processing apparatus, which is used for specifically implementing the method in fig. 1, and a schematic structural diagram of the SQL processing apparatus is shown in fig. 4, and specifically includes:

the obtaining unit 401 is configured to obtain at least one SQL statement in the SQL text.

An extracting unit 402, configured to extract, for each SQL statement, feature information of the SQL statement.

The first determining unit 403 is configured to determine whether each SQL statement meets a preset auditing standard according to the feature information of the SQL statement.

A second determining unit 404, configured to determine, in the test environment, whether syntax of each SQL statement is correct if all SQL statements meet the audit standard.

And an execution unit 405, configured to execute the SQL text in the database if the syntax of all the SQL statements is correct.

With reference to fig. 4, an embodiment of the present application further provides another SQL processing apparatus, as shown in fig. 5, the SQL processing apparatus further includes an output unit 406 and a sending unit 407, where the output unit 406 is configured to output a prompt message that the SQL statement does not meet the audit standard if any SQL statement does not meet the audit standard, or if any SQL statement has a wrong syntax. The sending unit 407 is configured to send prompt information for auditing the SQL statement to an account of an auditor with an auditing authority.

The specific implementation manner of the obtaining unit 401 obtaining at least one SQL statement in the SQL text is as follows: acquiring an SQL work order, wherein the SQL work order at least comprises an SQL text and a database identity mark corresponding to the SQL text; judging whether the SQL text in the SQL work order meets the requirement of the non-dangerous field and the requirement that the text capacity is smaller than the capacity threshold value; if the SQL sentences are satisfied, the SQL texts are segmented to obtain at least one SQL sentence.

The specific implementation manner of the first determining unit 403 determining whether the SQL statement meets the preset auditing standard according to the feature information of the SQL statement is as follows: judging whether the characteristic information of each SQL statement conforms to preset auditing content, wherein the auditing content at least comprises statement end number auditing, SQL type auditing, database type auditing and update operation auditing; if the characteristic information of the SQL statement conforms to the preset auditing content, determining that the SQL statement conforms to the auditing standard; and if the characteristic information of the SQL statement does not accord with any item in the auditing content, determining that the SQL statement does not accord with the auditing standard.

The second determining unit 404 is configured to, if all the SQL statements meet the audit standard, determine whether the syntax of each SQL statement is correct in the test environment in a specific implementation manner as follows: and analyzing the SQL sentence by adopting a grammar analyzer in the database, and judging whether the grammar of the SQL sentence is correct or not according to an analysis result.

The specific implementation manner of the execution unit 405 executing the SQL text in the database is as follows: determining a database corresponding to the SQL text according to the database identity corresponding to the SQL text; executing each SQL statement in a database corresponding to the SQL text one by one; if the current SQL statement fails to be executed, rolling back the successfully executed SQL statement; and after each SQL statement is successfully executed, backing up the changed data in the database.

According to the SQL processing device, the SQL sentences can be guaranteed to be audited from a plurality of audit dimensions through the set audit standard, so that the accuracy of auditing the normalization of the SQL sentences can be improved, and meanwhile, after the SQL sentences are determined to accord with the audit standard, the syntax accuracy of the SQL sentences is further audited. In conclusion, the device provided by the application can be used for checking the normalization of the SQL sentences in a multi-layer manner according to the checking standard and the correctness of the grammar of the SQL sentences, so that the correctness of checking the normalization of the SQL sentences is improved, and the accuracy of database change is finally improved.

The embodiments are described in a progressive manner, each embodiment focuses on differences from other embodiments, and the same or similar parts among the embodiments are referred to each other.

The previous description of the disclosed embodiments is provided to enable any person skilled in the art to make or use the present application. Various modifications to these embodiments will be readily apparent to those skilled in the art, and the generic principles defined herein may be applied to other embodiments without departing from the spirit or scope of the application. Thus, the present application is not intended to be limited to the embodiments shown herein but is to be accorded the widest scope consistent with the principles and novel features disclosed herein.

Claims

1. An SQL processing method, comprising:

acquiring at least one SQL statement in the SQL text;

2. The method of claim 1, wherein the obtaining at least one SQL statement in SQL text comprises:

3. The method of claim 2, wherein executing the SQL text in a database comprises:

4. The method according to claim 1, wherein the determining whether each SQL statement meets a preset auditing standard according to the characteristic information of the SQL statement comprises:

5. The method of claim 1, wherein if all of the SQL statements meet the auditing criteria, determining whether the syntax of each of the SQL statements is correct in a test environment comprises:

analyzing the SQL statement by adopting a syntax analyzer in a database;

6. The method of claim 1, further comprising:

7. The method according to claim 1, before executing the SQL text in the database, further comprising sending a prompt message for auditing the SQL statement to an account of an auditor with auditing permissions.

8. An SQL processing apparatus, comprising:

9. The apparatus according to claim 8, wherein the obtaining unit is configured to obtain at least one SQL statement in SQL text, and includes:

the acquiring unit is specifically configured to acquire an SQL work order, where the SQL work order at least includes an SQL text and a database identity identifier corresponding to the SQL text; judging whether the SQL text in the SQL work order meets the requirement of no-danger fields and the requirement that the text capacity is smaller than a capacity threshold value; and if the SQL sentences are satisfied, segmenting the SQL text to obtain the at least one SQL sentence.

10. The apparatus of claim 9, wherein the execution unit is configured to execute the SQL text in a database, and wherein the execution unit is configured to:

the execution unit is specifically configured to determine a database corresponding to the SQL text according to the database identity corresponding to the SQL text; executing each SQL statement in a database corresponding to the SQL text one by one; if the current SQL statement fails to be executed, rolling back the successfully executed SQL statement; and after each SQL statement is successfully executed, backing up the changed data in the database.