Configure an SQL injection prevention rule

Deep Security's 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.

Topics in this article:

What is an SQL injection attack?

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.

What are common characters and strings used in SQL injection attacks?

Here are some commonly used characters and strings. The list is not exhaustive.

  • ('
  • %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

How does the Generic SQL Injection Prevention rule work?

To detect SQL injection attacks, the Generic SQL Injection Prevention rule uses a scoring system. It works like this:

  1. Packets from your application arrive at the Deep Security Agent for analysis.
  2. The Generic SQL Injection Prevention rule looks at the packets and determines whether any of the strings shown in the table below are present. Notice that 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 clarification.
  4. Using the total score, Deep 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 table below might not match exactly the ones in Deep Security Manager.

The use of '\W' in the lines below means 'followed by a non-alphanumeric character'.

Group Score
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,database(,database%28,@@hostname,%40%40hostname,@@basedir,%40%40basedir,@@tmpdir,%40%40tmpdir,
@@datadir,%40%40datadir		 2

Examples of the rule and scoring system in action

Below are some examples of how the scores are tallied and what actions are undertaken in each scenario.

Example 1: Logged and dropped traffic

Let's 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

And this 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

Let's 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

And this 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 configurable options are shown in the image below.

To configure the rule:

  1. Log in to Deep Security Manager.
  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 haven't 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 guidelines below 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 than10 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 table below shows characters and their encoded equivalents, as well as character classes that you can use to denote extended patterns.

Enter this string... To denote...

\a

\A

alphabetic characters, a-z A-Z

non-alphabetic characters

example: delete\a means "the word 'delete' followed by alphabetical characters"

\w

\W

alphanumeric characters, a-z A-Z 0-9

non-alphanumeric characters

example: delete\W means "the word 'delete' followed by non-alphanumeric characters"

\d

\D

digits 0-9

non-digit characters

example: delete\d means "the word 'delete' followed by digits between zero and nine"

\s

\S

whitespace

not whitespace [\r,\n,\t,0x32]

example: delete\S means "the word 'delete' followed by non-whitespace"

\p

\P

punctuation character, printable ascii other than above

non-punctuation character

example: delete\p means "the word 'delete' followed by a punctuation character or printable ascii"

\c

\C

control character, below 32, or greater than or equal to 127, not including whitespace

non-control character

You can find details on control characters here.

\.

any

\xDD

hex byte 0xDD
\x2c

comma character (,)
\x22

double-quotes character (")

\\

escaped backslash (\)

\|

escaped pipe (|)

|xx xx xx...|

hex pipe (byte sequence)