October 10, 2007
Sanitize Your Database Inputs!
From www.xkcd.org
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 treatval ' or '1'='1asval ''— 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.
Comments
paul — October 29, 2007
Stored procs are all well and good but if the end user doesnt have permission to execute statements like that in the first place
You can remove the ability to use ddl in ad hoc statements
just give them select permissions if thats what they are doing right ^ ^ Least priveledge > all