CN111309765B

CN111309765B - Method and device for directly inquiring structured data

Info

Publication number: CN111309765B
Application number: CN202010075999.1A
Authority: CN
Inventors: 魏永明; 耿岳; 薛淑明
Original assignee: Beijing Feynman Software Technology Co ltd
Current assignee: Beijing Feynman Software Technology Co ltd
Priority date: 2020-01-22
Filing date: 2020-01-22
Publication date: 2023-07-04
Anticipated expiration: 2040-01-22
Also published as: CN111309765A

Abstract

The invention discloses a method and a device for directly inquiring structured data, wherein the method comprises the following steps: performing validity check according to a data query request sent by a user agent, and generating an effective data query statement; executing according to the effective data query statement to obtain a query result; and writing the query result into the socket to obtain the response output of the query result. According to the method and the device for directly inquiring the structured data, provided by the embodiment of the invention, a new lsql, rsql, rsqls drawing is introduced, so that a method for directly accessing a local or remote database and executing SQL sentences is provided for an HTML webpage, and the code amount for writing a front-end script program or a background server program is reduced. Therefore, by implementing the invention, the technical problems that additional script programs need to be written when structured data are operated and the occupied memory is larger in the prior art are solved.

Description

Method and device for directly inquiring structured data

Technical Field

The invention relates to the technical field of web page front ends, in particular to a method and a device for directly inquiring structured data.

Background

The front end of the webpage is a webpage which is displayed to a user to browse and is operated on a browser such as a PC end and a mobile end. Front-end technology is generally divided into front-end design, which can be generally understood as the visual design of a website, and front-end development, which is the implementation of the front-end code of the website, including basic HTML (Hypertext Marked Language, hypertext markup language) and CSS (Cascading Style Sheets ), and JavaScript/ajax.

Prior to HTML5, the browser could only access the database through the HTTP server, nor could it directly operate the local database (only a simple local storage mechanism such as cookies is provided). In HTML5, interfaces for accessing local storage and local databases are added. Among them, the local store includes session storage (sessionStorage) and local persistent storage (localStorage). The local database is created by the webpage script, and the CRUD operation can be performed by the SQL statement, so that the webpage can operate the structured data more conveniently.

However, the above functions in HTML5 can only be used by writing ECMAScript script programs; therefore, when the structured data is operated by adopting the prior art, additional script programs are required to be written, so that the workload of developers is increased; meanwhile, ECMAScript adopted in the scheme is an explanatory programming language, the execution efficiency is relatively low, and the occupied memory is large.

Disclosure of Invention

In view of this, the embodiment of the invention provides a method and a device for directly querying structured data, so as to solve the problem that in the prior art, when structured data is operated, additional script programs are required to be written, and the workload of developers is increased.

The technical scheme provided by the invention is as follows:

an embodiment of the present invention provides a method for directly querying structured data, including the following steps: performing validity check according to a data query request sent by a user agent, and generating an effective data query statement; executing according to the effective data query statement to obtain a query result; and writing the query result into a socket according to the query result, and obtaining response output of the query result.

Optionally, executing according to the valid data query statement, and before obtaining the query result, further including: judging whether the data query is local query or remote query according to the effective data query statement; when the data query is a local query, judging whether the queried database is executing a data query request according to the effective data query statement; and when the data query request is being executed in the queried database, storing the valid query statement.

Optionally, the method for directly querying structured data further comprises: when the data query is a remote query, connecting a remote management module according to the effective data query statement; and controlling the number of the queried servers to be smaller than a preset threshold value according to the remote management module.

Optionally, validity check is performed according to a data query request sent by the user agent, and a valid data query statement is generated, including: checking whether the data query request exceeds a set value according to the data query request sent by the user agent; when the set value is not exceeded, separating request parameters in the data query request; and generating a valid data query statement according to the separated request parameters.

Optionally, executing according to the valid data query statement to obtain a query result, including: when the queried database does not execute the data query request, determining the user identity according to the user identifier in the effective data query statement; opening a queried database according to the user identity; and executing the effective data query statement according to the queried database to obtain a query result.

Optionally, executing according to the valid data query statement to obtain a query result, including: when the data query is a remote query, determining a remote server where the queried database is located and a corresponding program interface according to the effective data query statement; transmitting the valid data query statement to the remote server according to the program interface; and receiving a query result corresponding to the effective data query statement according to the program interface.

Optionally, writing the socket according to the query result to obtain a response output of the query result, including: setting a response body key value in the query result according to the query result; calculating the length of a response body according to the query result; and writing the response body key value and the response body length into the socket, and obtaining response output of the query result.

A second aspect of an embodiment of the present invention provides an apparatus for directly querying structured data, the apparatus including: the request validity checking module is used for checking validity according to the data query request sent by the user agent and generating an effective data query statement; the execution module is used for executing according to the effective data query statement to obtain a query result; and the response output module is used for writing the query result into the socket according to the query result to obtain the response output of the query result.

A third aspect of the embodiments of the present invention provides a computer-readable storage medium storing computer instructions for causing a computer to perform the method for directly querying structured data according to any one of the first aspect and the first aspect of the embodiments of the present invention.

A fourth aspect of an embodiment of the present invention provides a terminal for directly querying structured data, including: the system comprises a memory and a processor, wherein the memory and the processor are in communication connection, the memory stores computer instructions, and the processor executes the computer instructions, so that the method for directly querying structured data according to any one of the first aspect and the first aspect of the embodiment of the invention is executed.

The technical scheme provided by the invention has the following effects:

