CN112686029A

CN112686029A - SQL new sentence identification method and device for database audit system

Info

Publication number: CN112686029A
Application number: CN202011579141.5A
Authority: CN
Inventors: 韦建明; 葛毅; 李宗霖
Original assignee: Jiangsu Suning Cloud Computing Co ltd
Current assignee: Jiangsu Suning Cloud Computing Co ltd
Priority date: 2020-12-28
Filing date: 2020-12-28
Publication date: 2021-04-20
Also published as: CA3144052A1

Abstract

The invention discloses a method and a device for identifying a SQL new sentence for a database audit system, relates to the technical field of big data, and can effectively reduce the resource occupation amount of the database audit system so as to improve the system performance. The method comprises the following steps: receiving SQL sentences in the network messages, and extracting main sentences after modularization processing; carrying out abstract calculation aiming at the main statement, and converting and processing an abstract value into an integral value; and looking up a table by using the integer value for comparison, and identifying whether the SQL sentence is a new SQL sentence or not based on the comparison result. The device is applied with the method provided by the scheme.

Description

SQL new sentence identification method and device for database audit system

Technical Field

The invention relates to the technical field of big data, in particular to a method and a device for identifying a SQL new sentence for a database auditing system.

Background

The database auditing system is mainly used for auditing various operation behaviors on the database server, acquiring network messages of the database server in a bypass mirror image or plug-in installation mode, and then extracting SQL statements in the messages and storing the SQL statements in a database of the database auditing system so as to carry out operations of inquiry, filtering, analysis and the like in the following process, thereby realizing the monitoring and auditing of the database server.

And the database auditing system judges whether the audited database server has a bug or an attacked behavior by analyzing the extracted SQL sentence. From the technical implementation point of view, the SQL statement is extracted, analyzed and stored, wherein the analysis is to audit whether the SQL statement has a bug or an attack. In order to ensure the accuracy of auditing, the auditing system needs to analyze all SQL statements, and as the number of database servers deployed by the auditing system is increased, the flow of database services to be audited is increased, and the existing software and hardware platforms cannot meet the requirements.

Analysis shows that in actual service flow, many SQL sentences are the same, and in addition, the syntax trunks of some sentences are the same, but the parameter values are different. The same statements as above need only be analyzed once in the audit system, and only a small portion of the traffic is required, assuming that the database audit system only processes these different SQL statements. This requires that the auditing system identify which statements are first to appear before performing SQL analysis.

The commonly used methods for identifying the SQL statement mainly include: character matching, AI recognition, SQL semantic parsing, and the like. The above methods all have certain disadvantages, for example, different parameter values cannot be ignored in character matching, for example, ID is 3 and ID is 4, the character matching considers that the two statements are two statements, repeated statements cannot be effectively filtered, and in addition, a large number of ultra-long SQL statements have high requirements on a CPU and a storage space; the accuracy of AI identification cannot be guaranteed in a short time in actual use, and in addition, an additional hardware server needs to be added, which increases the deployment cost of the system; SQL semantic analysis also occupies resources comparatively, and new sentence recognition is to filter a large number of repeated sentences and reduce the load of subsequent SQL semantic analysis, and only new sentences need to be subjected to semantic analysis.

Disclosure of Invention

The invention aims to provide a method and a device for identifying a SQL new sentence for a database auditing system, which can effectively reduce the resource occupation of the database auditing system and further improve the system performance.

In order to achieve the above object, a first aspect of the present invention provides a method for identifying a new SQL sentence for a database auditing system, including:

receiving SQL sentences in the network messages, and extracting main sentences after modularization processing;

carrying out abstract calculation aiming at the main statement, and converting and processing an abstract value into an integer value;

and looking up a table by using the integral value for comparison, and identifying whether the SQL statement is a new SQL statement or not based on a comparison result.

Preferably, the method for extracting the stem statement after modular processing comprises:

and identifying SQL grammar keywords in the SQL sentences, and removing the contents except the SQL grammar keywords in the SQL sentences to obtain the main sentences.

Preferably, before performing the summary calculation on the stem statement, the method further includes:

identifying whether a combined value exists in the stem statement;

if the combined value exists, the combined value is merged, and then the value of the parameter value is removed by adopting universal character replacement;

if the parameter value does not exist, directly replacing the parameter value with a universal character to perform value removal processing;

the parameter values include variable parameters and/or strings.

and replacing the non-ascii codes of the main statement by using universal characters.

Preferably, the method for performing digest calculation on the stem statement and converting a digest value to obtain an integer value includes:

