SQL Server tutorial for beginners - Quotename function in SQL Server

Text version of the video http://csharp-video-tutorials.blogspot.com/2017/04/quotename-function-in-sql-server.html 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. https://www.youtube.com/channel/UC7sEwIXM_YfAMyonQCrGfWA/?sub_confirmation=1 Slides http://csharp-video-tutorials.blogspot.com/2017/04/quotename-function-in-sql-server_26.html All ASP .NET Text Articles http://csharp-video-tutorials.blogspot.com/p/free-aspnet-video-tutorial.html All ASP .NET Slides http://csharp-video-tutorials.blogspot.com/p/aspnet-slides.html All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists 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 http://csharp-video-tutorials.blogspot.com/2017/04/quotename-function-in-sql-server.html 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',']')