Forum Moderators: phranque
We are are developing a site with php and mysql; we are not sure of the pros
and cons with procedures, anyone can shed some lite?
search on sql injection as a security risk.
+++
plumsauce is spot on about mentioning SQL injection attacks. In my former life, we came across many PHP apps that coded the SQL statement within the comments of the HTML returned from the PHP script, in what appeared to be a crude debugging process. Then, we'd find that it was often easy to do a SQL command by a POST routine.
As you can imagine, this is not desirable. Stored procedures help reduce (but not eliminate) this sort of attack.
I do want to object with the poster above, however: stored procedures have been implemented in the development version of MySQL 5, AFAIK. I cannot attest to the stability of them or MySQL 5, as we don't use MySQL 5.
Stored procedures are generally desirable in a large corporate development enviornment, where one group is soely responsible for the DB, and you're paying a DB Admin. The other group may be programmers, and stricly responsible for the performance of their code. The groups will often fight back and forth about who's code isn't performing up to spec. ;-)
IMHO, for small apps, there's not a heck of a lot of benefit. They aren't necessary for 90% of the things you'll be doing on the web. Often, what may look like a need for stored procedures is really a need for code optimization, or query optimization (you'd be surprise at how much you can achieve by only performing one query per page - we strive to do it here).
Another disadvantage of stored procedures is the annoyance of having to go into the DB and change the procedure every time you might want to debug or modify the query.
Other times, you may just have large amounts of data, and you may need some sort of data warehousing. I don't think MySQL does any of that - you'd have to move to an enterprise level database. Again, we're talking tens GBs of data here.
Just IMHO: You'll KNOW when you need stored procedures. Most web apps simply do not, and there are many code optimizations that can be done before implementing stored procedures.
if it's a one person effort, then the author is
effectively the dba. if not, then *someone* can
be the dba.
right now i am redoing a commerce site that had
200+ stored procs as well as additional triggers.
a project that took about 6 person/years in version
1 has taken me about 2 weeks to translate from
coldfusion to asp/jscript because none of the
stored procedures had to be reworked. additionally,
all of the stored proc calls that were sprinkled
throughout the old app are now centralised into
one include file. this makes it much more likely
that the error recovery routines will be done
properly, purely through economies of scale.
the problem with adhoc statements is that you
end up with every single developer trying out
a different way of getting the same job done
in multiple pages. when you have deadlock
problems this is going to drive you bonkers
trying to find out where it's coming from.
then, you have to revisit every single
occurence of the offending statements and
code around them. this is otherwise known
as the cut and paste, paste, paste ...
school of coding.
besides being technical architect on this thing,
i was also the dba. so a policy that *all* sql
access had to be through stored procedures was
easily enforceable. today, it's paying off.
in case you hadn't guessed, i like sp's :)
++++
when you have deadlock problems this is going to drive you bonkers trying to find out where it's coming from. then, you have to revisit every single occurence of the offending statements and code around them.
*laugh* I hear you plumsauce.
As I said, most people will know when they need SPs. You definitely know that you do. :)
btw, i tried something new tonight.
since i am using a single asp/jscript file for all
db calls, it was possible to enclose a single
call in a try/catch/finally sequence to avoid
crashing out of the jscript. it was also possible
to do a trim on all strings by coding this just
once in the central file.
this is nothing to do with stored procs, but it
follows the same reasoning:
identical code should never appear in more than
one place in an application.(rule #1 for lazy bastards
like me!)
++++
