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!