I need to parameterize against sql injection in asp classic, what things should I take some time to get to know before I start making changes? (coming from php)

Question

I can already see that I'm not going to enjoy the experience, but I have to do some sql cleanup on this 1000 file asp classic web-app without any prior knowledge of asp, and before I get to hacking away at it I'd like to be aware of any major gotchas to watch out for while coding in asp classic/sql parameter preparing/making asp whitespace modifications. What are some good quick overview resources, and what should I watch out for?

Links to web resources welcomed.

Answer 1

Make sure you are not using string concatenation to add parameter values to a SQL query. Learn how to use ADO Command and Parameter objects. Always use placeholders in the SQL query string, and add Parameter objects to your Command to provide the value for the placeholders.

Answer 2

Agreed... Parameterized querying via place-holder and ex: SqlDb.Command.Parameters.Add() would be a big help... Don't just rely on selects, you can also have injection with insert and deletes too.

Answer 3

1. I would create a function that encapsulates all or most of data access. In previous projects, I have created a GetRecordset function that takes a SQL statement and returns a Recordset instance. In the function, I open the database, execute the query, close the database and return the recordset. This ensures that connections get closed.

2. I would create a function for cleaning parameters to a SQL statement or even better is to use parameterized queries. In code where I did not want to rewrite queries and thus was using concatenation, the function I would use required a vbVarType parameter so that I can verify that the value passed is of the type indicated and to ensure that dates are put in the format that is not specific to the culture of the server.

3. I would search for instances of a single quote followed by a double quote. Here you are looking for Select ... Where StringOrDateCol = '" & Request.QueryString("GodKnowsWhat") & ...

Even with all of that, you will not catch everything. For example, you would not catch Select ...Where NumericCol = " & Request.QueryString("GodKnowsWhat"). The final search might be to search on Select, Update, Insert and Delete and inspect each SQL statement to ensure it uses the function you created in #2 above.