|
This article is for devlopers who use a front-end language (C#, php etc.) to manipulate SQL.
Once I was reviewing c# code of my friend.
I asked him : "Do you change the data of this table anywhere else?".
Without thinking he answered :"Yes! with Enterprise Manager".
PraVeeN
blog.ninethsense.com/
|
|
|
|
|
Stored Procedures (and parameters) are not a magic bullet against that, the incoming data must still be validated by the interface.
NinethSense wrote: If one have access to the system, he can easily do any kind of mis-using which you mention. Such activities does not belongs to a developer's duty but an Administrator's.
What!? That's ridiculous. It's everyone's duty, and the sooner such an attack is detected and foiled the better. As a programmer should I just pass along the data and when the system crashes say, "I thought you were handling the validation"?
Plus, who wrote the Stored Procedure? A programmer!
|
|
|
|
|
Oh dear, here comes another bunch of ORM and Amber's do not use SPs disciples:
> executable "disappears" at least it can't do any damage.
It is like saying if one app disappears then go ahead foget that you can still hack the DB apart with the second one.
> Distributing updated executables is also easier than distributing updated stored procedures and such.
Learning how to use SQL transactional functionality, versioning, services and other is more fruitful than bothering with SVN and CVS (which you can also do with SPs).
> not all database systems support stored procedures
Which toy is that?
> so if you're writing an application that is to support multiple databases you need to write all the statements in code anyway.
Like simple statements and simple use of x\SQL dialect. If you are running a serious database application you are probably using features that would not allow you to use them even if you wanted to bring in another database vendor.
Do tell, you check that in code and translate as appropriate.. hmm.
If you need to update both, enlist them in a transaction via (via your app? yikes!) otherwise oh momma.
> A database is for data.
Since at least late 1980s it actually was not, that is why it has all those things built on top that push the versions from 3.0 to 9.0 and so on.
> Stored Procedures (and parameters) are not a magic bullet against that, the incoming data must still be validated by the interface.
Validate that in code and you truly do not need a database.
> It's everyone's duty
At least one Database Administrator duty is to administer and another to design access for it.
> and the sooner such an attack is detected and foiled the better
If you take that approach, you should not be even aware that a particular table exists frankly; if you are, then you should hire a database guy.
>Plus, who wrote the Stored Procedure? A programmer!
A database designer, one paid more than programmer because he does not see your random X,Y,Z language code and 'uniform data access' mix well if at all.
LINQ will not help you there either, dynamic SQL is a first sign you are taking the wrong route not just on performance you need to see for yourself on native providers and heavy load, versioning, security and more, but on general solution/app design and approach to relational databases and SQL99 at least.
-- modified at 18:44 Friday 18th May, 2007
|
|
|
|
|
You misunderstoond NinthSense. It's everyone's duty to write code that validates data. It's NOT everyone's duty to be mucking around in the PRODUCTION database.
Developers should not have access to the PRODUCTION database. That's what the DBA is for.
I'm a developer. I don't have, or want, write access to any production data. When my stored procedures have been through QA, then and only then are they submitted to the DBA to be created/modified on the production DB.
|
|
|
|
|
>> Developers should not have access to the PRODUCTION database. That's what the DBA is for
Is that your DBA writing validation scripts in your database? If yes, you are on wrong way. DBA have a big role in database and it is not simply writing validation for your scripts.
PraVeeN
blog.ninethsense.com/
|
|
|
|
|
I've read my post, and your reply, a dozen times, and we might as well be talking different languages. In no way was I attempting to enumerate the roles of a DBA.
Let me start over.
Keep in mind the context: PIEBALDconsult claimed his stored procedures were getting changed and deleted, and thus constituted a security risk.
I was merely trying to point out that the solution isn't to stop using stored procedures, but to have and enforce a protocol for changes to the production DB. That usually means that the DBA, not developers, is in charge of making changes to the production DB. It doesn't have to be that way, but you need a protocol so you know who changed what and when, and are sure it passed proper QA.
I'm not sure who you think should be writing the code to validate data, but it doesn't really matter. What matters is that it is done, and passes QA. I don't care how good anyone is, everyone makes mistakes. Something that important has to be reviewed.
|
|
|
|
|
I don't know what you are doing but your all wrong. All applikasjons should use stored procedures when accessing the database for the following reasons
1. You can update, rename, split, merge tables without changing your code in the client applikasjons
2. The code will execute much faster since stored procedures has a precompiled execution plan
3. Stored procedure can return multiple results
4. Dynamic SQL statements in your code is a maintenance hell. If you have 10-20 apps accessing the same table you have a problem
5. Dynamic SQL statements can result in injection attacks
6. Stored procedure can perform multiple updates/changes in the database with one call transactional og non transactional
7. Microsoft, Oracle etc recommends it
It sounds like you have a maitenence problem or your are not to familiar with databases. All stored procedures scripts should be stored in source control and only be installed by the database admin in the production and test environment. When it comes to stored procedures that suddenly disappears: what kind of database are you using? No real database suddenly removes any stored procedure without someone removing it (by code or manual delete).
If your using databases that does not support stored procedures -> write dynamic SQL
If your using databases that does support stored procedures -> use stored procedures
When it comes to views -> i'm not the biggest fan.
Jarle
|
|
|
|
|
I am also not a fan of Views. Thats why I didnt mention it on the above list. Well, once I find a 'useful' tip, I will add to this article.
PraVeeN
blog.ninethsense.com/
|
|
|
|
|
Views are a good way to get a consistent, um, view, of your data.
For example, I have a 'product' table that includes foreign keys to several lookup tables. I have a number of stored procedures that return 'product' for various queries. Instead of listing all the columns and performing all the lookup table joins in every stored procedure, I do it once in the view, and every stored procedure returns from the view. If I need to add a new foreign key/lookup, I do it in one place, the view.
|
|
|
|
|
Cool reply Jarle, very informative. I agree with your statements mentioned above.
And know the use of stored procedures.
But some people say stored procedrues create one more layer and that is not good. So Procedures have to be created only when very necessary. I am not very clear on this.
Ravi
|
|
|
|
|
I'm not sure if you're joking, but in case you're not, you will find that the vast majority of database professionals disagree with you.
Yes, store you stored procedures in source control. It's not that hard.
You are applying the wrong solution to the problem. Your problem is that the wrong people have access to your production DB.
|
|
|
|
|
I also believe that the use of stored procedures is not always the best choice for all kind of operations against the db.
Take the case of multiple query choices where the user can lookup a Customer table by name, location, age or by a combination of them all... The stored procedure that would provide this query would not be optimized for all cases and it would be better that a new query plan be calculated by the db engine prior to executing (mainly when the data is due to frequent changes). Stating the "use of stored procedures" as a de facto for optimizing performance is not adequate and may be misleading in several situations.
Gonçalo Borrêga
|
|
|
|
|
d. Use Camel notations for all user created objects. Eg: @EmployeeCode
^ That is Pascal case camel case would be @employeeCode
|
|
|
|
|
Hi,
Actually I am not wrong. It is of course "Camel Notation". May be the best word is "UpperCamel Notation".
Eg:
camelCaseLooksLikeThis
lowerCamelCaseLooksTheSame
UpperCamelCaseLooksLikeThis
Check this http://en.wikipedia.org/wiki/CamelCase[^]
PraVeeN
blog.ninethsense.com/
|
|
|
|
|
Dont mean to be picky but could you clarify point 9 i.e. Stored procedure give you performance benefits over programming in code
|
|
|
|
|
|
|
"excellent" as opposed to "superior". Ad hoc queries also "result in excellent performance".
There are more important things than performance.
There are also different ways of measuring performance.
|
|
|
|
|
I don't know what kind of database you are using. Is it SQL server? Are you quering large tables? If you are you will notice there is a big difference. Try using the SQL profiler.
If your creating a large scale solution you will quickly realise that database performance is everything. Why? Because its simple. Web servers can be clustered, database servers cannot. If you have a bottleneck in the database -> your doomed. So build your database smart and think about performance and build your application to save performance on the database by implementing caching in your code. When the web servers get to jammed by taffic,add a couple more. Problem solved.
Jarle
|
|
|
|