I favor stored procedures (MySQL has had stored procedures support since 5.0) from a security point of view - the advantages are -
- Most databases (including MySQL) enable user access to be restricted to executing stored procedures. The fine grained-grained security access control is useful to prevent escalation of privileges attacks. This prevents compromised applications from being able to run SQL directly against the database.
- They abstract the raw SQL query from the application so less information of the database structure is available to the application. This makes it harder for people to understand the underlying structure of the database and design suitable attacks.
- They accept only parameters, so the advantages of parameterized queries are there. Of course - IMO you still need to sanitize your input - especially if you are using dynamic SQL inside the stored procedure.
The disadvantages are -
- They (stored procedures) are tough to maintain and tend to multiply very quickly. This makes managing them an issue.
- They are not very suitable for dynamic queries - if they are built to accept dynamic code as parameters then a lot of the advantages are negated.