according to the method and the device for directly inquiring the structured data, provided by the embodiment of the invention, a new lsql, rsql, rsqls drawing is introduced, so that a method for directly accessing a local or remote database and executing SQL sentences is provided for an HTML webpage, and the code amount for writing a front-end script program or a background server program is reduced. Therefore, by implementing the invention, the technical problems that additional script programs need to be written when structured data are operated and the occupied memory is larger in the prior art are solved.

Drawings

In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings that are needed in the description of the embodiments or the prior art will be briefly described, and it is obvious that the drawings in the description below are some embodiments of the present invention, and other drawings can be obtained according to the drawings without inventive effort for a person skilled in the art.

FIG. 1 is a flow chart of a method of directly querying structured data according to an embodiment of the invention;

FIG. 2 is a flow chart of a method of directly querying structured data according to another embodiment of the invention;

FIG. 3 is a flow chart of a method of directly querying structured data according to another embodiment of the invention;

FIG. 4 is a flow chart of a method of directly querying structured data according to another embodiment of the invention;

FIG. 5 is a flow chart of a method of directly querying structured data according to another embodiment of the present invention;

FIG. 6 is a block diagram of an apparatus for directly querying structured data according to an embodiment of the present invention;

FIG. 7 is a structural schematic diagram of an apparatus for directly querying structured data according to another embodiment of the present invention;

FIG. 8 is a structural schematic diagram of an apparatus for directly querying structured data according to another embodiment of the present invention;

FIG. 9 is a structural schematic diagram of an apparatus for directly querying structured data according to another embodiment of the present invention;

FIG. 10 is a structural schematic diagram of an apparatus for directly querying structured data according to another embodiment of the present invention;

fig. 11 is a schematic diagram of a terminal hardware structure for directly querying structured data according to an embodiment of the present invention.

Detailed Description

For the purpose of making the objects, technical solutions and advantages of the embodiments of the present invention more apparent, the technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings in the embodiments of the present invention, and it is apparent that the described embodiments are some embodiments of the present invention, but not all embodiments of the present invention. All other embodiments, which can be made by those skilled in the art based on the embodiments of the invention without making any inventive effort, are intended to be within the scope of the invention.

The embodiment of the invention provides a method for directly inquiring structured data, as shown in fig. 1, which comprises the following steps:

step S101: and carrying out validity check according to the data query request sent by the user agent, and generating an effective data query statement.

In an embodiment, before the validity check is performed, a data query request sent by the user agent may be obtained according to a preset protocol, where the data query request includes a request row, a header, a blank row and a request body. The preset protocol may be Unix Domain Socket communication protocol between the local server and the user agent (browser or other client) process, which is similar to HTTP1.1, and may be referred to as LSQL/RSQL protocol, in which embodiment the name of the communication protocol may be LSQL/1.0, and 1.0 refers to the version number.

In one embodiment, the user agent (browser or other program) initiates an asynchronous request or a synchronous request, depending on the requirements of the web page (whether the request is executed synchronously or asynchronously can be specified through the Ajax interface) or the context (form submission requests are typically executed synchronously). Synchronous requests or asynchronous requests are handled within the user agent with respect to the context of the web page content or script, and no concern is placed on the request handling process. Thus, for LSQL/RSQL requests, the process and subsequent processing of the request at the user agent side is almost identical to the HTTP drawings. The difference is that: for the HTTP and other drawings, the URL request processing process is connected with a remote server, sends a request according to the agreement of the HTTP protocol and waits for the return of the server; for LSQL/RSQL schematics, the URL request processing process is connected to a local server process providing LSQL/RSQL services through a socket, sends a request through the socket, and waits for the content returned by the server; for the HTTP and other drawings, the URL request processing process processes local cache according to response header information of the HTTP protocol; for LSQL/RSQL schemas, the URL request processing process does not handle local caching, that is, does not handle local caching for any results returned by the LSQL/RSQL local server.

Assuming that there is a SQL database locally, SQL queries can be executed on it by the following URLs. lsql: the data/testdb/dbsqlQuery = SELECT%20a%2c%20b%2c%20sum (c)% 20FROM%20tab1%20GROUP%20BY%20a%3B

Of this URL, lsql is a new URL schema (schema) introduced by the present invention to specify that this URL will execute SQL queries directly on the local SQL database; data/testdb specifies the path of the local SQL database file; db designates the target database name of the SQL statement to be executed thereafter; the value of the SQL query parameter is an SQL sentence after URI coding (performing escape processing on special characters in the URL, such as space, comma, semicolon and the like), and the decoded content is:

SELECT a，b，sum(c)FROM tab1 GROUP BY a

the URL may also be understood as an LSQL request (or LSQL request) initiated, whose target resource is the db database in the local database file/data/testdb, and the SQL query statement to be executed is SELECT a, b, sum (c) FROM tab1 GROUP BY a.

In addition, the SQL database may also be located on a remote server, on which the SQL query may be executed by the URL:

rsql：//username：password＠foo.bar.com：3306/mysql/dbsqlQuery＝SELECT％20a％2C％20b％2C％20sum(c)％20FROM％20tab1％20GROUP％20BY％20a％3B

of this URL, rsql is a new URL schema (schema) introduced by the present invention to specify that this URL will execute an SQL query directly on a remote SQL database; username is the user account name; the password is a password corresponding to the user account; foo.bar.com is the hostname of the running SQL database; 3306 is the port number of the SQL database providing SQL query services; mysql/db specifies the type of remote SQL database and the target database name of the SQL statement to be executed thereafter; the value of the SQL query parameter is an SQL sentence after URI encoding (performing escape processing on special characters in the URL, such as space, comma, semicolon and the like), and the decoded content is the same as the above.

