[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.