SQL Injection and Bulk Import in SQL Server

In this article we will learn about SQL Injection and Bulk Import in SQL Server. SQL injection is yet another common vulnerability that is the result of lax input validation. Unlike cross-site scripting vulnerabilities that are ultimately directed at your site visitors. Please read my previous article How to Add an Identity on Column in SQL SERVER.

SQL Injection and Bulk Import in SQL Server

SQL injection

SQL injection is yet another common vulnerability that is the result of lax input validation. Unlike cross-site scripting vulnerabilities that are ultimately directed at your site visitors, SQL injection is an attack on the site itself in particular its database. The goal of SQL injection is to insert arbitrary data, most often a database query, into a string that eventually executed by the database. The insidious query may attempt any number of actions, from retrieving alternate data, to modifying or removing information from the database.

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.

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.

Using OPENROWSET to bulk insert file data into a varbinary(max) column

The following example creates a small table for demonstration purposes, and inserts file data from a file named Testsample.txt located in the C: root directory into a varbinary(max) column.

USE TestSampleDB;

GO

CREATE TABLE myTable(FileName nvarchar(60),

  FileType nvarchar(60), Document varbinary(max));

GO



INSERT INTO myTable(FileName, FileType, Document)

   SELECT 'Testsample.txt' AS FileName,

      '.txt' AS FileType,

      * FROM OPENROWSET(BULK N'C:\Testsample.txt', SINGLE_BLOB) AS Document;

GO

When used with the BULK provider keyword you can name a data file to read as one of three types of objects:

1. SINGLE_BLOB, which reads a file as varbinary(max)
2. SINGLE_CLOB, which reads a file as varchar(max)
3. SINGLE_NCLOB, which reads a file as nvarchar(max)

OPENROWSET returns a single column, named BulkColumn, as its result. Here is an example that reads a text file:

SELECT BulkColumn
FROM OPENROWSET (BULK 'c:\temp\mytxtfile.txt', SINGLE_CLOB) MyFile 

The correlation name, in this case MyFile, is required by OPENROWSET. 

There are additional requirements when reading single files that must also be observed as mentioned below.

Access control is always a concern. The operating system level file operations to read the file are executed with the privileges of the account that the SQL Server data engine is using. Therefore, only files accessible to that account may be read. This includes network drives or UNC paths, which are permitted if the account has the privileges. If you want to read network files, run SQL Server as a domain user. 

The BULK provider won’t convert between Unicode and plain ASCII files. It must be told which type of encoding is used in the file. If you don’t the result is error 4806 as seen here: 

SELECT BulkColumn 
FROM OPENROWSET (BULK 'c:\temp\SampleUnicode.txt', SINGLE_CLOB) MyFile 

Unicode files must be read with the SINGLE_NCLOB option shown here:

SELECT BulkColumn 
FROM OPENROWSET (BULK 'c:\temp\SampleUnicode.txt', SINGLE_NCLOB) MyFile 

Similarly, files with non text structures, such as Word documents are not converted. They must be converted by some other mechanism before being read or they can be read as binary files with the SINGLE_BLOB option.

Bulk Importing from a Remote Data File

To use 
BULK INSERT or INSERT...SELECT * FROM OPENROWSET(BULK...) 

to bulk import data from another computer, the data file must be shared between the two computers. To specify a shared data file, use its universal naming convention (UNC) name, which takes the general form, \\Servername\Sharename\Path\Filename. Additionally, the account used to access the data file must have the permissions that are required for reading the file on the remote disk.

SELECT BulkColumn 
FROM OPENROWSET (BULK ‘\\Servername\Sharename\SampleUnicode.txt', SINGLE_NCLOB) MyFile

Conclusion

So far this article we discussed SQL Injection and Bulk Import in SQL Server.