The URL may also be understood as an RSQL request (or RSQL request) initiated, whose target resource is database db (database type is mysql) on remote host foo.bar.com, and the SQL query statement to be executed is SELECT a, b, sum (c) FROM tab1 GROUP BY a. The remote host processes rsql requests on port 3306, and the user account name and password used by the connection are respectively: username, password.

In addition, as with the encrypted transport protocol where https is http, rsqls can be used as the encryption scheme for rsql (the lsql scheme need not support encrypted transport).

In an embodiment, for the LSQL URL, the GET method may be used for delivery, and the POST method may be used for delivery. When the GET method is adopted, the contents of the LSQL request sent by the user agent are as follows:

GET

/data/testdb/dbsqlQuery＝SELECT％20a％2C％20b％2C％20sum(c)％20FROM％20tab1％20GROUP％20BY％20a％3B LSQL/1.0

User－Agent：Mozilla/5.0(Windows；U；Windows NT 5.1；en－US；rv：1.7.6)Gecko/20050225Firefox/1.0.1

User－Name：username

in addition, when the POST method is adopted to transmit the data query request, the request content is as follows:

POST/data/testdb/db LSQL/1.0

User－Id：100

Content－Type：application/x－www－form－urlencoded

Content－Length：71

sqlQuery＝SELECT％20a％2C％20b％2C％20sum(c)％20FROM％20tab1％20GROUP％20BY％20a％3B

in the two data query requests transmitted by adopting the GET method or the POST method, the corresponding header information includes: user-Agent (representing the User Agent identification string), user-Id (either one of the User identifier for executing the User Agent, and User-Name, in this case 100; this header information will be used as security check), user-Name (either one of the User account Name for executing the User Agent, and User-Id; this header information will be used as security check, mainly for use on non-Unix Type operating systems), content-Type (when using the POST method, this header information specifies the Content Type of the request body, typically application/x-www-form-url code), content-Length (when using the POST method, this header information specifies the Content Length (number of bytes) of the request body, in this case 67).

In an embodiment, for the RSQL URL, the GET method may be used for delivery, and the POST method may be used for delivery. When the GET method is adopted, the content of the RSQL request sent by the user agent is as follows:

GET

/mysql/dbsqlQuery＝SELECT％20a％2C％20b％2C％20sum(c)％20FROM％20tab1％20GROUP％20BY％20a％3B RSQL/1.0

User－Agent：Mozilla/5.0(Windows；U；Windows NT 5.1；en－US；rv：1.7.6)Gecko/20050225 Firefox/1.0.1

Host：foo.bar.com

Port：3306

User：username

Password：password

POST/mysql/db RSQL/1.0

User－Agent：Mozilla/5.0(Windows；U；Windows NT 5.1；en－US；rv：1.7.6)

Gecko/20050225Firefox/1.0.1

Host：foo.bar.com

Port：3306

User：username

Password：password

Content－Type：application/x－www－form－urlencoded

Content－Length：71

for RSQL requests, the header information it contains is partially the same as that of LSQL. In addition, the header information of the RSQL request further includes: host (indicating the Host on which the remote database to be connected is specified), port (indicating the Port number at which the remote database provides services), user (indicating the User name of the remote database to be connected), password (indicating the Password corresponding to the User name of the remote data to be connected).

In an embodiment, for the encryption mode RSQLs request of the RSQL URL, the request may also be initiated by the RSQL protocol, except that the RSQLs request needs to additionally provide information such as SSL connection certificates in the header information. Therefore, when the RSQLS request is transferred by using the GET method, the request content is as follows:

GET

User－Agent：Mozilla/5.0(Windows；U；Windows NT 5.1；en－US；rv：1.7.6)

Gecko/20050225Firefox/1.0.1

Host：foo.bar.com

Port：3306

User：username

Password：password

SSL－Ca：/home/mysql/ca－cert.pem

SSL－Cert：/home/mysql/client－cert.pem

SSL－Key：/home/mysql/client－key.pem

for the RSQLS request, the header information further includes: SSL-Ca (indicating a Ca certificate when an SSL connection is specified), SSL-Cert (indicating a client certificate when an SSL connection is specified), SSL-Key (indicating a client Key when an SSL connection is specified).

Step S102: executing according to the effective data query statement to obtain a query result.

In one embodiment, the method further comprises the following steps before executing the valid data query statement: judging whether the data query is local query or remote query according to the effective data query statement; when the data query is a local query, judging whether the queried database is executing a data query request according to the effective data query statement; when a data query request is being executed in the queried database, valid query statements are saved.

Because the queried database can be located in the local database or the database of the remote server, the schema in the valid data query statement can be identified according to the predefined local query (LSQL) and the predefined remote query (RSQL), when the local query (LSQL) is determined, because the LSQL request uses a single SQL database engine (SQLite), and neither small SQL database engine such as SQLite provides concurrent access capability of the database, before executing the SQL statement, whether a specific LSQL request is being executed on the queried database needs to be checked, and if so, a new request can be executed after the request is waited for. In this case, a queue is required to hold new requests, and when a certain request ends, the held request is checked, and if there are no other executing requests on the corresponding database, the request is executed.

In addition, other database engines such as Oracle Berkeley DB may be used to implement the lsql schema, but support for SQL statements is required to be added, including related modules for SQL syntax parsing and execution. In the case of supporting a variety of different local database engines, the lsql schema may determine the particular database engine by determining the format of the database file.

In one embodiment, when the data query is a remote query, the remote management module is connected according to the valid data query statement; and controlling the number of the queried servers to be smaller than a preset threshold value according to the remote management module.

