(security) Rules of thumb for escaping user input?

I'm a relative novice to SQL in general.  I'm a little on edge right now
because I recently had a frightening conversation with a friend who's a
security analyst about the standard ways to hack into SQL-using apps,
and I'm wondering what steps I need to take to prevent similar attacks
against my own apps.

My friend gave me a verbal walkthrough of an exploit he had demonstrated
to a Major Software Company of how their app allowed an unprivileged
user to trash the database server machine (running MS SQL Server)
because it didn't properly escape user-inputted strings before
sprintf-ing them into queries.   By a combination of single quotes,
double quotes, and comment characters introduced into the fields of a
form, he was able to break out of the formatted query and have the
server execute SQL that he typed as part of the form.   Apparently the
default install of SQL Server enables a server side scripting language
that permits the running of arbitrary programs on the server... so he
was basically able to type 'format c:' or something equally clever into
a form and have it go to town :(

So the only escaping I do in my app currently is to replace ' with '' in
user-input strings.  If I assume that the goal is to prevent any
user-input strings from being evaluated as SQL statements (only to allow
user input as constant values), what other escaping do I need to do?

Is there a SQL "hack prevention" FAQ out there somewhere?

Thanks,
b.g.