performing abstract calculation on the processed main statement by adopting an MD5 algorithm to obtain an abstract value;

and extracting partial byte data of a fixed position from the abstract value, and converting the partial byte data into an integer value by adopting an integer algorithm.

Further, the method for looking up a table and comparing by using the integer value and identifying whether the SQL statement is a new SQL statement or not based on the comparison result comprises the following steps:

the table comprises a plurality of key-value key value pairs which are in one-to-one correspondence with the trunk statements, the key values are integer values of the corresponding trunk statements, and the value values are receiving time of the corresponding SQL statements;

looking up a table for an integer value corresponding to a currently received SQL statement, if a key value identical to the integer value exists in the table, identifying that the SQL statement is not a new SQL statement, meanwhile, taking the integer value as the key value, taking the receiving time of the SQL statement as a value, and storing the value in the table in a key-value key value pair mode;

and if the key value which is the same as the integral value does not exist in the table, identifying the SQL statement as a new SQL statement.

Further, identifying that the SQL statement is not a new SQL statement further comprises:

and comparing the receiving time difference value of the SQL statement newly added into the table with the receiving time difference value of the SQL statement corresponding to the same existing key value in the table, and deleting the existing key-value key value pair in the table when the receiving time difference value exceeds a threshold value.

Preferably, the method according to claim 1, further comprising:

carrying out new sentence identification on the SQL sentences identified as the SQL new sentences;

integrating the identification result, the corresponding SQL statement and the corresponding integer value into json data, and submitting the json data to a message queue for consumption;

and consuming the json data from the message queue by using a risk engine, and providing risk analysis service aiming at the SQL new statement.

Compared with the prior art, the SQL new sentence identification method for the database auditing system has the following beneficial effects:

in the SQL new sentence identification method for the database auditing system, when an SQL sentence of a network message is received, a trunk sentence is extracted after modularization processing, abstract calculation is carried out on the trunk sentence, an abstract value is converted into an integer value, then the integer value is used for table lookup and comparison, if a key value identical to the integer value exists in a table, the SQL sentence is identified not to be the SQL new sentence, meanwhile, the integer value is used as the key value, the receiving time of the SQL sentence is used as the value, the SQL sentence is stored in the table in a key-value pair mode, and if the key value identical to the integer value does not exist in the table, the SQL sentence is identified to be the new SQL sentence.

The method firstly carries out templating processing on the SQL sentences, extracts the SQL grammar backbone, then carries out summary calculation on the SQL grammar backbone, and finally uses an integer value to mark a group of SQL sentences with the same semantics. Compared with the character matching scheme in the prior art, the scheme of the invention shields the value information which changes constantly and only focuses on the main stem of the statement. Compared with SQL semantic analysis in the prior art, the scheme of the invention does not perform specific semantic analysis, focuses on whether the sentences are the same or not, and avoids unnecessary semantic analysis from occupying too much system resources. The practical use data of the production environment shows that the resources occupied by the new sentence identification method adopting the scheme of the invention can be almost ignored in the system, so that the resource occupation of the database audit system can be effectively reduced, and the system performance is improved.

A second aspect of the present invention provides an SQL new sentence recognition apparatus for a database auditing system, which is applied to the SQL new sentence recognition method for a database auditing system according to the foregoing technical solution, and the apparatus includes:

the system comprises a trunk extraction unit, a network message processing unit and a data processing unit, wherein the trunk extraction unit is used for receiving SQL sentences in the network message and obtaining trunk sentences after modularization processing;

the integer processing unit is used for carrying out abstract calculation on the main statement and converting and processing an abstract value into an integer value;

and the identification unit is used for looking up a table by using the integer value for comparison and identifying whether the SQL statement is a new SQL statement or not based on a comparison result.

Compared with the prior art, the beneficial effects of the SQL new sentence recognition device for the database auditing system provided by the invention are the same as the beneficial effects of the SQL new sentence recognition method for the database auditing system provided by the technical scheme, and the description is omitted here.

A third aspect of the present invention provides a computer-readable storage medium, on which a computer program is stored, the computer program, when being executed by a processor, executing the steps of the SQL new sentence recognition method for a database auditing system.

Compared with the prior art, the beneficial effects of the computer-readable storage medium provided by the invention are the same as the beneficial effects of the SQL new sentence identification method for the database auditing system provided by the technical scheme, and the description is omitted here.

Drawings

The accompanying drawings, which are included to provide a further understanding of the invention and are incorporated in and constitute a part of this specification, illustrate embodiments of the invention and together with the description serve to explain the invention and not to limit the invention. In the drawings:

