Dynamic sql table name variable Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 145 of sequence over
tutorial in this video we'll discuss how
to pass table name dynamically for store
procedure in sequel server so here is
what we want to be able to do we want to
create a web page but a text box where
we can enter a table name once we enter
a table name and when we click the slow
data button we want to load data from
that respect to table and display it on
the web page as you can see here for the
purpose of this demo we are going to
make use of these two tables I've
already created these tables and here is
the sequel script to create and populate
them with test data I will have the
script available on my blog in case you
need it the next thing that we want to
do is create a stored procedure to which
we can pass this table name as a
parameter
so let's report a sequel server
management studio fire up a new query
editor window and in here let's create a
procedure let's name it SP dynamic table
name and to this we are going to pass
table name parameter and within the body
of this stored procedure let's first
declare a variable let's call it dead
SQL and this is going to be of type and
we're care of Max and we're going to use
this variable to hold our dynamic sequel
statement so here is our dynamic sequel
statement select star from whatever
table name that we are going to pass to
this store procedure and then finally
let's use system store procedure SD
underscore execute SQL to execute our
dynamic sequel statement now notice we
have a passing table name is a parameter
to the procedure and we are concatenated
strings to build a dynamic sequel
statements in our previous video we
discuss that this is bad because it
opened doors for single injection
attacks so the obvious question that
comes to our mind at this point is why
are we not creating parametrized sequel
statements instead the answer is we
can't sequel server does not allow us to
pass table names and column names as
parameters
if we try to do that we will get an
error let's look at that in action so
instead of concatenating strings like
this to build up a dynamic sequence
statement let's use a parameterised
secret statement so here we have a
parameter within our dynamic sequel
statement and the parameter type is n
we're care and we are setting that
parameter to a value that is coming into
the store procedure as a parameter now
look at this when I execute this the
command completed successfully but then
if we try to execute the store procedure
let's pass the table name as employees
and when we execute this look at that it
throws an error must declare the table
variable at table name so let's undo our
changes so the only way to pass a table
name as a parameter to a stored
procedure is like this by concatenated
strings but we know this open doors for
single injection will prove that in just
a bit
but for now let's go ahead and alter
this procedure so the procedure is
altered successfully and now if we pass
a table name for example like employees
and then when we execute this notice we
get that table data and similarly if we
pass countries we get data from that
respective table now let's quickly call
this from a web page this is the same
project that we worked with in our
previous videos to this project let's
add a new webform let's name it dynamic
table name dot aspx and on this page
let's paste some HTML this HTML gives us
a page that looks like what we have on
the slide right here so notice we've got
a text box that we can enter our table
name and then the load data button we
have a label here which is used to
display any errors that we get and then
finally agree to your control to display
the table data I've used bootstrap to
style this page if you're new to
bootstrap please check out bootstrap
video tutorial
now let's click on the button control to
generate that click event handler we
need a few idiot in spaces on this page
in the interest of time let's copy them
over from the other page that we have
implemented in our previous video and
I'm also going to copy the code that we
have in the button click event handler
let's paste this in the button click
event handler of this new page and then
we'll change the bits that are required
now if the user did not enter anything
within the table name text box and when
they click load data button we don't
want to do anything so first we want to
check if the user has typed anything at
all in this table name text box and if
you look at the ID of the table name
text box it is input table name so let's
go ahead and do that check right here so
within the button click event handler
the first thing that we are doing is
checking if that text box has got
anything within that if the user has
typed something into that that's when we
want to execute all this code
so we are reading the connection
strained and we are creating a new
sequel connection object and the name of
the procedure is sp dynamic table name
so let's specify that right here and
this stored procedure has got only one
parameter and the name of the parameter
is at table name
so let's specify that right here and the
value for that is coming from input
table name text box and then we don't
need to do this check so let's get rid
of that and we also don't need to have
all these parameters here so let's
delete all the three parameters and
let's format this code a bit so we are
then opening the connection executing
the reader and then setting the result
that they get as the data source for the
grid we control the ID of our grid view
control on this page is TV table data so
let's copy that and specify it right
here so with all these changes let's run
our application by pressing ctrl f5 in
the table name text box let's enter a
table name that does not exist within
our employee DB database and see what
happens
we don't have countries one table so at
this point when we click load data
button look at that the page blows up
with an exception let's handle this
exception and display it within a label
control so whatever code that we have
with no button click even handler let's
wrap all this within a try block if
there is any exception you want to catch
the exception and display the exception
message in the error label control the
ID of the error label control is LDL
error so let's set it text property to
the exception message now if we are able
to display the table data successfully
then we want to clear the error messages
that we have already displayed that in
the label control so let's set its text
property to
empty string so these changes let's run
our application one more time now if we
enter a table name that does not exist
notice the error messages displayed
within the label control invalid object
name country's one if we enter a table
that does exist within our database we
get data from that respective table
similarly if we enter countries as the
table name we get data from the
countries table now if you look at the
way we have been building our dynamic
sequel statement here you have built it
by concatenating strings and we know
this is dangerous because it opened
doors for sequel injection imagine
what's going to happen if somebody types
in this text box something like
countries semicolon and then a single
space and then this drop command drop
database sales dB what do you think is
going to happen this will be appended to
this string right here select star from
countries we have got a semicolon and
then this is treated as a separate
command drop database csdb
and within our sequel server we've got a
database with the name sales dB look at
what's going to happen when we click
this load data button so we still see
country.you data here but then if we go
back to sequel server and refresh this
databases folder look at what's going to
happen to the sales dB we don't have it
anymore so this application is
susceptible to sequel injection attack
now one way to prevent the sequel
injection attack is by using codename
function so in the procedure right here
instead of directly concatenated
whatever value that we have in this
table name variable I'm going to pass it
to this codename function and in a bit
we'll understand what this codename
function is going to do but for now
let's alter this procedure and create
sales DB database again
now with the same value typed in table
name text box let's click this load data
button look at this we get a message
saying invalid object name country's
semicolon drop database sales dB so you
can imagine the entire value that we
have typed in this table name text box
is being treated as a table name and
that's what this code name function is
doing here now if we refresh the
databases folder notice we still have
this hails DB database there so we are
not able to inject sequel and now let's
understand what this code name function
is doing so let's copy that and use it
with select statement and within the
table name text box we have this value
so let's pass that to this quote name
function and see what we get back so
when we execute this notice the value
that we have passed is wrapped in a pair
of square brackets which is what is
forcing it to be treated as a table name
so now drop database csdb is not treated
as a separate sequel command since that
is present along with the string here
within a pair of square brackets it is
treating that entire value as a table
name because we don't have a table with
that name we get this message invalid
object name you know and whatever string
we have specified so if you're building
dynamic sequel statements by
concatenating strings it's a good
practice to use code name function to
prevent sequel injection attacks and
here we have those two examples which we
discussed just now in our next video
where we'll discus quoting function in
detail with examples thank you for
listening and have a great day