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. |
Further Security Configuration
The security configuration file contains other default security settings. In particular, they can warn you when a security file has a syntax error and could not be loaded. Changing these defaults is possible but not recommended:
Advanced Input Validation | ||
Configuration File | ||
.../WEB-INF/classes/esapi/security-config.properties | ||
Property | Default Value | Description |
log.msg.security.off | SECURITY for [%s] is OFF | If security is turned OFF, this message will be logged. This message in the logs can alert administrators if the security configuration has been tampered with. |
msg.cannot.load | Security configuration [%s] | If there is an error in the security configuration files, this message is logged. This is a severe error and should be resolved by the administrator. |
Recommended Comments
There are no comments to display.