<< MaskedTextBox: stripping the masked characters | Home | Tampa Code Camp >>

Stored Procedures vs In-Line SQL

This post is in response to Frans Bouma's post, where he explains why Stored Procedures are bad. I'm not going to go over the differences of why to use Stored Procedures or why to use In-Line SQL, you can find many opinions on the topic by doing a simple search. I'm just going to reply to his post with my opinions.

One of the reasons why Frans likes to use Ad-Hoc (In-Line) SQL is because with SPs you have to write one SP for everything you do, which can become very annoying to maintain. I agree with him. Then he goes on criticizing Rob Howard for not knowing what Role Based security is and saying that you can replace the security benefit of using SP by using views. Then my question to Frans is the following, if one of the reasons for not using SPs is because of the maintenance nightmare, what do you think will happen with the views? You're going to get the same maintenance nightmare as with the SPs. Also SPs bring an additional security benefit that In-Line SQL doesn't. You can give a user (or role/group) execute permission to a SP without having to give that same user read access to the table. Now that is very important! Imagine an application that uses a very restrictive security model, where the user connecting to the DB only has access to execute SPs and no access to read, insert, update or delete on any tables. Unfortunately this application was poorly coded and is vulnerable to SQL Injection. The hacker will not be able to read any tables, nor delete, update, or insert data. How will you accomplish that with in-line SQL without having a maintenance nightmare with the Views?

One of the rules of securing an application is to have multiple levels of security and not to rely on only one level. By using SPs and only giving the users access to execute them you are adding an extra layer of security, which would have not been there with In-Line SQL and our SQL Injection vulnerable application would have been used by a hacker to get what he wanted.

Thus far I haven't found a really good reason (or sets of reason) of why to use In-Line SQL instead of SPs so far now I will continue to use them.

Happy programming!

author: Jonas Stawski | Monday, July 09, 2007 5:17 PM

Your Comments.

# re: Stored Procedures vs In-Line SQL
Funny I was just reading Frans' old post when I decided to search for newer commentary on the subject and came across your post.

As someone who writes literally dozens of situational applications and has spent more than my fair share writing data access layers, I often wonder why we have such an argument at all.

For me, it is as simple as this:

1. Does your app REALLY require security to be so tight that you should grant the application SP access and yet not grant it table access? If so, then you're stuck with stored procedures. In some environments, this makes sense, but the instances I can think of are actually quite rare, and even so, the regulation does not always need to apply to the entire database.

2. If you've reached this point, great! Your app can access either stored procs or tables directly. My recommendations for this scenario are as follows:

a) Use inline SQL for CRUD operations.
b) Use views for complex select joins
c) Use stored procs for other complex operations

My reasons are quite simple:

1. Why write a stored proc for a simple CRUD operation? Most would agree it's simply a waste of time in most cases.

2. Why write a stored proc simply to select data? If the select statement is based on a single table, use inline SQL... it's quick, easy, and to the point. If the select requires complex joins, filtering, or other processing, use a view.

If you have a business process that simply must reside in the database (such as a transacted order process or a multi-table financial calculation), go ahead and use a stored proc. That's what they were made for!

It seems to me the overall underlying argument is about using one style of coding or the other, when it is absolutely clear (to me, anyway) that a blend of both styles is often the optimal solution in terms of time spent, maintainability, and developer sanity.

Left by http:// at 7/18/2007 3:17 AM
# re: Stored Procedures vs In-Line SQL
Chris,
I respect your opinion, but I don't agree. Today security should be part of your design phase and not an afterthought. With the arrival of the internet and globalization hacking has become more and more popular. One of the main rules of a secured application is to have many levels of security and not rely on only one level. I always use the same example when I'm explaining this to my clients. Would you leave your money lying around the house just because you have an alarm system set up or would you put all your valuables on a safe? If the thief is able to pass the first level of security, the alarm, now he has to deal with a second, the safe. The same applies to Security Design. Today we know that SQL Injection exist and we also know that parameters solve that problem. Tomorrow there might be a way to use SQL Injection even with parameters and all your parameterized in line queries will be vulnerable. Maybe a new type of attack is invented and if you used a secured model of using Stored Procedures and only given access to execute SP to the user then you’re adding a new layer of security which might (or might not) save you from that attack. Bottom line is the hacker will find it harder to get you and will not waste any more time on you and move on. I don’t think anyone should even ask themselves about whether or not security should be so tight. Applications should be secured by default.

Left by jstawski at 7/18/2007 3:49 AM
# re: Stored Procedures vs In-Line SQL
Ok,
I don't get this argument. I you can take the time to use inline sql then you have the time to create and deploy a stored procedure.

