Insert with parameters

Question

I should change all queries to use parameters to protect app from SQL injection.

Current insert sample which works is:

If Len(ExecuteQuery("INSERT INTO ICE_Order_Details(Order_ID, Product_ID, License_Type_ID, Maintenance_ID, Qty, Shareit_Running_No, Price, Total) VALUES(" & OrderID & ", " & ProductID & ", " & LicenseTypeID & ", " & MaintenanceID & ", " & Val(Request("QUANTITY")) & ", " & Val(Request("RUNNING_NO")) & ", " & Price & ", " & Price * Val(Request("QUANTITY")) & ")")) > 0 Then
    'SendBadRequest "Could not run insert Order detail query"

Can you help me to write parametric query instead of this? I tried a lot of ways to do this but here is below last one.

Dim ConnString As New SqlConnection("Provider=SQLOLEDB.0;Data Source=something;Initial Catalog=something;Persist Security Info=True;User ID=something;Password=something")
Dim SqlString As String ="INSERT INTO ICE_Order_Details(Order_ID, Product_ID, License_Type_ID, Maintenance_ID, Qty, Shareit_Running_No, Price, Total) VALUES(@OrderID, @ProductID, @LicenseTypeID, @MaintenanceID, @Qty, @RunningNo, @Price, @Total)"

Using conn As New OleDbConnection(ConnString)
  Using cmd As New OleDbCommand(SqlString, conn)
    cmd.CommandType = CommandType.Text
    cmd.Parameters.AddWithValue("@OrderID", OrderID)
    cmd.Parameters.AddWithValue("@ProductID", ProductID)
    cmd.Parameters.AddWithValue("@LicenseTypeID", LicenseTypeID)
    cmd.Parameters.AddWithValue("@MaintenanceID", MaintenanceID)
    cmd.Parameters.AddWithValue("@Qty", Val(Request("QUANTITY")))
    cmd.Parameters.AddWithValue("@RunningNo", Val(Request("RUNNING_NO")))
    cmd.Parameters.AddWithValue("@Price", Price)
    cmd.Parameters.AddWithValue("@Total", Price * Val(Request("QUANTITY")))
    conn.Open()
    cmd.ExecuteNonQuery()
  End Using
End Using

Edit: It still doesn't work. Here is my current code for adding parameters:

cmd.Parameters.Add("@OrderID", SqlDbType.Int).Value = OrderId
cmd.Parameters.Add("@ProductID", SqlDbType.Int).Value = ProductID
cmd.Parameters.Add("@LicenseTypeID", SqlDbType.Int).Value = LicenseTypeID
cmd.Parameters.Add("@MaintenanceID", SqlDbType.Int).Value = MaintenanceID
cmd.Parameters.Add("@Qty", SqlDbType.Int).Value = Int32.Parse(Request("QUANTITY"))
cmd.Parameters.Add("@RunningNo", SqlDbType.Int).Value = Int32.Parse(Request("RUNNING_NO"))
cmd.Parameters.Add("@Price", SqlDbType.Money).Value = Money.Parse(Price)
cmd.Parameters.Add("@Total", SqlDbType.Money).Value = Money.Parse(Price * Int32.Parse(Request("QUANTITY")))

Edit: I changed my insert query to test only insert with parameters. But it don't work

Dim ConnString As String = ConfigurationManager.ConnectionStrings("DB_Connection_String0").ConnectionString

Dim SqlString As String ="INSERT INTO Unsubscribed(E-Mail) VALUES(@E-Mail)"

Using conn As New OleDbConnection(ConnString)
  Using cmd As New OleDbCommand(SqlString, conn)
    cmd.CommandType = CommandType.Text
        cmd.Parameters.Add("@E-Mail", SqlDbType.nvarchar).Value = "[email protected]"
    conn.Open()
    cmd.ExecuteNonQuery()
  End Using
End Using

here is error which i got (it marked 'As') if I change connection string it show error on next 'As' in code with same error message

Microsoft VBScript compilation error '800a0401'

Expected end of statement

/Test.asp, line 8

Dim ConnString As String = ConfigurationManager.ConnectionStrings("DB_Connection_String0").ConnectionString ---------------^

Your code looks fine to me, except the use of the AddWithValue method. this method infers the data type of the parameter and can sometimes get it wrong. better use Add. i.e cmd.Parameters.Add("@OrderID", SqlDbType.Int).Value = OrderId — Zohar Peled
I can't see error message. This is big problem here. because this page call ecomerce site and I can't debug it. I got only generic error message from ecomerce site — Mr No
Please follow Zohar's advice. Also note that you should not use the "magic" Val function. Use the static Parse (or TryParse) methods like Int32.Parse and Decimal.Parse. — Bjørn-Roger Kringsjå
It still don't work I try change way for adding parameters. You can see in post new code (in edit section) — Mr No

Mr No Mr No · Accepted Answer · 2015-08-18T11:11:39

Finally I found solution for this

Thanks all for help!

here is code below which work fine

<% 
Dim oConn, oCmd, ds, sql
    p1 = "test"
    p2 = "test"
    p3 = "test"
ds = "Provider=SQLOLEDB.1;Data Source=___;Initial Catalog=___;User ID=___;Password=___;"
sql = "INSERT INTO table (prop1, prop2, prop3) VALUES (?,?,?)"

Set oConn=Server.CreateObject("ADODB.Connection")
oConn.Open ds

Set oCmd = Server.CreateObject("ADODB.Command")
oCmd.ActiveConnection = oConn
oCmd.CommandText = sql
oCmd.CommandType = 1
oCmd.Parameters(0) = p1
oCmd.Parameters(1) = p2
oCmd.Parameters(2) = p3
oCmd.Execute()
oConn.close
Set oConn=nothing
%>

Insert with parameters

2 Answers