Any reason not to use stored procedures for every query?

I se开发者_C百科e most applications generating queries within PHP when it seems so much neater to designate stored procedures for direct data access. Is there any reason to avoid stored procedures?

This is almost a religious discussion depending on who you ask. There is a delicate balance that developers & dbas have to reconcile in these types of cases.

Basically here's the thinking you really want to put in play:

If PHP code is using dynamic SQL, protections against SQL injection attacks must be considered at all times. Inputs MUST be sanitized before putting to the database.

If PHP code is using dynamic SQL but the convention is to use prepared statements then you're safer to some degree but care must be taken in how the prepared statements are used.

If PHP code is using stored procedures, much of the control of the SQL is removed from the developer and left up to the DBA to work with the developers to provide an adequate solution that meets their needs. This unfortunately can cause much time and effort and going back and forth on development/maintenance.

It's one of those things that is a gray area and there are many schools of thought on which side of the equation is right because they both are from certain points of view.

There are reasons for and against.

Nowadays, the line is blurring with things like Command-Query Separation. For example, you can query my tables however you like with an ORM but you will use stored procs for write.

Are you in a corpoarate shop with a standard DB platform? Or a vendor who has to support several DB engines?

Data outlives code: you'll refactor your DAL repository every year or so: stored procs don't have to change

You can provide a consistent "API" for several clients: we have external c#, Excel, Java + our own c#.

A stored proc is a method, arguably: why not encapsulate your table?

Links:

• Why use Stored Procedures? (external)
• Recent SO
• Another SO

Nothing as such. It's just a matter of programmer's comfort as far as i see it.

Two main things to learn though is that Stored procedures are much faster than in-code queries but they involve a bit(just a bit) more code. In-code queries are the way forward(as people say), thats why LINQ in .NET is introduced. They enable you to sway from n-tier architecture when not important.