Product Documentation

XML SQL Injection Check

Aug 31, 2016

The XML SQL Injection check examines the user requests for possible XML SQL Injection attacks. If it finds injected SQL in XML payloads, it blocks the requests. 

A XML SQL attack can inject source code into a web application such that it can be interpreted and executed as a valid SQL query to perform a database operation with malicious intent.  For example, XML SQL attacks can be launched to gain unauthorized access to the contents of a database or to manipulate the stored data. XML SQL Injection attacks are not only common, but can also be very harmful and costly.

Compartmentalizing the privileges of the database users can assist in protecting the database to some extent. All database users should be given only the required privileges to complete their intended tasks, so that they cannot execute SQL queries to perform other tasks. For example, a read-only user should not be allowed to write or manipulate data tables. The application firewall XML SQL Injection check inspects all XML requests to provide special defenses against injection of unauthorized SQL code that might break security. If the application firewall detects unauthorized SQL code in any XML request of any user, it can block the request.

The NetScaler application firewall inspects the presence of SQL keywords and special characters to identify the XML SQL Injection attack. A default set of keywords and special characters provides known keywords and special characters that are commonly used to launch XML SQL attacks. The application firewall considers three characters, single straight quote ('), backslash (\), and semicolon (;) as special characters for SQL security check processing. You can add new patterns, and you can edit the default set to customize the XML SQL check inspection. 

The application firewall offers various action options for implementing XML SQL Injection protection. You can Block the request, Log a message in the ns.log file with details regarding the observed violations, and collect Stats to keep track of the number of observed attacks. 

In addition to actions, there are several parameters that can be configured for XML SQL injection processing. You can check for SQL wildcard characters. You can change the XML SQL Injection type and select one of the 4 options (SQLKeyword, SQLSplChar, SQLSplCharANDKeyword, SQLSplCharORKeyword) to indicate how to evaluate the SQL keywords and SQL special characters when processing the XML payload. The XML SQL Comments Handling parameter gives you an option to specify the type of comments that need to be inspected or exempted during XML SQL Injection detection.

You can deploy relaxations to avoid false positives. The application firewall XML SQL check is performed on the payload of the incoming requests, and attack strings are identified even if they are spread over multiple lines. The check looks for SQL Injection strings in the element and the attribute values. You can apply relaxations to bypass security check inspection under specified conditions. The logs and statistics can help you identify needed relaxations. 

Action Options

An action is applied when the XML SQL Injection check detects an SQL Injection attack string in the request. The following actions are available for configuring an optimized XML SQL Injection protection for your application:

Block—If you enable block, the block action is triggered only if the input matches the XML SQL injection type specification. For example, if SQLSplCharANDKeyword is configured as the XML SQL injection type, a request is not blocked if it does not contain any key words, even if SQL special characters are detected in the payload. Such a request is blocked if the XML SQL injection type is set to either SQLSplChar, or SQLSplCharORKeyword.

Log—If you enable the log feature, the XML SQL Injection check generates log messages indicating the actions that it takes. If block is disabled, a separate log message is generated for each location (ELEMENT, ATTRIBUTE) in which the XML SQL violation was detected.  However, only one message is generated when the request is blocked. You can monitor the logs to determine whether responses to legitimate requests are getting blocked. A large increase in the number of log messages can indicate attempts to launch an attack.

Stats—If enabled, the stats feature gathers statistics about violations and logs. An unexpected surge in the stats counter might indicate that your application is under attack. If legitimate requests are getting blocked, you might have to revisit the configuration to see if you need to configure new relaxation rules or modify the existing ones.

XML SQL Parameters

In addition to the block, log and stats actions, you can configure the following parameters for XML SQL Injection check:

Check for XML SQL Wildcard Characters—Wild card characters can be used to broaden the selections of a structured query language (SQL-SELECT) statement. These wild card operators can be used in conjunction with LIKE and NOT LIKE operators to compare a value to similar values. The percent (%), and underscore (_) characters are frequently used as wild cards.  The percent sign is analogous to the asterisk (*) wildcard character used with MS-DOS and to match zero, one, or multiple characters in a field.  The underscore is similar to the MS-DOS question mark (?) wildcard character. It matches a single number or character in an expression.

For example, you can use the following query to do a string search to find all customers whose names contain the D character.

SELECT * from customer WHERE name like "%D%"

The following example combines the operators to find any salary values that have 0 as the second and third character.

SELECT * from customer WHERE salary like '_00%'

Different DBMS vendors have extended the wildcard characters by adding extra operators. The NetScaler application firewall can protect against attacks that are launched by injecting these wildcard characters. The 5 default Wildcard characters are percent (%), underscore (_), caret (^), opening square bracket ([), and closing square bracket (]). This protection applies to both HTML and XML profiles.

The default wildcard chars are a list of literals specified in the *Default Signatures:

  • <wildchar type=”LITERAL”>%</wildchar>
  • <wildchar type=”LITERAL”>_</wildchar>
  • <wildchar type=”LITERAL”>^</wildchar>
  • <wildchar type=”LITERAL”>[</wildchar>
  • <wildchar type=”LITERAL”>]</wildchar>

Wildcard characters in an attack can be PCRE, like [^A-F]. The application firewall also supports PCRE wildcards, but the literal wildcard chars above are sufficient to block most attacks. 

메모

The XML SQL wildcard character check is different from the XML SQL special character check. This option must be used with caution to avoid false positives.  

Check Request Containing SQL Injection Type—The application firewall provides 4 options to implement the desired level of strictness for SQL Injection inspection, based on the individual need of the application. The request is checked against the injection type specification for detecting SQL violations.  The 4 SQL injection type options are:

  • SQL Special Character and Keyword—Both an SQL keyword and an SQL special character must be present in the inspected location to trigger SQL violation. This least restrictive setting is also the default setting.
  •  SQL Special Character—At least one of the special characters must be present in the processed payload string to trigger SQL violation.
  • SQL keyword—At least one of the specified SQL keywords must be present in the processed payload string to trigger an SQL violation. Do not select this option without due consideration. To avoid false positives, make sure that none of the keywords are expected in the inputs.
  • SQL Special Character or Keyword—Either the keyword or the special character string must be present in the payload to trigger the security check violation. 

If you select the SQL Special Character option, the application firewall skips strings that do not contain any special characters. Since most SQL servers do not process SQL commands that are not preceded by a special character, enabling this option can significantly reduce the load on the application firewall and speed up processing without placing your protected web sites at risk.

SQL comments handling—By default, the application firewall parses and checks all comments in XML data for injected SQL commands. Many SQL servers ignore anything in a comment, even if preceded by an SQL special character. For faster processing, if your XML SQL server ignores comments, you can configure the application firewall to skip comments when examining requests for injected SQL. The XML SQL comments handling options are:

  • ANSI—Skip ANSI-format SQL comments, which are normally used by UNIX-based SQL databases.
  • Nested—Skip nested SQL comments, which are normally used by Microsoft SQL Server.
  • ANSI/Nested—Skip comments that adhere to both the ANSI and nested SQL comment standards. Comments that match only the ANSI standard, or only the nested standard, are still checked for injected SQL.
  • Check all Comments—Check the entire request for injected SQL, without skipping anything. This is the default setting.

In most cases, you should not choose the Nested or the ANSI/Nested option unless your back-end database runs on Microsoft SQL Server. Most other types of SQL server software do not recognize nested comments. If nested comments appear in a request directed to another type of SQL server, they might indicate an attempt to breach security on that server. 

Relaxation rules

If your application requires you to bypass the XML SQL Injection inspection for a specific ELEMENT or ATTRIBUTE in the XML payload, you can configure a relaxation rule. The XML SQL Injection inspection relaxation rules have the following parameters: 

  • Name: You can use literal strings or regular expressions to configure the name of the ELEMENT or the ATTRIBUTE. The following expression exempts all ELEMENTS beginning with the string PurchaseOrder_ followed by a string of numbers that is at least two and no more than ten characters long:

    Comment:  "Exempt XML SQL Check for Purchase Order Elements"

    XMLSQLInjection:  "PurchaseOrder_[0-9A-Za-z]{2,10}"

    IsRegex:  REGEX          Location:  ELEMENT

    State:  ENABLED

Note: The names are case sensitive. Duplicate entries are not allowed, but you can use capitalization of the names and differences in location to create similar entries. For example, each of the following relaxation rules is unique:

1)      XMLSQLInjection:  XYZ    IsRegex:  NOTREGEX

        Location:  ELEMENT       State:  ENABLED

2)      XMLSQLInjection:  xyz    IsRegex:  NOTREGEX

        Location:  ELEMENT       State:  ENABLED

3)      XMLSQLInjection:  xyz    IsRegex:  NOTREGEX

        Location:  ATTRIBUTE     State:  ENABLED

4)      XMLSQLInjection:  XYZ    IsRegex:  NOTREGEX

        Location:  ATTRIBUTE     State:  ENABLED

  • Location: You can specify the Location of the XML SQL Inspection exception in your XML payload. The option ELEMENT is selected by default. You can change it to ATTRIBUTE.
  • Comment: This is an optional field. You can use up to a 255 character string to describe the purpose of this relaxation Rule. 

경고

Regular expressions are powerful. Especially if you are not thoroughly familiar with PCRE-format regular expressions, double-check any regular expressions you write. Make sure that they define exactly the name that you want to add as an exception, and nothing else. Careless use of Regular Expressions can have results that you do not want, such as blocking access to web content that you did not intend to block or allowing an attack that the XML SQL Injection inspection would otherwise have blocked. 

Using the Command Line to Configure the XML SQL Injection Check

To configure XML SQL Injection actions and other parameters by using the command line

In the command line interface, you can use either the set appfw profile command or the add appfw profile command to configure the XML SQL Injection protections. You can enable the block, log, and stats action(s). Select the type of SQL attack pattern (key words, wildcard characters, special strings) you want to detect in the payloads.  Use the unset appfw profile command to revert the configured settings back to their defaults. Each of the following commands sets only one parameter, but you can include multiple parameters in a single command:

  • set appfw profile <name> -XMLSQLInjectionAction (([block] [log] [stats]) | [none])
  • set appfw profile <name> -XMLSQLInjectionCheckSQLWildChars (ON |OFF)
  • set appfw profile <name> -XMLSQLInjectionType ([SQLKeyword] | [SQLSplChar] |  [SQLSplCharANDKeyword] | [SQLSplCharORKeyword])
  • set appfw profile <name> -XMLSQLInjectionParseComments ([checkall] | [ansi|nested] | [ansinested])

