Stored Procedure Security

All modern database engines provide support for stored procedures or prepared statements, database commands (or more frequently, routines containing multiple commands) that are stored in the database itself alongside the data. (MySQL was the last holdout, and didn’t implement stored procedures until version 5.0, which was released in October 2005.) Using stored procedures in your application can improve it in many ways. Stored procedure code can be easier to maintain than dynamic SQL built into the application source code, since developers and database administrators (DBAs) can make changes to stored procedures without having to recompile the application. Stored procedures also often have much better performance than dynamic SQL, since the database engine can compile and optimize the stored procedure code. And finally, using ....application too. In this section, we’ll take a look at some best security practices around the use of stored procedures, and point out some misconceptions and pitfalls to avoid, too.