Welcome back. In the last lesson,
I gave you a brief overview of
the three types of Injection attacks.
For this lesson, I'm
basically going to introduce the idea of
SQL Injection vulnerabilities and
talk about some mitigation strategies.
After this lesson, you'll be able to describe
and discuss SQL Injection issues.
So, let's start with defining what is SQL?
Very briefly, SQL is
an interpreted language that
allows for querying, manipulating,
and controlling data,
and it also allows for the creation and
modification of your data schema of your database.
It's used by web applications for
being able to communicate with the backend database.
For example, to query or insert,
update or delete data in the database.
There are different types of implementations of SQL,
and it just depends on what database you're using.
For example, SQL looks slightly different in Oracle
versus Microsoft SQL versus MySQL, etc.
If you want to have a practice using SQL,
you can go to sqlzoo.net.
Now, going back to the general idea of Injection attacks,
remember that Injection attacks
come about when a malicious user
can input data into a command or a query string.
That command or query string is interpreted
completely by the interpreter as a command or a query.
Whereas the web application developer
has treated the command
or query string basically a split
between data and an actual command or query.
However, the interpreter interprets
that string completely as a command or query.
So, for an example,
here is a SQL string that
performs a select from a table called members.
Where the login is
some login and the password is some password.
Here, you can see that the login and
the password are user-defined.
In this example, this select statement should only be
true or return true when the login and
the password would match at least one existing entry.
Now, what happens if a malicious user
inputted for their password
this particular partial SQL statement.
So, here in our particular example,
it's one and then a quote,
single quote, then single quote one equals one.
So, what ends up happening is that
the total SQL string is then
rewritten as a resulting SQL string here.
As you can see, the overall SQL statement is
modified such that it will always return true.
With that, this whole SQL string is interpreted by
the interpreter as one
giant SQL string to be interpreted.
So, in general, there is a mismatch about how
the developer sees a string coming into the interpreter,
and how the interpreter sees this string.
The string being fed into
the interpreter is considered by
the interpreter as 100 percent a command or query.
However, it is treated
programmatically by the developer
as part data and part command.
On top of that,
this string is modifiable by an untrusted party.
So, again, SQL Injection comes about when user supplied
data is injected into a web application SQL query,
and then is interpreted by
the web application's backend database.
This can lead to privilege escalation
or modification or extraction of data.
Now, here we have the concept tree for SQL Injection.
The fundamental concepts that we need to know in
order to understand SQL Injection are;
the SQL language, relational databases,
and differences in relational database implementations.
Now, you can find more information about
the fundamental concepts in some external resources,
which we'll touch upon later.
Now, here's another SQL Injection example.
This example I've obtained from a book called,
The Web Application Hackers Handbook,
and this is actually a very excellent book if you
would like more detail about SQL Injection,
especially more advanced attack methods
compared to what we're talking about
in our class right now.
So, here in this example,
we have a SQL statement,
which is an insert statement.
For this example, the fields that are controlled by
the attacker are username and password.
For this example, this field called privs is an integer,
where zero means admin account privileges,
and one means regular account privileges.
So, if this following string
here is injected into the username field,
then the attacker can create an admin user.
Notice the use of this dash dash.
This is a way to comment
out the rest of the SQL statement.
The resulting string that's created as
a SQL statement is the following;
where the values are replaced by foo,
bar, 9999 and zero.
Then the rest of the original SQL statement is actually
commented out such that this whole SQL statement
is still syntactically correct
even though the attacker has modified the SQL statement.
Now, there are four ways to
mitigate SQL Injection vulnerabilities,
and we're going to talk about them in
more detail in later lessons.
But now, we're going to summarize them,
so you know what's coming up.
The first way that you can mitigate
SQL Injection vulnerabilities is
basically the idea of using
the principle of least privilege.
What this means is you want to set the user role of
the web application such that
this user role doesn't use root privileges.
You want to reduce what the user role of
the web app can actually do.
You want to reduce the capabilities to only what
the web application user role requires to function.
In general, it's good to think on
what is the least privilege that it requires.
The next method that you can use to mitigate
SQL Injection vulnerabilities is to use
something called prepared statements.
These are precompiled SQL statements
that act like a template.
Because they're precompiled, there's
no way for the interpreter to interpret
them in any other way other than
a SQL statement with buckets where data will live.
The input parameters are placed
into these buckets in the prepared statements.
These input parameters are always treated
as data and not as part of a SQL statement.
Another mitigation strategy is to use stored procedures.
Stored procedures are slightly
different from prepared statements,
in that they are SQL statements that
you can generate and store in your database.
A stored procedure allows us to use
a static SQL and helps to avoid SQL Injection.
But, you have to keep in mind that
stored procedures can use a
dynamic and also a static SQL.
So, to avoid SQL Injection vulnerabilities,
you need to avoid dynamically
generating SQL in your stored procedures.
The last method to mitigate
SQL Injection vulnerabilities is
to use query whitelisting, or input validation.
It's basically the idea that for certain types of input,
you can have a whitelist of
inputs that are considered to be okay.
Now, to summarize all of what we have just talked about.
We talked about SQL Injection as
a specific kind of Injection attack
and it affects how the web application
communicates to the backend database.
We also looked at the SQL Injection concept tree and
briefly introduced methods that we
can use to mitigate SQL Injection.
That's all for now. Thank you.