Specifically, because SQL execution of RSQL requests is performed on a remote server, the differences between different remote database types are handled by loading different sub-drivers prior to execution, while a remote connection management module is required to manage the connections: the remote management module controls the number of connections to the same remote server not to exceed a limit; controlling the number of control connections from the same user agent to the same remote server not to exceed a limit; frequent closing and opening of connections to the same remote database is avoided, and when the users are the same, priority multiplexing of connections should be considered.

In this embodiment, to distinguish between different types of remote databases (e.g., mySQL, postgreSQL, etc.), a mechanism is added to the rsql schema implementation to negotiate the database instance types after connection establishment, and the user agent loads the sub-drivers corresponding to the database instance types according to the negotiation results to complete the communication with the remote database instance. Meanwhile, different schema names can be directly defined for different database types, for example:

mysql：//foo.bar.com/dbsqlQuery＝...

postgresql：//foo.bar.com/dbsqlQuery＝...

sqlserver：//foo.bar.com/dbsqlQuery＝...

db2：//foo.bar.com/dbsqlQuery＝...

step S103: and writing the query result into the socket to obtain the response output of the query result. Specifically, when generating response output of the query result, a JSON object representing response body may be first constructed, then a socket is written according to the format requirement of LSQL/RSQL response, and finally, corresponding output of the query result is obtained.

According to the method for directly inquiring the structured data, provided by the embodiment of the invention, a new lsql, rsql, rsqls schema is introduced, so that a method for directly accessing a local or remote database and executing SQL sentences is provided for an HTML webpage, and the code amount for writing a front-end script program or a background server program is reduced. Therefore, by implementing the invention, the technical problems that additional script programs need to be written when structured data are operated and the occupied memory is larger in the prior art are solved.

As an optional implementation manner of the embodiment of the present invention, as shown in fig. 2, step S101 performs validity check according to a data query request sent by a user agent, and generates an effective data query statement, including the following steps:

step S111: checking whether the data query request exceeds a set value according to the data query request sent by the user agent; in particular, it is necessary to check whether the unreturned LSQL or RSQL requests are being processed beyond a set value, including the number of all processing requests from a single user agent running instance (distinguished by socket identifier) and the number of all processing LSQL or RSQL requests.

Step S112: and when the set value is not exceeded, separating the request parameters in the data query request.

In one embodiment, for LSQL requests, the database file path (dbFile, e.g.,/data/testdb), database name (dbName, e.g., db), user identifier (userId) to open the database, user name (userName) to open the database, variational SQL statement (sqlQuery), and the line format of the result set (sqlRowFormat) in the request line and request body may be separated and organized into dictionary data results. In addition, other request parameters in the request line and the request body can be separated.

Where userId is the user identifier used to open the database. Although the request header of the LSQL contains User-Id information, we cannot rely on this header information to determine the User identifier of the User agent process, but should get the process on the other end of the Unix socket and the User identifier information through a system call. On Unix-type systems, this information is obtained by calling the getsockopt function to obtain the value of the SO_PEERCRED option. If the user identifier obtained by the system call and the user identifier in the request header by the LSQL do not match, an illegal request processing should be performed.

In order to facilitate the formation of different SQL sentences in the HTML document by the names and values of form elements, the data query requests transmitted as parameters can be subjected to variable processing; specifically, a variable, such as Id, in the data query request may be defined as a prefix, which means that the variable is replaced with a URL parameter of the same name that is transmitted at the same time when the final data query request is generated. Other URL parameters may also be used to define the value of the variable. Thus, for the above LSQL URL, the variable processing can be performed to obtain:

lsql：///data/testdb/dbsqlQuery＝DELETE％20FROM％20Books％20WHERE％20Id％3D￥Id％3B&Id＝1

the URL contains two parameters: the value corresponding to the sqlQuery is a variational SQL statement, and after decoding, the value is: DELETE FROM Books WHERE Id =.Id and Id correspond to a value of 1, and this set of parameters will be used to replace Id in the variant SQL statement above. The SQL statement that the URL finally executes is therefore: DELETE FROM Books WHERE Id =1

By mutating the SQL statement (data query request), it is advantageous to set different values in the SQL statement by the entries in the form, while the parameterized SQL statement can be kept unchanged, thereby reducing the encoding amount. Whereas additional script code would be required to generate the final SQL statement if the parameterized SQL statement were not used.

Variable names in the variable SQL statement need to meet the following specifications: beginning with the character, the character immediately following must be a letter; and thereafter contains only numbers, letters and underlines; and any non-numeric, alphabetic, and underlined characters represent the ending of the variable name; the case of reserving the character followed by the number can be used as a parameter for referring to the order; variable names are case sensitive; that is, abc and Abc represent different variables. In order to correctly process the condition that the original text of the SQL sentence contains the character, the escape processing is carried out on the character: the rule is processed as variable name in the original SQL sentence, and the escape processing is performed. If the variable name is not processed according to the rule, the escape processing is not needed.

The following original SQL statement:

SELECT＊FROM Books WHERE Code＝＇￥name￥15￥＇

the following escape treatment is needed:

SELECT＊FROM Books WHERE Code＝＇￥￥name￥￥15￥＇

the last character in the original SQL statement does not need to be escape because it does not constitute the variable name in the mutable SQL statement. In addition, for a variant SQL statement, a variable name definition rule other than the character, such as%, etc., may be employed.

For example, all Fyodor Dostoyevsky Books need to be looked up from the book table and the results ordered using ISBN, then the corresponding SQL statement should be:

