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

i maintainer (but thankfully not creator) of old, large , badly written classic asp site electronics manufacturer.

security joke. thing done sanitize input before throwing mouth of mysql:

function txtval(data)     txtval = replace(data,"'","'")     txtval = trim(txtval) end function  productid = txtval(request.querystring("id")) sql = "select * products id = " & productid set rs = conn.execute(sql) 

because of that, site unfortunately (but perhaps not surprisingly) victim of sql injection attacks, of them succesful.

the simple means taken above not enough. nor using server.htmlencode. escaping slashes doesn't either attacks quite sophisticated:

 product.asp?id=999999.9+union+all+select+0x393133353134353632312e39,0x393133353134353632322e39,0x393133353134353632332e39,0x393133353134353632342e39,0x393133353134353632352e39,0x393133353134353632362e39 

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

 microsoft ole db provider odbc drivers error '80004005'  [mysql][odbc 5.3(w) driver][mysqld-5.1.42-community] used select statements have different number of columns  /product.asp, line 14 

this means injection made through in case did not succeed in getting data. others do, however.

the site consists of hundreds of asp files spaghetti code summing many thousands of lines without structure. because of not option go parameterized queries. work enormous , error prone well.

one thing though input parameters in code consistently passed through txtval function, here chance better augmenting function. also, since sql calls done conn.execute(sql) quite straightforward search , replace eg. conn.execute(sanitize(sql)) here chance too.

given circumstances, options prevent or @ least minimize risc of sql injection?

any input appreciated.

updates:

1. understand parameterized queries correct way handle problem. use myself when create websites. given way site built , size of it, take 1-2 months modify, test , debug it. if end (which doubt) need right now.

2. replacement html entity not typo. replaces single quote html entity. (i didn't make code!)

3. in specific example above, using cint(id) solve problem, anything, not numerical inputs.

update 2:

ok, know not asking correct solution. knew start. that's why wrote "given circumstances".

but still, filtering inputs mysql keywords select, union etc @ least make better. not good, little bit better. , asking for, ideas make little bit better.

although appreciate comments, telling me option use parameterized queries doesn't help. because know :)

i wouldn't give on parameterized queries. single best tool can use protect sql injection. if plan replace of these calls:

conn.execute(sql) 

to these calls:

conn.execute(sanitize(sql)) 

then you're looking @ modifying each interaction sql (btw, don't forget command.execute() , recordset.open(), may used run sql statements). , since you're planning on changing these calls, consider calling custom function run statement. example, replace:

set rs = conn.execute(sql) 

with:

set rs = myexecute(sql) 

and use custom function set proper parameterized query using command object instead. you'll need cleverly parse sql statement in custom function. identify values in where clause, determine type (perhaps can query table schema), , add parameters accordingly. can done.

you can take opportunity sanitize input. use regexp object strip [^0-9\.] numeric fields, example.

but there's still opportunity you'll return recordset function used write values directly page without being html-encoded first. that's real concern, since sounds site has been targeted in past. wouldn't trust data coming database. option see here (that wouldn't involve touching every page) return "clean", html-encoded recordset instead of default one.

unfortunately, you're still not out of woods. xss attacks can done via querystring parameters, cookies, , form controls. how safe going feel after "fixing" sql injection issues knowing xss still real possibility?

my advice? explain supervisor security threats plaguing site , convince him/her need thorough review or complete rewrite. may seem lot of resources throw @ "old, already-working website", moment defaces website or truncates database tables, you'll wish invested time.