Re: SQL Injection

Silvio Genco wrote:
> 
> http://www.mydomain.com/products/products.asp?productid=123 or 1=1
> The corresponding SQL Statement is:
> 
> SELECT ProductName, Product Description From Products WHERE ProductNumber =

> 123 OR 1=1
> 
> I'm interested about your opinions and/or solutions

Hello Silvio,

In dbase applications an SQL select is not the only way to filter data. I always

use findKey() or setRange() so this malicious command will not work.

For those who prefer SQL filters, a simple validation should work.

    if "or" $ oCGI['productid']
       oCGI.sorryPage()
    endif

Extract from some article in internet:

When a user enters the following URL:

http://www.mydomain.com/products/products.asp?productid=123
The corresponding SQL query is executed:

SELECT ProductName, ProductDescription FROM Products WHERE ProductNumber = 
123
An attacker may abuse the fact that the ProductID parameter is passed to the 
database without sufficient validation. The attacker can manipulate the 
parameter's value to build malicious SQL statements. For example, setting 
the value "123 OR 1=1" to the ProductID variable results in the
following 
URL:

http://www.mydomain.com/products/products.asp?productid=123 or 1=1
The corresponding SQL Statement is:

SELECT ProductName, Product Description From Products WHERE ProductNumber = 
123 OR 1=1
This condition would always be true and all ProductName and 
ProductDescription pairs are returned. The attacker can manipulate the 
application even further by inserting malicious commands

Some basic counsils are to che validate the strings replacing dangerous 
words where not expressly requested..

 RequestCheck = replace(request(var),"'", "''")
 RequestCheck = replace(request(var),"drop", "")
 RequestCheck = replace(request(var),"insert", "")
 RequestCheck = replace(request(var),"update", "")
 RequestCheck = replace(request(var),"xp_", "")

This is applicable to any ASP,PHP page and I believe in DBW too

I'm interested about your opinions and/or solutions

Thanks
Silvio 

Silvio Genco wrote:
> I'v here still some ASP pages where authenticate login to a table, so 
> searching in internet I'v found this good function:

In dBL you can write this function too:

? FixSQL("drop [or% insert 'and_] update xp_")
Function FixSQL(stringa)
    local oRegExp
    oRegExp = new OleAutoClient("VBScript.RegExp")
    oRegExp.global  := true
    oRegExp.Pattern := "drop|insert|update|xp_"
    stringa=oRegExp.Replace(stringa , "" )
    oRegExp.Pattern := "'"
    stringa=oRegExp.Replace(stringa , "''" )
    oRegExp.Pattern := "(%|\[|\]|_|#)"
    stringa=oRegExp.Replace(stringa , "[$1]" )

> In dbase applications an SQL select is not the only way to filter data. I 
> always use findKey() or setRange() so this malicious command will not 
> work.
>
> For those who prefer SQL filters, a simple validation should work.
>
>    if "or" $ oCGI['productid']
>       oCGI.sorryPage()
>    endif


These seems a god point of view...
I'v here still some ASP pages where authenticate login to a table, so 
searching in internet I'v found this good function:

Function FixSQL(stringa)
stringa = Replace(stringa, "'", "''")
stringa = Replace(stringa, "%", "[%]")
stringa = Replace(stringa, "[", "[[]")
stringa = Replace(stringa, "]", "[]]")
stringa = Replace(stringa, "_", "[_]")
stringa = Replace(stringa, "#", "[#]")
stringa = Replace(stringa, "drop", "")
stringa = Replace(stringa, "insert", "")
stringa = Replace(stringa, "update", "")
stringa = Replace(stringa, "xp_", "")
FixSQL = stringa
End function

obviouselly this can be applied to Dbase web application using your method:
if "%" $ oCGI['productid'] or "drop" $ oCGI['productid']  //
 and so on...
    oCGI.sorryPage()
endif

Silvio

> In dBL you can write this function too:
>
> ? FixSQL("drop [or% insert 'and_] update xp_")
> Function FixSQL(stringa)
>    local oRegExp
>    oRegExp = new OleAutoClient("VBScript.RegExp")
>    oRegExp.global  := true
>    oRegExp.Pattern := "drop|insert|update|xp_"
>    stringa=oRegExp.Replace(stringa , "" )
>    oRegExp.Pattern := "'"
>    stringa=oRegExp.Replace(stringa , "''" )
>    oRegExp.Pattern := "(%|\[|\]|_|#)"
>    stringa=oRegExp.Replace(stringa , "[$1]" )
>    return stringa

I'll try it.

Thanks for the detailed example Michael
Silvio