Prevent sql injection with dynamic sql Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 141 of secret server
tutorial in this video we'll discuss how
to prevent sequel injection when using
dynamic sequel in our previous video we
have implemented this search page using
dynamic sequel to build our dynamic
sequel statements we have used
parameters this is an example of good
dynamic sequel implementation this code
as it stands is immune to sequel
injection in this video let's look at an
example of bad dynamic sequel
implementations I've seen many software
developers not just the beginners but
even experienced software developers
building their dynamic sequel statements
by concatenating user input values
instead of using parameters without
realizing that they're opening doors for
sequel injection let's rewrite this code
to build our dynamic sequel statements
by concatenating user input values and
then see how it is prone to sequel
injection so let's make a copy of this
line and then comment these three lines
of code so at the moment the query we
have a select star from employees where
one equals one and then if the user has
provided a value within the first name
textbox we want to append you know this
condition here and first name equals
instead of using the parameter here
I'm going to concatenate the value the
user has provided in the first name
textbox directly to the string so let's
retrieve the value from the textbox and
append it to the string which is going
to you know include that and condition
for us so here we are building our
dynamic sequel statements by
concatenating the user input value and
remember first-name is an N where column
and we need to wrap that within single
quotes so the single quote begins here
we have the value from the textbox and
we need to end the single quote
and if the user has typed something into
the last name gender and salary columns
we need to add those as well so let's do
the same thing for those three columns
as well comment these three conditions
for last name gender and celery and
let's build those three conditions by
concatenating the use of input values
that the user has typed into those
respective text boxes one important
thing to note here is that the salary
column is an integer column so we don't
have to wrap this calorie column value
within single quotes so with these
changes let's run the project by
pressing ctrl f5 and see how it is prone
to sequel injection
let's also run a new sequence of trace I
have the sequence of profiler running
already let's start a new trace and
let's provide a value for first-name we
have the marks record as expected now
let's look at the query that's captured
in sequence of a profiler look at the
query here select star from employees
where 1 equals 1 and first-name equals
whatever value we typed in the first
name textbox so here we are building our
dynamic sequel statement by
concatenating the values that the user
has entered in the user input controls
with the string now this is dangerous
it's very easy to inject sequel using
this webpage now imagine what's going to
happen if I type something like this if
I type a single quote what is this going
to do this is going to close this single
quote that we have opened so at that
point the query is going to be select
star from employees where one equals one
and first name equals an empty string
and then after that technically I have
any command that we want for example we
can even have something like this drop
database sales dB notice here we have
caught a database called sales DB and
I'm trying to drop that by injecting
this equal here drop database sales dB
now if we execute this as it is now we
are going to get an error because we
have a nun closed single quote here but
we can very easily comment that now look
at this when I click search button at
this point we get an error unclosed
quotation mark after the character
string now what we can do is include the
common characters - - so this is going
to comment that unclosed quotation mark
so now when we execute this look at that
the command completed and if you look at
what we have captured in sequence of a
profiler look at the command here select
star from employees where one equals one
and first name equals an empty string
and then we have this command drop
database sales DB and we have that
single quote commented and if you look
at the object Explorer here csdb is
still present but when we refresh the
database folder notice sales DB is gone
it's no longer there the reason we are
able to inject sequel here is because of
the way we have implemented this dynamic
sequel we should never ever concatenate
user input values to build our dynamic
sequel statements this is an example of
bad dynamic sequel implementations on
the other hand if we use parameters to
build our dynamic sequel statements we
will not have this sequel injection
problem let's actually prove that let's
comment this line and uncomment these
three lines right here similarly let's
comment these three F blocks where we
are concatenated user input values to
build our dynamic sequel statements and
let's uncomment these three F blocks
where we are using parameters to build
our dynamic sequel statements let's run
the application one more time by
pressing ctrl f5 in the first name text
box let's type mark and click search
notice we get marks record as expected
now let's see what's captured in sequel
profiler look at the command right here
select star from employees where
one equals one and first name equals
that first name and a value for this
parameter is whatever value that we have
typed in the first name textbox so
anything that you type into this text
box is now treated as a value for at
first name parameter so this makes our
code immune to sequel injection let's
actually prove that first let's go back
to object Explorer and let's create a
new database let's call our database
sales DB click OK so we have the sales
DB database created here now let's go
back and inject the same sequel that we
have injected earlier
let's click search now first of all
notice we don't have any search results
displayed and loots look at sequel
server profiler look at the command
right here select star from employees
where 1 equals 1 and first-name equals
at first-name and now the value for this
at first name parameter is whatever we
have typed in the text box so this
entire string is the value for first
name parameter and since we don't have
any employee whose first name is thirst
the page displays no results but the
important thing to keep in mind here is
that this man drop database sales DB is
not executed and as a result we didn't
lose our sales DB database so if i
refresh the databases folder notice we
still have the sales DB database we also
don't have this problem of sequel
injection if we are using store
procedures if you recollect in one of
our previous videos we have implemented
this page search page without dynamic
sequel and this page is making use of
this stored procedure SP search
employees so let's navigate to that page
the name of the page is search page
without dynamic sequel and let's inject
the same sequel let's click the search
button notice we don't get any results
and let's look at the profiler look at
the command right here execute SP search
employees it has got a parameter at
first name and the value for that
parameter
is whatever we have typed in the first
name text-box and if you look at object
explorer they still have our sales DB so
when we refresh this notice the sales TV
is still there but one important thing
to keep in mind is that in your store
procedure if you are creating dynamic
sequel statements by concatenating user
input values then your store procedure
is still prone to sequel injection if
this is not clear at the moment don't
worry we'll discuss an example of this
in our next video finally let's quickly
recap what we have discussed so far
dynamic sequel provides great
flexibility when implementing
complicated logic with lot of
permutations and combinations always use
parameters to build your dynamic sequel
statements which prevents sequel
injection never ever rely on concatenate
in user input values to build your
dynamic sequel statements it opened
doors for sequel injection strop
procedures are also prone to sequel
injection if you're building dynamic
sequel statements inside store
procedures by concatenating user input
values instead of using parameters we'll
discuss an example of this in our next
video using parameters to build dynamic
sequel statements not only prevents
sequel injection but also increases
performance by reusing the cached query
plans we'll discuss an example of this
in a later video and here we have the
area dotnet code where we have built our
dynamic sequel statements by
concatenating user input values that
open the doors for sequel injection
thank you for listening and have a great
day