Further Security Configuration

SQL injection is an attack that uses malicious SQL queries in reports to gain access or do damage to your databases. By default, JasperReports Server validates query strings to protect against SQL injection.

Whenever the server runs an SQL query, the server validates the query string with the following rules:

•

SQL queries must start with SELECT.

•

Queries may not contain INTO clauses.

•

Queries may call stored procedures (CALL command used by JDBC drivers).

•

Multiple queries separated by semi-colons (;) are also prohibited.

•

SQL comments are allowed, but will be removed before being transmitted.

If your reports or Domains use such queries, you need to either change your queries or update the security configuration to allow them.

Users who run a report with a query that does not meet the rules will see an error. Administrators can monitor the server logs to search for evidence of attempted security breaches.

SQL query validation is enabled by default when installing JasperReports Server. To turn off this protection, edit the following file:

SQL Query Validation
Configuration File
.../WEB-INF/classes/esapi/security-config.properties
Property	Default Value	Description
security.validation.sql.on	true	Turns SQL query validation on or off in the server. Any other value besides case-insensitive “false” is equivalent to true.

SQL query validation rules were added to comply with security guidelines for web applications. Turning off query validation or modifying the validation rules may make the server more vulnerable to web attacks.

Customizing the Error Message

When query validation blocks a query that violates a security rule, the server displays an error in the UI. By default, security messages are intentionally generic to avoid alerting potential attackers to security errors.

We highly recommend that external deployments customize the security error message to be unique, yet still generic. You can change both the message and the error number. Choose any combination of numbers or letters so administrators can easily search the logs to detect security violations.

Query Validation Messages
Configuration File
.../WEB-INF/bundles/security.properties
Property	Default Value
message.validation.sql	An error has occurred. Please contact your system administrator. (6632)

If you translate your application into other languages, be sure to create a locale-specific copy of this file and translate these messages as well.

Understanding Query Validation

Query validation uses a mechanism to validate every SQL query before running it. The validation process is defined by a validation rule that references a validator expression. The rule and the expression are defined in separate files.

The security.properties and validation.properties files contain many validation rules and expressions. These were used for general input validation in the server, but this mechanism is deprecated and no longer used. Only the expressions for SQL validation are still applicable.

Query Validation Rule
Configuration File
.../WEB-INF/classes/esapi/security.properties
Property	Default Value
sqlQueryExecutor	Alpha,ValidSQL,500000,true,SQL_Query_Executor_context

The validation rule contains 5 comma-separated values:

•

Alpha – Not used for query validation.

•

ValidSQL – The name of the SQL validator expression in the other file.

•

500000 – The maximum length allowed for the query.

•

true – Whether the query can be blank.

•

SQL_Query_Executor_context – Context string for log messages.

SQL Validator Expression
Configuration File
.../WEB-INF/classes/esapi/validation.properties
Property	Default Value
Validator.ValidSQL	(?is)^s(select\|call)b((?!bintob)[^;]);?s*$

The validator expression is a regular expression that must match the query string. The default expression enforces the following:

•

Queries may only use the SELECT statement, which is read-only. The following write statements are forbidden: DROP, INSERT, UPDATE, DELETE

•

SELECT statements may not use the INTO clause that could copy data.

•

CALL statements for stored procedures are allowed.

•

Multiple queries separated by semi-colons (;) will be rejected. The following example will cause a security error: SELECT f1,f2 FROM tbl_1; SELECT f3 from tbl_2;

The rule and validator expression are commented by default because the server implements the same SQL validation with an internal mechanism. If you wish to customize the SQL validation, uncomment the rule and create a new validator expression as described below.

Customizing Query Validation

If you wish to use a different validator expression for queries, always create a new validator expression with a new name in validation.properties, then substitute that name in the validation rule in security.properties. For example, if you wish to forbid queries from running stored procedures in your database, you can add the following validator expression in validation.properties:

Then you would uncomment and modify the validation rule in security.properties as follows:

It is also possible to have two or more validation rules that will be applied sequentially (logical AND) until one fails. The rules must have the same names but with a numerical suffix, for example:

With multiple rules for query validation, each rule is applied in the order listed until one fails. When one rule fails, the whole validation fails.

Performance Issues

By default, the internal SQL validation mechanism accesses the query metadata to allow semicolons (;) in the data part of the query, for example in table names. This access can cause performance issue with certain JDBC drivers, in which case you can disable it as follows:

Advanced Input Validation
Configuration File
.../WEB-INF/classes/esapi/security-config.properties
Property	Default Value	Description
validate.sql.via.metadata.query.execution	true	Set this value to false to disable semicolon checking in query metadata if SQL validation causes performance issues with your JDBC driver.