Quotename function in SQL Server Video Tutorial - SQL ~ NIIT POST

Quotename function in SQL Server

Suggested Videos
Part 143 - Sql server query plan cache
Part 144 - exec vs sp_executesql in sql server
Part 145 - Dynamic sql table name variable

In this video we will discuss Quotename function in SQL Server. This is continuation to Part 145. Please watch Part 145 from SQL tutorial before proceeding. 



This function is very useful when you want to quote object names. Let us understand the use of this function with an example.

We will use the following table for the examples in this demo




SQL Script to create and populate the table with test data

Create table [USA Customers]

(

     ID int primary key identity,

     FirstName nvarchar(50),

     LastName nvarchar(50),

     Gender nvarchar(50)

)

Go

Insert into [USA Customers] values ('Mark', 'Hastings', 'Male')

Insert into [USA Customers] values ('Steve', 'Pound', 'Male')

Insert into [USA Customers] values ('Ben', 'Hoskins', 'Male')

Insert into [USA Customers] values ('Philip', 'Hastings', 'Male')

Insert into [USA Customers] values ('Mary', 'Lambeth', 'Female')

Insert into [USA Customers] values ('Valarie', 'Vikings', 'Female')

Insert into [USA Customers] values ('John', 'Stanmore', 'Male')

Go

Let us say, we are using dynamic SQL to build our SELECT query as shown below

Declare @sql nvarchar(max)

Declare @tableName nvarchar(50)

Set @tableName = 'USA Customers'

Set @sql = 'Select * from ' + @tableName

Execute sp_executesql @sql

When we execute the above script, we get the following error
Msg 208, Level 16, State 1, Line 1
Invalid object name 'USA'.

The query that our dynamic sql generates and executes is as shown below. To see the generate SQL statement, use Print @sql.

Select * from USA Customers

Since there is a space in the table name, it has to be wrapped in brackes as shown below

Select * from [USA Customers]

One way to fix this is by including the brackets in @tableName variable as shown below

Set @tableName = '[USA Customers]'

The other way to fix this is by including the brackets in @sql variable as shown below

Set @sql = 'Select * from [' + @tableName +']'

While both of the above methods give the result we want, it is extremely dangerous because it open doors for sql injection.

If we set the brackets in @tableName variable, sql can be injected as shown below and SalesDB database is dropped

Declare @sql nvarchar(max)

Declare @tableName nvarchar(50)

Set @tableName = '[USA Customers] Drop Database SalesDB'

Set @sql = 'Select * from ' + @tableName

Execute sp_executesql @sql

If we set the brackets in @sql variable, sql can be injected as shown below and SalesDB database is dropped

Declare @sql nvarchar(max)

Declare @tableName nvarchar(50)

Set @tableName = 'USA Customers] Drop Database SalesDB --'

Set @sql = 'Select * from [' + @tableName +']'

Execute sp_executesql @sql

So, the right way to do this is by using QUOTENAME() function as shown below. 

Declare @sql nvarchar(max)

Declare @tableName nvarchar(50)

Set @tableName = 'USA Customers Drop Database SalesDB --'

Set @sql = 'Select * from ' + QUOTENAME(@tableName)

Execute sp_executesql @sql

When we execute the above script we get the following error. SalesDB database is not dropped. The reason we get this error is because we do not have a table with name - [USA Customers Drop Database SalesDB --]. To see the sql statement use PRINT @sql.
Invalid object name 'USA Customers Drop Database SalesDB --'.

If we set @tableName = 'USA Customers', the query executes successfully, without the threat of SQL injection.

Declare @sql nvarchar(max)

Declare @tableName nvarchar(50)

Set @tableName = 'USA Customers'

Set @sql = 'Select * from ' + QUOTENAME(@tableName)

Execute sp_executesql @sql

If you want to use sql server schema name "dbo" along with the table name, then you should not use QUOTENAME function as shown below.

Declare @sql nvarchar(max)

Declare @tableName nvarchar(50)

Set @tableName = 'dbo.USA Customers'

Set @sql = 'Select * from ' + QUOTENAME(@tableName)

Execute sp_executesql @sql

The above query produces the following error
Invalid object name 'dbo.USA Customers'

Instead use QUOTENAME function as shown below

Declare @sql nvarchar(max)

Declare @tableName nvarchar(50)

Set @tableName = 'USA Customers'

Set @sql = 'Select * from ' + QUOTENAME('dbo') + '.' + QUOTENAME(@tableName)

Execute sp_executesql @sql

QUOTENAME() function

• Takes two parameters - the first is a string, and the second is a delimiter that you want SQL server to use to wrap the string in.
• The delimiter can be a left or right bracket ( [] ), a single quotation mark ( ' ), or a double quotation mark ( " )
• The default for the second parameter is []

QUOTENAME() function examples

SELECT QUOTENAME('USA Customers','"') returns "USA Customers"

SELECT QUOTENAME('USA Customers','''') returns 'USA Customers'

All the following statements return [USA Customers]
SELECT QUOTENAME('USA Customers') 
SELECT QUOTENAME('USA Customers','[') 
SELECT QUOTENAME('USA Customers',']')

If you use a delimiter other than a single quotation mark, double quotation mark, left bracket or a right bracket, you get NULL. The following statement returns NULL.

SELECT QUOTENAME('USA Customers','*')

For some reason if you have a bracket in the table name, QUOTENAME function will double it to indicate an escape character.
SELECT QUOTENAME('USA ] Customers') returns [USA ]] Customers]

To remove the QUOTENAME use, PARSENAME() function as shown below. 

Declare @tableName nvarchar(50)

Set @tableName = 'USA ] Customers'

Set @tableName = QUOTENAME(@tableName)

Print @tableName

Set @tableName = PARSENAME(@tableName,1)

Print @tableName

Result:
[USA ]] Customers]
USA ] Customers

PARSENAME() takes 2 parameters. The first is the object name and the second is the object piece. It is an int and can be
1 = Object name
2 = Schema name
3 = Database name
4 = Server name

by :- kudvenkat