Filestream Support - SQL Server 2008

In the world of databases, the growth of data is increasing year after year. These new data items are not the traditional integers and text that we find in spreadsheets and reports; rather, the data is made up of digitally born medical images, music and video data files, and the like. With all these different kinds of data, it is becoming increasingly difficult to perform useful tasks, such as metadata searches or basic database management. SQL Server 2008 has a solution to this problem.

From a developer standpoint, dealing with heterogeneous data has always been a challenge. In SQL Server, we have two basic options to address the issue.

One option is for the developer to store the files in the file system and maintain a pointer to the file within the database. This solution works in cases where the file could be larger than 2GB and where fast streaming performance of the file is required. For those fairly new to databases, reading directly from the file system offers better streaming performance than reading the same data through the database. This is due to the fact that the database engine must manage locks on the table, even if the user is just reading the data.

Before you go off and design your new applications following this approach, it is important to note the downside of trying to maintain consistency between the files on the file system and the metadata about those files stored in the database. First, any changes that are made to the files directly through the file system are not detected by the database and will need to be addressed in your database application. Second, the administrators will have the heavy burden of trying to determine the appropriate security to apply to the file given the context of the data user. These are just some of the issues in a tedious, development-intensive solution that up until SQL Server 2005 was really the only answer to the problem.

SQL Server 2005 introduced the VARBINARY(MAX) datatype, which allows any arbitrary large object up to 2GB in size to be stored directly in the database. This mitigated a lot of user scenarios that required the use of the file system and gave developers a less error-prone database storage option for their large objects. Still, even with being able to store these large objects within the database, developers gave up on the streaming performance of the file system and faced a hard limit of 2GB for the storage of the data.

If it were a perfect world, wouldn’t it be great if we could leverage the streaming performance of the file system in addition to the transactional consistencies of the database in one big, happy solution? Well, although this world is far from perfect, the new FILESTREAM attribute to VARBINARY(MAX) may just be the one feature that causes you to see the world with a different perspective. With FILESTREAM, you can have it all and even cake for dessert, without gaining an ounce on the waistline. Actually, no one can make promises about your waistline—not even Weight Watchers—but with FILESTREAM, you will be able to at least store large objects in the file system and have these files integrated within the database.

Integration is the key to the value of FILESTREAM. With integration, when database backups are performed, the files are included in the backups. Since these files are stored in the file system, the size limit of storage within the database is the size of the volume on the disk. Thus, there is no longer a 2GB limit. Also, the security of these files is managed via SQL Server security features, so if database users have access to the table, they can access the contents of the file, without the Windows administrator needing to give them specific privileges. To top off the list of these benefits, developers can leverage the Win32 file APIs against the data stored within the database to essentially give them the performance of the file system within the database.

The only significant downside to FILESTREAM is the requirement that the drive volume that has the files must be in NTFS format.

FILESTREAM is best used when the objects that are being stored are larger than 1MB and when fast read access is important. Also, FILESTREAM will be beneficial when your database applications perform their application logic within the middle tier. For smaller objects (less than 1MB), storing VARBINARY(MAX) large objects within the database may give you better streaming performance.

Enabling Filestream Functionality

Before filestream functionality can be used, it must be enabled using the sp_filestream_configure_system stored procedure. This stored procedure has two parameters: enable_level and share_name. The enable_level parameter supports one of four values, as defined in Table.

@enable_level Definitions

enable_level Definitions
To get started, enable filestream functionality for both T-SQL and file system access, as follows:
sp_filestream_configure @enable_level = 2

If you used a value of 3 instead, you would be able to provide a name for a fileshare located on the Windows server hosting your SQL Server instance. This share could then be used by remote clients who want to access the filestream data.

When you enable filestream functionality on the server, one of the things that happens is the creation of a fileshare. If you issue a NET SHARE statement in your command window, you can see the newly created fileshare, called MSSQLSERVER, created by SQL Server for use with filestreams. The result of this statement is as follows:

result of this statement

