Am I immune to SQL injections if I use stored procedures?

Question

Lets say on MySQL database (if it matters).

Answer 1

No, you will not be completely safe. As others have mentioned, parameterized queries are always the way to go -- no matter how you're accessing the database.

It's a bit of an urban legend that with procs you're safe. I think the reason people are under this delusion is because most people assume that you'll call the procs with parameterized queries from your code. But if you don't, if for example you do something like the below, you're wide open:

SqlCommand cmd = new SqlCommand("exec @myProc " + paramValue, con);
cmd.ExecuteNonQuery();

Because you're using unfiltered content from the end user. Once again, all they have to do is terminate the line (";"), add their dangerous commands, and boom -- you're hosed.

(As an aside, if you're on the web, don't take unfiltered junk from the query string of the browser -- that makes it absurdly easy to do extremely bad things to your data.)

If you parameterize the queries, you're in much better shape. However, as others here have mentioned, if your proc is still generating dynamic SQL and executing that, there may still be issues.

I should note that I'm not anti-proc. Procs can be very helpful for solving certain problems with data access. But procs are not a "silver-bullet solution to SQL injections.

Answer 2

You are only immune to SQL injections if you consistenly use parameterized queries. You are nearly immune to SQL injections if you use proper escaping everywhere (but there can be, and has been, bugs in the escaping routines, so it's not as foolproof as parameters).

If you call a stored procedure, adding the arguments by concatenation, I can still add a random query at the end of one of the input fields - for example, if you have CALL CheckLogin @username='$username', @password='$password', with the $-things representing directly concatenated variables, nothing stops me from changing the $password variable to read "'; DROP DATABASE; --".

Obviously, if you clean up the input beforehand, this also contributes to preventing SQL injection, but this can potentially filter out data that shouldn't have been cleaned.