SQL Injection Prevention

Question

I'm currently working on a legacy ASP project where security has now become a large concern. Not only is it insecure encryption methods (md5), but I'm worried about SQL injection problems. I'm not very good with injection quite yet, and I've tried only the basics of what I know. I've found the function which "secures" any user input, but I'm wondering if it is actually doing anything to prevent injection attacks. Here is the function:

function sqlfix(input)
    if not isnull(input) and input <> "" then
        input = replace(input, ";", "&#59;")
        input = replace(input, "'", "&#39;")
        input = replace(input, """", "&#34;")
        input = replace(input, "(", "&#40;")
        input = replace(input, ")", "&#41;")
        input = replace(input, "|", "&#124;")
        input = replace(input, "<", "&#60;")
        input = replace(input, ">", "&#62;")
        input = replace(input , "'", "''")
        'input = Server.HTMLEncode(input)
        'input = Server.UrlEncode(input)
        sqlfix = input
    else
        sqlfix = ""
    end if
end function

I remember doing something like this many years ago when I first started PHP with mysql_* functions, but now I've moved onto PDO and parameter binding. However I don't know how safe this is for ASP applications. Thanks for any input.

Answer 1

Don't fall into the string-interpolation trap! It's not secure.

You can use real SQL query parameters even in ASP Classic.

I'm not an ASP programmer, but I found this blog with a clear example of using an ADODB.Command object for a parameterized SQL query, and binding values to parameters before executing.

http://securestate.blogspot.com/2008/09/classic-asp-sql-injection-prevention_30.html

Also see this SO question for some more examples of using named parameters:

ASP Classic Named Parameter in Paramaterized Query: Must declare the scalar variable

Answer 2

This is as close as you can get to PDO in ASP Classic...

with createobject("adodb.command")
    .activeConnection = application("connectionstring")
    .commandText = "select * from sometable where id=?"
    set rs = .execute( ,array(123))
end with

How can I make a prepared statement in classic asp that prevents sql injection?