Efficient allow listing of SQL inside a database
Disclosed is an improved approach to implement an in-database allow-list to address SQL injection threats. The allow-list includes a list of legitimate SQL that is permitted to run within the database, which can be used to block the execution of any SQL that does not appear on the list. In this way, any malicious SQL from a SQL injection would be blocked since such malicious SQL would not be permitted to be included on an allow-list.
1 . A method, comprising:
capturing information of a Structured Query Language (SQL) statement that has previously executed within a database into a database log, the database log comprising a plurality of logs, each log of the plurality of logs including a plurality of fields populated with respective captured SQL statement information;
analyzing the previously executed SQL statement information captured into the database log to determine whether the previously executed SQL statement should be permitted for subsequent execution within the database;
generating or updating a SQL allow-list to include the previously executed SQL statement after determining that the previously executed SQL statement should be permitted for subsequent execution within the database; and
after generating or updating the SQL allow-list, receiving a SQL statement at the database for processing and enforcing the SQL allow-list within the database, wherein the received SQL statement is permitted to execute in the database when the received SQL statement matches the previously executed SQL statement in the allow-list.
2 . The method of claim 1 , wherein the previously executed SQL statement is represented as a signature value in the SQL allow-list.
3 . The method of claim 2 , wherein the previously executed SQL statement is converted into the signature value by performing SQL normalization on the previously executed SQL statement, identifying accessed objects, and hashing against a combination of the normalized previously executed SQL statement and the accessed objects.
4 . The method of claim 1 , wherein the plurality of logs of the database log comprises:
a SQL signature log, wherein a SQL signature entry in the SQL signature log is generated to uniquely identify each distinct captured SQL statement,
a session log, wherein an entry in the session log is generated for each new database connection, and
an event log, wherein an entry in the event log is generated for each previously executed SQL command.
5 . The method of claim 4 , wherein
the SQL signature log comprises entries or fields for a SQL signature, a SQL text, an accessed object and a command type,
the event log comprises entries or fields for a session identifier, the SQL signature, a current user identifier (ID), a top level (Y/N) indicator, and a session user ID, and
the SQL log comprises entries or fields for the session ID, the session user ID, an Internet Protocol (IP) address, a client program, an operation system (OS) username and a timestamp.
6 . The method of claim 1 , further comprising generating or updating a context allow-list comprising one or more session attributes, and enforcing the context allow-list to determine if a condition of the one or more session attributes is met for a session to execute the received SQL statement.
7 . The method of claim 1 , wherein a SQL enforcement module inside of a kernel of the database performs enforcement of the SQL allow-list within the database.
8 . The method of claim 1 , further comprising generating or updating a SQL deny-list, and enforcing the SQL deny-list within the database, wherein execution of the received SQL statement is permitted to execute when the received SQL does not match an entry in the SQL deny-list.
9 . The method of claim 1 , wherein
a first log and a second log of the plurality of logs both include first information, and
the second log and a third log of the plurality of logs both include second information different from the first information.
10 . The method of claim 9 , wherein the first log is a SQL log, the second log is an event log, and the third log is a session log.
11 . The method of claim 10 , wherein
the SQL log and the event log include first information comprising a SQL signature, and
the event log and the session log include second information comprising a session identifier (ID).
12 . The method of claim 11 , the second log and a third log of the plurality of logs both include third information different from the first information and the second information, the third information comprising a session user ID.
13 . The method of claim 10 , wherein
the SQL log comprises entries or fields for a SQL signature, a SQL text, an accessed object and a command type,
the event log comprises entries or fields for a session identifier, the SQL signature, a current user ID, a top level (Y/N) indicator, and a session user ID, and
the session log comprises entries or fields for the session ID, the session user ID, an Internet Protocol (IP) address, a client program, an operation system (OS) username and a timestamp.
14 . The method of claim 1 , wherein each log of the plurality of logs of the database log is structured as a relational database table comprising a plurality of columns and a plurality of rows.
15 . A system, comprising:
a processor;
a memory for holding programmable code; and
wherein the programmable code includes instructions executable by the processor for capturing information of a Structured Query Language (SQL) statement that has previously executed within a database into a database log, the database log comprising a plurality of logs, each log of the plurality of logs including a plurality of fields populated with respective captured SQL statement information;
analyzing the previously executed SQL statement information captured into the database log to determine whether the previously executed SQL statement should be permitted for subsequent execution within the database;
generating or updating a SQL allow-list to include the previously executed SQL statement after determining that the previously executed SQL statement should be permitted for subsequent execution within the database; and
after generating or updating the SQL allow-list, receiving a SQL statement at the database for processing and enforcing the SQL allow-list within the database, wherein the received SQL statement is permitted to execute in the database when the received SQL statement matches the previously executed SQL statement in the allow-list.
16 . The system of claim 15 , wherein the previously executed SQL statement is represented as a signature value in the SQL allow-list.
17 . The system of claim 16 , wherein the previously executed SQL statement is converted into the signature value by performing SQL normalization on the previously executed SQL statement, identifying accessed objects, and hashing against a combination of the normalized previously executed SQL statement and the accessed objects.
18 . The system of claim 15 , wherein the plurality of logs of the database log comprises:
a SQL signature log, wherein a SQL signature entry in the SQL signature log is generated to uniquely identify each distinct captured SQL statement,
a session log, wherein an entry in the session log is generated for each new database connection, and
an event log, wherein an entry in the event log is generated for each executed SQL command.
19 . The system of claim 15 , further comprising generating or updating a context allow-list comprising one or more session attributes, and enforcing the context allow-list to determine if a condition of the one or more session attributes is met for a session to be run to execute the received SQL statement.
20 . The system of claim 15 , wherein a SQL enforcement module inside of a kernel of the database performs enforcement of the SQL allow-list within the database.
21 . The system of claim 15 , further comprising generating or updating a SQL deny-list, and enforcing the SQL deny-list within the database, wherein execution of the received SQL statement is permitted to execute when the received SQL does not match an entry in the SQL deny-list.
22 . A computer program product embodied on a computer readable medium, the computer readable medium having stored thereon a sequence of instructions which, when executed by a processor, executes at least:
capturing information of a Structured Query Language (SQL) statement that has previously executed within a database into a database log, the database log comprising a plurality of logs, each log of the plurality of logs including a plurality of fields populated with respective captured SQL statement information;
analyzing the previously executed SQL statement information captured into the database log to determine whether the previously executed SQL statement should be permitted for subsequent execution within the database;
generating or updating a SQL allow-list to include the previously executed SQL statement after determining that the previously executed SQL statement should be permitted for subsequent execution within the database; and
after generating or updating the SQL allow-list, receiving a SQL statement at the database for processing and enforcing the SQL allow-list within the database, wherein the received SQL statement is permitted to execute in the database when the received SQL statement matches the previously executed SQL statement in the allow-list.
23 . The computer program product of claim 22 , wherein the previously executed SQL statement is represented as a signature value in the SQL allow-list.
24 . The computer program product of claim 23 , wherein the previously executed SQL statement is converted into the signature value by performing SQL normalization on the previously executed SQL statement, identifying accessed objects, and hashing against a combination of the normalized previously executed SQL statement and the accessed objects.
25 . The computer program product of claim 22 , wherein wherein the plurality of logs of the database log comprises:
a SQL signature log, wherein a SQL signature entry in the SQL signature log is generated to uniquely identify each distinct captured SQL statement,
a session log, wherein an entry in the session log is generated for each new database connection, and
an event log, wherein an entry in the event log is generated for each executed SQL command.
26 . The computer program product of claim 22 , further comprising generating or updating a context allow-list comprising one or more session attributes, and enforcing the context allow-list to determine if a condition of the one or more session attributes is met for a session to be run to execute the received SQL statement.
27 . The computer program product of claim 22 , wherein a SQL enforcement module inside of a kernel of the database performs enforcement of the SQL allow-list within the database.
28 . The computer program product of claim 22 , further comprising generating or updating a SQL deny-list, and enforcing the SQL deny-list within the database, wherein execution of the received SQL statement is permitted to execute when the received SQL does not match an entry in the SQL deny-list.