CODEWITHSUNDEEP
mysqlsqlasp-classicsql-injection
Jason Kaczmarsky
Jason Kaczmarsky

Reputation: 1686

SQL Injection Prevention

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.

Upvotes: 4

Views: 970

Answers (4)

Randy Pitkin
Randy Pitkin

Reputation: 31

The line 

input = replace(input , "'", "''")

is doing most of the work. What I have done for secure sites is several distinct functions for each datatype

fn_validstring replacing single quotes
fn_validnumber testing isnumeric 
fn_validint leveraging fn_validnumber and rounding
fn_bool 
etc ...

Replacing dynamic with stored procedures and removing all permissions except execute secures environment regardless.

Upvotes: 1

ThatGuyInIT
ThatGuyInIT

Reputation: 2239

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?

Upvotes: 3

Bill Karwin
Bill Karwin

Reputation: 562330

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

Upvotes: 6

Oleksi
Oleksi

Reputation: 13097

PDO and prepared statements are the best way to prevent SQL injections. Hand-writing SQL sanitization code like the code above is significantly more dangerous since there's a lot you can miss easily.

Using prepared statements will make the SQL statements secure.

Upvotes: 0

Related Questions