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.