First of all, with inline sql there should be some thought on sql injections.
For example:
strMySQLStatement = "Insert Into TblAuthors (AuthorName, Date) values (" + strMyAuthorName + ", GetDate())"
Wow now that was alot to type.
Now what if strMyAuthorName = O'Henry opps now my SQL statement won't work, ok now we have to write some kind of trap to replace ' with '' so that we can have a working SQL Statement.

Wow what a waste of time. Just create the Stored Procedure and then call it in the application.
Oh wait, the argument prestented was now we have added a non-nullable column to the table.

Ok if we have a stored proc that only has one parameter @AuthorName then lets open the stored procedure and change the insert statement to insert the new column, but wait we can also add a new variable @NewColumn Varchar = null and we have a optional parameter that could or could not be passed in.

Run the alter script and we are done.

Oh the other way, we still need to add the new column to the in-line sql, so Open the project, check it out, change the code, run a test, compile the new component and deploy the component.

Oh to deploy we have to shut down the web - OH MY! we don't want that.

It is too much trouble to argue this point, I get so tired of catching flack from my boss who thinks In-line SQL is the way to go. My 10 years of web developement have never given me access to inline SQL and I don't even know how to manage such code. Why should I? If I can write the inline SQL, why not just create the SP?

Left by http:// at 8/24/2007 2:24 PM
# re: Stored Procedures vs In-Line SQL
Robin,
In line SQL doesn't have to be vulnerable to SQL Injection. You can resolve that issue with parameterized queries!

Left by http:// at 8/24/2007 2:36 PM
# re: Stored Procedures vs In-Line SQL
Security?

Simply put; you want to give role members the permission to read data newer than 1 month, now try this with table permissions, here you go..

Left by Onur Safak at 9/16/2007 12:23 AM
# re: Stored Procedures vs In-Line SQL
Here is one of many reasons i prefer stored procedures... I can modify a SQL statement without having to compile and redeploy the application. Nor do I have to worry about regression testing the application either. If something goes wrong i can roll back a stored procedure ALOT faster than i can roll back and application especially one that may be locked if users are in it (older VS2003 apps).

Left by http:// at 9/21/2007 9:13 PM
# re: Stored Procedures vs In-Line SQL
Amazing. This argument runs and runs. sprocs are useful, but aren't religious artefacts.

istawski: I agree with Chris at the top of this list of posts, but then I don't work with sensitive or financially valuable data. If a hacker wants to penetrate my router/dmz/firewall/application firewall/ and then try and access or edit my data, they have more time on their hands than most people. Very sad. Are you a securanoid? (http://www.securanoia.us/)

Onur Safak: newer than a month? I assume you are saying this is easy. A 1:1 table containing PKs for the last month's inserts (a trigger can automate this) is all you need. Add the role permissions to that table and use a view to get the data. Basta!

Don't forget the power of dynamic SQL is also available in sprocs. I have used dynamically created sprocs ( a sproc creates a sproc then runs it) to solve many a complex issue. (Product note: I have only done this in SQL Server)

Bottom line is as Chris says - use the right tools for the job and know them all well.

Left by http:// at 10/3/2007 7:55 AM
# re: Stored Procedures vs In-Line SQL
Well for every problem there are a million different ways to code it, it depends on programmer preference in most cases.

I don't need to see it in books online, I have proven it time after time, my apps run faster with Sprocs returning the data than they do with In line SQL....sometimes by a little, sometimes by alot. I always believe it is best to leave the functions with the systems that handle them best.. Querying data is best handled by the database in most cases. There are reasons to deviate from this.
All code should be securely written. Hackers have too much time on their hands. So do virus writers and other malicious people. Unless you are writing personal code for personal use then it is a huge lack of professinalism to avoid security. Even if you don't believe your app needs it, the security compromise created by your app could affect another app.
Given the prevalance of malicious code and coders I am absolutely security paranoid and my clients thank me for it.

Left by http:// at 10/9/2007 6:37 PM
# re: Stored Procedures vs In-Line SQL
It's pretty easy to write a routine that takes a stored procedure name and an array of parameter info, and builds the command object. If ALL database access uses this one routine, then nowhere else in the code should there be a command object, and there shouldn't be any SQL anywhere. That's pretty easy to validate over an entire project. And if the project passes this validation, SQL injection WILL NOT occur at the application level.

If the application does not use stored procedures exclusively, there will be SQL fragments in several places in the code, and every single fragment must be inspected in detail - and followed from its origin to the formation of a complete SQL statement and its handoff to the database - to verify that the application is safe from SQL injection. This is a great deal more work.

True, not every query needs to be safe from SQL injection. But when the app goes through a major (or perhaps even a minor) revision, every query that is not obviously-safe from SQL injection must be re-inspected for safety and/or re-evaluated to see if it needs to be safe.