SELECT Title，Isbn FROM Books WHERE Author＝＇Fyodor Dostoyevsky＇SORT BY Isbn ASC；

however, the SQL statement may be subjected to a variational process according to the variational process specification described above:

SELECT Title，Isbn FROM Books WHERE Author＝＇￥author＇SORT BY￥sortBy￥sortOrder；

when the data query request is then passed, the actual variable value may be passed through the following code: lsql: the data/testdb/dbsqlQuery=select%20title%2c%20isbn%20FROM%20Books%20WHERE%20Author%3D% 24 author%20SORT%20BY%2024sortBy%2024sortorder%3B & author=Fyodor%20dostyvsky & sortBy=isbn & sortOrder%ASC

Since the user agent performs the variable quantization process when generating the data query request, the variable SQL statement (sqlQuery) parameter can be separated when separating the request parameters in the request line and the request body.

In an embodiment, when performing validity check on the LSQL request, it may also be checked whether the length of the request entity and the length of each URI defined in the request header exceeds a limit value; it is checked whether the user identifier userId or userName defined in the request header matches the option value returned by the socket. The method comprises the steps of carrying out a first treatment on the surface of the Checking whether dbFile specifies a database file exists and checking access rights. The method comprises the steps of carrying out a first treatment on the surface of the The value specified by sqlrowFormat is checked, ensuring that it is either a direct or an array.

In one embodiment, for RSQL requests, the Host (Host, such as foo.bar.com), database name (Port, such as 3306), user name (o dbusernames) for connecting to the remote database, password (dbPassword) for connecting to the remote database, remote database type (dbType, such as mysql), remote database name (dbName, such as db), variable SQL statement (SQL query), line format of result set (SQL rowformat), CA certificate file (SSLCa) for establishing SSL connection, client certificate file (SSLCert) for establishing SSL connection, and client key file (SSLKey) for establishing SSL connection may be separated and organized as dictionary data results. In addition, other request parameters in the request line and the request body can be separated.

In an embodiment, when performing validity check on the RSQL request, it may also be checked whether the length of the request entity and the length of each URI defined in the request header exceeds a limit value; checking whether a user identifier userId or userName defined in the request header is matched with an option value returned by the socket; checking whether a given database type (dbType) is supported; the value specified by sqlrowFormat is checked, ensuring that it is either a direct or an array.

Meanwhile, when the RSQL request is checked for validity, a connection to the designated host and port number (if SSLCa is designated, an encrypted connection is used), and parameters such as dbUsername, dbPassword and dbName may be transmitted.

Step S113: and generating a valid data query statement according to the separated request parameters. Specifically, when generating the final valid data query statement, it is necessary to determine whether all the variables of the SQL statement that conform to the contract contain corresponding parameters, and if so, replace the variables with the values of the parameters, that is, replace the variables with the actual values to generate the valid data query statement to be executed.

When the validity of the request is checked, an error state may be returned in advance in the case of an illegal request. The error status may be represented by a status code such as 400Bad Request, 403Forbidden, 401 unauthenticated, 501Not Implemented, or 404Not Found. In addition, other state codes may be included, which may be a subset of the HTTP state codes. Specifically, the message status code (corresponding to the HTTP status code range of 100-199) and the redirection status code (corresponding to the HTTP status code range of 300-399) may be included.

As an optional implementation manner of the embodiment of the present invention, as shown in fig. 3, step S102 is performed according to a valid data query statement, to obtain a query result, and includes the following steps:

step S201: when no data query request is being executed in the queried database, the identity of the user is determined according to the user identifier in the valid data query statement.

Step S202: and opening the queried database according to the identity of the user.

Step S203: and executing the effective data query statement according to the queried database to obtain a query result.

Specifically, for an LSQL request, if there is no executing request on the database corresponding to the new LSQL request, the request may be executed, and the flow is as follows: creating a pipeline, and executing a fork system call creation subprocess; invoking setup in the child process to set a valid user identifier, and setting one end of a pipeline created by the parent process as standard output (file descriptor is 1); so far, the subprocess runs with the user identity designated by userId, and because the local server is usually executed with root user identity, if the setup system call is not called, a huge security vulnerability is created, so that a general user agent can execute a high-risk SQL query statement by utilizing an LSQL request; the subprocess opens the database and executes the SQL query, analyzes the result of the SQL query and converts the result into a JSON format, and then prints the result to a standard output. In the parent process, the other end of the pipeline is read, and the child process, namely the query result, can be obtained.

In one embodiment, for Unix-like systems, the parent process may call the following functions to do the above and return a pipe file descriptor that reads the SQL query output:

/>

/>

it should be noted that, when the LSQL request is executed, the server should call the waitpid system as a parent process to monitor the exit state of the child process, so as to determine whether the SQL query is executed correctly, and set the status code in the LSQL response accordingly. At this stage, a status code (an error in processing the request) may be generated.

As an optional implementation manner of the embodiment of the present invention, as shown in fig. 4, step S102 is performed according to a valid data query statement, to obtain a query result, including:

step S211: when the data query is a remote query, determining a remote server where the queried database is located and a corresponding program interface according to the effective data query statement.

Step S212: and sending the valid data query statement to a remote server according to the program interface.

Step S213: and receiving a query result corresponding to the effective data query statement according to the program interface.

Specifically, when the data query is a remote query, that is, when the data query request sent by the user agent is a request for RSQL, since the large SQL database is issued with one SDK, the SDK is used by other applications to connect to the remote server to execute the SQL query, and the result is returned. Thus, for an RSQL request, it can be implemented as follows: submitting SQL sentences to a remote server through a sub-driver interface corresponding to the queried database type, and then returning; the results of all the executed SQL queries are checked through polling, and returned data is saved; all the line data in the result set are formatted according to the requirement of the sql RowFormat. And closing the connection after the SQL query is executed and all returned data are received. At this stage, a status code (an error in processing the request) may be generated.