To configure a SQL Injection relaxation rule by using the command line

Use the bind or unbind command to add or delete relaxation rules, as follows:

  • bind appfw profile <name> -XMLSQLInjection <string> [isRegex (REGEX | NOTREGEX)] [-location ( ELEMENT | ATTRIBUTE )] –comment <string> [-state ( ENABLED | DISABLED )]
  • unbind appfw profile <name> -XMLSQLInjection <String>

Example:

> bind appfw profile test_profile -XMLSQLInjection "PurchaseOrder_[0-9A-Za-z]{2,15}" -isregex REGEX -location ATTRIBUTE

> unbind appfw profile test_profile –XMLSQLInjection "PurchaseOrder_[0-9A-Za-z]{2,15}" -location ATTRIBUTE

Using the Configuration Utility to Configure the XMLSQL Injection Security Check

In the configuration utility, you can configure the XML SQL Injection security check in the pane for the profile associated with your application.

To configure or modify the XML SQL Injection check by using the configuration utility

  1. Navigate to Application Firewall > Profiles, highlight the target profile, and click Edit
  2. In the Advanced Settings pane, click Security Checks.

The security check table displays the currently configured action settings for all the security checks. You have 2 options for configuration: 

a. If you just want to enable or disable Block, Log, and Stats actions for XML SQL Injection, you can select or clear check boxes in the table, click OK, and then click Save and Close to close the Security Check pane.

b. If you want to configure additional options for this security check, double click XML SQL Injection, or select the row and click Action Settings, to display the following options:

Check for SQL Wildcard Characters—Consider SQL Wildcard characters in the payload to be attack patterns.

Check Request Containing—Type of SQL injection (SQLKeyword, SQLSplChar, SQLSplCharANDKeyword, or SQLSplCharORKeyword) to check.

SQL Comments Handling—Type of comments (Check All Comments, ANSI, Nested, or ANSI/Nested) to check.

After changing any of the above settings, click OK to save the changes and return to the Security Checks table. You can proceed to configure other security checks if needed. Click OK to save all the changes you have made in the Security Checks section, and then click Save and Close to close the Security Check pane.

To configure a XML SQL Injection relaxation rule by using the configuration utility

  1. Navigate to Application Firewall > Profiles, highlight the target profile, and click Edit.
  2. In the Advanced Settings pane, click Relaxation Rules.
  3. In the Relaxation Rules table, double-click the XML SQL Injection entry, or select it and click Edit.
  4. In the XML SQL Injection Relaxation Rules dialogue box, perform Add, Edit, Delete, Enable, or Disable operations for relaxation rules.  

To manage XML SQL Injection relaxation rules by using the visualizer

For a consolidated view of all the relaxation rules, you can highlight the XML SQL Injection row in the Relaxation Rules table, and click Visualizer. The visualizer for deployed relaxations offers you the option to Add a new rule or Edit an existing one. You can also Enable or Disable a group of rules by selecting a node and clicking the corresponding buttons in the relaxation visualizer.  

To view or customize the SQL Injection patterns by using the configuration utility

You can use the configuration utility to view or customize the SQL patterns.

The default SQL patterns are specified in Application Firewall > Signatures > *Default Signatures.  If you do not bind any signature object to your profile, the default SQL patterns specified in the Default Signatures object will be used by the profile for XML SQL Injection security check processing. The rules and patterns in the Default Signatures object are read-only. You cannot edit or modify them.  If you want to modify or change these patterns, create a user-defined signature object by making a copy of the Default Signatures object and changing the SQL patterns. Use the user-defined signature object in the profile that processes the traffic for which you want to use these customized SQL patterns.

