Hello everyone. Today, we're going to
be chatting about mitigating
SQL injection vulnerabilities using stored procedures.
So we're going to talk about what stored procedures are.
I'm going to show you an example of what that looks like,
specifically in Java and then you'll be able to discuss
some pros and cons of using stored procedures.
Let me start with talking about stored procedures.
Stored procedures are basically a way to generate
a SQL query and have it
be stored in your backend database.
Then when you need to run it,
it's called by the web application.
Now stored procedures are similar to prepared statements,
and that you can create static SQL queries
much like prepared statements.
But you have to make sure that you're only creating
static SQL queries and using
parameters much like prepared statements.
So this means avoid using
stored procedures by generating dynamic SQL queries.
For example, concatenating variable names
that have data from user input,
much like the way you would avoid
incorrectly using prepared statements.
If you used stored procedures to
generate static SQL and use parameterization,
then you will be fairly safe
from SQL injection vulnerabilities.
Here for example, this example comes from OWASP.
Here you can see this is written in
Java and the way the construct
is for this code is very much
the way you would construct prepared statements.
Here it says you prepare the call,
and then you have
the question mark here for your parameterization.
Your bucket for your input data and here as you
can see they are specifying that
the data in that bucket or that parameter must be
a string and it's indexed by number one.
Once the stored procedure is created,
then they go ahead and execute that query.
Now some pros and cons.
Much like prepared statements,
you have the ability to specify
the data type for your parameter.
So there's no confusion between user input that should be
treated as data instead of part of the SQL query.
However, the cons are much like prepared statements.
If you have lots of parameters,
it could make the stored procedure statement
cumbersome to read.
But this isn't horrible in the grand scheme of things.
On some database systems like MS SQL Server.
Stored procedures require the database user
to have execute rights
in order to run the stored procedures.
But this increases the privilege
of the database user or the web app user.
Well, so I should clarify the web app
process user not a human user,
but the web application process.
Because of this, this increases
the privilege and could be
leveraged by an attacker if there is a compromise.
So remember that idea of least privilege.
We also want to make sure that we are
giving just enough privilege to
the application process such
that it could function but not
too much that it can do
more damage if a compromise occurs.
In summary, we talked about what stored procedures are,
and we talked about a specific example,
and also mentioned some pros and
cons of using stored procedures.
That's it for now. Thank you.