And not every app needs to be safe from SQL injection. But applications get new functionality added and get extended and deployed in unexpected places. It's hard to predict. Why not form, and keep, the habit of writing injection-preventing code in the first place?

Left by http:// at 11/5/2007 6:05 PM
# re: Stored Procedures vs In-Line SQL
80% of the common applications do NOT deal with ANY sensitive data. Most applications floating on the net are content management systems, or simple data entry systems. This is turning into a security argument so i wont argue on that too much. Just that an application that is vunerable to an attack which acctually does not have any sensitive data in minimal to the point on nothing at all. However spending enormous amount of time and money to secure an application that does not need to be secured in the first place basically tells you that your manager has been hipnotized by a consultant. For those who say security is important by default, well you paranoide securanoid freaks, how many applications really have you secured that has defended an attack?

60% applications are simple data entry applications that do not requre those security elements in them, but because the stupid lame paranaiod consultants use all the buzz words to acctually scare the living nightmare out of the managers that everyone ends up coding more then they really should.

My method: Dont spend too much time on securing an application that does not need to be secure in the first place, just have minimal secure methods that do not take many many weeks or months to code. IF it does, sack the method and roll out the application. Because an attack would happen when the application goes through a change as well, so it really doesnt matter, role the next version of security method in.

The whole point of software configuration is another lame buzz word for consultants...80% of the projects mostly get reworked after a few years any, so what is the point of doing it anyway. New technologies roll out every month, you want to be updated then you recode, remodule, remethod. Code reuse doest not work in reality my friends.

I speak with experience. Just my two cents :)

Left by http:// at 11/8/2007 1:07 PM
# re: Stored Procedures vs In-Line SQL
By the way, regarding the stored procedure and inline SQL, well it really all blogs down to personal preference.

Use sp only when the data is criticle and you want it to be fast even if there is only a 0.0 mili second difference.

I personally use inline SQL (ofcourse parameterized - so dont go bursting out injection attacks etc etc)

