Quotename function in SQL Server Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 146 of sequence of
auditoria in this video we'll discuss
code lien function in sequel server this
function is very useful if you want to
code object names let's understand the
use of this function with a few examples
for the purpose of this demo we'll be
making use of this table us a customers
and here is the secret script to create
and populate it the test data
I'll have the script available on my
blog in case you need it now notice
there is a space in the name of the
table and when there is a space like
this if we don't wrap the name of the
table within a pair of square brackets
and when we try to execute this look at
that we get an error invalid object name
USA on the other hand in this query we
have the name wrapped in a pair of
square bracket so when we execute this
we get the result that we expect so this
is true even in case of dynamic sequel
if you have got a space in the name of
the table then it has to be wrapped in a
pair of square brackets otherwise even
in the case of dynamic sequel we get the
same error look at this example notice
within the table name being able we have
the name of the table without a pair of
square brackets now when we execute this
we get the same error 'invalid object
name USA one way to fix this is by
including the pair of square brackets
within table name variable like this so
at this point when we execute this
dynamic sequel we get the result that we
expect while this is working it opened
doors for sequel injection because we
are building our dynamic sequel
statement by concatenating strings
imagine what's going to happen if
somebody injects sequel into this table
name variable so let's say we have this
command drop database sales DB and
within our databases folder we have a
database called sales dB so at this
point when we execute this dynamic
sequel we get the result from USA
customers table but then when we refresh
this databases folder notice sales DB
database is gone so we are able to
inject sequel in this case another way
to do this is by including this pair of
square bracket
within this variable at SQL so instead
of having the square brackets here we
can include the square brackets right
here so select star from the open the
square bracket and whatever value we
have in the table name variable and then
pick close the square bracket now again
this open doors for single injection
because imagine if somebody is going to
close the square brackets like this so
us a customers and then we have the term
and drop database sales DB and then we
need to comment this closing square
bracket in order to avoid any syntax
errors so let's include these two
characters and let's create the sales DB
database again and at this point when we
execute this dynamic sequel we get the
data from USA customers when we refresh
the databases folder notice sales DB
database is gone again even in this case
we are able to inject sequel so the
right way to fix this is by using code
name function so we don't have to
include a pair of square brackets like
that and instead we are going to use
code name function now with this code
name function even if somebody tries to
inject sequel like this select star from
USA customers and then we have this
command drop database sales dB
so when I execute this notice we get an
average says invalid object name USA
customers drop their by sales DB so it's
basically treating this entire value
that is present with this variable at
table name as a table name and that's
the reason why we get that error if you
want to see the query that is generated
let's use the print statement and when
we execute this look at the query that
is generated select star from and within
the pair of square brackets whatever
value we have specified for this
variable all that is included so it's
treating all that as a table name and
since we don't have a table with that
name we get that error invalid object
name on the other hand if we include
you know the correct table name that we
have and when we execute this dynamic
sequel notice we get the data that we
expect if you want to use sequel server
schema name along with the table name
you know the default schema is tbo so if
I want to use that schema name like that
and when we execute this notice we are
getting an error invalid object name dbo
dot USA customers and if you look at the
cipa statement that is generated select
star from dbo dot USA customers now this
has got a syntax error it has to be like
this dbl and then dot USA customers now
when we execute this we get the result
that we expect so we have to build our
query like this and to do that instead
of including the schema name in this
variable select star from we use the
codename function here and let's pass
tbo to this function which is going to
wrap it in a pair of square brackets and
that we want to append a dot and then to
that we want to append the name of the
table so when we execute this dynamic
sequel notice we get the result that we
expect this cotinine function takes two
parameters the first parameter is a
string and the second parameter is a
delimiter we want the string to be
wrapped in the delimiter can be single
quote double quote or a square bracket
the default is a square bracket now so
far in this video we have only used the
first parameter and since the second
parameter the default for that is a
square bracket whatever string we pass
to the code name function by default
it's wrapped in a pair of square
brackets now for some reason let's say
you want that string to be wrapped in a
pair of single quotes then we can
explicitly specify the second parameter
the delimiter so in this
case the delimiter is a single code and
when we execute this notice the string
USA customers is wrapped and pair of
single quotes similarly if you want that
to be wrapped in in a pair of double
quotes then specify the delimiter as a
double quote now notice it's wrapped in
a pair of double quotes now if we don't
specify the delimiter at all since the
default is a square bracket the string
is wrapped in a pair of square brackets
you can even explicitly specify the
delimiter you can either use you know
the square bracket or this square
bracket or you can leave it all together
because the default is a square bracket
it will be wrapped in a pair of square
brackets now you can only specify the
delimiter as a single quote double quote
or left or right square bracket if you
specify any other delimiter apart from
these characters we get null back so in
this case we are specifying star as the
delimiter when I execute that look at
that we get now now let's say for some
reason we've got you know a square
bracket within the name of our table for
example and then I execute this look at
that the square bracket is doubled
basically to indicate that as an escape
character to remove code name that is to
undo whatever code name function has
done we can make use of parse name
function let's look at an example so
here we have declared a variable at
table name and within that we are
storing this is the table name USA
customers with a square bracket now
let's set add table name equals code
name and to this code name function
let's pass table name variable and then
let's print what we have in our table
name variable so when we execute this
notice the table name USA customers is
wrapped in a pair of square brackets
and one of these square brackets which
we have as a part of cable name is
double to indicate that it is the name
of the table and not any special
character now if you want to undo this
under whatever this code name function
has done we can make use of parts name
function so set add table name equals
let's use the parse name function and
this lets pass whatever we have in our
table name variable and again this
function takes two parameters the first
parameter is the object name and the
second parameter is the object part and
it's an integer it can be one two three
or four one if it's an object name - if
it is the schema name three if it is the
database name and for f it is the server
name in our case it's an object name so
I'm going to specify the object part as
one and then let's print the table name
so let's execute all these sequel
statements together look at that this is
what code name has done and past name
has done the opposite it has removed old
name it has basically undone what code
name function has done so code name
function takes two parameters the first
is a string and the second is a
delimiter that you want sequel server to
use to wrap the string in the delimiter
can be a left or a right bracket a
single quotation mark or a double
quotation mark the default for the
second parameter is a square bracket and
here is an example thank you for
listening and have a great day