Protecting Against SQL Injection

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. If you want to allow special queries such as stored procedures, you can modify the default settings.

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

•

SQL queries must start with SELECT.

•

Comments in queries are not allowed.

•

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

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	Value	Description
security.validation.sql.on	true <default>	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	Value
message.validation.sql	An error has occurred. Please contact your system administrator. (6632) <default>

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 the same mechanism as input validation, but the query executor process performs the validation before running every query. The validation process is defined by a validation rule that references a validator expression. The rule and the expression are defined in separate files.

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

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	Value
Validator.ValidSQL	^s*((?i)select)s+[^;]+$ <default>

The validator expression is a regular expression that must match the query string. By default, this 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

•

SQL comments are forbidden.

•

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;

Do not modify the default SQL validator expression provided with the server. We have thoroughly tested this expression to provide reasonable query validation security while allowing for the general use of the application.

If you wish to use a different validator expression for queries, always create a new validator expressions with a new name.

For more details about the validation mechanism, see Protecting Against Cross-Site Scripting.

Adding Validation for Stored Procedures

If you want to use stored procedures as your queries, the default query validation rule above will not allow them. You must add a custom validator expression to the validation.properties file.

To allow stored procedures in addition to SQL queries:

Make a backup copy of the file <js-webapp>/WEB-INF/classes/esapi/security.properties, then open it for editing.

Add the second validation rule for queries, as shown in the following table:

Stored Procedure Rule
Configuration File
.../WEB-INF/classes/esapi/security.properties
Property	Value
sqlQueryExecutor	Alpha,ValidSQL,500000,true,SQL_Query_Executor_context <default>
sqlQueryExecutor2	Alpha,ValidPROC,500000,true,SQL_SProc_Executor_context <new expression>

Make a backup copy of the file <js-webapp>/WEB-INF/classes/esapi/validation.properties, then open it for editing.

Add the validator expression for procedures, as shown in the following table:

Stored Procedure Expression
Configuration File
.../WEB-INF/classes/esapi/validation.properties
Property	Value
Validator.ValidSQL	^s*((?i)select)s+[^;]+$ <default>
Validator.ValidPROC	^s*(((?i)call)s+[^;]+)$ <new expression>

Save the files, and restart the server or redeploy its web application.

With multiple rules for query validation, each rule is applied in the order listed until one passes (equivalent to a logical OR). The rules that fail still appear as security warnings in the logs. This means that every time a stored procedure is validated, the rule with the ValidSQL expression will fail first and appear as a false-positive in the logs.

Sign In