Quotename function in SQL Server

Text version of the video

Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.

Slides

All ASP .NET Text Articles

All ASP .NET Slides

All Dot Net and SQL Server Tutorials in English

All Dot Net and SQL Server Tutorials in Arabic

In this video we will discuss Quotename function in SQL Server.

This function is very useful when you want to quote object names.

Please use the SQL Script to create the table used in the example from my blog using the link below

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 --'
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'
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'
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'
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',']')

[raqibul]

The world is still beautiful because of some people like you.

[omostan]

I have watched several tutorials from you and each time, your explanations are simply exceptional. You are in my opinion one of the very best teachers around. Thank you very much for your contribution to the software development community.

[chetangadhavi]

It is a great video tutorial to learn sql technology. I find ur tutorials excellent.
Thanks for giving us so much of ur valueable knowledge.

[edmilsonbernardo]

Wauuuu you are awesome venkat, Heii tell me something which host service u use for your asp.net web application

[ramprashathmba]

Thank you for adding additional videos in SQL . Please add yet more..

[JavaReactExpress]

hlo sir follow u r all videos or required topics it's aswsom, fantastic, Fabulous ...

[chetangadhavi]

Sir can you please make a video on multi value parameters. like passing 1, 2, 3 to parameter and searching id in table with these ids.

[saurabhchauhan]

Sir what will be the next ? Can you ask for the choice or votes ?

[KochharAmandeep]

Sir i want to have info which version of sql and visual studio you're using. Reply would be useful to me. Thanks in Advance

[santoshgiri]

sir how to add payment concept in asp.net with C# for submitting fee in school online

[godfreydsouza]

Hello, so I noticed an odd behaviour, if I run SELECT QUOTENAME('USA Customers', '[' ) or SELECT QUOTENAME('USA Customers', ']' ), the result is [USA Customers] whereas it should have been [USA Customers[ and ]USA Customers] respectively, but the results is different so that means whenever we specify [ or ], QUOTENAME reverts to its default behaviour of enclosing it in [ and ].

[virajvora]

sir, I have got one problem during practicing. I created this Procedure for practicing purpose.
when I Executing with parameter '' Drop Database SalesDetailDB--' but
its still Dropping Database. how i can prevent from droping.

Create Proc Sp_Check_Quotename
@tbname nvarchar(200)
as
begin
Print @D1
EXECUTE sp_executesql @D1
End

Exec Sp_Check_Quotename '' Drop Database SalesDetailDB--'