Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pacha1
Starting Member
4 Posts |
 Posted - 2004-10-09 : 10:50:21
|
From an expert point of view, in server-side scripting (ASP,PHP,etc) for business application are you guys only using stored procedure for call with the database or you use dynamic sql ? depsite the performance side, only for the security aspect. I know/think its better use Stored procedure all the way but I think they are pain in the ass...Give me your toughs  |
|
|
pacha1
Starting Member
4 Posts |
Posted - 2004-10-09 : 10:52:26
|
| Oh yeah, i forgot... do some of you use both ? like stored procedure for input related sql query and dynamic sql for any data that come from the users session (after secure logon) ? |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-10-09 : 11:42:52
|
| SQL code embedded in web code is always less secure than a stored procedure. Yes, you can write an insecure stored procedure, and yes, you can write pretty secure embedded SQL, but either circumstance only enhances the problem/benefit of the other. It is a good practice to ALWAYS use stored procedures for ALL interaction with a database, even if it requires a lot of extra coding. Dynamic SQL can be used in stored procedures too, but it usually defeats the purpose of having a procedure.The encapsulation and isolation of database code, considered an important part of multi-tiered application design, benefits greatly from stored procedure use. Embedded SQL code in another layer does not accomplish this. Stored procedures can be re-used by any layer with minimal coding, and provide a solid interface for the database. Security and data integrity is enhanced as well, and performance is almost always better with stored procedures.The problem with SQL, for non-SQL developers, is getting used to thinking in set-oriented ways. Most programmers write cursor-based apps that perform badly, especially in SQL Server, and either give up or never learn how to use SQL effectively. If you don't know SQL well or don't feel comfortable with it, either dig into it yourself or work with someone who does know it, but whatever you do, use stored procedures as much as you can. Your goal should be 100% stored procedure and zero embedded SQL code. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-10-09 : 13:39:53
|
| Well said...And buy some books...look at the online store at this site...And visit here often...just reading the questions is an education in itself...Then there are guys like Rob....(and others) whom it appears could have written the damn thing...Brett8-) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-10-11 : 07:20:15
|
| I used to do client-side dynamic SQL. Now we only do Stored Procedures.We had some written for us initially to "give us a leg up" and I though "Wow, what a pain in the arse", so I though about that problem and now I am here to tell you they are far FAR from a PITA!We mecahnically generate all the run of the mill SProcs, and anywhere else, where custom logic is required, the ablity to test and subsequently change the SQL outside[ the application is a huge bonus. Updating the database logic with only SProcs is much easier, and more controlled, than randomly uploading a handful of modified ASP files! But if the web sites in question can cope with piecemeal updates and some lost of service to users during the operation then this may not be too important.Performance and security is pretty important to me - our tests show SProcs outperforming dynamic SQL but a huge margin; our clients would be really annoyed if their mediocry little websites suddenly got some good publicity, loads of traffic, and then fell over in a heap because they couldn;t handle the throughput!Kristen |
|
|
|
|
|
|
|
|
Stored procedure for SQL Injections,