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.