CN108572968B - Data query method, device, server and system - Google Patents
Data query method, device, server and system Download PDFInfo
- Publication number
- CN108572968B CN108572968B CN201710138100.4A CN201710138100A CN108572968B CN 108572968 B CN108572968 B CN 108572968B CN 201710138100 A CN201710138100 A CN 201710138100A CN 108572968 B CN108572968 B CN 108572968B
- Authority
- CN
- China
- Prior art keywords
- data query
- user
- module
- data
- request
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Active
Links
Images
Abstract
The invention provides a data query method, a data query device, a server and a data query system. The method carries out first-class security detection through the data query request of the security log module, and the database service module carries out second-class security detection on the data query request; the safety log module carries out log recording on a data query request returned by the database service module and triggers the database query module to construct a data query statement according to the request; the database service module carries out data query according to the data query statement; and the user interaction module generates a result file according to the query result and sends a file downloading prompt to the user equipment. The invention can realize quick response to user query, customize query results according to user input, and effectively avoid the influence of SQL injection and excessive query on the server based on the first type of security detection on the user query request and the second type of security check on the execution result of the user query request.
Description
Technical Field
The invention belongs to the technical field of internet, and particularly relates to a data query method, device and system.
Background
Data query and data subscription are two main methods for data acquisition in the internet. For the data query requirement newly submitted by a user, a set of complete flow of requirement acceptance, requirement realization, result test and requirement online needs to be carried out on each conventional service system. The whole function has long line-connecting period and complicated dragging in all aspects.
Therefore, if the data query requirement is only the non-general requirement for serving the VIP user, the prior art is usually to directly access the database by operation and maintenance personnel to perform data query, and then export the result to the user. However, if these requirements are high frequency query or immediate feedback is needed, it will bring great cost and efficiency problems to users and operation and maintenance personnel.
In terms of data subscription: the report pushing is a main means of data subscription, a very mature technology is provided for generating reports at present, but various report platforms at present are very heavy, and a certain knowledge background is required for use. Moreover, accessing such reporting systems often brings many problems in complexity, security and the like to the business system, especially when the VIP user only needs simple reports such as monitoring and early warning, statistical summary and the like, the operation and maintenance personnel can not directly derive data from the database and then generate reports by Excel, which is a common practice in the prior art, but the efficiency and cost are still the bottleneck that is difficult to ignore.
When the prior art serves VIP users, the requirements of high-frequency query and instant feedback existing in non-general requirements of the prior art are difficult to meet in the aspect of data query; it is also susceptible to challenges in reporting services from the aspects of system complexity, security, efficiency, and cost.
Disclosure of Invention
In order to solve the problems in the prior art, embodiments of the present invention provide a data query method, apparatus, and system. The technical scheme is as follows:
in a first aspect, a data query method is provided, where the method includes the following steps: the user interaction module receives a data query request from a user terminal and sends the data query request to the security log module; the security log module receives a data query request from the user interaction module, performs first-class security detection on the data query request, and sends the request to a database service module if the first-class security detection is passed; the database service module carries out second-class security detection on the data query request, and returns the data query request to the security log module if the second-class security detection is passed; the security log module carries out security log recording on the data query request returned by the database service module, triggers the database query module to construct a data query statement according to the request and sends the data query statement to the database service module; the database service module carries out data query according to the data query statement and returns a query result to the user interaction module; and the user interaction module receives the query result, generates a result file according to the query result, stores the result file in a file service module, and sends a file downloading prompt to the user equipment.
In a second aspect, a data query apparatus is provided, the apparatus comprising: the system comprises a user interaction module, a safety log module and a data query module, wherein the user interaction module is used for receiving a data query request from a user terminal and sending the data query request to the safety log module; the user interaction module is also used for receiving the query result, generating a result file according to the query result, storing the result file in a file service module and sending a file downloading prompt to the user equipment; the security log module is used for receiving a data query request from the user interaction module, performing first-class security detection on the data query request, and sending the request to the database service module if the data query request passes the first-class security detection; the security log module is also used for carrying out security log recording on the data query request returned by the database service module, triggering the database query module to construct a data query statement according to the request and sending the data query statement to the database service module; the database service module is used for carrying out second-class security detection on the data query request, and returning the data query request to the security log module if the data query request passes the second-class security detection; the database service module is also used for carrying out data query according to the data query statement and returning a query result to the user interaction module.
In a third aspect, a data query method is provided, where the method includes: receiving a data query request, carrying out first-class security detection on the data query request, and if the data query request passes the first-class security detection, sending the request to a database service module so that the database service module carries out second-class security detection on the data query request; receiving a second type of safety detection result returned by the database service module; and carrying out safety log recording on a data query request returned by the database service module, constructing a data query statement according to the request, and sending the data query statement to the database service module so that the database service module carries out data query according to the data query statement.
In a fourth aspect, a data query apparatus is provided, the apparatus including the following modules: the security log module is used for receiving a data query request, performing first-class security detection on the data query request, and if the data query request passes the first-class security detection, sending the request to the database service module so that the database service module performs second-class security detection on the data query request; and carrying out safety log recording on the data query request returned by the database server; and the data query module constructs a data query statement according to the data query request, and sends the data query statement to the database service module so that the database service module can perform data query according to the data query statement.
In a fifth aspect, a server is provided, which includes the foregoing data query apparatus.
In a sixth aspect, a data query system is provided, which includes the aforementioned data query device.
The invention can achieve the following beneficial effects: the data query method and the data query device provided by the embodiment of the invention can realize quick response to user query, customize a query result according to user input, and effectively avoid the influence of SQL injection and excessive query on the server based on the first type of security detection on the user query request and the second type of security check on the result executed by the user query request.
Drawings
The following describes embodiments of the present invention in further detail with reference to the accompanying drawings;
FIG. 1 is a schematic diagram of an implementation environment provided by an embodiment of the invention.
Fig. 2 is a schematic structural diagram of a data query system according to an embodiment of the present invention.
Fig. 3 is a schematic flow chart of the working principle provided by the embodiment of the invention.
Fig. 4 is a flowchart of a data query method according to an embodiment of the present invention.
Fig. 5 is a flowchart of a data query method according to an embodiment of the present invention.
Fig. 6 is a flowchart of a data query method according to an embodiment of the present invention.
Fig. 7 is a flowchart of a data query method according to an embodiment of the present invention.
Fig. 8 is a schematic structural diagram of a server terminal according to an embodiment of the present invention.
Detailed Description
In order to make those skilled in the art better understand the technical solutions of the present invention, the technical solutions in the embodiments of the present invention will be clearly and completely described below with reference to the drawings in the embodiments of the present invention, and it is obvious that the described embodiments are only a part of the embodiments of the present invention, 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 invention.
In order to make the objects, technical solutions and advantages of the present invention more apparent, embodiments of the present invention will be described in detail with reference to the accompanying drawings.
The technical terms related to the embodiment of the invention are as follows:
the VIP user: a small number of user groups, such as system administrators, game administrators (gamemasters), forum administrators, etc., that need to get a quick response. Such people have low requirements on user interfaces, but need to get timely and accurate data acquisition services.
EPO system: and the material purchasing system is used for material purchasing and process interaction inside the enterprise.
Non-generalized data query requirements: the data query needs of a small portion of a specific population, which are not integrated into the existing business system due to easy change and low audience.
Referring to fig. 1 and 2, schematic structural diagrams of implementation environments involved in the data query method provided by the embodiment of the present invention are shown. The implementation environment includes: the system comprises user terminal equipment, a WEB server, a file server, a background server, a mail server, a database server and a system database server. Fig. 3 shows an exemplary flow of operations of each part in the implementation environment of fig. 1 and 2, after a user uses a data query function through a user device, the user device sends a request to a WEB server, the WEB server transmits the user request to a background server, the background server determines user permissions, if the user permissions meet requirements, the user request is transmitted to a security log server, log recording and security detection are performed at the security log server, a number of result lines is detected after the security detection, so as to avoid unlimited downloading of the user, after the number of result lines is detected, a detection result is transmitted to a security verification server through the data server, a log is recorded and the request is transmitted to a system database server, a data query statement is constructed at the system database server and transmitted to the data server for querying, the data server returns a query result obtained according to the query statement to a data query module of the system data server, the query result is returned to the WEB server, the WEB server pops up the query result on a file server, the WEB server transmits an instruction to the user device, and a file download prompt box is provided on the user device.
Fig. 3 is a diagram illustrating a data query method according to an embodiment of the present invention, which can be applied to the implementation environment shown in fig. 1. The method may comprise the steps of:
s310, the foreground server receives a data query request from the user equipment and sends the data query request to the background server.
After the user uses the user equipment, a data query request is generated through the user equipment, and the data query request comprises request information input by the user. The data function query request includes request information input by a user, for example, a request category, a category name, a price, a date, a quantity, and the like of the user, and as information transmitted between the client and the server, the query request is made only after the client logs in, the function cannot be used if the user does not log in, and the query function cannot be used or the query category is limited if the user logs in by using a tourist or an account with limited functions.
The foreground server is used for responding to a data query request of the user equipment, and after the foreground server receives the query request from the user terminal, the foreground server sends the query request to the background server so as to obtain the authority information of the user.
In one example, user devices include, but are not limited to, mobile devices, which may be smart phones, PADs, PDAs, etc., and terminal devices, which may be personal PCs. The mobile class device and the terminal device communicate with a server based on a BS (Browser-server) protocol or a CS (Client-server) protocol, and adapt to different terminals (computers, mobile phones, tablets and the like) based on a BS architecture; without complex menu items and business logic, the user is fast to get. Based on the CS framework, a user can operate on a terminal provided with a client.
S320, the background server receives the data query request from the foreground server, performs first-class security detection on the data query request, and sends the request to a database server if the data query request passes the first-class security detection.
The database server performs security detection on the query request, wherein the security detection can be local detection of the database server or combined detection of the background server and the database server. In the database query process, the security risk is mainly focused on three aspects: 1. a user may perform SQL injection during a query, thereby incurring SQL injection risks; 2. the query of the user is recorded excessively, so that the risk of overload of the server is brought; 3. the query result is anti-repudiation. In order to prevent the risks of the three aspects, safety detection is performed for the three aspects. After passing the security check, the database server returns the query request to the backend server.
In one example, the first type of security check is a verification of the security attributes of the data query command itself, such as risk item verification and query interval verification.
And S330, the database server performs second-class security detection on the data query request, and if the data query request passes the second-class security detection, the database server returns the data query request to the background server.
In one example, the second type of security check is a check and verification of the results of a data query command execution. For example, by verifying the number of result lines, it is determined whether the query result is overloaded.
S340, the background server carries out safety log recording on the data query request returned by the database server, constructs a data query statement according to the request and sends the data query statement to the database server.
The background server receives a data query request sent by the database server, records the query request record into a safety log record, constructs a data query statement according to the query request, and then sends the constructed query statement to the server of the database for query.
And S350, the database server carries out data query according to the data query statement and returns a query result to the foreground server.
S306, the foreground server receives the query result, generates a result file according to the query result, stores the result file in the foreground server, and sends a file downloading prompt to the user equipment.
Fig. 4 is a diagram illustrating a data query method according to an embodiment of the present invention, which can be applied to the implementation environment shown in fig. 1. The method may comprise the steps of:
s410, the foreground server receives a data query request from the user terminal and sends the data query request to the background server.
After the user uses the user equipment, a data query request is generated through the user equipment, and the data query request comprises request information input by the user. The data function query request includes request information input by a user, for example, a request category, a name, a price, a date, a quantity, and the like of the user, and as information transmitted between the client and the server, the query request may be performed after the client logs in, the function may not be used if the user does not log in, or the query function may not be used or the query category may be limited if the user logs in using a guest or an account with limited functions.
The foreground server is used for responding to a data query request of the user equipment, and after the foreground server receives the query request from the user terminal, the foreground server sends the query request to the background server so as to obtain the authority information of the user.
In one particular example, user devices include, but are not limited to, mobile devices, which may be smartphones, tablets (PADs), PDAs, etc., and terminal devices, which may be personal PCs. The mobile class device and the terminal device communicate with a server based on a BS (Browser-server) protocol or a CS (Client-server) protocol, and adapt to different terminals (computers, mobile phones, tablets and the like) based on a BS architecture; without complex menu items and business logic, the user is fast to get. Based on the CS framework, a user can operate on a terminal provided with a client.
In a specific example, the foreground server may include a WEB server for receiving and forwarding various requests and files, and a file server for storing the generated data files.
S420, the background server receives the data query request from the foreground server, performs first-class security detection on the data query request, and sends the request to a database server if the data query request passes the first-class security detection.
After receiving the data query request sent by the foreground server, the background server analyzes the data query request, verifies the user permission of the data query request, if the user permission is matched with the data query request, the user has the permission to query corresponding data, the background server releases the query request, the background server performs safe log recording on the query request, records the user request and sends the request to the database server.
In one example, the backend server may include a rights verification module, a security log module, and a database query module. And the authority verification module is used for verifying the user authority of the data query request from the foreground server. And the safety log module is used for recording the safety log and performing safety detection in cooperation with the database server. And the database query module is used for constructing a data query statement according to the data query request which passes the security verification and is returned by the database server and sending the data query request to the database server.
In one example, the authority verification module queries a data query instance set (smart data _ SQL) belonging to the user through a data query authority table (smart data _ RIGHT) according to the user identity information (staff _ id). Alternatively, the rights verification server directly determines whether the set of data query instances (smart data _ SQL) belongs to the user. No matter which judgment method is used, the authority of the user is verified by using the authority server, and after the authority of the user passes the verification, namely the user has the authority matched with the initiating request, the data query request is legal.
And S430, the database server performs second-class security detection on the data query request, and if the second-class security detection is passed, the database server returns the data query request to the background server.
In one example, the security log module is used for performing security verification and security log recording on the user data query request which passes the authority verification of the authority verification module. The security verification mainly comprises three aspects: 1. the user may perform SQL injection in the query; 2. the system pressure brought by excessive record of user query; 3. is that the query results are anti-repudiated. In contrast, the safety verification module comprises three sub-modules for performing verification in three aspects, wherein the three sub-modules are respectively an SQL injection prevention sub-module, a query record result number judgment module and a log recording module. Illustratively, the SQL injection prevention sub-module is used to prevent SQL injection that may exist in a user data query command, and the module is used to determine whether a data query request includes a risk item, and when the request is found to include the risk item, the request includes SQL injection risk, reject the data query request, generate alarm information, and then return a reject request message to the front-end server, where the risk item may be, for example, an Insertion (INSERT), an UPDATE (UPDATE), or a specified pattern (LIKE) in a search column in a clause. Illustratively, the query record result number judging module sends a data request to the database server, judges the number of returned result lines, judges that the number of result lines requested to be accessed by the user is excessive when the number of result lines is greater than a predetermined result line number threshold, rejects the data query request, generates alarm information, and returns a request rejection message to the front-end server. Illustratively, the logging module records information such as the IP of the user, the query function used, the query parameter, and the number of obtained recording lines into a log table and a local file, so as to prevent the user from repudiating the query content. Without loss of generality, the logging module will backup the log table and local files to the database server.
In one example, as shown in fig. 5, after the background server receives the user data query request, the following steps are initiated to perform the security check.
S4301, the SQL injection prevention sub-module verifies whether the data query request contains risk items, if the data query request contains the risk items, the data query request is rejected, alarm information is generated, and then the rejection request message is returned to the front-end server.
And S4302, if no risk item is found, the request interval judgment module judges whether the interval of the user data query request is too short, if the interval is smaller than a preset time threshold, the request interval judgment module judges that the data request of the user is too frequent, rejects the current data query request, generates alarm information, and then returns a request rejection message to the front-end server.
S4303, if the interval of the data query request of the user is greater than the preset time threshold, the query record result number judging module sends the data request to the database server, judges the number of returned result lines, judges that the number of result lines accessed by the user request is excessive when the number of result lines is greater than the preset result line number threshold, rejects the data query request, generates alarm information, and returns the request rejection message to the front-end server.
S4304, if the number of returned result lines meets the requirement, the safety verification is passed, the log recording module records the IP of the user, the used query function, the query parameter, the obtained number of record lines and other information to the log table and the local file, so as to prevent the user from denying the query content.
Through the interactive cooperation between the background server and the database server in the steps S4301-S4304, the injection prevention, the frequent query prevention of the user, the too large query amount prevention of the user and the repudiation of the query result of the user to the SQL are realized.
S440, the background server carries out safety log recording on the data query request returned by the database server, constructs a data query statement according to the request, and sends the data query statement to the database server.
In one example, constructing the query statement includes constructing a query statement of a data switching manner, the key to the database query implementation is to switch a target database of the query and perform the data query, and the data switching manner is as follows: and according to the TARGET data (TARGET _ DB) field in the data table (SMARTDATA _ SQL), acquiring a corresponding database connection character string in the configuration management system to open the database. As shown in fig. 1, data required by a user is not stored in one database but stored in a plurality of databases (DB 1, DB2, DB3, \8230;), which may be database servers or distributed servers based on cloud servers. In order to realize the query in a plurality of databases and ensure the account security and the data security when the data is queried, the switching between the databases is realized by using a TARGET database (TARGET _ DB) field in a database table. The target data field stores a series of character string values (PRODUCT _ DB _1, PRODUCT _db _2, \8230; in the query process, the server does not use a user name and a password to query, but uses a Key Value (Key) to query, queries the character string corresponding to the Value (Value) corresponding to the Value, and then opens the corresponding database according to the character string to read and write the content, thereby realizing the switching of the query among different servers.
In one example, the query with the character string is performed in a database query module of the background server, for example, a query on the configuration management system using PRODUCT _ DB _1 can obtain a database connection character string, and then the corresponding database can be opened according to the character string for reading and writing the content.
And S450, the database server carries out data query according to the data query statement and returns a query result to the foreground server.
The data query mode is as follows: the user's input is used as a binding variable to participate in the query to further avoid SQL injection. When a user logs in, the user needs to search the database for the existence of the user name and the password, and at this time, two ways exist:
one is a concatenated string, such as select from dt _ users name = 'username' and psw = 'password'.
Yet another way is to use binding variables: select from dt _ users where name = @ p1 and psw = @ p2. In the statement, the variable is bound by putting the information input by the user at the position of the variable for searching, and in the binding process, the input by the user only exists in the form of the queried information variable (@ p1, @ p 2), so that SQL injection risk can be prevented.
In one example, as shown in fig. 6, switching to query the target database and performing the data query includes the following steps:
s4501, a query condition input by a user and a SMART _ SQL record corresponding to the query function are recorded, and data query is started.
S4502, obtaining the database connection character string from the TARGET _ DB deconfiguration table.
S4503, a use binding variable SqlCommand is constructed according to the input of the user and SQL _ TEXT.
S4504, the target database is opened, and query is carried out by using SqlCommand.
S4505, closing the database connection, converting the query result into a DataTable, and then returning.
And S460, the foreground server receives the query result, generates a result file according to the query result, stores the result file in the foreground server, and sends a file downloading prompt to the user equipment.
In the above embodiment, before the user equipment sends the data query request, the method further includes: s400, a user login step. As shown in fig. 7, the user login step S400 specifically includes the following sub-steps:
s4001, the user client sends the login information to a foreground server.
S4002, the foreground server receives the login information and forwards the login information to the background server.
S4003, the background server receives the login information, verifies the authority of the login information, and returns a data query function set belonging to the login information to the front-end server.
S4004, the foreground server sends rendering user interface information to the user equipment according to the data query function set returned by the background server.
In the process, the background server realizes the program logic of data query and data subscription, and queries different service database servers according to the specific configuration of the query; the authority of the user, the configuration of the query function and the like are stored in a system database; the background server can use the mail service provided by the company mail server when executing the data subscription task; the operation and maintenance personnel can complete the configuration only by executing SQL on the system database.
As shown in fig. 1, it illustrates a data query system provided by an embodiment of the present invention. The system may be used in the implementation environment shown in fig. 1. The system comprises:
the foreground server receives a data query request from the user terminal and sends the data query request to the background server.
After the user uses the user equipment, a data query request is generated through the user equipment, and the data query request comprises request information input by the user. The data function query request includes request information input by a user, for example, a request category, a category name, a price, a date, a quantity, and the like of the user, and as information transmitted between the client and the server, the query request is made only after the client logs in, the function cannot be used if the user does not log in, and the query function cannot be used or the query category is limited if the user logs in by using a tourist or an account with limited functions.
The foreground server is used for responding to a data query request of the user equipment, and after the foreground server receives the query request from the user terminal, the foreground server sends the query request to the background server so as to obtain the authority information of the user.
In one particular example, user devices include, but are not limited to, mobile devices, which may be smartphones, tablets (PADs), PDAs, etc., and terminal devices, which may be personal PCs. The mobile class device and the terminal device communicate with a server based on a BS (Browser-server) protocol or a CS (Client-server) protocol, and adapt to different terminals (computers, mobile phones, tablets and the like) based on a BS architecture; without complex menu items and business logic, the user is fast to get. Based on the CS framework, a user can operate on a terminal provided with a client.
In a specific example, the foreground server may include a WEB server for receiving and forwarding various requests and files, and a file server for storing the generated data files.
The background server is used for receiving the data query request from the foreground server, carrying out first-class security detection on the data query request, and sending the request to the database server if the data query request passes the first-class security detection. After receiving the data query request sent by the foreground server, the background server analyzes the data query request, verifies the user authority of the data query request, if the user authority is matched with the data query request, indicates that the user has the authority to query corresponding data, releases the query request, performs safe log recording on the query request, records the user request, and sends the request to the database server.
In one example, the backend server can include an authority verification module, a security log module, and a database query module. And the authority verification module is used for verifying the user authority of the data query request from the foreground server. And the safety log module is used for recording the safety log and performing safety detection by matching with the database server. And the database query module is used for constructing a data query statement according to the data query request which is returned by the database server and passes the security verification, and initiating the data query request to the database server.
In one example, the authority verification module queries a data query instance set (smart data _ SQL) belonging to the user through a data query authority table (smart data _ RIGHT) according to the user identity information (staff _ id). Alternatively, the rights verification server directly determines whether the set of data query instances (smart data _ SQL) belongs to the user. No matter which judgment method is used, the authority of the user is verified by using the authority server, and after the authority of the user passes the verification, namely the user has the authority matched with the initiating request, the data query request is legal.
And the database server performs second-class security detection on the data query request, and returns the data query request to the background server if the second-class security detection is passed.
In one example, the security log module is used for performing security verification and security log recording on the user data query request which passes the authority verification of the authority verification module. The security authentication mainly comprises three aspects: 1. the user may perform SQL injection in the query; 2. the system pressure brought by excessive record of user query; 3. is that the query results are anti-repudiation. In contrast, the security verification module comprises three sub-modules for performing verification in three aspects, wherein the three sub-modules are respectively an SQL injection prevention sub-module, an inquiry record result number judgment module and a log record module. Illustratively, the SQL injection prevention sub-module is used to prevent SQL injection that may exist in a user data query command, and the module is used to determine whether a data query request includes a risk item, and when the request is found to include the risk item, the request includes SQL injection risk, reject the data query request, generate alarm information, and then return a reject request message to the front-end server, where the risk item may be, for example, an Insertion (INSERT), an UPDATE (UPDATE), or a specified pattern (LIKE) in a search column in a clause. Illustratively, the query record result number judging module sends a data request to the database server, judges the number of returned result lines, judges that the number of result lines requested to be accessed by the user is excessive when the number of result lines is greater than a predetermined result line number threshold, rejects the data query request, generates alarm information, and returns a request rejection message to the front-end server. Illustratively, the logging module records information such as the IP of the user, the used query function, the query parameter, and the obtained number of record lines into the log table and the local file, so as to prevent the user from being repudiated with the query content. Without loss of generality, the logging module will backup the log table and local files to the database server.
In one example, after receiving the user data query request at the background server, the security check is performed by the following sub-modules.
The SQL injection-proof sub-module is used for executing the first type detection, verifying whether the data query request contains a risk item or not by the SQL injection-proof sub-module, rejecting the data query request and generating alarm information when finding that the request contains the risk item, and then returning rejection request information to the front-end server.
And the interval detection submodule is used for executing the first type of detection, judging whether the interval of the user data query request is too short or not by the request interval judgment module if the risk item is not found, judging that the data request of the user is too frequent if the interval is less than a preset time threshold, rejecting the current data query request, generating alarm information and returning the rejection request message to the front-end server.
And the result line number detection module is used for executing the second type of detection, if the data query request interval of the user is greater than a preset time threshold, the query record result number judgment module sends a data request to the database server and judges the number of returned result lines, when the number of result lines is greater than the preset result line number threshold, the result line number of the user requesting access is judged to be excessive, the data query request is rejected, alarm information is generated, and then a request rejection message is returned to the front-end server.
And if the number of returned result lines meets the requirement, the safety verification is passed, and the log recording module can record information such as the IP of the user, the used query function, the query parameter, the obtained number of record lines and the like into the log table and the local file so as to prevent the user from denying the query content.
Through the interactive cooperation between the background server and the database server, the SQL is prevented from being injected, the frequent query of users is prevented, the excessive query quantity of the users is prevented, and the user can be denied to the query result.
And the background server carries out safety log recording on the data query request returned by the database server, constructs a data query statement according to the request and sends the data query statement to the database server.
In one example, constructing a query statement includes constructing a query statement in a data switching manner, the key to implementing database query is to switch a target database of the query and perform data query, and the data switching manner is: and according to the TARGET data (TARGET _ DB) field in the data table (SMARTDATA _ SQL), acquiring a corresponding database connection character string in the configuration management system to open the database. As shown in fig. 1, data required by a user is not stored in one database but stored in a plurality of databases (DB 1, DB2, DB3, \8230;), which may be database servers or distributed servers based on cloud servers. In order to realize the query in a plurality of databases and ensure account security and data security in the data query, the switching between the databases is realized by using a TARGET database (TARGET _ DB) field in a database table. The target data field stores a series of character string values (PRODUCT _ DB _1, PRODUCT _db _2, \8230; in the query process, the server does not use a user name and a password to query, but uses a Key Value (Key) to query, queries the character string corresponding to the Value (Value) corresponding to the Value, and then opens the corresponding database according to the character string to read and write the content, thereby realizing the switching of the query among different servers.
In one example, the query with the character string is performed in a database query module of the background server, for example, a query on the configuration management system using PRODUCT _ DB _1 can obtain a database connection character string, and then the corresponding database can be opened according to the character string for reading and writing the content.
And the database server performs data query according to the data query statement and returns a query result to the foreground server.
The data query mode is as follows: the user's input is used as a binding variable to participate in the query to further avoid SQL injection. When a user logs in, the user needs to search the database for whether the user name and the password exist, and at this time, two ways exist:
one is a concatenated string, such as select from dt _ users name = 'username' and psw = 'password'.
Yet another way is to use binding variables: select from dt _ users where name = @ p1 and psw = @ p2. In the statement, the variable is bound by putting the information input by the user at the position of the variable for searching, and in the binding process, the input by the user only exists in the form of the queried information variable (@ p1, @ p 2), so that SQL injection risk can be prevented.
In one example, switching to query the target database and performing the data query is implemented by:
and the data query sub-module is used for starting to perform data query after having query conditions input by a user and SMART _ SQL records corresponding to the query functions.
And the character string acquisition submodule acquires the database connection character string from the configuration table according to the TARGET _ DB.
And the binding variable constructing submodule constructs a used binding variable SqlCommand according to the input of the user and the SQL _ TEXT.
And the SQL query sub-module opens the target database and queries by using the SqlCommand.
And the output sub-module closes the connection of the database, converts the query result into a DataTable and then returns.
And the foreground server receives the query result, generates a result file according to the query result, stores the result file in the foreground server, and sends a file downloading prompt to the user equipment.
In the above embodiment, before the user equipment sends the data query request, a user login process is further included. The user login process is realized through the following modules:
and the login information sending submodule is arranged on the foreground server, and the user client sends the login information to the foreground server.
And the login information forwarding submodule is arranged on the foreground server and used for receiving the login information and forwarding the login information to the background server.
And the authority verification submodule is arranged on the background server and used for receiving the login information, verifying the authority of the login information and returning a data query function set belonging to the login information to the front-end server.
And the login information return submodule is arranged on the premise server and used for sending the rendering user interface information to the user equipment according to the data query function set returned by the background server.
As shown in fig. 2, in one example, the Web server is based on a front-end implementation of AngularJS and asp.
An interface rendering module: and generating webpage content according to the user authority, and performing automatic adaptation of multiple terminals.
A user interaction module: and acquiring user input, calling background service, and feeding back a result to the user.
A file generation module: and converting the result returned by the database query into a file for downloading by a user, such as an Excel file.
In one example, a backend server is based on a.net WCF implementation, program logic for performing data queries and data subscriptions, comprising:
the authority verification module: judging the authority of the user according to the login information of the user;
a security log module: carrying out safety filtration, download limitation and log recording on the operation;
a database query module: generating a query character string, querying on a target database and then returning a query result;
a chart generation module: generating a chart according to the related query result and the chart style;
the abstract generating module: generating an abstract according to the related query result and an abstract style;
a mail generation module: combining the attachments, the abstract and the diagram according to the mail template to generate and send a mail;
a subscription queue control module: and controlling the pushing of the subscription task.
In this embodiment, a file server providing a file download service, a mail server providing a mail push service, and each service database server that needs to perform data query are used.
Referring to fig. 8, a schematic structural diagram of a server according to an embodiment of the present invention is shown. The server is used for implementing the data query method on the server side provided in the above embodiment. Specifically, the method comprises the following steps:
the server 1200 includes a Central Processing Unit (CPU) 1201, a system memory 1204 including a Random Access Memory (RAM) 1202 and a Read Only Memory (ROM) 1203, and a system bus 1205 connecting the system memory 1204 and the central processing unit 1201. The server 1200 also includes a basic input/output system (I/O system) 1206 to facilitate transfer of information between devices within the computer, and a mass storage device 1207 for storing an operating system 1213, application programs 1214, and other program modules 1215.
The basic input/output system 1206 includes a display 1208 for displaying information and an input device 1209, such as a mouse, keyboard, etc., for a user to input information. Wherein the display 1208 and input device 1209 are connected to the central processing unit 1201 through an input-output controller 1210 coupled to the system bus 1205. The basic input/output system 1206 may also include an input/output controller 1210 for receiving and processing input from a number of other devices, such as a keyboard, mouse, or electronic stylus. Similarly, an input-output controller 1210 also provides output to a display screen, a printer, or other type of output device.
The mass storage device 1207 is connected to the central processing unit 1201 through a mass storage controller (not shown) connected to the system bus 1205. The mass storage device 1207 and its associated computer-readable media provide non-volatile storage for the server 1200. That is, the mass storage device 1207 may include a computer-readable medium (not shown) such as a hard disk or CD-ROM drive.
Without loss of generality, the computer-readable media may comprise computer storage media and communication media. Computer storage media includes volatile and nonvolatile, removable and non-removable media implemented in any method or technology for storage of information such as computer readable instructions, data structures, program modules or other data. Computer storage media includes RAM, ROM, EPROM, EEPROM, flash memory or other solid state memory technology, CD-ROM, DVD, or other optical storage, magnetic cassettes, magnetic tape, magnetic disk storage or other magnetic storage devices. Of course, those skilled in the art will appreciate that the computer storage media is not limited to the foregoing. The system memory 1204 and mass storage device 1207 described above may be collectively referred to as memory.
The server 1200 may also operate as a remote computer connected to a network via a network, such as the internet, in accordance with various embodiments of the present invention. That is, the server 1200 may connect to the network 1212 through a network interface unit 1211 coupled to the system bus 1205, or the network interface unit 1211 may be used to connect to other types of networks and remote computer systems (not shown).
The memory also includes one or more programs stored in the memory and configured to be executed by one or more processors. The one or more programs include instructions for performing the method of the backend server side.
In an exemplary embodiment, a non-transitory computer readable storage medium is further provided, for example, a memory including instructions executable by a processor of a terminal to perform the steps of the method embodiments on the side of a sender client or a receiver client, or executed by a processor of a server to perform the steps of the method embodiments on the side of a background server. For example, the non-transitory computer readable storage medium may be a ROM, a Random Access Memory (RAM), a CD-ROM, a magnetic tape, a floppy disk, an optical data storage device, and the like.
It should be understood that the reference to "a plurality" in the present embodiment means two or more. "and/or" describes the association relationship of the associated object, indicating that there may be three relationships, for example, a and/or B, which may indicate: a exists alone, A and B exist simultaneously, and B exists alone. The character "/" generally indicates that the former and latter associated objects are in an "or" relationship.
The above-mentioned serial numbers of the embodiments of the present invention are merely for description and do not represent the merits of the embodiments.
It will be understood by those skilled in the art that all or part of the steps for implementing the above embodiments may be implemented by hardware, or may be implemented by a program instructing relevant hardware, where the program may be stored in a computer-readable storage medium, and the storage medium may be a read-only memory, a magnetic disk or an optical disk.
The above description is only for the purpose of illustrating the preferred embodiments of the present invention and is not to be construed as limiting the invention, and any modifications, equivalents, improvements and the like that fall within the spirit and principle of the present invention are intended to be included therein.
Claims (22)
1. A method for data query, the method comprising the steps of:
the user interaction module receives a data query request from a user terminal and sends the data query request to the security log module;
the security log module receives a data query request from the user interaction module, and performs a first type of security detection on the data query request, including: the security log module inquires whether the data inquiry request contains a risk item, and rejects the data inquiry request and returns a rejection result to the user equipment when finding that the data inquiry request contains the risk item; if no risk item is found, the security log module inquires whether the use interval time of the user is too short, if so, the data inquiry request is rejected, and the rejection result is returned to the user equipment, and if not, the first-class security detection is passed; if the first type of security detection is passed, the request is sent to a database service module;
the database service module performs a second type of security detection on the data query request, including: the database service module judges whether the number of recording lines of the query result is greater than a preset threshold value, if so, the database service module rejects the data query request and returns a rejected result to the user equipment; if not, passing the second type of safety detection; if the data passes the second type of security detection, returning a data query request to the security log module;
the security log module carries out security log recording on the data query request returned by the database service module, triggers the database query module to construct a data query statement according to the request and sends the data query statement to the database service module;
the database service module carries out data query according to the data query statement and returns a query result to the user interaction module;
and the user interaction module receives the query result, generates a result file according to the query result, stores the result file in a file service module, and sends a file downloading prompt to the user equipment.
2. The method according to claim 1, characterized in that the method further comprises the steps of:
the user client sends the login information to the user interaction module;
the user interaction module receives the login information and forwards the login information to the authority verification module;
the authority verification module receives the login information, verifies the authority of the login information and returns a data query function set belonging to the login information to the user interaction module;
and the user interaction module triggers the interface rendering module to generate interface rendering information according to the data query function set returned by the permission verification module, and sends the interface rendering information to the user equipment.
3. The method according to claim 1, further comprising, before the first type of security check is performed on the data query request, the following steps:
the security log module sends the data query request to the authority verification module;
and the authority verification module verifies the user authority of the data query request, and returns the data query request to the security log module if the user authority is matched with the data query request.
4. The method of claim 3, wherein the step of the authority verification module verifying the user authority of the data query request comprises:
the authority verification module queries a structured query instance set belonging to the user data through a data authority table according to the identity information of the user login;
or, the permission verification module judges whether the data structured query instance set belongs to the user.
5. The method of claim 1, wherein the securely logging, by the background service module, the data query request returned by the database service module comprises: and recording the user information and the query parameters into a security log table and a local file.
6. The method of claim 1, wherein the step of the database service module performing data query according to the data query statement comprises:
receiving a query condition and a data structured query language input by a user;
acquiring a database connection character string according to a target database configuration table;
constructing a structured query language command using binding variables according to user input and a structured query language;
opening a target database according to the database connection character string, and querying by using a structured query language command of a binding variable;
and converting the query result into a data table.
7. A data query apparatus, characterized in that the apparatus comprises:
the system comprises a user interaction module, a security log module and a security log module, wherein the user interaction module is used for receiving a data query request from a user terminal and sending the data query request to the security log module; the user interaction module is also used for receiving the query result, generating a result file according to the query result, storing the result file in a file service module and sending a file downloading prompt to the user equipment;
the security log module is used for receiving a data query request from the user interaction module, performing first-class security detection on the data query request, and if the data query request passes the first-class security detection, sending the request to the database service module; the safety log module is also used for carrying out safety log recording on a data query request returned by the database service module, triggering the database query module to construct a data query statement according to the request and sending the data query statement to the database service module; the security log module is also used for inquiring whether the data query request contains risk items, rejecting the data query request when finding that the data query request contains the risk items, and returning the rejected result to the user equipment; if no risk item is found, inquiring whether the use interval time of the user is too short, if so, rejecting the data inquiry request, returning a rejection result to the user equipment, and if not, passing the first-type safety detection;
the database service module is used for carrying out second-class security detection on the data query request, and returning the data query request to the security log module if the second-class security detection is passed; the database service module is also used for carrying out data query according to the data query statement and returning a query result to the user interaction module; the database service module is also used for judging whether the number of the recording lines of the current query result exceeds a preset threshold value, if so, rejecting the data query request, and if not, returning the rejected result to the user equipment, and if not, passing the second type of security detection.
8. The apparatus of claim 7, wherein the user interaction module is further configured to receive login information from the user equipment, and forward the login information to the permission verification module;
the authority verification module is used for receiving the login information, verifying the authority of the login information and returning a data query function set belonging to the login information to the user interaction module;
and the user interaction module is used for triggering the interface rendering module to generate interface rendering information according to the data query function set returned by the permission verification module and sending the interface rendering information to the user equipment.
9. The apparatus of claim 7,
the security log module is used for sending the data query request to the authority verification module;
and the authority verification module is used for verifying the user authority of the data query request, and returning the data query request to the security log module if the user authority is matched with the data query request.
10. The apparatus of claim 9, wherein the permission verification module verifies the user permission of the data query request includes:
inquiring a structured inquiry example set belonging to the user data through a data authority table according to the identity information of the user login;
alternatively, a determination is made as to whether the set of data structured query instances belongs to the user.
11. The apparatus of claim 7, wherein the back office module records user information and query parameters into a security log table and a local file.
12. The apparatus of claim 7, wherein the database service module further comprises sub-modules for:
the receiving submodule is used for receiving the query conditions and the data structured query language input by the user;
the connection character string acquisition sub-module is used for acquiring a database connection character string according to a target database configuration table;
the structured query submodule is used for constructing a structured query language command using binding variables according to user input and a structured query language;
the database query submodule is used for opening a target database according to the database connection character string and querying by using a structured query language command of a binding variable;
and the result conversion sub-module is used for converting the query result into a data table.
13. A method of data query, the method comprising the steps of:
receiving a data query request, and performing first-class security detection on the data query request, wherein the first-class security detection comprises the following steps: inquiring whether the data query request contains risk items, if so, rejecting the data query request and returning a rejection result to the user equipment; if no risk item is found, inquiring whether the use interval time of the user is too short, if so, rejecting the data inquiry request, returning a rejection result to the user equipment, and if not, passing the first-type safety detection; if the first-class security detection is passed, the request is sent to a database service module, so that the database service module performs second-class security detection on the data query request, and the method comprises the following steps: the database service module judges whether the number of the recording lines of the query result is greater than a preset threshold value, if so, the database service module rejects the data query request and returns a rejected result to the user equipment, and if not, the database service module passes the second-class security detection; receiving a second type of safety detection result returned by the database service module;
and carrying out safety log recording on a data query request returned by the database service module, constructing a data query statement according to the request, and sending the data query statement to the database service module so that the database service module carries out data query according to the data query statement.
14. The method according to claim 13, further comprising, before the first type of security check is performed on the data query request, the following steps:
and verifying the user authority of the data query request, and if the user authority is matched with the data query request, performing first-class security detection on the data query request by the background service module.
15. The method of claim 14, wherein the step of verifying the user right to send the data query request comprises:
according to the identity information of user login, a structured query instance set belonging to the user data is queried through a data authority table;
alternatively, a determination is made as to whether the set of data structured query instances belongs to the user.
16. The method of claim 13, wherein securely logging the data query request returned by the database service module comprises: and recording the user information and the query parameters into a security log table and a local file.
17. A data query apparatus, characterized in that the apparatus comprises the following modules:
the security log module is used for receiving a data query request and performing first-class security detection on the data query request, and comprises: inquiring whether the data query request contains risk items, if so, rejecting the data query request and returning the rejection result to the user equipment; if no risk item is found, inquiring whether the use interval time of the user is too short, if so, rejecting the data inquiry request, returning a rejection result to the user equipment, and if not, passing the first-type safety detection; if the first type of security detection is passed, sending the request to a database service module so that the database service module performs second type of security detection on the data query request, wherein the second type of security detection comprises the following steps: the database service module judges whether the number of the recording lines of the query result is greater than a preset threshold value, if so, the database service module rejects the data query request and returns a rejected result to the user equipment; if not, passing the second type of safety detection; and the security log record is carried out on the data query request returned by the database server;
and the data query module constructs a data query statement according to the data query request, and sends the data query statement to the database service module so that the database service module can perform data query according to the data query statement.
18. The apparatus of claim 17, further comprising a permission verification module configured to verify a permission of a user sending the data query request, and if the permission of the user matches the data query request, trigger the security log module to perform a first type of security detection on the data query request.
19. The apparatus of claim 18, wherein the permission verification module is configured to:
according to the identity information of user login, a structured query instance set belonging to the user data is queried through a data authority table;
alternatively, a determination is made as to whether the set of data structured query instances belongs to the user.
20. The apparatus of claim 17, wherein securely logging the data query request returned by the database server comprises: and recording the user information and the query parameters into a security log table and a local file.
21. A server comprising the data query device of any one of claims 7-12 and 17-20.
22. A data interrogation system comprising the apparatus of any one of claims 7 to 12, 17 to 20.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201710138100.4A CN108572968B (en) | 2017-03-09 | 2017-03-09 | Data query method, device, server and system |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201710138100.4A CN108572968B (en) | 2017-03-09 | 2017-03-09 | Data query method, device, server and system |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN108572968A CN108572968A (en) | 2018-09-25 |
| CN108572968B true CN108572968B (en) | 2022-10-25 |
Family
ID=63577797
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201710138100.4A Active CN108572968B (en) | 2017-03-09 | 2017-03-09 | Data query method, device, server and system |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN108572968B (en) |
Families Citing this family (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN111784337B (en) * | 2019-04-04 | 2023-08-22 | 华控清交信息科技(北京)有限公司 | Authority verification method and system |
| CN111506629B (en) * | 2020-04-22 | 2024-02-27 | 合肥工大高科信息科技股份有限公司 | Rail information query method, server, maintenance machine and system |
| CN111767297B (en) * | 2020-06-30 | 2023-07-04 | 深圳平安智慧医健科技有限公司 | Big data processing method, device, equipment and medium |
| CN112818016A (en) * | 2021-01-21 | 2021-05-18 | 广州汇通国信科技有限公司 | API-based real-time and off-line data query method and system |
| CN112835856A (en) * | 2021-02-01 | 2021-05-25 | 长沙市到家悠享网络科技有限公司 | Log data query method and device, equipment and medium |
| CN117522217A (en) * | 2023-11-21 | 2024-02-06 | 中国人民解放军海军工程大学 | Information management study assessment system |
Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101075256A (en) * | 2007-06-08 | 2007-11-21 | 北京神舟航天软件技术有限公司 | System and method for real-time auditing and analyzing database |
| CN103281377A (en) * | 2013-05-31 | 2013-09-04 | 北京鹏宇成软件技术有限公司 | Cryptograph data storage and searching method for cloud |
| CN104281808A (en) * | 2014-09-25 | 2015-01-14 | 中国科学院信息工程研究所 | Universal detection method for malicious act of Android system |
Family Cites Families (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101997732A (en) * | 2009-08-14 | 2011-03-30 | 华为技术有限公司 | Method, device and system for inquiring service |
| KR101285729B1 (en) * | 2011-10-28 | 2013-08-23 | 김영주 | System and method for securing databse |
| CN103294966B (en) * | 2013-03-12 | 2016-02-24 | 中国工商银行股份有限公司 | A kind of safety access control method of database and system |
| CN103338208B (en) * | 2013-07-16 | 2017-05-24 | 五八同城信息技术有限公司 | Method and system for SQL injection and defense |
| US10268721B2 (en) * | 2013-11-07 | 2019-04-23 | Salesforce.Com, Inc | Protected handling of database queries |
| CN106372469A (en) * | 2016-08-19 | 2017-02-01 | 上海宝尊电子商务有限公司 | Process-based database permission automated management system meeting international auditing standards |
-
2017
- 2017-03-09 CN CN201710138100.4A patent/CN108572968B/en active Active
Patent Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101075256A (en) * | 2007-06-08 | 2007-11-21 | 北京神舟航天软件技术有限公司 | System and method for real-time auditing and analyzing database |
| CN103281377A (en) * | 2013-05-31 | 2013-09-04 | 北京鹏宇成软件技术有限公司 | Cryptograph data storage and searching method for cloud |
| CN104281808A (en) * | 2014-09-25 | 2015-01-14 | 中国科学院信息工程研究所 | Universal detection method for malicious act of Android system |
Also Published As
| Publication number | Publication date |
|---|---|
| CN108572968A (en) | 2018-09-25 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN108572968B (en) | Data query method, device, server and system | |
| CN108574620B (en) | Data subscription method, device, server and system | |
| CN107798038B (en) | Data response method and data response equipment | |
| CN107733863B (en) | Log debugging method and device under distributed hadoop environment | |
| WO2017084290A1 (en) | Public account two-dimensional code generation method and server, and public account following method, server and terminal | |
| CN102724221A (en) | Enterprise information system using cloud computing and method for setting user authority thereof | |
| CN105897704B (en) | The methods, devices and systems of permission addition, permission addition request | |
| CN115118705B (en) | Industrial edge management and control platform based on micro-service | |
| WO2016091002A1 (en) | Method and device for providing authentication information on web page | |
| US20140173693A1 (en) | Cookie Optimization | |
| CN112527873B (en) | Big data management application system based on chain number cube | |
| WO2016007178A1 (en) | System and method for providing contextual analytics data | |
| WO2024027328A1 (en) | Data processing method based on zero-trust data access control system | |
| CN107438054A (en) | The method and system of menu information control are realized based on public platform | |
| CN103457802A (en) | Information transmission system and method | |
| US20180349983A9 (en) | A system for periodically updating backings for resource requests | |
| CN106506568B (en) | Information interaction system | |
| CN109005058A (en) | A kind of intelligence system control platform and management-control method | |
| CN107665237A (en) | Data structure sorter, the distribution subscription system of unstructured data and method | |
| CN108282480B (en) | User authorization multi-party monitoring sharing method and system | |
| CN107483477B (en) | Account management method and account management system | |
| CN114969066A (en) | Enterprise management data interaction system and method based on big data regulation and control | |
| CN112818056A (en) | Log security sharing method, system and device of block chain | |
| US9577967B2 (en) | Method and system for managing an informational site using a social networking application | |
| CN112511515B (en) | Chain number cube for data chaining |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |