Prevent SQL injection on free response text fields in classic ASP

Question

I've got some free-response text fields and I'm not sure how to scrub them to prevent SQL injection. Any ideas?

Answer 1

Create a parameterized query instead of concatenating the user's input into the query.

Here is how to do this in classic asp: http://blog.binarybooyah.com/blog/post/Classic-ASP-data-access-using-parameterized-SQL.aspx

It's also important to note that the only way you can be 100% safe from sql injection is to parameterize any sql statement that uses user input, even once it's in the database. Example: Say you take user input via a parameterized query or stored procedure. You will be safe on the insert, however you need to make sure that anything down the road that uses that input also uses a parameter. Directly concatenating user input is a bad idea anywhere, including inside the db.

Answer 2

Call a stored procedure.

EDIT: Just to clarify. Building dynamic sql in a sp can of course be just as dangerous as doing it in the app, but binding user inputs into a query will protect you against sql injection, as described here (oracle-specific discussion, but the principle applies elsewhere):

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:23863706595353

It is not dynamic sql that is the issue (all sql is dynamic in Oracle actually -- even static sql in pro*c/plsql!). It is "the construction" of this sql that is the problem. If a user gives you inputs - they should be BOUND into the query -- not concatenated. The second you concatenate user input into your SQL -- it is as if you gave them the ability to pass you code and you execute that code. Plain and simple.