From a quick look at the resource that is being shared, you can see that the MSSQLSERVER share isn’t your traditional folder in the file system. What is happening here is SQL Server has a special driver used by the operating system that allows SQL Server to expose and manage a fileshare. Since SQL Server owns and manages this fileshare, it allows for applications to leverage the powerful streaming support of Win32 APIs while working in the context of a SQL Server transaction.

A Filestream Example

To help illustrate the power of SQL Server’s filestream support, let’s consider a scenario involving an online pet shop database that stores images in a database. In this example, you will create a database application called PetPhotoManager that will connect to a SQL Server instance and upload and view pictures stored in a table. First, you will create a database called OnlinePetStore, which contains a table called Pets that stores pictures of all the animals on the web site. Then you will write an application that will allow you to upload pictures to SQL Server, as well as read pictures and display them on aWinForm. These pictures will be stored as VARBINARY(MAX) and leverage the FILESTREAM attribute.

Creating the Database

Using FILESTREAM requires the designation of a special filegroup in the database. The filestream filegroup cannot be the primary filegroup, and you cannot specify the SIZE, MAXSIZE, or FILEGROWTHparameters of this filegroup, since it’s really just a file directory. The following database-creation code defines a filegroup called FileStreamPhotos, and the statement CONTAINS FILESTREAM follows the FILEGROUP definition. This extra statement designates this filegroup to be one that contains filestream data.

CREATE DATABASE OnlinePetStore
ON PRIMARY
(
NAME = OnlinePetStore_data
,FILENAME = 'C:dataFileStreamDB_data.mdf'
,SIZE = 10MB
,MAXSIZE = 50MB
,FILEGROWTH = 15%
),
FILEGROUP FileStreamPhotos CONTAINS FILESTREAM DEFAULT
(
NAME = PetPhotos
,FILENAME = 'C:dataphotos'
)
LOG ON
(
NAME = OnlinePetStore_log
,FILENAME = 'C:logFileStreamDB_log.ldf'
,SIZE = 5MB
,MAXSIZE = 25MB
,FILEGROWTH = 5MB
)

This database-creation script indicates that the folder used to store filestream data will be C:dataphotos. In order for the database engine to accept this, the C:data folder must exist, and the c:dataphoto folder must not exist prior to execution of this script.

After you create the database, you are ready to define the simple Pets table that will be used to store the pet photographs:

USE OnlinePetStore
GO
CREATE TABLE Pets(
pet_id INT NOT NULL,
FSRowGuidColumn UNIQUEIDENTIFIER
NOT NULL ROWGUIDCOL UNIQUE DEFAULT NEWID(),
pet_name VARCHAR(50) NOT NULL,
pet_photo VARBINARY(MAX) FILESTREAM)

Notice the first two bold statements. The first one is a column definition for a UNIQUEIDENTIFIER that has a designator for a GUID-based unique ID column called ROWGUIDCOL. FILESTREAM requires that you specify a ROWGUIDCOL; failure to do so means FILESTREAM won’t work.

The next bolded line demonstrates how to define a filestream column. The example has a column called pet_photo whose datatype is defined as VARBINARY(MAX) with the FILESTREAM attribute. Filestream
columns can be only of type VARBINARY(MAX) in SQL Server 2008.

With the database objects created, you can now create the application that will insert and select filestream data.

Creating the Application

Before diving into the code, there are a few disclaimers to make. This example is written to demonstrate how one goes about accessing and managing filestream data. To keep the code brief, we intentionally skipped necessary things, like optimizing the code by creating helper classes and implementing more elaborate error-handling routines. And to keep things interesting, the code is written with a security bug. After we present the code, we will talk about this bug, even though it’s not specific to SQL Server filestream data.

The PetPhotoManager application will be run from the command line and accept two options:

  • The /upload option will allow the user to type the name of the pet and the local file path to a picture of the pet.
  • The /view option will allow the user to view the picture of a pet whose photo is stored in the Pets table.

