What is a good way to sanitize mysql in an old classic ASP site?

Question

I am the maintainer (but thankfully not the creator) of a very old, very large and very badly written classic ASP site for an electronics manufacturer.

Security is a joke. This is the only thing done to sanitize input before throwing it into the mouth of MySQL:

Function txtval(data)
    txtval = replace(data,"'","'")
    txtval = trim(txtval)
End Function

productid = txtval(Request.QueryString("id"))
SQL = "SELECT * FROM products WHERE id = " & productid
Set rs = conn.execute(SQL)

Because of that, the site is unfortunately (but perhaps not surprisingly) victim of SQL injection attacks, some of them succesful.

The simple means taken above is not nearly enough. Nor is using Server.HTMLEncode. Escaping slashes doesn't help either as the attacks are quite sophisticated:

product.asp?id=999999.9+UnIoN+AlL+SeLeCt+0x393133353134353632312e39,0x393133353134353632322e39,0x393133353134353632332e39,0x393133353134353632342e39,0x393133353134353632352e39,0x393133353134353632362e39

The url above (an arbitrary attempt taken from the access log) gives the folling response from the site:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[MySQL][ODBC 5.3(w) Driver][mysqld-5.1.42-community]
The used SELECT statements have a different number of columns

/product.asp, line 14

This means that the injection made it through but in this case did not succeed in getting any data. Others do, however.

The site consists of hundreds of ASP files with spaghetti code summing up to many thousands of lines without much structure. Because of that it is not an option to go for parameterized queries. The work would be enormous and error prone as well.

One good thing though is that all input parameters in the code are consistently passed through the txtval function, so here is a chance to do it better by augmenting the function. Also, since all SQL calls are done with conn.execute(SQL) it is quite straightforward to search and replace with eg. conn.execute(sanitize(SQL)) so here is a chance to do something about it too.

Given the circumstances, what are my options to prevent or at least minimize the risc of SQL injection?

Any input is much appreciated.

Updates:

1. I do understand that parameterized queries is the correct way to handle the problem. I use that myself when I create websites. But given the way the site is built and the size of it, it will take 1-2 months to modify, test and debug it. Even if that is what we end up with (which I doubt) I need to do something right now.

2. The replacement with the html entity is not a typo. It replaces single quote with its html entity. (I didn't make the code!)

3. In the specific example above, using CInt(id) would solve the problem, but it could be anything, not only numerical inputs.

UPDATE 2:

Ok, I know that I am not asking for the correct solution. I knew that from the start. That's why I wrote "Given the circumstances".

But still, filtering inputs for mysql keywords like select, union etc would at least make it better. Not good, but a little bit better. And this is what I am asking for, ideas to make it a little bit better.

Although I appreciate your comments, telling me that the only good option is to use parameterized queries doesn't really help. Because I know that already :)

Answer 1

I wouldn't give up on parameterized queries. They are the single best tool you can use to protect yourself from SQL Injection. If your plan is to replace all of these calls:

conn.execute(SQL)

to these calls:

conn.execute(sanitize(SQL))

then you're already looking at modifying each interaction with SQL (BTW, don't forget Command.Execute() and Recordset.Open(), which may also be used to run SQL statements). And since you're already planning on changing these calls, consider calling a custom function to run the statement. For example, replace:

set rs = conn.execute(SQL)

with:

set rs = MyExecute(SQL)

and then use your custom function to set up a proper parameterized query using a Command object instead. You'll need to cleverly parse the SQL statement in this custom function. Identify the values in the where clause, determine their type (perhaps you can query the table schema), and add parameters accordingly. But it can be done.

You can also take this opportunity to sanitize the input. Use a RegExp object to quickly strip [^0-9\.] from numeric fields, for example.

But there's still the opportunity that you'll return a recordset from this function that will be used to write values directly to the page without being HTML-encoded first. That's a real concern, especially since it sounds like your site has already been targeted in the past. I wouldn't trust any data coming from your database. The only option I see here (that wouldn't involve touching every page) is to return a "clean", HTML-encoded recordset instead of the default one.

Unfortunately, you're still not out of the woods. XSS attacks can be done via QueryString parameters, cookies, and form controls. How safe are you going to feel after "fixing" the SQL Injection issues knowing that XSS is still a very real possibility?

My advice? Explain to your supervisor the security threats plaguing your site and convince him/her the need for a thorough review or a complete rewrite. It may seem like a lot of resources to throw at an "old, already-working website", but the moment someone defaces your website or truncates your database tables, you'll wish you invested the time.