As an optional implementation manner of the embodiment of the present invention, as shown in fig. 5, step S103 writes the query result into the socket according to the query result, and obtains a response output of the query result, including:

step S301: setting a response body key value in the query result according to the query result; specifically, whether an error occurs in the validity checking and executing process can be judged, when the error occurs, the rows in the response body is set to NULL, key values such as statusCode, errorMsg are correspondingly set, and then the result is output to the socket. When no error occurs, the formatted result set data can be used as the rows key value of the response body, and key values such as statusCode, errorMsg and the like are set correspondingly.

Step S302: and calculating the length of the response body according to the query result.

Step S303: and writing the response body key value and the response body length into the socket, and obtaining response output of the query result. The LSQL/RSQL response can be obtained by writing the response body key value and the response body length into the socket, and the format of the LSQL/RSQL response is similar to that of the LSQL/RSQL request, and the LSQL/RSQL response comprises the following parts: status line (status-line), header (headers), blank line (blank line), response body (response-body).

For example, for the LSQL request, the corresponding response content is:

/>

The header information includes: date: response time of transmission; content-Type: the header information specifies the content type of the response body and the character set, the content type (MIME) is fixed to application/json, and the character set is generally UTF-8; content-Length: the header information specifies the content length (number of bytes) of the response body, which is 433 in the above example.

It should be noted that, the character set code of the LSQL response body is set up on the ASCII control characters in consideration of the working mechanism of the filter, and therefore, the character set and code of the response body must be an ASCII compatible character set or code, such as ISO8859-1, UTF-8, and the like.

For the RSQL request, the response content is almost the same as that of LSQL, but the status lines are different:

/>

as an optional implementation manner of the embodiment of the present invention, the query result obtained according to the above steps should include the following JSON field information: status code: integer values, like HTTP status codes, such as 200 indicate successful execution, 404 indicate non-existent target databases; error msg: a string or NULL, which indicates execution error information of the SQL query statement, and indicates no error when NULL; rowsAffected: the number of affected recording lines; when performing Update, delete, etc., operations, the number of affected record lines will be returned through this field, which is typically 0 when performing Retrieve operations; rows: for returning a result set; when a search (Retrieve) is performed, a result set is returned through this field, NULL is taken without the result set, the result set uses an array to represent all rows, each row is represented by a dictionary data structure (default) or array.

For example, a book table for recording book information is created in an SQLite database file, and three Books' titles, authors, and ISBN number information are inserted therein:

CREATE TABLE Books(Id integer PRIMARY KEY，Title text，Author text，Isbn text default＇not available＇)；

INSERT INTO Books VALUES(1，＇War and Peace＇，＇Leo Tolstoy＇，＇978－0345472403＇)；

INSERT INTO Books VALUES(2，＇The Brothers Karamazov＇，＇FyodorDostoyevsky＇，＇978－0486437910＇)；

INSERT INTO Books VALUES(3，＇Crime and Punishment＇，＇Fyodor Dostoyevsky＇，＇978－1840224306＇)；

the following URL is then used:

lsql：///data/testdb/dbsqlQuery＝SELECT％20＊％20FROM％20Books％3B

the following SQL statement will be executed on the database:

SELECT＊FROM Books；

the JSON data returned by this URL is as follows:

when the following SQL statement is executed on the database to delete the first record:

DELETE FROM Books WHERE Id＝1；

the corresponding URL is:

lsql：///data/testdb/dbsqlQuery＝DELETE％20FROM％20Books％20WHERE％20Id％3D1％3B

the result is that:

if multiple SQL statements are executed at a time, the result array is used to represent the execution result of each statement. For example, two SQL statements are executed at a time (execute DELETE statement followed by SELECT statement), whose URL is:

lsql：///data/testdb/dbsqlQuery＝DELETE％20FROM％20Books％20WHERE％20Id％3D1％3B％20SELECT％20＊％20FROM％20Books％3B

the result is that:

in order to facilitate the subsequent processing of the result set requested by the lsql and the like, besides the SQL query parameter being used for transmitting the SQL query statement, the invention introduces another parameter SQL RowFormat for specifying the line output format of the result set, and one of two values, that is, a direct value and an array value, is taken, and the latter represents the line output format using an array as the result set. When sqlrowFormat is not specified or an erroneous value is specified, a dictionary data structure is adopted. For example, for the above result, if the lsql URL contains the sql rowformat=array parameter, the returned result is:

As an alternative implementation of the embodiment of the present invention, when using the URL of the lsql etc. schema as the action attribute of the form submission, the content returned by the lsql etc. schema is in JSON format and cannot be directly provided for the user agent to be exposed. Therefore, a normal result page URL needs to be defined at the same time.

The following is shown:

＜form action＝＂lsql：///data/testdb/db＂method＝＂get＂destination＝＂/books.html＂＞

＜input type＝＂hidden＂name＝＂sqlQuery＂value＝＂DELETE FROM BooksWHERE Id＝￥Id＂/＞

＜input type＝＂hidden＂name＝＂Id＂value＝＂1＂/＞

＜input type＝＂submit＂value＝＂Delete＂/＞

＜/form＞