For more information, see Signatures.

To view default SQL patterns:

a. Navigate to Application firewall > Signatures, select *Default Signatures, and click Edit.  Then click Manage SQL/XSS Patterns.

The Manage SQL/XSS Paths table shows following four rows pertaining to SQL Injection:

Injection (not_alphanum, SQL)/ Keyword

Injection (not_alphanum, SQL)/ specialstring

Injection (not_alphanum, SQL)/ transformrules/transform

Injection (not_alphanum, SQL)/ wildchar

b. Select a row and click Manage Elements to display the corresponding SQL patterns (keywords, special strings, transformation rules or the wildcard characters) used by the application firewall SQL injection check.

To customize SQL Patterns: You can edit a user-defined signature object to customize the SQL key words, special strings, and wildcard characters. You can add new entries or remove the existing ones. You can modify the transformation rules for the SQL special strings.  

a. Navigate to Application firewall > Signatures, highlight the target user-defined signature, and click Edit. Click Manage SQL/XSS Patterns to display the Manage SQL/XSS paths table.

b. Select the target SQL row.

i. Click Manage Elements, to Add, Edit or Remove the corresponding SQL element.

ii. Click Remove to remove the selected row. 

경고

You must be very careful when removing or modifying any default SQL element, or deleting the SQL path to remove the entire row. The signature rules as well as the XML SQL Injection security check rely on these elements for detecting SQL Injection attacks to protect your applications. Customizing the SQL patterns can make your application vulnerable to XML SQL attacks if the required pattern is removed during editing.

Using the Log Feature with the XML SQL Injection Check

When the log action is enabled, the XML SQL Injection security check violations are logged in the audit log as APPFW_XML_SQL violations. The application firewall supports both Native and CEF log formats. You can also send the logs to a remote syslog server.  

To access the log messages by using the command line

Switch to the shell and tail the ns.logs in the /var/log/ folder to access the log messages pertaining to the XML Cross-Site Scripting violations:

Shell

> tail -f /var/log/ns.log | grep APPFW_XML_SQL

To access the log messages by using the configuration utility

The Citrix configuration utility includes a useful tool (Syslog Viewer) for analyzing the log messages. You have multiple options for accessing the Syslog Viewer:

  • Navigate to Application Firewall > Profiles, select the target profile, and click Security Checks. Highlight the XML SQL Injection row and click Logs.  When you access the logs directly from the XML SQL Injection check of the profile, the configuration utility filters out the log messages and displays only the logs pertaining to these security check violations.
  • You can also access the Syslog Viewer by navigating to System > Auditing.  In the Audit Messages section, click the Syslog messages link to display the Syslog Viewer, which displays all log messages, including other security check violation logs. This is useful for debugging when multiple security check violations might be triggered during request processing.
  • Navigate to Application Firewall > Policies > Auditing. In the Audit Messages section, click the Syslog messages link to display the Syslog Viewer, which displays all log messages, including other security check violation logs.

The XML based Syslog Viewer provides various filter options for selecting only the log messages that are of interest to you.  To select log messages for the XML SQL Injection check, filter by selecting APPFW in the dropdown options for Module. The Event Type list offers a rich set of options to further refine your selection. For example, if you select the APPFW_XML_SQL check box and click the Apply button, only log messages pertaining to the XML SQL Injection security check violations appear in the Syslog Viewer. 

If you place the cursor in the row for a specific log message, multiple options, such as Module, Event Type, Event ID, and Client IP appear below the log message. You can select any of these options to highlight the corresponding information in the log message.

Statistics for the XML SQL Injection violations

When the stats action is enabled, the counter for the XML SQL Injection check is incremented when the application firewall takes any action for this security check. The statistics are collected for Rate and Total count for Traffic, Violations, and Logs. The size of an increment of the log counter can vary depending on the configured settings. For example, if the block action is enabled, a request for a page that contains three XML SQL Injection violations increments the stats counter by one, because the page is blocked as soon as the first violation is detected. However, if block is disabled, processing the same request increments the statistics counter for violations and the logs by three, because each violation generates a separate log message.

To display XML SQL Injection check statistics by using the command line

At the command prompt, type:

> sh appfw stats

To display stats for a specific profile, use the following command:

> stat appfw profile <profile name>

To display XML SQL Injection statistics by using the configuration utilitY

  1. Navigate to System > Security > Application Firewall.
  2. In the right pane, access the Statistics Link. 
  3. Use the scroll bar to view the statistics about XML SQL Injection violations and logs. The statistics table provides real-time data and is updated every 7 seconds.