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.