the target URL is defined by using the destination attribute in the form element above. When the form submission is successful (i.e., lsql:///data/testdb/dbsqlquery=. State code of 2 xx.) the user agent should navigate to the destination URL defined by the destination attribute.

As an alternative implementation manner of the embodiment of the invention, when an LSQL/RSQL request contains a plurality of SQL sentences, the SQL sentences should be executed sequentially, and each result is packaged in a result array to be returned. If all the SQL sentences are normally executed, the corresponding status code is 200, and at this time, the result contains the result and/or the result set corresponding to each SQL sentence. When an execution error of a certain SQL statement is encountered, only the SQL statement which is normally executed before is returned, the corresponding status code is 500, and at the moment, the result contains a corresponding result and/or result set of the normally executed SQL statement. If all SQL statements fail to execute normally, the corresponding status code is 500 and result is NULL.

As an optional implementation manner of the embodiment of the present invention, the extended URL type LSQL/RSQL newly defined by the present invention can directly use the two types of URLs (such as forms) in an HTML page, so that the database can be directly operated without writing additional server-side codes. Since the resources obtained by the lsql and other diagrams are always expressed in JSON format, the result can be directly used by JavaScript script programs to realize specific functions, such as dynamically generating DOM nodes. This example illustrates how the lsql et al schema is used without the use of JavaScript scripts.

For example, the following HTML page fragment defines a table in which three books in the above example are listed, and a user accessing the page may click on the "Delete" button at the end of each row to directly manipulate the database to Delete the corresponding record:

＜table＞

＜tr＞

＜th＞Title＜/th＞

＜th＞Author＜/th＞

＜th＞ISBN＜/th＞

＜th＞Delete＜/th＞

＜/tr＞

＜tr＞

＜td＞War and Peace＜/td＞

＜td＞Leo Tolstoy＜/td＞

＜td＞978－0345472403＜/td＞

＜td＞

＜formaction＝＂lsql：///data/testdb/db＂method＝＂get＂destination＝＂/books.html＂＞

＜inputtype＝＂hidden＂name＝＂sqlQuery＂value＝＂DELETEFROMBooksWHEREId＝￥Id＂/＞

＜inputtype＝＂hidden＂name＝＂Id＂value＝＂1＂/＞

＜inputtype＝＂submit＂value＝＂Delete＂/＞

＜/form＞

＜/td＞

＜/tr＞

＜tr＞

＜td＞TheBrothersKaramazov＜/td＞

＜td＞FyodorDostoyevsky＜/td＞

＜td＞978－0486437910＜/td＞

＜td＞

＜inputtype＝＂hidden＂name＝＂Id＂value＝＂2＂/＞

＜inputtype＝＂submit＂value＝＂Delete＂/＞

＜/form＞

＜/td＞

＜/tr＞

＜tr＞

＜td＞CrimeandPunishment＜/td＞

＜td＞FyodorDostoyevsky＜/td＞

＜td＞978－1840224306＜/td＞

＜td＞

＜inputtype＝＂hidden＂name＝＂Id＂value＝＂3＂/＞

＜inputtype＝＂submit＂value＝＂Delete＂/＞

＜/form＞

＜/td＞

＜/tr＞

＜/table＞

the embodiment of the invention also provides a device for directly inquiring the structured data, as shown in fig. 6, which comprises:

the request validity checking module 1 is used for checking validity according to a data query request sent by the user agent, and generating an effective data query statement; for details, see the description of step S101 in the above method embodiment.

The execution module 2 is used for executing according to the effective data query statement to obtain a query result; for details, see the description related to step S102 in the above method embodiment.

And the response output module 3 is used for writing the query result into the socket to obtain the response output of the query result. For details, see the description of step S103 in the above method embodiment.

In open-source Web engines such as WebKit, requests including URLs such as HTTP and HTTPs are all handled by independently running sub-processes (referred to as "URL request handling processes"), and therefore, the device for directly querying structured data provided by the embodiment of the present invention also follows this architecture.

As an alternative implementation of the embodiment of the present invention, the introduction of the URL schema of lsql et al may be implemented in a variety of ways.

Specifically, as shown in fig. 7, the processing of the LSQL URL may be implemented in a locally running server process, called "LSQL server", to which the URL request processing process of the user agent is connected through a socket, submits the request parameters through the socket, and then waits for the server to return the corresponding processing result. This approach may also provide an implementation of the lsql request for other programs than user agents as may be initiated in a generic C/c++ program or even Python script program.

Alternatively, as shown in fig. 8, the request of the lsql URL may be directly integrated into the URL request processing process of the user agent, a new sub-process is started for each lsql request, the database file is opened in the sub-process, the SQL statement is executed, and the execution result is obtained and then returned.

As an alternative implementation of the embodiment of the present invention, the URL schema of rsql et al may be introduced in various ways.

In the case of an RSQL request, as shown in fig. 9, because the request is essentially connected to the remote host to perform the corresponding operation, an implementation similar to an HTTP server may be selected to support the RSQL protocol, i.e., to perform an RSQL server on the remote host, then connect to the database and perform the corresponding SQL query by the RSQL server, and finally return the result and the result set. However, this approach is not essentially different from an HTTP server that can handle the corresponding URL.

Specifically, a method of accessing a remote database using an HTTP connection belongs to an indirect access method, and an HTTP server accesses the remote database to obtain data and then generates an HTML page or JSON data to return to a user agent. In this case, the script program at the HTTP server is responsible for connecting to the database and submitting the SQL statement to be executed to the database, and then obtaining the query result of the database.

Alternatively, as shown in FIG. 10, when accessing using an RSQL connection, an RSQL server program can run on the local host, with the RSQL server running on the local host executing SQL queries directly connected to the remote database using the database-provided client SDK.

The functional description of the device for directly querying structured data provided by the embodiment of the invention refers to the description of the method for directly querying structured data in the above embodiment in detail.

The embodiment of the present invention further provides a terminal for directly querying structured data, as shown in fig. 11, where the terminal for directly querying structured data may include a processor 51 and a memory 52, where the processor 51 and the memory 52 may be connected by a bus or other manners, and in fig. 11, the connection is exemplified by a bus.

The processor 51 may be a central processing unit (Central Processing Unit, CPU). The processor 51 may also be other general purpose processors, digital signal processors (Digital Signal Processor, DSP), application specific integrated circuits (Application Specific Integrated Circuit, ASIC), field programmable gate arrays (Field-Programmable Gate Array, FPGA) or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, or combinations thereof.

The memory 52 serves as a non-transitory computer readable storage medium that may be used to store non-transitory software programs, non-transitory computer-executable programs, and modules, such as corresponding program instructions/modules in embodiments of the present invention. The processor 51 executes various functional applications of the processor and data processing, i.e. the method of directly querying structured data in the above-described method embodiments, by running non-transitory software programs, instructions and modules stored in the memory 52.

Memory 52 may include a storage program area that may store an operating system, at least one application program required for functionality, and a storage data area; the storage data area may store data created by the processor 51, etc. In addition, memory 52 may include high-speed random access memory, and may also include non-transitory memory, such as at least one magnetic disk storage device, flash memory device, or other non-transitory solid state storage device. In some embodiments, memory 52 may optionally include memory located remotely from processor 51, which may be connected to processor 51 via a network. Examples of such networks include, but are not limited to, the internet, intranets, local area networks, mobile communication networks, and combinations thereof.

The one or more modules are stored in the memory 52 and when executed by the processor 51 perform the method of directly querying structured data as in the embodiments shown in fig. 1-5.

The specific details of the terminal directly querying the structured data may be understood correspondingly with reference to the corresponding related descriptions and effects in the embodiments shown in fig. 1 to 5, which are not repeated herein.

It will be appreciated by those skilled in the art that implementing all or part of the above-described embodiment method may be implemented by a computer program to instruct related hardware, where the program may be stored in a computer readable storage medium, and the program may include the above-described embodiment method when executed. Wherein the storage medium may be a magnetic Disk, an optical Disk, a Read-Only Memory (ROM), a random access Memory (RandomAccessMemory, RAM), a Flash Memory (Flash Memory), a Hard Disk (HDD), a Solid State Drive (SSD), or the like; the storage medium may also comprise a combination of memories of the kind described above.

Although embodiments of the present invention have been described in connection with the accompanying drawings, various modifications and variations may be made by those skilled in the art without departing from the spirit and scope of the invention, and such modifications and variations fall within the scope of the invention as defined by the appended claims.

Claims

1. A method for directly querying structured data, wherein the method is applied to a browser using HTML, and comprises the steps of:

performing validity check according to a data query request sent by a user agent, and generating effective data query sentences, wherein the effective data query sentences comprise local data query sentences and remote data query sentences;

executing according to the effective data query statement to obtain a query result;

writing the query result into a socket according to the query result, and obtaining response output of the query result;

performing validity check according to a data query request sent by a user agent, generating an effective data query statement, including:

checking whether the data query request exceeds a set value according to the data query request sent by the user agent;

when the set value is not exceeded, separating request parameters in the data query request;

and generating a valid data query statement according to the separated request parameters.

2. The method of directly querying structured data as in claim 1, wherein prior to performing according to the valid data query statement to obtain a query result, further comprising:

Judging whether the data query is local query or remote query according to the effective data query statement;

when the data query is a local query, judging whether the queried database is executing a data query request according to the effective data query statement;

and when the data query request is being executed in the queried database, storing the valid data query statement.

3. The method of directly querying structured data as in claim 2, further comprising:

when the data query is a remote query, connecting a remote management module according to the effective data query statement;

and controlling the number of the queried servers to be smaller than a preset threshold value according to the remote management module.

4. The method of directly querying structured data according to claim 2, wherein executing according to the valid data query statement results in a query result comprising:

when the queried database does not execute the data query request, determining the user identity according to the user identifier in the effective data query statement;

opening a queried database according to the user identity;

and executing the effective data query statement according to the queried database to obtain a query result.

5. A method of directly querying structured data as claimed in claim 3, wherein executing according to the valid data query statement results in a query result comprising:

when the data query is a remote query, determining a remote server where the queried database is located and a corresponding program interface according to the effective data query statement;

transmitting the valid data query statement to the remote server according to the program interface;

and receiving a query result corresponding to the effective data query statement according to the program interface.

6. The method of directly querying structured data as in claim 1, wherein writing the socket according to the query result to obtain a response output of the query result comprises:

setting a response body key value in the query result according to the query result;

calculating the length of a response body according to the query result;

and writing the response body key value and the response body length into the socket, and obtaining response output of the query result.

7. An apparatus for directly querying structured data, the apparatus being applied to a browser employing HTML, comprising:

the request validity checking module is used for carrying out validity checking according to a data query request sent by the user agent, and generating effective data query sentences, wherein the effective data query sentences comprise local data query sentences and remote data query sentences;

The execution module is used for executing according to the effective data query statement to obtain a query result;

the response output module is used for writing the socket according to the query result to obtain response output of the query result;

8. A computer-readable storage medium storing computer instructions for causing a computer to perform the method of directly querying structured data as claimed in any one of claims 1 to 6.

9. A terminal for directly querying structured data, comprising: a memory and a processor in communication with each other, the memory storing computer instructions that, when executed, perform the method of directly querying structured data as claimed in any one of claims 1 to 6.