The core part of this application revolves around two functions: UploadPhoto() and ViewPhoto().
The following is the PetPhotoManager C# application.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Runtime.InteropServices;
using Microsoft.Win32.SafeHandles;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Drawing;
namespace PetPhotoManager
{
class Program
{
public static string strPet_Name = "";
public static string strPhoto_Location = "";
static void Main(string[] args)
{
if (args == null || args.Length == 0 || args.Length > 3)
{
Console.WriteLine("PetPhotoManager examples:nn");
Console.WriteLine("PetPhotoManager /upload [pet_name]
[photo_location]n");
Console.WriteLine("PetPhotoManager /view [pet_name]n");
return;
}
strPet_Name = args[1].ToString();
if (args[0] == "/upload")
{
strPhoto_Location = args[2].ToString();
UploadPhoto();
return;
}
else if (args[0] == "/view")
{
ViewPhoto();
}
}
private static void UploadPhoto()
{
try
{
//Open a connection to the OnlinePetStore database
string strCommand = "";
SqlConnection con = new SqlConnection
("server=.;database=OnlinePetStore;integrated security=true");
con.Open();
//We need to create a GUID that will be used
//as the unique identifier for the row
Guid NewGuid = Guid.NewGuid();
//Create an entry in the table for our new pet
strCommand = "INSERT INTO Pets VALUES(CAST('" + NewGuid +
"' AS UNIQUEIDENTIFIER),'" + strPet_Name + "',0)";
SqlCommand MySQLCommand = new SqlCommand(strCommand, con);
MySQLCommand.ExecuteNonQuery();
//Now query to get the filestream fileshare and transaction context
strCommand = "SELECT pet_photo.PathName()," +
"GET_FILESTREAM_TRANSACTION_CONTEXT() FROM Pets " +
"WHERE FSRowGuidColumn=CAST('" + NewGuid + "' AS UNIQUEIDENTIFIER)";
SqlTransaction SQLTrans = con.BeginTransaction();
MySQLCommand.CommandText = strCommand;
MySQLCommand.Transaction = SQLTrans;
SqlDataReader rdr = MySQLCommand.ExecuteReader();
if (rdr.Read())
{
//Variables for the filestream path, transaction context
//and length
string path = (string)rdr[0];
byte[] txnContext = (byte[])rdr[1];
int length = txnContext.Length;
//It is best practice to close the DataReader
//before calling OpenSqlFilestream
rdr.Close();
SafeFileHandle handle = OpenSqlFilestream(
(string)path,
1, // 1=Write access
0,
txnContext,
(UInt32)length,
new LARGE_INTEGER_SQL(0));
//Destination
FileStream writeBlob = new FileStream(handle,
FileAccess.Write);
//Source
FileStream readPhoto = new FileStream(strPhoto_Location,
FileMode.Open, FileAccess.Read);
//Define block size, 512K is what NTFS is.
const int blockSize = 1024 * 512;
// Stream bytes from a local file into a sql table
// via a Filestream!
byte[] buffer = new byte[blockSize];
int bytesRead;
while ((bytesRead =
readPhoto.Read(buffer, 0, buffer.Length)) > 0)
{
writeBlob.Write(buffer, 0, bytesRead);
}
writeBlob.Close();
readPhoto.Close();
SQLTrans.Commit();
Console.WriteLine("n" + strPhoto_Location +
" inserted into Pets table.n");
}
con.Close();
}
catch (Exception E)
{
Console.WriteLine("Error: " + E.Message.ToString());
}
return;
}
private static void ViewPhoto()
{
string strCommand = ""; try{
//Create connection to OnlinePetStore database
SqlConnection con = new SqlConnection
("server=.;database=OnlinePetStore;integrated security=true");
con.Open();
SqlTransaction SQLTrans = con.BeginTransaction();
//Get the network share path for the photo and transaction context
strCommand = "SELECT pet_photo.PathName()," +
"GET_FILESTREAM_TRANSACTION_CONTEXT() FROM Pets " +
"WHERE pet_name='" + strPet_Name + "'";
SqlCommand MySQLCommand =
new SqlCommand(strCommand, con, SQLTrans);
SqlDataReader rdr = MySQLCommand.ExecuteReader();
if (rdr.Read())
{
//Fill variables with information from the query
//This is the path name and the transaction context
string path = (string)rdr[0];
byte[] txnContext = (byte[])rdr[1];
int length = txnContext.Length;
//Its a best practice to close the Datareader
//before calling OpenSqlFilestream
rdr.Close();
SafeFileHandle handle = OpenSqlFilestream(
(string)path,
0, // 0=Read access
0,
txnContext,
(UInt32)length,
new LARGE_INTEGER_SQL(0));
//Create a FileStream object based on the handle from sql
FileStream readBlob = new FileStream(handle, FileAccess.Read);
//Create our Windows Form a Picture Box control
System.Windows.Forms.Form frmPicture = new Form();
frmPicture.Height = 480;
frmPicture.Width = 640;
frmPicture.Top = 100;
frmPicture.Left = 100;
frmPicture.Text = "Displaying " + strPet_Name;
PictureBox pb = new PictureBox();
//Create the image from our sql filestream
pb.Image = Image.FromStream(readBlob);
pb.Size = frmPicture.Size;
pb.SizeMode = PictureBoxSizeMode.StretchImage;
frmPicture.Controls.Add(pb);
frmPicture.ShowDialog();
readBlob.Close();
}
else
{
Console.WriteLine("No record for " + strPet_Name +
" in the Pets table.n");
}
con.Close();
}
catch (Exception E)
{
Console.WriteLine("Error: " + E.Message.ToString());
return;
}
return;
}
[DllImport("sqlncli10.dll",
SetLastError = true, CharSet = CharSet.Unicode)]
public static extern SafeFileHandle OpenSqlFilestream(
string FilestreamPath,
UInt32 DesiredAccess,
UInt32 OpenOptions,
byte[] FilestreamTransactionContext,
UInt32 FilestreamTransactionContextLength,
LARGE_INTEGER_SQL AllocationSize);
[StructLayout(LayoutKind.Sequential)]
public struct LARGE_INTEGER_SQL
{
public Int64 QuadPart;
public LARGE_INTEGER_SQL(Int64 quadPart) { QuadPart = quadPart; }
}
}
}

To use this application, upload a photo with a statement like this:

PetPhotoManager /upload Zeus C:picturesZeus.jpg

To retrieve the photo, issue a statement like this:

PetPhotoManager /view Zeus

You’ll see aWinForm window containing a picture of the pet, as in the example in Figure.
PetPhotoManager view pet window (showing the author’s St Bernard, Zeus, recently
shaved for the summer)

PetPhotoManager view pet window (showing the author’s St Bernard, Zeus, recently shaved for the summer)

Let’s start the analysis by looking at the UploadPhoto function. After making the connection to the database, you create a GUID and insert this GUID and a pet’s name into the Pets table.

Guid NewGuid = Guid.NewGuid();
strCommand = "INSERT INTO Pets VALUES(CAST('" + NewGuid +
"' AS UNIQUEIDENTIFIER),'" + strPet_Name + "',0)";

Having a GUID for each row is a requirement for SQL filestream data. Once you have this entry in the table, you want to query this entry and obtain two important pieces of information:

