Hello everyone. Today,
we're going to talk about mitigating SQL injection
using prepared statements. So, today we're going to answer
what prepared statements, are and what specific things
you need to do with prepared statements to be effective against SQL injection. Then, we're going to talk
about pros and cons. After this lesson, you'll
be able to formulate a plan to prefer prevent
SQL injection issues. Prepared statements are basically precompiled SQL queries
and when you use one, you would just compile it
just once before using it, and it basically
acts or can act like a SQL query template
that you basically have buckets for where your
user input will go. With prepared statements, you can generate static SQL queries, and you can force
the data typing of user input before passing
it into the SQL query. But there is a caveat
with those things, and we're going to get to more detail of that
in a little bit. But I would like to point
out here in this example, notice the use of
the question mark in that SQL statement. So, here it says, select name from user where name equals question mark and
password equals question mark. Now, these are called
parameters and notice that the user input with
a variable names called name and pass are specified as string data types using
the setString method. That's on line 11 and line 13. This ensures that the interpreter differentiates between
application code in actual data. But there's a caveat
to this which is that, you have to use
parameters in order for prepared statements
to be effective against SQL injection
vulnerabilities. I'm going to show you
a counter example right now. So, if instead you use string concatenation instead
of parameterization, you will still be vulnerable
to SQL injection. For example, if you use
prepared statement and concatenated your variables in your SQL strings statement, then this is still
vulnerable to SQL injection, and this is because it hasn't bucketized these
user_data input yet. That's effectively what happens. Some pros and cons of using prepared statements
with parameterization r. The pro is that you can specify the data type of
user input parameters. So, from our very first example, we showed that with
parameterization, you can set the datatype as
string for the user input. However, the drawback is
that if you have several, for example, more than three
input parameters, the indexing of these variables
can get a bit cumbersome, and the resulting code can be
a little bit hard to read. But I would say that seems like a better trade off than having a SQL injection vulnerability. In summary, we talked about what prepared statements
are and the fact that prepared
statements alone don't prevent SQL injection
vulnerabilities. You have to use
prepared statements along with parameterization
of your user input. I showed an example of
what that would look like. Then, we talked about
the pros and cons. That's it for now. Thank you.