Jump to content
  • This documentation is an older version of JasperReports Server Security Guide. View the latest documentation.

    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.

    warning-icon-ns_28x28.png.96c934422ed5e6f9ddd188367d407c88.png

    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.

    note-icon-ns_28x28.png.8e095a008250181987a3ea05689e0323.png

    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;

    warning-icon-ns_28x28.png.e968869e904d046003f9b031a917d3c6.png

    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:

    note-icon-ns_28x28.png.64a10e4929dbfee116350a3865cbb6be.png

    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]
    cannot be loaded.

    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.


    User Feedback

    Recommended Comments

    There are no comments to display.



    Guest
    This is now closed for further comments

×
×
  • Create New...