  • The path to the photo that is stored in the table. This path is based on the Uniform Naming Convention (UNC) name that you saw when you issued the NET SHARE statement earlier in this chapter.
  • The transaction context, which is essentially a token that represents the transaction of the active session.

The SELECT statement to obtain this information is as follows:

strCommand = "SELECT pet_photo.PathName()," +
"GET_FILESTREAM_TRANSACTION_CONTEXT() FROM Pets " +
"WHERE FSRowGuidColumn=CAST('" + NewGuid + "' AS UNIQUEIDENTIFIER)";

After you have the path and transaction context, you can use the OpenSqlFilestream() function:

SafeFileHandle handle = OpenSqlFilestream(
(string)path,
1, // 1=Write access
0,
txnContext,
(UInt32)length,
new LARGE_INTEGER_SQL(0));

The OpenSqlFilestream() function allows for both read and write access to the file. The handle that is returned can then be used directly in Win32 APIs. You can see the true power of SQL filestreams by the simple file copy that follows the OpenSqlFilestream statement:

FileStream writeBlob = new FileStream(handle, FileAccess.Write);
FileStream readPhoto = new FileStream(strPhoto_Location,
FileMode.Open, FileAccess.Read);
.
.
.
while ((bytesRead =
readPhoto.Read(buffer, 0, buffer.Length)) > 0)
{
writeBlob.Write(buffer, 0, bytesRead);
}

Here, you have two FileStream objects, named readPhoto and writeBlob, which are the source and destination, respectively. The source FileStream object is created with the handle obtained via the SQL filestream, and the destination FileStream object is created by passing the local filename directly into the API.

To view the photo, the process is similar to uploading the photo, in that you need to obtain the pathname and transaction context from the Pets table for the given pet name requested. The SELECT statement is as follows:

SELECT pet_photo.PathName(),GET_FILESTREAM_TRANSACTION_CONTEXT()
FROM Pets WHERE pet_name='" + strPet_Name + "'";

Once you have this information, you can pass it into an OpenSqlFilestream() statement and request read access, as follows:

SafeFileHandle handle = OpenSqlFilestream(
(string)path,
0, // 0=Read access
0,
txnContext,
(UInt32)length,
new LARGE_INTEGER_SQL(0));

Now with a read handle, you can create a FileStream object based on the handle obtained from SQL.

FileStream readBlob = new FileStream(handle, FileAccess.Read);

To display the actual photo, you simply created aWindows Form, included a Picture Box control, and bound the image using the .FromStream() function, as follows:

PictureBox pb = new PictureBox();
pb.Image=Image.FromStream(readBlob);

Although this example is fairly simple, you can see the power of being able to leverage the streaming performance of Win32 API calls against data stored in SQL Server.

Before we conclude this discussion, we need to address the security bug that is present in the sample code. By now, you should have at least browsed the entire code listing for the PetPhotoManager
application. If you haven’t done so, go back and see if you can spot a common security vulnerability.

One of the most common and easiest exploits to SQL Server is the SQL-injection attack. In the example, you take the user input stored in strPet_name and in the UploadPhoto() function, insert this pet name directly into the SQL statement, as follows:

strCommand = "INSERT INTO Pets VALUES(CAST('" +
NewGuid + "' AS UNIQUEIDENTIFIER),'" + strPet_Name + "',0)";

To exploit this, all you need to do is modify the parameters you are passing into the PetPhotoManager application.

Let’s have this harmless photo-viewer application create a SQL login and give it sysadmin access:

PetPhotoManager.exe /upload "hack',0);CREATE LOGIN Hacker WITH
PASSWORD='asdfasdf';EXEC master..sp_addsrvrolemember @loginame =
N'Hacker', @rolename = N'sysadmin';--" c:demoblah.jpg

When SQL Server executes the INSERT statement, to SQL it looks like the following:

INSERT INTO Pets VALUES
(CAST('0e87d864-f6ab-4e0e-b8c8-4b409d79f9e6' AS UNIQUEIDENTIFIER),
'hack',0);CREATE LOGIN Hacker WITH PASSWORD='asdfasdf';
EXEC master..sp_addsrvrolemember @loginame = N'Hacker',
@rolename = N'sysadmin';--',0)

The key to note here is that the call is wrapped up nicely by appending the rest of the name followed by the closing apostrophe and ,0), so the INSERT statement is happy. Now all you need to do is append whatever code you desire. In this case, it is to create a login called Hacker and grant sysadmin access to the new login. To wrap things up, you just need to put in two dashes (--) at the end, indicating whatever else is just a comment. In this case, the rest of the ',0) that the real application appended is now a comment.

There are a few lessons here. Never run under elevated privileges. If the application wasn’t running as a sysadmin, you wouldn’t have this particular problem. However, the exploit is still here.

To mitigate the SQL-injection attack, you could cleanse the data before allowing the variable to be used within the statement. This would include removing characters like the comment dashes and limiting the number of characters of the variable. A more secure solution would be to wrap the INSERT statement inside a stored procedure. Your code to call the stored procedure would then look something like this:

SqlCommand command = new SqlCommand("createPhotoEntry",connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@pet_Name",SqlDbType.Text);
command.Parameters[0].Value = strPet_Name;
command.Parameters.Add("@photo_Path",SqlDbType.Text);
command.Parameters[0].Value = strPhoto_Location;
SqlDataReader dataReader = command.ExecuteReader();

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

SQL Server 2008 Topics