Dynamic SQL in Stored Procedure Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 142 of sequel server
tutorial in this video well discuss
using dynamic sequel in the stored
procedure and its implications from
sequel injection perspective in a later
video we'll discuss the performance
implications of using dynamic sequel in
a stored procedure in one of our
previous videos we have implemented the
stored procedure SP search employees
notice this stored procedure does not
contain any dynamic sequel it's all
static sequel and it is immune to sequel
injection this stored procedure is
called by this page search page with our
dynamic C fill dot aspx and if you
remember in one of our previous videos
we try to inject the sequel into the
first name textbox and when we click
this search button whatever we have
typed in this textbox is treated as the
value for first name parameter so our
sequel injection attacks were not
successful now let's create a stored
procedure with dynamic sequel in it
instead of static sequel like this let's
make a copy of this procedure and let's
name this new procedure SP search
employs bad dynamic sequel it is still
going to have these four parameters
within the body of the procedure let's
declare a variable let's call it at SQL
the type is and we're care and let's set
the size to maximum we are going to use
this variable to build our dynamic
sequel statement so set X equal equals
select star from employees where 1
equals 1 so that's our initial query and
depending on whether these parameters
are known or not we are going to add the
conditions dynamically so let's check if
the first name parameter is not now if
it is not now then what do we want to do
we want to set an SQL equals whatever it
has already got plus we want to add of a
condition here and first name equals
whatever value we have got in this
parameter and since the first name
column isn't in the hair column the
who needs to be wrapped inside single
quotes so let's include a single quote
and then whatever value we have in this
first name parameter and then we need to
close the single quote we need to do the
same thing to build conditions for last
name gender and salary let's quickly do
that so here we have conditions for last
name gender and salary as well and then
finally we want to execute the dynamic
secret statement that we have in this
variable at SQL and to do that
we are going to use the system stored
procedure SP underscore execute SQL and
then to that let's pass our dynamic
sequel statement so let's create this
procedure SP search employs bad dynamics
equal now notice within the stored
procedure we are building or dynamic
sequel statement by concatenating
strings instead of using parameters and
this is dangerous because it opened
doors for sequel injection attacks let's
actually prove that let's flip to visual
studio now this is the same project that
we have been working with in our
previous videos to this project let's
add a new webform let's name it dynamic
sequel in store procedure dot aspx and
let's copy whatever HTML we have on the
other page and paste it on this new page
that we have just created so that should
give us the same look and feel let's
double click on the button to generate a
click event handler and let's also copy
the code behind code so let's first copy
the namespaces and then let's copy
whatever code we have in the button
click event handler
and paste it let another new page and
let's change the name of the procedure
the name of the procedure that we have
just created is st search employees bad
dynamic sequel so let's copy that and
paste it right here
so this stored procedure has got the
same parameters as the other stored
procedure so we don't have to change any
of this code so with all these changes
let's run our application by pressing
ctrl f5 here is the page before we do
anything let's quickly go back to sequel
server notice here we have a database
called sales dB
now let's try to index equal into this
first name textbox let's inject the same
sequel that we injected in our previous
videos drop database sales DB because we
have built our dynamic sequel statements
within this procedure by concatenating
strings this is prone to sequel
injection so when we click the search
button the command is executed and this
saves DB databases drop at the moment we
still see it but when we refresh the
databases folder notice sales DB
database is gone so this store procedure
is prone to sequel injection attacks now
let's rewrite this procedure using
parameters instead of concatenate in
strings like this so let's make a copy
of this procedure fire up a new query
editor vendôme and let's name our new
procedure st search employees good
dynamic sequel and within this procedure
we're going to use parameters instead of
concatenating strings so let's delete
that and set first-name equals @ FN so
parameter name here is FN similarly
let's set the parameter for last name s
at L N and for gender that set it to at
GN and finally for salary let's set it
to at SAR now when we call our SP
execute sequence essential procedure we
also have to specify the declarations
for these parameters FN Ln 10 and salary
so let's
that here so @fn is of type and well
care and let's set the size to 50 let's
do the same thing for last name
parameter as well so the last name
parameter is ethylene and this is also
of time and we're care and gender is
also of type and we're can wear as
salary is of type integer so let's
change the type here from and we're care
to integer and then finally we need to
supply values for these parameters as an
lndian and salary and where are the
values for those parameters going to
come from they're coming from these
store procedure parameters so at FN
equals access named similarly at Ln
equals ad last name and at GN equals at
gender and finally at cell equals add
celery so let's execute this to create
destroyed procedure stored procedure
created now let's copy the name of this
procedure and then call that procedure
from this page dynamics equal in stored
procedure let's run our application one
more last time by pressing ctrl f5 here
is the page now let's go back to sequel
server let's create a new database let's
call it sales dB
so we have our sales DB database created
now let's try to inject the same sequel
drop database sales DB and let's click
the search button completed now let's
refresh the databases folder notice we
still have the sales DB database so
whatever value we have time to this
first name textbox it is treated as the
value for this parameter at FN instead
of executing that as a sequel command so
we don't have our sales DB database
dropped so in this case our application
is immune to sequel injection attacks so
here is the summary whether you're
creating dynamic sequel in a client
application for example like an asp.net
web application or in a store procedure
always use parameters instead of
concatenated strings using parameters to
create dynamic sequel statements
prevents sequel injection and tags
here's that example of the stored
procedure where we are building our
dynamic sequel statements by
concatenating strings which is prone to
sequel injection attacks whereas in this
example we're building our dynamic
sequel statements using cran meters
which makes our application immune to
sequel injection attacks thank you for
listening and have a great day