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.

IIS can also filter sql injection. But as Bill pointed out, the proper way to handle this is to parametize your queries. — Frank, Nov 27 '12 at 09:21

score 6 · Accepted Answer · edited May 23 '17 at 10:29

6

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

edited May 23 '17 at 10:29

Community

1
1

answered Nov 26 '12 at 21:36

Bill Karwin

538,548
86
673
828

Thanks, this is really what my boss needed to hear. We planned on doing a rewrite anyway due to scalability issues, this will only get it into motion faster. – Jason Kaczmarsky Nov 26 '12 at 22:21

score 3 · Answer 2 · edited May 23 '17 at 11:55

3

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?

edited May 23 '17 at 11:55

Community

1
1

answered Nov 26 '12 at 21:41

ThatGuyInIT

2,239
17
20

Good to know that there is a way at least. However I would need to rewrite the thousands of queries in the application which would require all of my time for far too long and be pointless when we planned on doing a rewrite. Thank you though. – Jason Kaczmarsky Nov 26 '12 at 22:23

score 1 · Answer 3 · edited Nov 26 '12 at 22:05

1

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.

edited Nov 26 '12 at 22:05

GraphicsMuncher

4,583
4
35
50

answered Nov 26 '12 at 21:36

Randy Pitkin

31
3

1

RE your last sentence, not exactly. If you are concatenating a string to call a stored proc, you are still just as vulnerable to SQL Injection. It's prepared statements/parameterized queries that protect you - whether the SQL code is sprocs, or not. – Andrew Barber Nov 26 '12 at 21:38

score 0 · Answer 4 · answered Nov 26 '12 at 21:32

0

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.

answered Nov 26 '12 at 21:32

Oleksi

12,947
4
56
80

I'm very aware of this and I do this in my own PHP applications, however this site is 8+ years old and I'm the new developer working on it. – Jason Kaczmarsky Nov 26 '12 at 21:33

SQL Injection Prevention

4 Answers4

Linked