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

Question

Let's say in a MySQL database (if that matters).

Answer 1

No, you will not be completely safe. As others noted, parameterized queries always go the way — no matter how you access the database.

This is a small urban legend in which you will feel safe. I think the reason that people are under this fallacy is because most people assume that you will call procs with parameterized queries from your code. But if you do not, if, for example, you do something like below, you are wide open:

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

Because you are using unfiltered content from the end user. Again, all they need to do is stop the line (";"), add their dangerous teams, and boom - you hit.

(Aside, if you are on the Internet, do not accept unfiltered garbage from the browser query string, which makes it absurdly easy to do very bad things for your data.)

If you parameterize queries, you are in better shape. However, as others have mentioned, if your proc still creates dynamic SQL and its execution, problems can still occur.

I should note that I am not an antiprock. Procs can be very helpful in resolving certain data access issues. But procs is not a "silver bullet solution for SQL injection."

Answer 2

You are immune to SQL injections if you use parameterized queries consistently. You are almost immune to SQL injections if you use proper escaping everywhere (but there may have been errors in escaping procedures, so they are not as reliable as the parameters).

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

Obviously, if you pre-clear the input, it also helps prevent SQL injection, but it can potentially filter out data that you didn't need to clear.

Answer 3

It depends on your stored procedures. If they dynamically generate SQL based on their parameters and then execute that SQL, then you are still vulnerable. Otherwise, you will most likely be fine, but I’m embarrassed to sound 100% confident!

Answer 4

No. If you create SQL that calls a stored procedure, you are still the target.

You must create parameterized queries on the client side.

Answer 5

No, since you can still use D-SQL in your stored procedures ... and in any case, checking and restricting input would be a good idea.

Answer 6

Stored procedures are not a guarantee, since any dynamic code is actually vulnerable and includes code inside stored procedures and dynamically generated calls to stored procedures.

Parameterized queries and stored procedures called with parameters are invulnerable to injection unless they use arbitrary inputs to generate code. Please note that there is a lot of dynamic code that is also not vulnerable to injection (for example, integer parameters in dynamic code).

The advantages are pretty much (I'm not sure 100% is really possible), storing the procs-based architecture, however, is that the injection can even be protected from (but not perfect) for dynamic client-side code,

EXEC-only permissions are granted to any user context that the application connects to, so any SELECT, INSERT, UPDATE, DELETE queries simply fail. Of course, DROP, etc. Should not be allowed. Thus, any injection must be in EXEC form, so ultimately only the operations that you define in your SP layer will be available (not arbitrary SQL) for injection.

Among the many other advantages of defining database services as a set of stored procedures (for example, any level of abstraction in software) there is the ability to refactor your database below without affecting the application, the ability to better understand and track usage patterns in your database with a profiler, and the ability to selectively optimization in the database without the need to deploy new customers.

Answer 7

Also, consider using fine-grained database access (also called, as a rule, role-based access control). The main user of your database should have exactly the permissions necessary to carry out his work, and nothing more. No need to create new tables after installation? REVOKE is permission. You have no legitimate need to run as sysdba? Then do not! An intensive injection instructing the user of "DROP DATABASE" will be blocked if the user does not grant this permission. Then all you have to worry about is data leak SELECT statements.