Configure an SQL injection prevention rule

The Intrusion Prevention module includes a built-in rule that detects SQL injection attacks and drops the connection or logs it depending on its characteristics. The rule is called 1000608 - Generic SQL Injection Prevention and can be configured to suit your organization's needs. For example, you can change the sensitivity of the rule by modifying the drop threshold.

About SQL injection attacks

An SQL injection attack, or SQL phishing attack, is a method of attacking data-driven applications wherein an attacker includes portions of SQL statements in an entry field. The newly-formed rogue SQL command is passed by the website to your database where it is executed. The command can result in the attacker being able to read, add, delete, or change information in the database.

Common characters and strings used in SQL injection attacks

The following is a non-exhaustive list of commonly used characters and strings:

• ('
• %27
• \x22
• %22
• char
• ;
• ascii
• %3B
• %2B
• --
• %2D%2D
• /*
• %2F%2A
• */
• %2A%2F
• substring
• drop table
• drop+table
• insert into
• insert+into
• version(
• values
• group by
• group+by
• create table
• create+table
• delete
• update
• bulk insert
• bulk+insert
• load_file
• shutdown
• union
• having
• select
• declare
• exec
• and
• or
• like
• @@hostname
• @@tmpdir
• is null
• is+null
• is not null
• is+not+null
• %3D
• CONCAT
• %40%40basedir
• version%28,user(
• user%28,system_user(
• (,%28,)
• %29
• @
• %40
• cast

About the Generic SQL Injection Prevention rule

To detect SQL injection attacks, the Generic SQL Injection Prevention rule uses a scoring system. It functions as follows:

1. Packets from your application arrive at the agent for analysis.
2. The Generic SQL Injection Prevention rule looks at the packets and determines whether any of the strings shown in the following table are present. The strings are separated by commas and divided into ten groups.
3. If strings are found, a score is calculated as follows:
   • If a single string is found, then the score associated with its group constitutes the total score.
   • If multiple strings are found in different groups, then the scores of those groups are added together.
   • If multiple strings are found in the same group, then the score of that group is counted only once.
     See Examples of the rule and scoring system in action for details.
4. Using the total score, Workload Security determines whether to drop the connection or log it. If the total score exceeds the Drop Threshold score, then the connection is dropped, and if it exceeds the Log Threshold score, then it is logged.

Trend Micro frequently updates its rules, so the strings in the following table might not match exactly the ones in Workload Security.

The use of '\W' in the table means Followed by a non-alphanumeric character.

Examples of the rule and scoring system in action

The following examples demonstrate how the scores are tallied.

Example 1: Logged and dropped traffic

Assume you are using this rule configuration where the score for the group comes after the colon (":"):

drop table,drop+table,insert into,insert+into,values\W,create table,create+table,delete\W,update\W,bulk insert,bulk+insert,shutdown\W,from\W:2
declare\W,select\W:2
cast\W,exec\W,load_file:2
union\W,group by,group+by,order by,order+by,having\W:2
and\W,or\W,like\W,is null,is+null,is not null,is+not+null,where\W:1
--,%2D%2D,/\*,%2F%2A,\*/,%2A%2F:1
',%27,\x22,%22,char\W:1
;,%3B:1
%2B,CONCAT\W:1
%3D:1
(,%28,),%29,@,%40:1
ascii,substring:1
version(,version%28,user(,user%28,system_user(,system_user%28,databas
(,database%28,@@hostname,%40%40hostname,@@basedir,%40%40basedir,@@tmpdir,%40%40tmpdir,@@datadir,
%40%40datadir:2

Log Threshold: 3
Drop Threshold: 4

The following attack string is encountered:

productID=BB10735166+UNION/**/+SELECT+FROM+user

Then the total score is 5 (2+1+0+2) because:

• The string UNION/ matches the fourth group for a score of 2.
• The string /* matches the sixth group for a score of 1.
• The string */ matches the sixth group for a score of 0 (because the score of the sixth group has already been counted).
• The string SELECT+ matches the second group for a score of 2.

With a total score of 5, a log is generated and the traffic is dropped.

Example 2: No logged or dropped traffic

Assume you are using this rule configuration where the select\W string has been moved to the same line as union\W:

drop table,drop+table,insert into,insert+into,values\W,create table,create+table,delete\W,update\W,bulk insert,bulk+insert,shutdown\W,from\W:2
declare\W:2
cast\W,exec\W,load_file:2
union\W,select\W,group by,group+by,order by,order+by,having\W:2
and\W,or\W,like\W,is null,is+null,is not null,is+not+null,where\W:1
--,%2D%2D,/\*,%2F%2A,\*/,%2A%2F:1
',%27,\x22,%22,char\W:1
;,%3B:1
%2B,CONCAT\W:1
%3D:1
(,%28,),%29,@,%40:1
ascii,substring:1
version(,version%28,user(,user%28,system_user(,system_user%28,databas
(,database%28,@@hostname,%40%40hostname,@@basedir,%40%40basedir,@@tmpdir,
%40%40tmpdir,@@datadir,%40%40datadir:2

Log Threshold: 3
Drop Threshold: 4

The following attack string is encountered:

productID=BB10735166+UNION/**/+SELECT+FROM+user

Then the total score is 3 (2+1+0+0) because:

• The string UNION/ matches the fourth group for a score of 2.
• The string /* matches the sixth group for a score of 1.
• The string */ matches the sixth group for a score of 0 (because the score of the sixth group has already been counted).
• The string SELECT+ matches the fourth group for a score of 0 (because the score of the fourth group has already been counted).

With a total score of 3, no log is generated and no traffic is dropped. The score must exceed the thresholds for them to take effect.

Configure the Generic SQL Injection Prevention rule

You can configure the Generic SQL Injection Prevention rule to suit your organization's needs. The following illustration shows configurable options:

To configure the rule:

1. Log in to the Workload Security console.
2. At the top, click Policies.
3. In the search box on the right, enter 1000608, which is the Generic SQL Injection Prevention rule's numeric identifier. Press Enter. The rule appears in the main pane.
4. Double-click the rule.
5. Click the Configuration tab. You see the SQL injection pattern in the text box at the top.
6. Update the SQL injection pattern with the latest version, if you have not customized it yet. To update to the latest pattern, go to the Details tab, copy the text under the Default SQL Pattern heading and paste it into the SQL Injection Patterns text box on the Configuration tab. You are now working with the most up-to-date pattern from Trend Micro.

7. Edit the fields as follows:

   • SQL Injection Patterns: This is where you to specify the list of characters and strings used in SQL injection attacks. Characters and strings are grouped and assigned a score. If you want to add or change the strings, make sure to use the proper encoding. See Character encoding rules for details.
   • Drop Threshold: This is where you specify the drop score. The connection is dropped when the score exceeds this threshold. If the score equals the drop threshold, the connection is maintained. The default is 4.
   • Log Threshold: This is where you specify the log score. The connection is logged when the score exceeds this threshold. If the score equals the log threshold, nothing is logged. The default is 4.
   • Max distance between matches: This is where you specify the number of bytes that can pass without a match to reset the score to 0. The default is 35.

   Consider using the next two options to create overrides for pages and fields that might cause the normal thresholds to be exceeded:

   • Pages (resource) with a non-default score to drop on: This is where you can override the Drop Threshold for specific resources. For example, if your Drop Threshold is 4, but you want a drop score of 8 for a questionnaire page, specify /example/questionnaire.html:8. With this configuration, /example/questionnaire.html needs to have a score higher than 8 in order for the connection to be dropped, while all other resources only need a score higher than 4. Specify each resource on a separate line.
   • Form parameters with a non-default score to drop on: This is where you can override the thresholds defined in Drop Threshold or the Pages (resources)with a non-default score to drop on fields for specific form fields. For example, if your Drop Threshold score is 4, but you want a higher drop score of 10 for a username field, specify /example/login.html:username=10, where /example/login.html is replaced with the path and name of the page where the username field appears, and username is replaced with the username field used by your application. With this configuration, the username field needs to have a score higher than 10 for the connection to be dropped, while the page itself only needs a score higher than 4. Specify each form field on a separate line.

   The Log Threshold does not take effect when connections are dropped due to a match on the Pages (resources) with a non-default score to drop on or Form parameters with a non-default score to drop on fields. For example, if you set the form parameter field to /example/login.html:username=10, and the username field scores 11, the connection is dropped but there is no log of this event.

8. Click OK.

You have now configured the Generic SQL Injection Prevention rule.

Character encoding guidelines

If you want to change or add strings to the Generic SQL Injection Prevention rule, you must encode them properly. For example, if you want to use the quote character ' in your pattern, you must enter \x22.

The following table shows characters and their encoded equivalents, as well as character classes that you can use to denote extended patterns.