Sanitize Your Database Inputs!
October 10, 2007
That's reason number 1 to use stored procedures in your application code — they automatically sanitize your SQL (assuming you aren't dynamically executing statements inside the procedure).
Stephen's top 4 reasons for using stored procedures rather than inline SQL:
- Sanitize's input by completely wrapping the data in the assigned data type, so that a input parameter
@param1 varchar(5)will always treat
val ' or '1'='1as
val ''— with the apostrophe escaped and all the characters beyond 5 dropped (or even rejected as an error).
- Completely separates database and application logic.
- Makes deployment of database changes and fixes much simpler (compared to redeploying application code, especially in a client-server environment).
- If procedures are saved in their own .sql files, makes it easy to re-use bits of code without having to dig through application code.