Hi, welcome back. At the end of the last lesson, we covered the process
for exploiting a SQL injection flaw and how
to protect against them. At the end of
the previous lesson, we began to work on a patch
to that vulnerable code. In this lesson, we're going
to cover prepared statements. After this lesson, you'll be able to explain how to properly modify queries to get them into prepare statements.
Let's take a look. So let's go through
it together and see how we could create
prepared statements out of this. So the first part is the statement that
they're already using, we're going to borrow that. So we're going to call
it prepared statement, I'm just going to use
a word statement. We're going to ask our connection to get us
a prepared statement, we're going to use
the same query, we're going to have to
come back and modify this query because it's not in the right
format right now, but we'll just go
ahead and use it. For the magic constant
of result type set, I'm just going to keep
using what they're using. So I'm going to use
their type scroll in case-sensitive and
conquered read only. So essentially, this
is going to return a statement very similar
to the previous statement, but we do need to
modify this query. So we need it to speak the same language that the prepared
statements understand. So instead of account name and all these different quotes, we're just going to simply
put a question mark there. So we have our
prepared statements, and somehow we need to replace this question mark with
the input of account name, which is what the user submitted. So there's a right way to do this and a wrong way to do this. The wrong way to do
this, and I've seen it before, is users, and I'm going to say wrong way so that nobody mistakes this. The wrong way to do this
is to say query.replace, and replacing the question-mark
with account name. Unfortunately, I've seen
this happen in real life, and this will execute, but it is not the right answer. Since in a large organization
you end up having many developers coding
the same thing, what I like to do is
put in some stuff to prevent these use cases
and abuse cases. So let's make our query final, because honestly this
should never change. In which case Java are pre-compiled actually
give us an error there saying you're
overriding this. So as a developer
originally writing this, if you make this a final, you're telling
feature developers, hey don't mess with
this, don't change this. But so now we have to somehow substitute something
for this question mark and get that account
name in there. The way that's done is
using statement.setString. In this case,
set string because we know what we're
getting is a string, and we're going to
replace object at index one with account name. Now, if you're a programmer and you've done this
for a long time, you're used to indexes
starting at zero, but for some reason
the engineer's behind prepared statements decided
to make it start from one. So if there were
more than one question mark, the next one would
have been a 2, 3, 4, and so on, and so on. Now, I did set string, but you could actually do setInt if what you expect as an int, but in this case we
know it's a string, so we're going to limit
it to specifically that. So the second part of this
is we're going to want to do the same exact thing
for the next lesson. Now, I didn't show you
the exploit for the next lesson, but it's actually more trivial
than challenge number 7. So if we go to
challenge number 8, and I'll refresh the page. So if our statement looks
like this, previously, we had to deal with
unbalanced comments, quote amounts, but in this one, it's just appending some
variable at the end of a string. So I can just simply put one. So select all the information from user_data where
the user id is one, or 1 equals 1. So just true, and I can ask for free to give me that info
and it returns everything, and I don't even have to
really think it through just because it's not
a complex thing to get through. Going to the code, this use case is
actually pretty simple. There's easy ways of
going about this. For starters, we could do something like
integer.parseInt(user ID). Now, this line will
throw an error, and if we actually go to
the definition of parseInt, you'll notice that it throws
a number format exception. If the string does not
contain parsable integer. So at that point, we're limiting the user's inputs
to only integers here. Otherwise, if they did
not provide an Integer, this parseInt would
simply break out of this, and go this exception, and print out the exception, and say that it failed
to do the query. Now, for this use case, that would be fine however, you never know how your code is going to
change in the future. Since we already
discussed there's really no cost to the control of
using prepared statements, it would actually make it really trivial to convert this to a prepared statement just like we did with
the previous one, and get all the benefits, and not have to deal with this. If you really wanted to, you could leave
that statement in here, and catch the error
that it throws, and provide a useful error
back to the user saying, hey you gave me a string, but I'm expecting the
user ID to be an integer. I'm going to take
that out just to not make this overly complicated, and we could actually copy the same code from our lesson 5A. So I'm just going to grab this statement code and
bring it over to 5B, going to replace it. I will modify this to
have a question mark, get rid of the rest of that. So now we need to
set the user ID. So in this case, since we know we want our
user ID to be an integer, we know that our all of our user IDs as you can
see are integers here, we can simply set it to integers. Now, if your database
is set up to go beyond integers 2-to-the-32, you could use floats
or whatever you seem safe for your use case. But since we know it
doesn't go that larger, we're just going to use integers. It's as simple as
saying statement.setInt is parameter number 1, and Int is Integer.parseInt(user ID). So again, this parseInt
will throw an error, this code isn't
a giant try-catch block which will currently catch
SQL exceptions, and if it's not caught
by the SQL exception, it'll catch all other exceptions. We can actually add
our own catch error and write our own error if we want
to be useful for the user, but I think it's a sufficient. Let's look through
the code again. So our query has
a question mark for the parameter that we want the user to be able to control. We have a prepared statement
using that query, and we are setting the first
parameter to the user ID, and the rest is the same. So statement execute query,
and that works here. Let's just go back to 5A to make sure we didn't leave
that query in there. So you don't actually need
to pass in a parameter for prepared statements on the query
would have had an error, it would have essentially been pushing that through and
that doesn't work for us. So I think everything looks good. I'm going to restart this
and begin to recompile. If we go back to
here, go to Lesson 7, refresh the page, and I reset the lesson plans so
it doesn't say it's solved. So previously in here, we had tried Smith' in
the quote 1 equals 1, and then comment out the rest, and he'd get info. Obviously, we're not going
to get anything back because the query isn't
going to run properly. It's going to use
all of this as data, it's going to maintain
the structure of the query. The SQL driver knows that this is not a code
that the developer wrote, so it's not executing on it. Going to the next one, we can actually ask
for user ID one, and that does return for us, but if we do what
we did last time, which is say or 1
equals 1, we get an error, and the error is for
input string 101 or 1 equals 1. We actually go in the code here, you'll see that there's
a number format exception that was thrown, and that's where we went to parse that Int in our code right here. Now, I could have just done set string and not actually
have to parse this int, and that would have still
been perfectly safe. Although you may have
other SQL issues there, but you can try it out. All right. Let's
try it out again, 101 greater works it
returns something, or 1 equals 1. Invalid character value cast. So essentially, it's saying
the data that you tried to give me is not able to cast
to the data that I want. Both are perfectly fine, and I hope you enjoyed that. This is probably one of the easiest ways to
mitigate this flaw. Now, there are
some use cases that you have dynamically
built SQL queries, and I've had software developers tell me that it's
difficult to rewrite it to do this because
the ware clauses and the parameters for
the ware clauses are being created on the fly. So it's difficult to be able to mitigate this type of
attack through those. That's not the use case
where most people. So if you find yourself in a situation where using a prepared statement
is difficult, maybe take another look
at how your data is engineered and how you're
using your database, because there might be
a way to reconfigure your queries so that you can
fit it in this structure.