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
]]>