[MUSIC] Hey, welcome back. In our previous listen,
we started working with basic SQL stuff. We left off with me challenging
you to try exploiting a SQL injection flaw as a practice activity. Look, you could just jump ahead and watch
the solution without actually going and doing it. But if you do that, I think you're going
to be missing out on a prime opportunity. In my experience, some of the best
penetration testers, hackers and breakers I've seen have gotten to where they are by
trying and failing over and over again. So if you find it challenging,
that's okay, that's part of the process. Just go back and give it a try. So if you haven't tried it yet,
I suggest doing that. After this lesson,
you'll be able to compare and contrast my approach with yours if it
worked, on how to break something. You'll be able to describe the process of
exploiting a SQL injection vulnerability. And you'll be able to explain how
to defend against such an attack. Let's get to it. So let's go through and see how we can
go about exploiting this together. So they tell us that we can
look up the name Smith. I'll go ahead and look it up and
we get the record we got back before. And all we have to do is
create a true statement to the right side of this where clause. And we get back all the records. At least that's what we think,
based on our experience with SQL zoo. So I'm going to end the developers quote. So this is their quote that I'm ending. And I'm going to put an or 1=1. And then I'm going to comment out the rest
of the stuff out that I'm not using from them And there we have it,
we're getting back everything. So if we look at the source code for this. Lesson 5a and 5b under the sql-injection
lessons is where both of these are stored. And we can see the SQL
query that they have. They're selecting star from user data,
where last name equals account name. And of course they gave
us that exact thing. Maybe they're using username in here where
with the variables really account name but that doesn't matter. The idea is the user has full
control over what goes in this item. So how do you go about
defending the SQL injections? So when we were talking about
our cross-side scripting, we talked about encoding it for
the proper context. From Sandra's course,
we talked about input validation and how important those are. And there's a lot of things that
we could do to prevent this. And I'm going to kind of walk through
a thought process with you guys. And hopefully we come to a consensus
that there's a couple ways to do this, some better than other. And depending on your use cases,
you might have constraints, and you may not be able to do certain things. As a result, we're going to want to talk
about all the defenses that we can employ. So in this use case,
I don't know if this is a true statement, but I don't know anybody that has an equal
sign, for instance, in their name. So, if that was a true statement for
our demographic of users, we could do some input validation. And we could do something like if
accountname.contains an equal sign, Throw illegal argument exception or
some exception. Actually, does this throw any exceptions? Let's see, yeah, right, cool. We can throw some exception. Now, legal argument exceptions is really
not the right exception to throw here, but this this works for now, okay? But the problem becomes,
maybe they don't always use an equal sign. There could be some clever way to get
around that, that we don't know yet. I'm not a SQL expert by any means. So what if there's some other something
else that they could use to get a equality statement to come out true. Especially depending on the shape of
our data in our user data store here, there might be things that
they can use to do that. So that might not be great. And then we can do a little bit more. Maybe we can say, an account name dot length is less than or equal 50 characters. I don't know.
Maybe that's a true statement, maybe all of our users have less than
50 characters in their account name. I'm not sure if that's true. You can modify that to then
lessen the impact of this. We can go down the rabbit
hole doing all of this. And that makes it very
difficult to maintain this. There could be edge cases that we miss. So this is not a good way to go about it. Another way we could go about it is, Maybe
using an API that was built in for this. Maybe we could, kind of how we use in
our cross site scripting approach. A way of encoding the information. Essentially telling our browser to treat
certain type of data as just data and treat some other data as actually code
in interpret it and execute on it. Maybe we could do something
along those lines for our database drivers to understand
the difference between user-provided data. And actual code that we wrote so
it doesn't intermix them. Well, lucky for us we can look up
the SQL cheat sheet from OWASP. Weird, not the first one. All right.SQL prevention cheat sheet. So there's a lot of defenses
that they talk about. But number one on their list
is prepared statements. And this is actually the one time in
security that I know of that using this control actually speeds up execution time,
rather than slowing down. We know historically,
security controls kind of get in the way. However true it may be at this point. Having something like TLS turned
on could potentially slow you down because there's more
handshakes that are happening. That's not really a significant
impact now nowadays. But that is that they used
to be true at some point. So, more often than not in security, we run into controls that
actually slow us down. With this exact instance, it actually
doesn't slow us down because the SQL driver doesn't have to
parse the entire query. It's actually just replacing the data and doesn't actually have to
comprehend what that data is. It's simply consuming it as data and
not doing anything else with it. So prepared statements is
definitely one way to go about it. So let's go about and converting this
piece of code into a prepared statement. Now remember for your lab you're
going to need to document this so note where the code is. Taking the image of it from
before modifying the code. And taking an image of it
after modifying the code. Showing that your SQL injection attack not
working as well would be another image. So that's all in the guidelines for
the lab. Give it a try and come back to
the next video for the result.