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.