How fix sql injection issue for the order by clause

Question

How can I resolve SQL injection issue for order by clause in the classic asp application?

Here is the demo code:

strSort=request("sort")
strSQL="select * from table1"
select case strSort
case "case1"
   strSQL=strSQL & "order by " & strSort
case "case2"
   strSQL=strSQL & "order by" & strSort
end select

score 1 · Answer 1 · edited Mar 17 '17 at 13:14

1

You should always use compiled queries / known also as prepared statements. They will simply do the job of validating the values for you, adding a layer of security to your application.

Please note that this still doesn't ensure at 100% that your code won't be vulnerable to SQL injection attacks, but it's already a good basis for security as SQL injection becomes much more complicated to be exploited with prepared statements. You can read more here about these advanced attacks.

So, in the end, keep in mind that as long as you're dealing directly with strings you won't be safe at all and you will have to check manually the validity of the parameter. And you simply can't do this without built-in objects/functions because you have too many characters to deal with (Unicode, etc...).

For instance this article will explain you why it's not enough to naively escape only the quotes.

So you just can't deal with the problem without sanitizing the string before the use.

NOTE: Classic ASP solution

In classic ASP what I introduced above is called Type-Safe SQL Parameters. The Parameters collection in SQL Server provides type checking and length validation. If you want to use type-safe SQL parameter you can read more about them here.

edited Mar 17 '17 at 13:14

Community

1
1

answered Dec 14 '15 at 13:26

Alex Gidan

2,619
17
29

can't I create the parameter for the strSort and Use it?? @Alex Gidan – sadanand Dec 14 '15 at 13:31
@sadanand, as long as you're dealing directly with strings you won't be safe at all and you will have to check manually the validity of the parameter. And you simply CAN'T do this because you have many, many characters to escape (Unicode, etc...). You just can't deal with the problem without sanitizing the string before the use. – Alex Gidan Dec 14 '15 at 13:33
well thanks for the suggestion, I will follow up with the link which you given. Thanks a lot.. – sadanand Dec 14 '15 at 13:41
@sadanand if you're using classic ASP I added a pointer to the right APIs. – Alex Gidan Dec 14 '15 at 13:47
@AlexGidan: That's not entirely true. I can agree that the best method for posting and obtaining data from a database is via the use of stored procedures, however, the query `SELECT afield, bfield FROM mytable` is impervious to SQL injection if the site itself is properly protected. – Paul Dec 14 '15 at 13:49
@sadanand: You should be using a mixture of [stored procedures and parameters](http://stackoverflow.com/questions/6367375/classic-asp-stored-procedures-parameters). – Paul Dec 14 '15 at 13:51
@Paul, the question is about a `select *` with a concatenation at the end of the query! Moreover, impervious doesn't mean not easily exploitable... Not sanitizing the SQL parameters is just reckless. – Alex Gidan Dec 14 '15 at 13:53
@sadanand, again, the answer you chose as correct is not safe _at all_. – Alex Gidan Dec 14 '15 at 13:58
@Alex Gidan, you mean the final solution will be sanitizing the string which is page getting as request? – sadanand Dec 14 '15 at 14:04
@sadanand yes, you have to sanitize `strSort` in your example – Alex Gidan Dec 14 '15 at 14:13

score 0 · Accepted Answer · answered Dec 14 '15 at 13:51

This is worked for me, the connection object Conn comes from global.asa

<%
    Dim Rs 
    Dim Sql
    Dim TableName
    Dim Cmd

    Const adParamInput = 1
    Const adVarChar = 200

    Set Cmd = Server.CreateObject("ADODB.Command")

    TableName = Request("table")

    Sql = "select table_name from user_tables where upper(table_name) = upper(?) "

    Cmd.ActiveConnection = Conn
    Cmd.CommandText = Sql

    Cmd.Parameters.Append cmd.CreateParameter("table_name", adVarChar, adParamInput, Len(TableName), TableName) 

    Set Rs = Cmd.Execute()

    Do While Rs.EOF = False 
        Response.Write Rs("table_name") & "<br />"
        Rs.MoveNext
    Loop

    Rs.Close 
    Set Rs = Nothing 
%>

The database field type list is here: http://www.w3schools.com/asp/met_comm_createparameter.asp I think it is possible to get it predefined by installing them to IIS in "add programs and features" — cem, Dec 14 '15 at 13:56

score -2 · Answer 3 · answered Dec 14 '15 at 13:33

-2

There is no any real anti-injection security in Classic ASP.

 <%
 Function Inj(ByVal Str)
    REM For Oracle DB
    Str = Replace(Src, "'", "''")
    Str = Replace(Src, "&", "' || '&' || '")
    Inj = Str
 End Function

 strSort = Inj(request("sort"))
 strSQL="select * from table1"
 select case strSort
 case "case1"
    strSQL=strSQL & "order by '" & strSort & "'"
 case "case2"
    strSQL=strSQL & "order by '" & strSort & "'"
 end select
 %>

answered Dec 14 '15 at 13:33

cem

1,535
19
25

This doesn't protect from most SQL injection attacks. E.g. [Unicode based](http://security.stackexchange.com/questions/54734/sql-injection-via-unicode) attacks. More [here](http://siderite.blogspot.com/2013/01/why-doubling-single-quotes-is-not.html). – Alex Gidan Dec 14 '15 at 13:35
This is the only one way to do it then: http://stackoverflow.com/questions/18523637/asp-classic-sql-multiple-parameters – cem Dec 14 '15 at 13:41
Yes, for classic ASP it seems a good candidate to be used to sanitize the SQL parameters. I added the pointer to complete my answer. Thanks. – Alex Gidan Dec 14 '15 at 13:49
Not true - use [stored procedures and parameters](http://stackoverflow.com/questions/6367375/classic-asp-stored-procedures-parameters). – Paul Dec 14 '15 at 13:50
Using stored procedures and parameters is almost same as using query with ADODB.Command. Please check my last code below. – cem Dec 14 '15 at 13:54
@cem: You need to use the ADODB.Command object for SPs and params... – Paul Dec 14 '15 at 14:47
@AlexGidan: You're asssuming that cem actually uses NVARCHAR fields, though. If he doesn't, and treats the string as an ANSI/ASCII string, then there's no real threat from Unicode attacks. – Paul Dec 14 '15 at 14:53
@Paul You can use ADODB.Command for the queries as well. – cem Dec 15 '15 at 09:52

How fix sql injection issue for the order by clause

3 Answers3