Last night's Atlanta Microsoft Database Forum meeting had another discussion of SQL Injection and how it is a bad thing. In the end, the best thing to do to avoid it is to use parameterized queries (which include, but are not limited to Stored procedures). In short, if you write your data access code in your application like the following, you are prime for an attack:
dim stSql as string = "select * from users where userid='" & Request("UserId") & "'"
Instead, take a little more care and use the following (please pardon any typo's as CS's intellisense is not working at the moment ;-):
dim cmd as SqlCommand = cn.CreateCommand()
cmd.CommandText = "select * from users where userid=@userid"
This takes care of the issue of SQL injection. Naturally, if you take a parameter into a stored procedure which uses SP_ExecuteSQL internally, you are still owned. Regardless, avoiding SQL injection is just the tip of the iceberg.
If you are not validating your input values to only accept valid values (don't waste your time trying to filter out the wrong values as you will likely miss somthing like unicode encoding/decoding issues), you are still suspect to cross-site scripting attacks and the like.
For a quick video of what can be done, I strongly recommend you check out Rocky Heckmann's Assembly Hijacking Attack screen cast. It's only about 20 minutes and he goes as far as showing how to replace a server side dll. It's one of the best security video's I've seen so far.