Stephen A. Fuqua (SAF) is a Bahá'í, software developer, and conservation and interfaith advocate in the DFW area of Texas.

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:

  1. 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'='1 as val '' — with the apostrophe escaped and all the characters beyond 5 dropped (or even rejected as an error).
  2. Completely separates database and application logic.
  3. Makes deployment of database changes and fixes much simpler (compared to redeploying application code, especially in a client-server environment).
  4. 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.

1 Comment

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