SQL Injection Prevention

Question

I am currently working on an old ASP project where security has now become a big issue. These are not only unsafe encryption methods (md5), but I am worried about problems with SQL injections. I am still not very good at injecting, and I have only tried the basics of what I know. I found a function that “protects” any user input, but I wonder if it really does something 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 how this was done many years ago when I first started PHP with mysql_ * functions, but now I switched to PDO and parameter binding. However, I do not know how safe this is for ASP applications. Thanks for any input.

Answer 1

Do not fall into the trap of string interpolation! It is not safe.

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 the ADODB.Command object to parameterize a SQL query and bind 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 examples of using named parameters:

ASP Classic named parameter in a parameterized query: must declare a scalar variable

Answer 2

This is as close as possible 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?

Answer 3

Line

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

does most of the work. What I did for secure sites are several different functions for each data type.

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

Replacing dynamic stored procedures and removing all permissions, except that execution protects the environment independently.

Answer 4

PDOs and prepared statements are the best way to prevent SQL injections. Manually writing the sanitation SQL code, like the code above, is significantly more dangerous, as you can easily skip.

Using prepared statements will make SQL statements safe.