Left by http:// at 11/8/2007 1:19 PM
# re: Stored Procedures vs In-Line SQL
As far as *opinions* go, the argument of store procs vs inline sql can last forever (it started more than 20 years ago and it's still going). How about bringing some *facts* to the discussion? Has anyone benchmarked the two approaches in a number of different scenarios and come up with conclusive evidence one way of the other?

Left by http:// at 11/9/2007 8:25 PM
# re: Stored Procedures vs In-Line SQL
@Sam on benchmarks
I think the arguments are about more than performance. As long as performance is good enough (subjective of course) then either approach passes.

The more important questions for me are related to the development and subsequent maintainability of a system.

Here the pros and cons vary depending considerably upon what you find important - the ability to swap between database vendors or the ability to swap between programming languages.

I also think the available tools have an influence. With simple queries, dynamic sql is very easy to create. If your applications have complex queries, then the lack of a visual SQL creation tool makes them very clumsy and error prone. This is probably my main justification for staying with sprocs (often dynamically creating other sprocs).

So benchmarking would ultimately be subjectively based on the benchmarker's expertise and the complexity of the data management needs.

Left by http:// at 1/14/2008 10:12 AM
# re: Stored Procedures vs In-Line SQL
Dude.. why is this even a question any more? Sprocs are about the last thing I resort to. It should be a question around parameterized sql, views, and table-valued functions. Dare I say it? The answer is "Yes, both -- they're compatible.".

And, ladies and gentlemen, equating dynamic SQL with spaghettied string building is a pretty stale, and false, argument, as modern ORM Mappers demonstrate.

Left by Keith J. Farmer at 7/3/2008 10:49 PM
# re: Stored Procedures vs In-Line SQL
SProcs, especially in MS SQL Server have a major (again: MAJOR) performance benefit. Period.

Left by Managed Server at 7/23/2008 10:56 AM
# re: Stored Procedures vs In-Line SQL
SPs are good ..Inline sql-s are also good. Depends on choice an drequirement. For a single name,id returning it is no use of using sps. Then your application will have thousands of sps. Or if u create a big sp with small small requirement then will difficult to manage the sp. So it is always better to stick to inline sql for small requirement and for a medium, secured and complex logic depend on sps. It is your call entirely.

Left by Coder at 11/28/2008 8:53 AM
# re: Stored Procedures vs In-Line SQL
For maintainability, my preference has always been to use Dynamic SQL for most cases. This includes any maintenance-type programs and simple transaction entry programs where the performance of the SQL is hidden by the user's own ability to type.

Stored procedures have been limited to batch processes where the ability to process large vlumes of data as quickly as possible is essential.

Our change management system requires us to track changes to programs, tables, views and stored procedures. Currently there are about 100 stored procs to track; if we abandoned dynamic SQL in favor of stored procs, that would probably number over 5000. What a nightmare of red tape that would be!

I'm currently trying to borrow some code from another system and adapt it to a different database. This other system accesses its database entirely with stored procedures, which is complicated further by multiple levels of abstraction across multiple modules. While trying to get some meaning out of all this, I find myself wishing I could see dynamic SQL inline.

Left by J.Delahunt at 1/27/2010 2:51 PM
# re: Stored Procedures vs In-Line SQL
Ok, I have been reading these arguments on both sides (here and many other places).

Here is my dilemma, I would REALLY like to believe only accessing through SPs is good - because I just started at a place that insists upon it.

Here is the thing - there are 1600 SPs in this application. The only argument I have seen that really makes a lot of sense seems to be security - well, the application itself has plenty of holes. I add a field to a table, I now have to update EACH of the CRUD queries, I have to go through each and every SP that uses this table. Most of the examples are "just update the SP...." well try sifting through 1600 of them.

I seem to agree with Jonas on this one. The place I worked previously was against the use of SPs and it was extremely frustrating. What I can say is that good code keeps your SQL calls (inline or SP) contained in the business objects or better yet a separate datalayer. It is a heck of a lot easier to find all the places I made a code function call (that applied to the SQL change) than find all times a field is used in 1600 SPs. Even worse, try to find the pieces of Business logic buried in the 1600 SPs instead of the business layer of the application.

Where I am now is taking months to do applications that should take a week - bad coding is a part, but no all of it.

Left by DanCaveman at 2/4/2010 1:44 PM
# re: Stored Procedures vs In-Line SQL
Hmmm I'm still not sure. The security thing for me is the most important, however having 1000's of SP's is a pain, especially if there ones that are almost the same.
However, the arguement vs inline for maintainability I can not get my head round. Surely it's a lot harder searching through code for statements, with all the language based chod in the way, than running a quick query to list up all the references to the table you need to change?
And using views for joins etc - imo that's not great either. Before you know it you'll have views joined with views, with where clauses etc which are very painful to debug and change, and worse still have appalling performance.
I think I'll stick to SP's for data derivation - it's in one place, it's easy to change and administer, it's secure (I use approles, so can lock users out of the database completely) and logically it makes sense.

Left by BrianJ at 2/9/2010 11:07 AM
# re: Stored Procedures vs In-Line SQL
Nothing is written in stone nor black or white. There are a lot of reason to go with in line SQL, Store Procedures, a Mix of both or an ORM Framework. Which way you go trully depends on your needs/requirements

Left by jstawski at 2/9/2010 12:15 PM
# re: Stored Procedures vs In-Line SQL
According to me,

Simply choose Stored Procedure if you really want Security as well as Performance..

Inline SQL is not suited for Bulky project & all that.

In academic project inline-sql is okbut in professional level try to use SPs

Left by Yogi K at 2/15/2010 12:28 AM
# re: Stored Procedures vs In-Line SQL
Code combined with sql is like anchovies in your spaghetti. Some people have wierd tastes and that's fine.

Left by Paul at 3/27/2010 8:15 PM
# re: Stored Procedures vs In-Line SQL
My two cents...

For medium to large object-oriented software projects, stored procedures win because they provide a well-defined conract between data provider and data consumer, and because they can provide a reasonably high level of abstraction on top of the db's underlying storage details.

This means that stored procedures are aligned much more closely to the tenets of design-by-contract, test-driven development, verifiable inputs/outputs, and code readablity.

The same arguments cannot be made as easily for raw access to tables and views. They are by definition more closely aligned with "how" data is stored physically. When data storage concerns leak into the code that you write, whether it's within BL or DAL, maintainability of that code over time is diminished. Period.

Left by Keith Ratcliffe at 5/21/2010 12:47 PM
# re: Stored Procedures vs In-Line SQL
"However, the arguement vs inline for maintainability I can not get my head round. Surely it's a lot harder searching through code for statements, with all the language based chod in the way, than running a quick query to list up all the references to the table you need to change?"

Um, in VS, very easy. There is a nice search function.

Left by Anonymous at 6/10/2010 2:34 PM
# re: Stored Procedures vs In-Line SQL
Sure, a search is easy if all the code that is ever going to hit a database is contained within a single solution. If you've got a database that is going to be used by several different applications that are developed by several different programmers, you just don't have an easy way to find all the code that hits a particular table whenever you need to update the access code to reflect a change in table schema.

If the data is ALWAYS accessed via stored procs, though, you just update in the one place (conveniently located on the db server) and you're done.

Left by Billy Moon at 1/7/2011 1:18 PM