Do you advocate mysql stored procedures across the board?

[nelsonm]

hi,

I debated on whether to post this question here or on the database sub message board. I decided to post it here since stored procedures need to be called from somewhere and since i'm using php, i thought this is as good a place as any.

I'm starting my first big (at least for me) web based project that requires mysql database integration. The project manager on this job thinks we should use mysql stored procedures for all database access in php. Another programmer feels its not always necessary.

So my question to those of you out there...

Do you advocate mysql stored procedures across the board?

thanks.

[topr8]

I personally do yes, although i came to MySQL from SQL Server where it is standard practice.

It means you can allow the 'web user' permission to execute stored procedures only and this in itself is an extra layer of security.

Combined with using typed, paramaterised variables within the php code to access mySQL this also locks down the database much more securely too.

The other benefits like making the queries centralised through the procedures does help on the rare occasions when you need to change the query slightly for some reason (if you are using it in multiple places on the site) and there are speed benefits from the way mySQL caches the queries i think.

the other thing i like is you can do much more complicated queries, much more easily by simpling calling the sp from php and keeping all the SQL seperate (well it's easier for me that way)

i don't get the benefit of only using them sometimes rather than universally.

[nelsonm]

thanks for responding,

I'll try looking it up, but when you say...

Combined with using typed, paramaterised variables within the php code to access mySQL

what does "parameterized variables" mean?

thanks.

[Dijkgraaf]

Parameterized variables means you are passing any variables you use as parameters to the stored procedures, rather than using the variables to generate SQL commands to execute.

[nelsonm]

That's what i thought, but it seemed too obvious.

thank a lot.