NF9 Form SQL Query

Feature: Form SQL Query

Goal: Technical

Developer: Carlos Ruiz

Sponsor: FH

Description

The new form SQL Query allows admin users to execute queries in the database.

The form DOES NOT support multiple SQL statements, or any statement containing a semicolon (;)

It allows just to execute single queries.

For example:

The SQL statement executed is logged in the AD_Issue table as shown here:

Configuration

SysConfig FORM_SQL_QUERY_ALLOWED_KEYWORDS

The allowed commands are defined in the SysConfig key FORM_SQL_QUERY_ALLOWED_KEYWORDS, by default the form is enabled to execute the following commands:

• SELECT
• WITH
• SHOW

Restricting one of those commands to be executed can be simply done by removing it from this SysConfig whitelist.

SysConfig FORM_SQL_QUERY_TIMEOUT_IN_SECONDS

The SysConfig key FORM_SQL_QUERY_TIMEOUT_IN_SECONDS defines the timeout in seconds allowed to run the query, by default is 120 seconds (2 minutes).

SysConfig FORM_SQL_QUERY_MAX_RECORDS

The SysConfig key FORM_SQL_QUERY_MAX_RECORDS defines the maximum number of records allowed to be queries with this tool, by default is set to 500 records.

SysConfig FORM_SQL_QUERY_LOG_ISSUE

The SysConfig key FORM_SQL_QUERY_LOG_ISSUE defines if the audit log is created in the AD_Issue table. The default is to create the audit record.

Technical Note

When a replica database is configured, the query is executed against the replica database when possible.

Technical Info: IDEMPIERE-5451