fetch

Dynamic sql table name variable



Sharing buttons:

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