In this demonstration,
I'm going to walk you through a common situation
where we found some code vulnerable
to SQL injection during a test.
SQL injection is pretty serious because it allows attackers
to mess with the database queries
by putting in harmful inputs.
We'll follow the advice from the test report and go through
the process of spotting the problem,
understanding what's wrong with the code,
and then making it more secure.
Let's start by looking at the original code,
which was flagged for being vulnerable
to a SQL injection attack.
The purpose of this code is to retrieve user information
from the SQLite database based
on the username provided by the user.
So, let's focus on the function that's actually executing
the code for the login process.
Now, let's identify it here as a vulnerable login.
Now, what is this thing doing?
We're creating a query
and we're selecting all from the table users,
which we created up here with three rows.
We have a user admin, user John, user Adam, passwords,
and then social security numbers.
So, the function is showing username and password.
So it's accepting two variables,
and it's going to insert it into the actual function itself.
Okay, the clause, the SQL function.
Now, what this Python function is saying is this query
is going to be equal to f.
Now the f is going to make sure anything
in this literal string
with curly braces is going to be a variable.
So, it won't look at the word 'username' explicitly.
It's going to look at the function of username.
So, you put John here, it's going to
substitute that for John.
But, you know, 123 here
is going to substitute password for 123,
so on and so forth.
And then it's going to print out the command executed
so we can see what's actually being executed
from the standpoint of SQL.
Now, let's go ahead
and go through this as it stands.
So, the user input will be here.
We're going to use John.
And then we're going to use the SQL injection, right?
This is the SQL injection right here.
Let's run this and see what happens.
Now, what we see here is it returns every single row.
Now, this is the sensitive data
that we speak of a lot of times.
Just for this simplified example,
it has social security numbers.
So, even though the username is John,
it should have returned the John row
if it was properly secured.
But it returned everything.
Now, let's see why.
So, we have this table right here.
Okay, this is the entire table.
And it's going to evaluate username John.
Okay, so that's not correct.
And it's going to say the password is equal to blank.
That's also not correct.
But again, it has this or clause,
meaning that this can be true,
or this can be true.
So pretty much, does one equal one?
Yes.
So that means, hey, it doesn't equal John.
The password is incorrect, but one equals one.
So, this works.
And then it's going to go down
and look for the second one.
Hey, does the username equal John?
Yes.
But the password is not blank, so that's bad.
But one equals one.
So this is included.
And then the same thing for Adam.
It's going to check, hey, does the username equal John?
No.
Does the password equal blank?
No.
But one equals one.
So, from the standpoint of SQL, that row is true
because again, it is already selected every single row
and we're only filtering down based on
if there's a true statement within this where clause.
And there is a true statement because one equals one.
All right?
So, just say we entered John's password here.
Just say it's password.
And we run this.
We will only return the John row.
We can do the same thing for admin.
And we do password 123,
we would get the admin row.
Same thing would be similar for Adam, right?
Adam here.
Then we do
123456.
All right.
Now again, when we input
the SQL statement,
it's logical operator OR and then we do quote one,
quote equals quote one.
Do run, and it returns everything.
So how is this evaluating this?
So, this is where the quotes come in.
So, it automatically includes a quote here.
And so, then it needs to have the other quote here.
So that's going to finish this statement.
So that means it's going to pretty much
be an empty string.
So, quote, quote.
Exclude these double quotes
because it's only evaluating what's inside of it.
It just let the system know that this is a literal string.
Okay, then we have the password,
which is going to be quote or quote one quote equals
and then it finishes it with this quote.
Because remember, it only evaluates things
that are in the double quotes.
Okay, so that's how we have a complete syntactic statement.
Okay, so how do we fix this?
We're going to use a parameterized query.
And we're going to do that by adding a different function.
I'm going to insert this new function here,
and we're going to show how it's different.
All right, now what is this function doing?
I named it secure login function,
and it's going to use a parameterized query
to prevent the SQL injection.
Now, how's it going to do this?
Now, remember, parameterized inputs are a way of safely
incorporating user-provided data into SQL queries
by separating the SQL code right here.
This is a separation
from the actual data,
the username and password, okay?
And this cursor.execute method separates that
by having a query here,
and then we have the user input separately.
These question marks are placeholders
for this data username and the data password, okay?
And this method prevents the SQL injection attacks
by ensuring that the user input is treated strictly as data
and not a part of the SQL command, as we see up here.
So, when it sees the OR,
it's not going to treat it as the OR logical operator.
It will treat it as the literal word,
or which has no meaning to SQL except
for matching parameters.
So, let's try to run this same function
and we're going to use the secure
login function, okay?
All right, so let's comment this out so it won't run this.
And we can take the print
the vulnerable portion out as well.
All right, so if it's successful,
it's going to print login was successful.
It's going to return the rows,
and if it wasn't, it's going to print login failed.
All right, so now we're going to attempt
the secure login function
using the username Adam.
And then we're going to try this SQL injection.
All right, so we see we have login failed.
Good.
That means we are not accepting this SQL injection anymore.
It's only treating it as if the password
is literally quote space
or like the word and not the logical operator,
and then space quote one quote equals one.
And that is not Adam's password.
Adam's password is 123456, which isn't much better,
but at least it's not this SQL injection.
So, let's put in Adam's password.
123456.
Now we're going to run this command.
Perfect.
So, it returned only Adam's input
because we have the correct information.
So, let's make sure this function still works.
Let's say we put in the wrong password here.
Let's say we add a seven, run, failed.
So, it is correctly checking for the information
and it's not allowing SQL injections.
So, to sum things up,
we covered how to identify an error in our Python code
that makes it vulnerable to SQL injection attacks.
We broke down how the function can be exploited
to bypass certain security checks.
And then we demonstrated how to fix the issue
by using parameterized inputs.
I hope you find this lesson valuable,
and I look forward to seeing you in the next one.