insert form's input security

[madanthrax]

Hi all,

I use Dreamweaver for html and css but nowadays like to write my own asp code (as advised in this forum). However I use an asp upload script which was sold as a DW extension. I have adapted it a lot to suit my needs, rewriting most of the silly DW asp code surrounding it. Now there is a new faster version so I fired up DW8 and using the new extension made a box standard upload then insert form to SQL DB page using the wizard to give me some code to pick to pieces and update the required parts of my applications.

While doing the dissection I noticed DW8 uses very different insert method to the older versions. Now I always clean up the form variables before putting them in an SQL insert statement, such as replacing single quotes with ' and replacing semicolons and amphersands etc. (I do this and much more since a visit from a professional web security firm who came in and demonstrated how to SQL inject all our apps...gulp)

Below is the DW insert code. Can anyone tell me please whats going on here? The parameter stuff is all new to me. I recognise the last numbers at the end refer to the DB character limit, 500, 300, 1000 etc. but what are the 201 and 1 numbers for? Is there any checking of the input going on at all? Or is this a cool way to insert data?

Set MM_editCmd = Server.CreateObject ("ADODB.Command")
    MM_editCmd.ActiveConnection = MM_NSfiles_STRING
    MM_editCmd.CommandText = "INSERT INTO dbo.tbl_"& appName &" (FileName, LinkText, FolderCode, UploadDate, UploadTime, Version, Notes, UserName) VALUES (?, ?, ?, ?, ?, ?, ?, ?)" 
    MM_editCmd.Prepared = true
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param1", 201, 1, 500, UploadFormRequest("filename")) ' adLongVarChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param2", 201, 1, 300, UploadFormRequest("LinkText")) ' adLongVarChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param3", 5, 1, 10, MM_IIF(UploadFormRequest("FolderCode"), UploadFormRequest("FolderCode"), null)) ' adDouble
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param4", 135, 1, -1, MM_IIF(UploadFormRequest("UploadDate"), UploadFormRequest("UploadDate"), null)) ' adDBTimeStamp
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param5", 201, 1, 11, UploadFormRequest("UploadTime")) ' adLongVarChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param6", 201, 1, 50, UploadFormRequest("Version")) ' adLongVarChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param7", 201, 1, 1000, UploadFormRequest("Notes")) ' adLongVarChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param8", 201, 1, 50, UploadFormRequest("UserName")) ' adLongVarChar
    MM_editCmd.Execute
    MM_editCmd.ActiveConnection.Close

By the way, in case its important, the MM_IIF Function (located above the above code) is:

' IIf implementation
Function MM_IIf(condition, ifTrue, ifFalse)
  If condition = "" Then
    MM_IIf = ifFalse
  Else
    MM_IIf = ifTrue
  End If
End Function

Thanks for any help.

Anthony.

[sub]"Nothing is impossible until proven otherwise"[/sub]​

 

[Sheco]

At first glance I'd say the number is the value of the datatype referenced by the comment at the end of each line.

adLongVarChar = 201
adDBTimeStamp = 135
adDouble = 5

 

[monksnake]

Take a look here for detail into the syntax of CreateParamter.

http://www.w3schools.com/ado/met_comm_createparameter.asp

Using parameters is a good way to prevent SQL injection.

Here's a small readup on W3Schools about parameters.

http://www.w3schools.com/ado/ado_ref_parameter.asp

<.

 

[Sheco]

Instead of building an INSERT statement as an SQL string and simply calling the ADO Connection object's Execute() method .... they are instead using the ADO Command object and defining each input value as a paramter to the Command object's Parameters collection.

This is a major pain in the rear but it does add an extra layer of protection because each parameter has a datatype so you wont get any letters in a numeric field and, perhaps the best is you have a smaller "SQL Injection" exposure because it each parameter will automatically be recognized as input data rather than as SQL.

 

[Tarwn]

Of course the interesting question is whether they bothered to add any additional coder before that section to do type validation before trying to shove a value in a typed parameter it might not fit in.

 

[madanthrax]

Thanks guys, very interesting stuff.

Sheco,

each parameter will automatically be recognized as input data rather than as SQL

Does this mean that even if DELETE or UPDATE are used in the text fields by hackers with the appropriate surrounding syntax that they won't be recognised by SQL as SQL?


Monksnake, Thanks for the links they sure cleared up the questions I had about parameters.

Tarwn, there is no code above the posted to do with the insert at all. Looks like DW analyses the setting on the sql field and expects you to limit the text boxes etc to that number of characters. For me that would be standard anyway, but what about newbies?

[sub]&quot;Nothing is impossible until proven otherwise&quot;[/sub]​

 

[Tarwn]

I would go back and add checks above that section to make sure your not going to get a SQL error when you pass it "abcd" as a numeric type.