Many web applications have web forms that use SQL to communicate with relational database servers. Malicious code or a hacker can use an insecure web form to send SQL commands to the web server. The application firewall HTML SQL Injection check provides special defenses against injection of unauthorized SQL code that might break security. If the application firewall detects unauthorized SQL code in a user request, it either transforms the request, to render the SQL code inactive, or blocks the request. The application firewall examines the request payload for injected SQL code in three locations: 1) POST body, 2) headers, and 3) cookies.
A default set of keywords and special characters provides known keywords and special characters that are commonly used to launch SQL attacks. You can add new patterns, and you can edit the default set to customize the SQL check inspection. The application firewall offers various action options for implementing SQL Injection protection. In addition to the Block, Log, Stats and Learn actions, the application firewall profile also offers the option to transform SQL special characters to render an attack harmless.
In addition to actions, there are several parameters that can be configured for SQL injection processing. You can check for SQL wildcard characters. You can change the 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 payload. The SQL Comments Handling parameter gives you an option to specify the type of comments that need to be inspected or exempted during SQL Injection detection.
You can deploy relaxations to avoid false positives. The application firewall learning engine can provide recommendations for configuring relaxation rules.
Following options are available for configuring an optimized SQL Injection protection for your application:
Block—If you enable block, the block action is triggered only if the input matches the SQL injection type specification. For example, if SQLSplCharANDKeyword is configured as the SQL injection type, a request is not blocked if it contains no key words, even if SQL special characters are detected in the input. Such a request is blocked if the SQL injection type is set to either SQLSplChar, or SQLSplCharORKeyword.
Log—If you enable the log feature, the SQL Injection check generates log messages indicating the actions that it takes. If block is disabled, a separate log message is generated for each input field in which the SQL violation was detected. However, only one message is generated when the request is blocked. Similarly, one log message per request is generated for the transform operation, even when SQL special characters are transformed in multiple fields. 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.
Learn—If you are not sure which SQL relaxation rules might be ideally suited for your application, you can use the learn feature to generate recommendations based on the learned data. The application firewall learning engine monitors the traffic and provides SQL learning recommendations based on the observed values. To get optimal benefit without compromising performance, you might want to enable the learn option for a short time to get a representative sample of the rules, and then deploy the rules and disable learning.
Transform SQL special characters—The application firewall considers three characters, Single straight quote ('), Backslash (\), and Semicolon (;) as special characters for SQL security check processing. The SQL Transformation feature modifies the SQL Injection code in an HTML request to ensure that the request is rendered harmless. The modified HTML request is then sent to the server. All default transformation rules are specified in the /netscaler/default_custom_settings.xml file.
The transform operation renders the SQL code inactive by making the following changes to the request:
These three characters (special strings) are necessary to issue commands to an SQL server. Unless an SQL command is prefaced with a special string, most SQL servers ignore that command. Therefore, the changes that the application firewall performs when transformation is enabled prevent an attacker from injecting active SQL. After these changes are made, the request can safely be forwarded to your protected web site. When web forms on your protected web site can legitimately contain SQL special strings, but the web forms do not rely on the special strings to operate correctly, you can disable blocking and enable transformation to prevent blocking of legitimate web form data without reducing the protection that the application firewall provides to your protected web sites.
The transform operation works independently of the SQL Injection Type setting. If transform is enabled and the SQL Injection type is specified as SQL keyword, SQL special characters are transformed even if the request does not contain any keywords.
You normally enable either transformation or blocking, but not both. If the block action is enabled, it takes precedence over the transform action. If you have blocking enabled, enabling transformation is redundant.
Check for 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 in the second and third place.
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:
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 SQL wildcard character check is different from the 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:
If you configure the application firewall to check for inputs that contain an SQL special character, the application firewall skips web form fields 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 checks all SQL comments for injected SQL commands. Many SQL servers ignore anything in a comment, however, even if preceded by an SQL special character. For faster processing, if your SQL server ignores comments, you can configure the application firewall to skip comments when examining requests for injected SQL. The SQL comments handling options are:
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.
Check Request headers—Enable this option if, in addition to examining the input in the form fields, you want to examine the request headers for HTML SQL Injection attacks. If you use the configuration utility, you can enable this parameter in the Advanced Settings -> Profile Settings pane of the application firewall profile.
If you enable the Check Request header flag, you might have to configure relaxation rule for the User-Agent header. Presence of the SQL keyword like and SQL special character semi-colon (;) might trigger false positive and block requests that contain this header.
If you enable both request header checking and transformation, any SQL special characters found in headers are also transformed. The Accept, Accept-Charset, Accept-Encoding, Accept-Language, Expect, and User-Agent headers normally contain semicolons (;). Enabling both Request header checking and transformation simultaneously might cause errors.
The application firewall gives you an option to exempt a specific form field, header, or Cookie from SQL Injection inspection check. You can completely bypass the inspection for one or more of these fields by configuring relaxation rules for the SQL Injection check.
The application firewall allows you to implement tighter security by fine tuning the relaxation rules. An application might require the flexibility to allow specific patterns, but configuring a relaxation rule to bypass the security inspection might make the application vulnerable to attacks, because the target field is exempted from inspection for any SQL attack patterns. SQL fine grained relaxation provides the option to allow specific patterns and block the rest. For example, the application firewall currently has a default set of more than 100 SQL keywords. Because hackers can use these keywords in SQL Injection attacks, the application firewall flags them as potential threats. You can relax one or more keyword(s) that are considered safe for the specific location. The rest of the potentially dangerous SQL keywords are still checked for the target location and continue to trigger the security check violations. You now have much tighter control.
The commands used in relaxations have optional parameters for Value Type and Value Expression. You can specify whether the value expression is a regular expression or a literal string. The value type can be left blank or you have an option to select Keyword or SpecialString or WildChar.
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 URL that you want to add as an exception, and nothing else. Careless use of wildcards, and especially of the dot-asterisk ( .*) metacharacter/wildcard combination, 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 HTML SQL Injection check would otherwise have blocked.
Points to Consider:
To configure 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 SQL Injection protections. You can enable the block, learn, log, stats action(s) and specify whether you want to transform the special characters used in SQL Injection attack strings to disable the attack. Select the type of SQL attack pattern (key words, wildcard characters, special strings) you want to detect in the payloads, and indicate whether you want the application firewall to also inspect the request Headers for SQL Injection violations. 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:
To configure a SQL Injection relaxation rule by using the command line
Use the bind or unbind command to add or delete binding, as follows:
bind appfw profile <name> -SQLInjection <String> [isRegex (REGEX | NOTREGEX)] <formActionURL> [-location <location>] [-valueType (Keyword|SpecialString|Wildchar) [<valueExpression>][-isValueRegex (REGEX | NOTREGEX) ]]
unbind appfw profile <name> -SQLInjection <String> <formActionURL> [-location <location>] [-valueType (Keyword|SpecialString|Wildchar) [<valueExpression>]]
In the configuration utility, you can configure the SQL Injection security check in the pane for the profile associated with your application.
To configure or modify the SQL Injection check by using the configuration utility
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, Stats, and Learn actions for HTML 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 HTML SQL Injection, or select the row and click Action Settings, to display the following options:
Transform SQL Special character—Transform any SQL Special characters in the request.
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.
3. To enable or disable the Check request Header setting, in the Advanced Settings pane, click Profile Settings. In Common Settings, Select or clear the Check Request Headers check box. Click OK. You can either use the X icon at the top right hand side of the Profile Settings pane to close this section or, if you have finished configuring this profile, you can click Done to return to the Application Firewall > Profile.
To configure an SQL Injection relaxation rule by using the configuration utility
When you add a new rule, the Value Expression field is not displayed unless you select Keyword or SpecialString or WildChar option in the Value Type Field.
To manage SQL Injection relaxation rules by using the visualizer
For a consolidated view of all the relaxation rules, you can highlight the HTML SQL Injection row 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 the SQL Injection security check processing. The rules and patterns, specified in the default signatures object, are read-only. You cannot edit or modify them. If you want to modify or change these patterns, make a copy of the Default Signatures object to create a User-Defined signature object. Make changes in the SQL patterns in the new User-defined signature object and use this signature object in your profile that is processing the traffic for which you want to use these customized SQL patterns.
For more information, see Signatures.
1. 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.
2. To customize SQL patterns: You can edit the 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 Injection 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 before you remove or modify any default SQL element, or delete the SQL path to remove the entire row. The signature rules as well as the SQL Inject 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 SQL attacks if the required pattern is removed during editing.
When the learn action is enabled, the application firewall learning engine monitors the traffic and learns the triggered violations. You can periodically inspect these learned rules. After due consideration, you can deploy the learned rule as an SQL Injection relaxation rule.
SQL Injection Learning enhancement—An application firewall learning enhancement was introduced in release 11.0 of the NetScaler software. To deploy fine grained SQL Injection relaxation, the application firewall offers fine grained SQL Injection learning. The learning engine makes recommendations regarding the observed Value Type (keyword, SpecialString, Wildchar) and the corresponding Value expression observed in the input fields. In addition to checking the blocked requests to determine whether the current rule is too restrictive and needs to be relaxed, you can review the rules generated by the learning engine to determine which value type and value expressions are triggering violations and need to be addressed in relaxation rules.
The application firewall’s learning engine can distinguish only the first 128 bytes of the name. If a form has multiple fields with names that match for the first 128 bytes, the learning engine might not be able to distinguish between them. Similarly, the deployed relaxation rule might inadvertently relax all such fields from SQL Injection inspection.
To view or use learned data by using the command line interface
At the command prompt, type one of the following commands:
To view or use learned data 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 Learned Rules. You can select the HTML SQL Injection entry in the Learned Rules table and double-click it to access the learned rules. You can deploy the learned rules or edit a rule before deploying it as a relaxation rule. To discard a rule, you can select it and click the Skip button. You can edit only one rule at a time, but you can select multiple rules to deploy or skip.
You also have the option to show a summarized view of the learned relaxations by selecting the HTML SQL Injection entry in the Learned Rules table and clicking Visualizer to get a consolidated view of all the learned violations. The visualizer makes it very easy to manage the learned rules. It presents a comprehensive view of the data on one screen and facilitates taking action on a group of rules with one click. The biggest advantage of the visualizer is that it recommends regular expressions to consolidate multiple rules. You can select a subset of these rules, based on the delimiter and Action URL. You can display 25, 50, or 75 rules in the visualizer, by selecting the number from a drop-down list. The visualizer for learned rules offers the option to edit the rules and deploy them as relaxations. Or you can skip the rules to ignore them.
When the log action is enabled, the HTML SQL Injection security check violations are logged in the audit log as APPFW_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 SQL Injection violations:
# tail -f /var/log/ns.log | grep APPFW_SQL
Example of a HTML SQL Injection log message when the request is transformed
Jun 26 21:08:41 <local0.info> 10.217.31.98 CEF:0|Citrix|NetScaler|NS11.0|APPFW|APPFW_SQL|6|src=10.217.253.62 geolocation=Unknown spt=54001 method=GET request=http://aaron.stratum8.net/FFC/login.php?login_name\=%27+or&passwd\=and+%3B&drinking_pref\=on&text_area\=select+*+from+%5C+%3B&loginButton\=ClickToLogin&as_sfid\=AAAAAAXjnGN5gLH-hvhTOpIySEIqES7BjFRs5Mq0fwPp-3ZHDi5yWlRWByj0cVbMyy-Ens2vaaiULKOcUri4OD4kbXWwSY5s7I3QkDsrvIgCYMC9BMvBwY2wbNcSqCwk52lfE0k%3D&as_fid\=feeec8758b41740eedeeb6b35b85dfd3d5def30c msg= Special characters seen in fields cn1=74 cn2=762 cs1=pr_ffc cs2=PPE1 cs3=9ztIlf9p1H7p6Xtzn6NMygTv/QM0002 cs4=ALERT cs5=2015 act=transformed
Example of a HTML SQL Injection log message when the post request is blocked
Jun 26 21:30:34 <local0.info> 10.217.31.98 CEF:0|Citrix|NetScaler|NS11.0|APPFW|APPFW_SQL|6|src=10.217.253.62 geolocation=Unknown spt=9459 method=POST request=http://aaron.stratum8.net/FFC/login_post.php msg=SQL Keyword check failed for field text_area\="(')" cn1=78 cn2=834 cs1=pr_ffc cs2=PPE1 cs3=eVJMMPtZ2XgylGrHjkx3rZLfBCI0002 cs4=ALERT cs5=2015 act=blocked
As part of the streaming changes in 10.5.e build (enhancement builds) as well as 11.0 build onwards, we now process the input data in blocks. RegEx pattern matching is now restricted to 4K for contiguous character string matching. With this change, the SQL violation log messages might include different information compared to the earlier builds. The keyword and special character in the input could be separated by a large number of bytes. We now keep track of the SQL keywords and special strings when processing the data, instead of buffering the entire input value. In addition to the field name, the log message now includes the SQL keyword, or the SQL special character, or both the SQL keyword and the SQL special character, as determined by the configured setting. The rest of the input is no longer included in the log message, as shown in the following example:
In 10.5, when the application firewall detects the SQL violation, the entire input string might be included in the log message, as shown below:
SQL Keyword check failed for field text=\"select a name from testbed1\;\\(\;\\)\".*\<blocked\>
In enhancement builds of 10.5.e that support request side streaming as well as 11.0 build onwards, we log only the field name, keyword and special character (if applicable) in the log message, as shown below:
SQL Keyword check failed for field text="select(;)" <blocked>
This change is applicable to requests that contain application/x-www-form-urlencoded, or multipart/form-data, or text/x-gwt-rpc content-types. Log messages generated during processing of JSON or XML payloads are not affected by this change.
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:
The HTML 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 HTML 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_SQL check box and click the Apply button, only log messages pertaining to the 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, Client IP etc. appear below the log message. You can select any of these options to highlight the corresponding information in the log message.
Click to Deploy functionality is available only in the configuration utility. You can use the Syslog Viewer to not only view the logs but also to deploy HTML SQL Injection relaxation rules based on the log messages for the application firewall security check violations. The log messages must be in CEF log format for this operation. Click to deploy functionality is available only for log messages that are generated by the block (or not block) action. You cannot deploy a relaxation rule for a log message about the transform operation.
To deploy a relaxation rule from the Syslog Viewer, select the log message. A check box appears in the upper right corner of the Syslog Viewer box of the selected row. Select the check box, and then select an option from the Action list to deploy the relaxation rule. Edit & Deploy, Deploy, and Deploy All are available as Action options.
The SQL Injection rules that are deployed by using the Click to Deploy option do not include the fine grain relaxation recommendations.
To use Click to Deploy functionality in the configuration utility
When the stats action is enabled, the counter for the 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, the request for a page that contains 3 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 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 HTML SQL Injection statistics by using the configuration utility
Note the following points about the SQL Injection check: