Escape strings for MS SQL Server 2012

[Brobbl]

Hey guys, I spent the last hours trying to figure out how to properly escape my SQL statement, but everything i find on the web lokks like it's just made for ppl who already know how to do that. I'm neither a regex pro, nor a SQL guru, so i guess I won't figure this stuff out myself...

 

My questions:

1. Has anyone an idea where I can find a good tutorial on how to properly evade injection attacks on MS SQL Server 2012? Also, maybe someone has already done this in AutoIt and I was just too brain-dead to find it?

2. As a temporary workaround, which function can I use to get some piece of code that strips everything but "0-9", "a-z", "A-Z", " ", "'" and "%" from a string?

 

For the exact use, I'm going to get a string from a config file. This string shall be used as a filter in my SQL select statement, but since I neither trust future-self, nor my collegues, I want to make sure the code is safe. 

$customerfilter = "Like '%DHL%'"

 

Thanks in advance

Edited February 4, 2016 by Brobbl
words

[lrstndm]

Local $text = 'your text to replace1234!@#$%'
Local $array = StringRegExp(StringLower($text), '[a-zA-Z0-9%]', 3)
Local $newString = ''
For $i = 0 To UBound($array) - 1 Step + 1
  $newString &= $array[$i]
Next

This is the code I use to replace all unnecessary characters. Where "newString" is the new string. In the second parameter of the StringRegExp method you can place new characters that will not be removed.

For the first questions, can you give me some more information about the background of your program?

Edited February 3, 2016 by lrstndm

[Brobbl]

57 minutes ago, lrstndm said:

This is the code I use to replace all unnecessary characters. Where "newString" is the new string. In the second parameter of the StringRegExp method you can place new characters that will not be removed.

Thanks, that should work. Can't test right now, but I'll send feedback tomorrow.

 

57 minutes ago, lrstndm said:

For the first questions, can you give me some more information about the background of your program?

Yeah, I'm getting that filter from a file. It's inserted into my prepared select statement.

$query = 'SELECT call."Call ID", call."Ihre Referenz", call.Status, call."Lief_ an Name" FROM DB.table call WHERE call."Ihre Referenz" Not Like ''%Payment%'') AND (call."Lief_ an Name"' & $customerfilter & ' ORDER BY call."Call ID"'

This is just a snipped from the query, but you get the idea. It works and I do get my data. I just want to avoid being fired one day because either I or my collegues thought it was funny to mess around with the config file and f**ed up, so I need to get some protection from injections. Also, I want to learn that stuff.

I could hard-code those queries because they won't change more often than twice a year, but I'm a fan of dynamic stuff...

 

Edited February 3, 2016 by Brobbl

[Brobbl]

17 hours ago, lrstndm said:

This is the code I use to replace all unnecessary characters. Where "newString" is the new string. In the second parameter of the StringRegExp method you can place new characters that will not be removed.

Func _SecureQuery($text)
   $temparray = StringRegExp($text, "[a-zA-Z0-9 %"'.-]", 3)
   $returnstring = ""
   For $i = 0 To UBound($temparray) - 1
      $returnstring &= $temparray[$i]
   Next
   Return $returnstring
EndFunc

Works like a charm! Thank you so much

I'm still looking for answers to my first question, though. I'd like to get rid of this temporary solution asap. Or does anybody know if this is completely safe?

Edited February 4, 2016 by Brobbl

[Brobbl]

Any thoughts? I know that my issue is not directly related to AutoIt itself, but maybe someone knows how to do this?

[Jewtus]

Well, personally I use ' in my queries and " for my strings in autoit

EX

$sQuery="Select * from "&$tbl&" where field='"&$value&"'"

 

Its also important to realize what data type SQL is using. For example, if the field is nvarchar you need to use ' but if the field is TEXT you need to use " in SQL. 

So there really isn't a standard on how to do this because it is really specific to the database your connecting to. I use a lot of Regex and StringReplace with my SQL. One that I use all the time is:

if Stringinstr($val,"'") then StringReplace($val,"'","`")

Additionally, don't use quotes to qualify your fields. Use []

EX:

"SELECT call.[Call ID], call.[Ihre Referenz], call.Status, call.[Lief_ an Name] FROM DB.table call WHERE call.[Ihre Referenz] Not Like '%Payment%')"

As for SQL injection, that really only is for URLs if I'm not mistaken so I'm not sure how it relates to autoit. Unless you are accepting CMD line args to pass to your autoit app, it shouldn't affect you. Its more for websites that have connections to DBs... EX: https://duckduckgo.com/?q=

Edited February 4, 2016 by Jewtus

[Brobbl]

Thank you very much @Jewtus. I think that's good advice, I'll definitely adapt my script to that. It's also easier to read

For your advice on the StringReplace(), what is the specific reason for having " ` " instead of " ' " ?

if Stringinstr($val,"'") then StringReplace($val,"'","`")

[Jewtus]

Here is a perfect example of why I use it:

$answer="Well, I think that Jim's dog is dumb"

If you parsed this into your SQL it would look like this:

Select * from Table where Answer='Well, I think that Jim's dog is dumb'

which SQL would see as "Well, I think that Jim" and "s dog is dumb" with unbalanced quotes. Usually an error about S is not a column or something kicks up.

[Jewtus]

Also, if you are a fan of dynamic stuff... use system tables to get field names:

SELECT Column_Name FROM Dev.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'IntakeStage' AND COLUMN_Name not in ('status','lockedby','AtTime')

then parse the result set into another query:

_ArrayTranspose($columns)
                    $Constructor="["
                    $fields=_ArrayToString($columns,"],[")
                    $Constructor=$Constructor&$fields&"]"
                    $sQuery="Select "&$Constructor&" From Table "
                    $sWhereClause="Where "&columns[0][0]&"="&$variable1
                    $sfullQuery= $sQuery&$swhereclause

 

[Brobbl]

Thank you so much for your support. I can't code / test right now, but I think I get what your approach is. I'll consider building my query like that but I'm afraid I can't afford the time right now. Well, I will see where this leads me. Thank you very much again!

Also, I see why it's so difficult to find good and beginner-friendly information on this topic. It's really depending on syntax and that's different for every version of SQL that's out there. I just wonder, somebody must have done a documentation on the different versions before.

[Jewtus]

Use this:

http://www.w3schools.com/sql/sql_intro.asp

 

They have examples of everything as well as putting the examples in Oracle, mysql, sqlserver format

 

Also, if you need it, I have a _Connect, _GetRecords, _InsertRecords function that are kind of crude but very functional for my purposes.

expandcollapse popup

Func DBConnect($host,$UN,$PW,$DB)
    Global $sqldb = ObjCreate("ADODB.Connection")
    $sqlitedb.Open('Provider=SQLOLEDB.1;Password='&$PW&';Persist Security Info=True;User ID='&$UN&';Initial Catalog='&$DB&';Data Source='&$host&';')
    If @error Then
        Return -1
    Else
        Return 0
    EndIf
EndFunc

Func _GetRecords($connection,$query,$debug=False)
    $sHeaders=StringReplace($query,"Select ","")
    $sHeaders=StringLeft($sHeaders,StringInStr($sHeaders,"from")-1)
    $aHeaders=StringSplit($sHeaders,",",2)
    For $z=0 to UBound($aHeaders)-1
        $aHeaders[$z]=StringStripWS(StringStripWS($aHeaders[$z],1),2)
        If StringInStr($aHeaders[$z]," as ") <> 0 Then
            $aHeaders[$z]=StringTrimLeft($aHeaders[$z],StringInStr($aHeaders[$z]," as ")+3)
        EndIf
    Next
    _ArrayTranspose($aHeaders)
    $sqlRs = ObjCreate("ADODB.Recordset")
    If $debug=True then ConsoleWrite($query&@CRLF)
    $sqlRs.open ($query,$connection)
        If $sqlRs.EOF = True Then
            Return $aHeaders
        Else
            $result = $sqlRs.GetRows
            If UBound($result)>0 Then
                _ArrayInsert($result,0,_ArrayToString($aHeaders))
                Return $result
                $sqlRs.Close
            Else
                SetError(1)
                $sqlRs.Close
            EndIf
        EndIf
EndFunc

Func _InsertRecord($connection,$query,$debug=False)
    $sqlRs = ObjCreate("ADODB.Recordset")
    If $debug=True then ConsoleWrite($query&@CRLF)
    $sqlRs.open ($query,$connection)
EndFunc

 

[Brobbl]

46 minutes ago, Jewtus said:

http://www.w3schools.com/sql/sql_intro.asp

I'll see if that helps me on which strings are interpreted as commands (and therefor need to be dealt with) in my case.

 

On the topic of sql-functions, I've been using these so far. They are not mine, but I don't have an idea where they are from. I just added the timeouts because I once had a project where I would receive like 30k data sets and it would take a few minutes to get the data exported, so I used a ten minute timeout on those:

Func _SQLConnect($sServer, $sDatabase, $fAuthMode = 0, $sUsername = "", $sPassword = "", $sDriver = "{SQL Server}", $Timeout = 180)
   Local $sTemp = StringMid($sDriver, 2, StringLen($sDriver) - 2)
   Local $sKey = "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers", $sVal = RegRead($sKey, $sTemp)
   If @error or $sVal = "" Then Return SetError(2, 0, 0)
   $oConn = ObjCreate("ADODB.Connection")
   If NOT IsObj($oConn) Then Return SetError(3, 0, 0)
   $oConn.CommandTimeout = $Timeout
   $oConn.ConnectionTimeout = $Timeout
   If $fAuthMode Then $oConn.Open ("DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase & ";UID=" & $sUsername & ";PWD=" & $sPassword & ";")
   If NOT $fAuthMode Then $oConn.Open("DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase)
   If @error Then Return SetError(1, 0, 0)
   Return $oConn
EndFunc   ;==>_SQLConnect

Func _SQLQuery($oConn, $sQuery)
   If IsObj($oConn) Then Return $oConn.Execute($sQuery)
   Return SetError(1, 0, 0)
EndFunc ;==>_SQLQuery

Func _SQLDisconnect($oConn)
   If NOT IsObj($oConn) Then Return SetError(1, 0, 0)
   $oConn.Close
   Return 1
EndFunc   ;==>_SQLDisconnect

and also:

$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")    ; Initialize a COM error handler when starting the script


Func MyErrFunc()
  Msgbox(262144,"AutoItCOM Error Message","AutoIt intercepted a COM Error !"    & @CRLF & @CRLF & _
            "err.description is: "      & @TAB & $oMyError.description      & @CRLF & _
            "err.windescription:"       & @TAB & $oMyError.windescription   & @CRLF & _
            "err.number is: "           & @TAB & hex($oMyError.number,8)    & @CRLF & _
            "err.lastdllerror is: "     & @TAB & $oMyError.lastdllerror     & @CRLF & _
            "err.scriptline is: "       & @TAB & $oMyError.scriptline       & @CRLF & _
            "err.source is: "           & @TAB & $oMyError.source           & @CRLF & _
            "err.helpfile is: "         & @TAB & $oMyError.helpfile         & @CRLF & _
            "err.helpcontext is: "      & @TAB & $oMyError.helpcontext)
Endfunc

 

Edited February 4, 2016 by Brobbl

[Jewtus]

So the connect that you use actually checks for the driver, which is nice, but SQLOLEDB.1 is meant for SQL Server, which is why I use that driver instead of looking for one. Connectionstrings.com is great for finding the best connection/driver, IMO.

As for your SQLQuery, it is just executing a query (which is great for T-SQL). If you use RecordSet you get back an array of data, which is nice if you are looking to loop through the data (but then you need to deal with BoF and EoF if you get no results).

For example, I have a list of people who need email notifications sent, so every 5 minutes, my script queries the table to find what emails need to go out and then I loop through the array and use the outlook UDF to send emails and mark the record as having had an email sent.

 

[Brobbl]

Yep, I know connectionstrings.com and it's so lovely <3

Also OMG, outlook UDF??? Didn't even know there was one. I'm gonna love that thing! I don't even need a GUI for my monitoring tool, I can get all data via email on alert! That's cool!

I always loop through my data like this:

$SQL = _SQLConnect("xxx")
   $query = "SELECT x FROM y WHERE z"
   $result = _SQLQuery($SQL, $query)

   $calls = ""
   While Not $result.EOF
      $calls=$calls & $result.Fields("Call ID").value & "|"
      $calls=$calls & $result.Fields("Ihre Referenz").value & "|"
      $calls=$calls & $result.Fields("Status").value & "|"
      $calls=$calls & $result.Fields("Suchbegriff").value & "|"
      $calls=$calls & $result.Fields("Lief_ an Name").value & "¦"
      $result.MoveNext
   Wend
   _SQLDisconnect($SQL)
   
   $calls = StringLeft($calls, StringLen($calls) - 1)
   $calls = _StringSplit2Dim($calls)

Func _StringSplit2Dim($string = "", $d1delimiter = "¦", $d2delimiter = "|")
   If Not $string Then
      SetError(1, 0, 0)
      Dim $returnarray[1][1]=[[0]]
      Return $returnarray
   EndIf
   $returnarray = ""
   $temparray1 = StringSplit($string, "¦")
   If Not IsArray($temparray1) Then 
      SetError(1, 0, 0)
      Dim $returnarray[1][1]=[[0]]
      Return $returnarray
   EndIf
   $d1count = $temparray1[0]
   StringReplace($temparray1[1], $d2delimiter, $d2delimiter)
   $d2count = @extended + 1
   $d2countmax = $d2count
   Dim $returnarray[($d1count+1)][($d2count+1)]
   $returnarray[0][0] = $d1count
   For $i = 1 To $d1count
      StringReplace($temparray1[$i], $d2delimiter, $d2delimiter)
      $d2count = @extended + 1
      If $d2count > $d2countmax Then
         $d2countmax = $d2count
         ReDim $returnarray[($d1count+1)][($d2count+1)]
      EndIf
      $temparray2 = StringSplit($temparray1[$i], $d2delimiter)
      If IsArray($temparray2) Then
         For $j = 0 To $d2count
            $returnarray[$i][$j] = $temparray2[$j]
         Next
      EndIf
   Next
   If Not IsArray($returnarray) Then Dim $returnarray[1][1]=[[0]]
   Return $returnarray
EndFunc

 

Edited February 4, 2016 by Brobbl

[Brobbl]

$sqlRs.open ($query,$connection)
        If $sqlRs.EOF = True Then
            Return $aHeaders
        Else
            $result = $sqlRs.GetRows
            If UBound($result)>0 Then
                _ArrayInsert($result,0,_ArrayToString($aHeaders))
                Return $result
                $sqlRs.Close
            Else
                SetError(1)
                $sqlRs.Close
            EndIf
        EndIf

Now that's handy. Looks sweet from a performance point of view. I might have to change my habits here, but I think it's worth. Thank you very much!

On a side note, your RecordSet will never be closed if it's successful. You "Return" from the function before closing the RS:

If UBound($result)>0 Then
        _ArrayInsert($result,0,_ArrayToString($aHeaders))
        Return $result
        $sqlRs.Close
    [...]

 

Edited February 4, 2016 by Brobbl