Ad Hoc SQL vs Stored Procedures

Well, i had the pleasure to assist to a Michael Li's conference about Security using ASP.Net in Delphi 2005.

One of the topics was the famous "SQL Injection" menace. I felt bad initially cause i use Ad Hoc SQL on my everyday, is extremely versatile, and you can build great search queries at run time. More static operations like reporting, specific updates and very plain searchs on tables are usually perfect places for stored procedures.

Maybe a difference is that i use parameterized ad Hoc sql, never pure text insertion (eg. "select * from users where id = '+id.text+'"), but i must admit that for user validation procedures i use stored procs.

I think that for abstraction of tables in a program, there is nothing better than ad Hoc, I never considered appropiate to have hundreds of sps to manage every single update. Constant changing databases will prove to be a hell for the hundreds of sps that depends from that table that you just changed.

Well, after that conference, i decided to do a little research about this topic and i found a great "good and bad" discussion about it.

Check it here at the Server Side, it brings some light to both sides of the discussion.

Good to read, enjoy.


Pst. btw, I will keep using my ad hoc queries. :)