FIG. 1 is a schematic flow chart of a method for identifying new SQL sentences for a database auditing system according to an embodiment of the present invention;

FIG. 2 is a schematic diagram of a processing flow of SQL statement templating according to an embodiment of the present invention;

FIG. 3 is a schematic flow chart of a SQL new sentence recognition process according to an embodiment of the present invention;

FIG. 4 is a flowchart illustrating a risk engine detecting a new SQL statement according to an embodiment of the present invention.

Detailed Description

In order to make the aforementioned objects, features and advantages of the present invention comprehensible, embodiments accompanied with figures are described in detail below. It is to be understood that the described embodiments are merely exemplary of the invention, and not restrictive of the full scope of the invention. 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 invention.

Example one

Referring to fig. 1 to 4, the present embodiment provides a method for identifying a new SQL sentence for a database auditing system, including:

receiving SQL sentences in the network messages, and extracting main sentences after modularization processing; carrying out abstract calculation aiming at the main statement, and converting and processing an abstract value into an integral value; and looking up a table by using the integer value for comparison, and identifying whether the SQL sentence is a new SQL sentence or not based on the comparison result.

In the method for identifying a new SQL sentence for a database auditing system provided by this embodiment, when an SQL sentence of a network message is received, a trunk sentence is extracted through modular processing, abstract calculation is performed on the trunk sentence, a digest value is converted into an integer value, then table lookup and comparison are performed by using the integer value, if a key value identical to the integer value exists in a table, it is identified that the SQL sentence is not the new SQL sentence, meanwhile, the integer value is used as the key value, the receiving time of the SQL sentence is used as the value, the value is stored in the table in a key-value pair form, and if a key value identical to the integer value does not exist in the table, the SQL sentence is identified as the new SQL sentence.

It can be seen that, in this embodiment, the templating process is performed on the SQL statements first, the SQL syntax backbone is extracted, then the abstract calculation is performed on the SQL syntax backbone, and finally, an integer value is used to identify a group of SQL statements having the same semantics. Compared with the character matching scheme in the prior art, the scheme of the embodiment shields the value information which changes constantly, and only focuses on the main stem of the statement. Compared with SQL semantic analysis in the prior art, the scheme of the embodiment does not perform specific semantic analysis, focuses on whether statements are the same or not, and avoids that unnecessary semantic analysis occupies too much system resources. The data actually used by the production environment shows that the resources occupied by the new sentence identification method adopting the scheme of the embodiment can be almost ignored in the system, so that the resource occupation of the database auditing system can be effectively reduced, and the system performance is improved.

Referring to fig. 2, in the above embodiment, the method for extracting the stem statement after the modular processing includes:

and identifying SQL grammar keywords in the SQL sentences, and removing the contents except the SQL grammar keywords in the SQL sentences to obtain the main sentences. Illustratively, the SQL syntax keywords include SELECT, INSERT, and the like.

In the above embodiment, before performing the digest calculation on the backbone statement, the method further includes:

identifying whether a combined value exists in the main statement; if the combined value exists, the combined value is merged, and then the value of the parameter value is removed by adopting universal character replacement; if the parameter value does not exist, directly replacing the parameter value with a universal character to perform value removal processing; for example, the combined VALUES are VALUES (1, 'TOM'), (2, 'JIMMY'), (3, 'SAM'), (4, 'LI'), and in the specific implementation, the common character can be used as "? ", the value after replacement with the universal character is VALUES (. The parameter values include a variable parameter and/or a string, illustratively, the variable parameter is "1" and the string is "TOM".

the non-ascii codes of the main sentence are replaced by universal characters, for example, in concat ('%', like "? ".

In the above embodiment, the method for performing digest calculation on a stem statement and converting a digest value to obtain an integer value includes:

performing abstract calculation on the processed main statement by adopting an MD5 algorithm to obtain an abstract value; and extracting partial byte data of the fixed position from the abstract value, and converting the partial byte data into an integer value by adopting an integer algorithm. Illustratively, the digest value is 10055897659947028311 bits in 16, and 8 th to 15 th byte data in a 16-bit byte are taken, and the 8-byte value is converted into a 64-bit integer value.

In the above embodiment, the method for looking up a table by using the integer value and comparing the table, and identifying whether the SQL statement is a new SQL statement based on the comparison result includes:

the table comprises a plurality of key-value key value pairs which are in one-to-one correspondence with the trunk statements, the key values are integer values corresponding to the trunk statements, and the value values are receiving time corresponding to the SQL statements; looking up a table for an integer value corresponding to a currently received SQL statement, if a key value identical to the integer value exists in the table, identifying that the SQL statement is not a new SQL statement, meanwhile, taking the integer value as the key value, taking the receiving time of the SQL statement as a value, and storing the value into the table in a key-value key value pair mode; and if the key value identical to the integer value does not exist in the table, identifying the SQL statement as the SQL new statement.

In the above embodiment, after identifying that the SQL statement is not a new SQL statement, the method further includes:

and comparing the receiving time difference value of the SQL statement newly added into the table with the receiving time difference value of the SQL statement corresponding to the same existing key value in the table, and deleting the existing key-value key value pair in the table when the receiving time difference value exceeds a threshold value. During specific implementation, the system periodically detects the key-value key value pair in the table, if the key value of the newly added SQL statement can be matched with the key-value key value pair in the table, the receiving time difference value is calculated according to the value of the newly added SQL statement and the corresponding value in the table, and if the receiving time difference value exceeds a threshold value, the previous key-value key value pair in the table is deleted.

In the above embodiment, after the table look-up comparison is performed by using the integer value, and whether the SQL statement is a new SQL statement is identified based on the comparison result, the method further includes:

carrying out new sentence identification on the SQL sentences identified as the SQL new sentences; integrating the identification result, the corresponding SQL statement and the corresponding integer value into json data, and submitting the json data to a message queue for consumption; and consuming json data from the message queue by using a risk engine, and providing a risk analysis service for the SQL new statement.

For ease of understanding, the scheme of the present embodiment is now described by way of example as follows:

when the method is specifically implemented, the SQL statement is subjected to templating processing, only SQL grammar keywords such as SELECT, INSERT AND the like in the SQL statement are reserved, value values such as numerical values, character strings AND the like in the SQL statement are shielded, the multi-row statements inserted by specific statements WHERE AND (), INSERT are subjected to merging processing, only an SQL grammar main stem is reserved, a main statement which does not contain specific numerical values is extracted, then MD5 summary calculation is carried out on the extracted main statement, the high-order 8 bytes of 16-bit summary values are taken, the 8-byte data are converted into 64-bit integer values, the purpose is to use one long integer value to represent one SQL statement, AND finally the long integer values are stored in an HASH table. The specific implementation method comprises the following steps:

step 1: receiving an SQL statement extracted from a network message, wherein the SQL statement is a complete SQ statement and comprises INSERT and/or SELECT SQL grammar keywords; for example, the INSERT syntax keywords are: INSERT intra user info _0(USERID, USERNAME) VALUES (1, 'TOM'), (2, 'JIMMY'), (3, 'SAM'), (4, 'LI'); the SELECT syntax key is: SELECT FROM spare _0WHERE ID is 1;

step 2: performing a value removal process on the SQL statement, namely uniformly replacing a numerical value or a character string in the SQL statement with "? ", such as replacing WHERE ID ═ 1 with WHERE ID? Replacement of WHERE NAME ═ titlename with WHERE NAME? ", it is emphasized that a complete value can only be replaced by a question mark, e.g. 123 by"? ", rather than" ".

After the processing of step 2, the above SELECT syntax key is replaced by: SELECT FROM using info _0WHERE ID? (ii) a

And step 3: combining the combined values of a plurality of records in the INSERT statement, for example, the INSERT statement INSERTs JSON array object data, the array object comprises a plurality of records, and different records can be identified into different statements, so that the records are required to be combined into one record;

through step 3, the INSERT statement is replaced by: INSERT intra USERINFO _0(USERID, USERNAME) VALUES (;

and 4, step 4: merging other sentences which need to merge values in the SELECT grammar keywords, such as a plurality of conditions in the sentences of WHERE AND, WHERE OR, WHERE NOT AND the like;

step 5, processing the non-ascii code, such as replacing the kanji character string with "? ";

step 6: after the processing in step 5 is completed, a trunk statement is obtained, and MD5 summarization is performed on the trunk statement, for example, the summarization values of the INSERT statement and the SELECT statement in the above step are 10055897659947028311 and 3935916992245373588 respectively;

and 7: the digest value is simplified by taking byte data of 8 th to 15 th bits in a 16-bit byte and converting the 8-bit value into a 64-bit integer value.

When a complete SQL statement is simplified into an integer value through the step of fig. 2, the complete SQL statement is learned into a hashmap table, where key of the table is the simplified integer value of the SQL statement, and vlaue is a timestamp of receiving the SQL statement.

Step 1: extracting SQL sentences from the network message, and extracting abstract values after performing templating processing as shown in the step of FIG. 2;

and 2, using the integer value obtained by the abstract calculation as a key to read the hashmap storage table, if the integer value can be read from the hashmap storage table, determining that the integer value is not a new SQL statement, and only updating the timestamp of the node at the moment, wherein the timestamp is used for overtime judgment. If the SQL statement cannot be read from the hashmap storage table, the SQL statement is considered as a new SQL statement, and at the moment, a key value and a value need to be added into the hashmap storage table;

and step 3: if the new SQL sentence is added with a new sentence mark, organizing the new sentence mark, the SQL sentence and the SQL sentence abstract integer value into json data;

and 4, step 4: the json data is submitted to a message queue for consumption.

Fig. 4 is a flowchart of a risk engine processing a new SQL statement, where the risk engine performs security analysis on the SQL statement and only processes the SQL statement with a new statement identifier.

Step 1: and the risk engine reads JSON data from the message queue, wherein the JSON data comprises a complete SQL statement and a new statement mark.

Step 2: and the risk engine judges the SQL sentence as a new sentence according to the new sentence mark, analyzes and processes the sentence, identifies whether the risk exists, and does not process the SQL sentence if the risk does not exist.

In summary, the present embodiment has the following beneficial effects:

SQL statement templating: the SQL statement is subjected to value removal and merging processing, and the statement backbone is extracted to form an SQL statement template, so that the occupation of system resources can be reduced, and the system performance is improved;

and (3) abstract processing: the abstract processing is carried out on the processed SQL statement template, and a long integer value is used for marking a main statement, so that the table lookup speed can be increased;

KMP optimization algorithm: in the templating process, sentence matching operation is required, the common intuitive character matching time complexity is O (MxN), the time complexity of the KMP algorithm is O (M + N), and the KMP algorithm is optimized by using the characteristics of SQL sentences, so that the time complexity is reduced to be below O (M-2N).

Example two

The embodiment provides an SQL new sentence recognition apparatus for a database auditing system, which includes:

the trunk extraction unit is used for receiving SQL sentences in the network messages and extracting trunk sentences after modularization processing;

Compared with the prior art, the beneficial effects of the SQL new sentence recognition device for the database auditing system provided by the embodiment of the invention are the same as the beneficial effects of the SQL new sentence recognition method for the database auditing system provided by the first embodiment, and are not repeated herein.

EXAMPLE III

The present embodiment provides a computer-readable storage medium, on which a computer program is stored, and when the computer program is executed by a processor, the steps of the SQL new sentence recognition method for a database auditing system are executed.

Compared with the prior art, the beneficial effects of the computer-readable storage medium provided by the embodiment are the same as the beneficial effects of the SQL new sentence recognition method for the database auditing system provided by the above technical scheme, and are not repeated herein.

It will be understood by those skilled in the art that all or part of the steps in the method for implementing the invention may be implemented by hardware instructions related to a program, the program may be stored in a computer-readable storage medium, and when executed, the program includes the steps of the method of the embodiment, and the storage medium may be: ROM/RAM, magnetic disks, optical disks, memory cards, and the like.

The above description is only for the specific embodiments of the present invention, but the scope of the present invention is not limited thereto, and any person skilled in the art can easily conceive of the changes or substitutions within the technical scope of the present invention, and the changes or substitutions should be covered within the scope of the present invention. Therefore, the protection scope of the present invention shall be subject to the protection scope of the appended claims.

Claims

1. A SQL new sentence identification method for a database auditing system is characterized by comprising the following steps:

2. The method of claim 1, wherein the method for extracting stem sentences after modular processing comprises:

3. The method of claim 1 or 2, wherein the performing the summary calculation for the stem statement further comprises:

identifying whether a combined value exists in the stem statement;

the parameter values include variable parameters and/or strings.

4. The method of claim 1 or 3, wherein the performing the summary calculation for the stem statement further comprises:

5. The method of claim 3 or 4, wherein the method for performing digest calculation on the stem statement and converting the digest value into an integer value comprises:

6. The method of claim 5, wherein the step of using the integer value to perform table lookup comparison and identifying whether the SQL statement is a new SQL statement based on the comparison result comprises:

7. The method of claim 6, wherein identifying that the SQL statement is not a new SQL statement further comprises:

8. The method of claim 1, further comprising:

9. An apparatus for identifying new SQL sentences for a database auditing system, comprising:

10. A computer-readable storage medium, on which a computer program is stored, which, when being executed by a processor, carries out the steps of the method according to any one of the claims 1 to 8.