Does stored procedure help eliminates SQL injection / What are the benefits of stored procedured over normal SQL statement in apps?

Question

Hi. I'm pretty new to SQL world. Here are my questions:

• What are the benefits of stored procedured over normal SQL statement in applications?
• Does stored procedure help eliminates SQL injection?
• In Microsoft SQL Server it is called stored procedure. How about in Oracle, MySQL, DB2, etc.?

Thanks for your explanation.

Answer 1

Some of the benefits that I consider when using stored procedures

• Stored procedures encapsulate query code at the server, rather than inside your application. This allows you to make changes to queries without having to recompile your application.
• Stored procedures can be used for more well defined application security. You can Deny all rights on the base tables, grant execute only on the procs. This gives you a much smaller security footprint to manage.
• Stored procedures are compiled code. With the latest versions of MSSQL the server does a better job of storing execution plans - so this isn't as big of an issue as it used to be, but still something to consider
• Stored procedures eliminate SQL injection risk ONLY when used correctly. Make sure to use the parameters the right way inside the stored proc - stored procs that are just executing concatenated dynamic SQL inside them aren't doing anyone any good.

Answer 2

Stored procedures allow you to store you sql code in a location outside of the application. this gives you the ability to:

• Change the SQL Code without recompiling/redistrubuting the application
• Have multiple applications use the same stored procedure to access the same data.
• Restrict users from having access to read/write to tables directly in the database.
• From a development perspective it also allows the DBAs/database programmers to work on sql code without having to go through application code to work on it. (separation of responsibilities essentially).

Do stored procedures protect against injection attacks? For the most part yes. In sql server you can create stored procedures which are not effective against this, mainly by using sp_executesql. Now this doesn't main that sp_executesql is a security hole, it just means that more precaution needs to be taken when using it.

This also does not mean that stored procedures are the only way to protect against this. You can use parameritized sql to accomplish the same task of protecting against sql injection.

I do agree with other people stored procedures can be cumbersome, but they have their advantages too. Where I work, we have probably 20 different production databases for various reasons (don't ask). I work on a subset of maybe three, and my teammate and I know those three really really well. How do stored procedures help us? People come to us and when they need to grab that information out of those databases, we can get it for them. We don't have to spend hours explaining the schemas and what data is de-normalized. It's a layer of abstraction which allows us to program the most efficient code against the databases we know. If this isn't the case for you, then maybe stored procedures aren't the way to go, but in some instances they can add a lot of value.

Answer 3

For the most part yes, SQL injection is far less likely with a stored procedure. Though there are times when you want to pass a stored procedure some data that requires you to use dynamic SQL inside the stored procedure and then you're right back where you started. In this sense I don't see any advantage to them over using parameterized queries in programming languages that support them.

Personally I hate stored procedures. Having code in two disjointed places is a pain in the ass and it makes deploys that much more complicated. I don't advocate littering your code with SQL statements either however as this leads to it's own set of headaches.

I recommend a DAL layer implemented one of two ways.

1. My favorite, use an object relational management system (ORM). I've been working with nHibernate and I absolutely love it. The learning curve in steep but definitely worth the payoff in my opinion.
2. Some kind of mechanism for keeping all your SQL code in one place. Either some sort of query library you select from or a really structured set of classes that design the SQL for you. I don't recommend this way since it's basically like building your own ORM and odds are you don't have the time to do it correctly.

Forget stored procedures. Use an ORM.

Answer 4

Stored procedures only directly prevent SQL injection if you call them in a paramerized way. If you still have a string in your app with the procedure name and concatenate parameters from user input to that string in your code you'll have still have trouble.

However, when used exclusively, stored procedures let you add some additional protection by making it possible for you to disable permissions to everything but the EXEC command. Aside from this, parameterized queries/prepared statements are normally cached by the server, and so are just like a stored procedure in nearly every respect.

In spite of this, stored procedures have two big advantages for larger enterprises:

• They allow you to define an application interface for the database, so that the system can be shared between multiple applications without having to duplicate logic in those applications.
• They move the sql code to the db, where you can easily have an experienced DBA tune, update, and otherwise maintain it, rather than application developers who often don't know exactly what they're doing with database code.

Of course, these advantages aren't without cost:

• It's harder to track changes in source control
• The database code is far separated from the code that uses it
• Developer tools for managing many stored procedures are less than ideal (if you've ever open the stored procedures folder in management studio to find 200 procedures for a database, you know what I'm talking about here).

Answer 5

One way in which stored procedures (ones which do not use dynamic SQL) can make the whole application more secure is that you can now set the permissions at the stored procedure level and not at the table level. If you do all of your data access this way (and forbid dynamic sql!) this means users can not under any circumstances do amnything to the database that is not in a stored proc. Developers always want to say that their application code can protect against outside threats, but they seem to forget that inside threats are often far more serious and by allowing permissions at the table level, they are at the mercy of any user who can find a way to directly query the database outside the application (another reason why in large shops only two or three people at most have production rights to anything in the datbase, it limits who can steal information).

Any financial system that uses anything except stored procs for instance is completely open to internal fraud which is a violation of internal controls that should prevent fraud and would not pass a good audit.