Using QUOTENAME() to Protect Against SQLInjection

QUOTENAME is function which has been available
since SQL Server 2005. This function returns a Unicode string with the
delimiters added to make the input string a valid SQL Server delimited
identifier, such as in the below example

The output for this query is as below:          

String

Default_QuoteName

QuoteName_WithQuote

Long_String

SQL Server

[SQL Server]

‘SQL Server’

NULL

How to use QUOTENAME to Protect Against SQLInjection

First let us look at sample of SQL
Injection.  Here is the sample script to demonstrate the SQLInjection.

After you execute this, you will find that
t1 table will be dropped!

Now to protect against this using QUOTENAME.

Using the QUOTENAME function has made a query
to get the result as single string rather than breaking with ;. So the drop table
statement will not be executed separately.

However, you need to remember that
QUOTENAME returns NULL if the string has more than 128 characters.  For that
you can use the REPLACE function to replace characters like ‘ and ; with space.

If you require more details about QUOTENAME please visit, http://msdn.microsoft.com/en-GB/library/ms176114(v=sql.120).aspx




Array

No comments yet... Be the first to leave a reply!