classic ASP protection against SQL injection

Question

I've inherited a large amount of Classic ASP code that is currently missing SQL injection protection, and I'm working on it. I've examined in detail the solutions offered here: Classic ASP SQL Injection Protection On the database side, I have a Microsoft SQL server 2000 SP4

Unfortunately stored procedures are not an option.

After studying php's mysql_real_escape_string ( http://www.w3schools.com/php/func_mysql_real_escape_string.asp ) , I've replicated its functionality in ASP.

My question(s) are:

1) Does Microsoft SQL server 2000 have any other special characters that need to be escaped that are not present in MySQL ( \x00 , \n , \r , \ , ' , " , \x1a )

2) From an answer in Can I protect against SQL Injection by escaping single-quote and surrounding user input with single-quotes? I read "One way to launch an attack on the 'quote the argument' procedure is with string truncation. According to MSDN, in SQL Server 2000 SP4 (and SQL Server 2005 SP1), a too long string will be quietly truncated."

How can this be used for an attack (I really can't imagine such a scenario) and what would be the right way of protecting against it?

3) Are there any other issues I should be aware of? Any other way of injecting SQL?

Note: A 30-min internet search said that there are no libraries for classic ASP to protect against SQL injection. Is this so, or did I really fail at a basic task of searching?

Answer 1

I use two layers of defense:

• create a 'cleanparameter' function, and every call that gets from querystring or form values, use it calling that function. The function at the very least should replace simple quotes, and also truncate the string to a value you pass. So, for example, if the field can't be longer than 100 chars, you would call it like x = cleanparameter(request.querystring("x"), 100). That's the first line of defense
• Use parameterized queries to run SQL instructions

Answer 2

The best option is to use parameterized queries. On how that is done, you must check out:

• SQL Injection Mitigation: Using Parameterized Queries

In PHP also, the PDO (and prepared statements) allows developers to use parameterized queries to avoid sql injection.

---

Update

Yes you can specify parameters in WHERE clause and for that you can use ADODB.Command object like below example:

' other connection code
set objCommand = Server.CreateObject("ADODB.Command") 
...

strSql = "SELECT name, info FROM [companies] WHERE name = ?" _ 
    & "AND info = ?;" 
... 
objCommand.Parameters(0).value = strName 
objCommand.Parameters(1).value = strInfo 
...

For more information, see the article link that I have posted above or you may want to research a little more on the topic if you want.