0

I am working within VBA in Excel using a MySQL ODBC 5.1 Driver, and am having trouble with a parameterized SQL query that I'm trying to use to insert rows into a table. Before parameterization the query worked fine, but I ran into an issue with SQL injection where one of the columns had comments inside it that contained SQL code and messed the Insert all up. I'm now shifting towards best practice of parameterization but I can't seem to find out what I'm doing wrong with the below code and why it's giving me the "Unknown column 'p1' in 'field list'" error.

For y = 0 To 20000
        qry = "INSERT INTO ticket_system.ticket_weekly_snapshot (issue_type,key_name,summary,assignee,reporter,priority,status,resolution,created,updated,due_date,project,linked_issues,parent_link,epic_link,parent_undefined,label,comment) VALUES ("
        Dim adoCmd As Object
        Dim adoRS As Object
        Set adoCmd = CreateObject("ADODB.Command")
        With adoCmd
            .ActiveConnection = cnn
            .CommandType = adCmdText
            If alltix.Range("A2").Offset(y, 0) = "" Then
                Exit For
            End If
            For x = 0 To 17
                Dim l As Integer
                l = Len(alltix.Range("A2").Offset(y, x))
                If l = 0 Then
                    l = 10
                End If
                .Parameters.Append .CreateParameter("p" & x + 1, adLongVarChar, adParamInput, l, alltix.Range("A2").Offset(y, x))
                If x = 17 Then
                    qry = qry & "p" & x + 1 & ")"
                Else
                    qry = qry & "p" & x + 1 & ","
                End If
            Next x
        End With
        adoCmd.CommandText = qry
        Set adoRS = adoCmd.Execute

I have a messagebox prompt currently in for testing that shows me that the query is building properly, ending with "VALUES (p1, p2, ... p18)" which is how I've seen pretty much all parameterized queries working so far. All parameters get added properly to the adoCmd, no errors with values or anything like that. The "unknown column in field list" error occurs on the final line when the adoCmd executes. Can anyone help me and show me where I've gone wrong?

StephenAbbott
  • 1
  • Could you add your table's definition?, maybe it's that vba cannot manage fully qualified names like database.table, just table names. Select your default database in your ODBC definition. – Pepe N O Aug 30 '21 at 16:18
  • I do have my database selected in my ODBC definition. I just tried taking it out of the query text and just using "ticket_weekly_snapshot" instead and that didn't fix it. I think the issue has to be somewhere within the parameters themselves, since before i switched to using parameters I was running queries just fine (i.e. the first 2 rows of my dataset were inserted with no issues). – StephenAbbott Aug 30 '21 at 16:41

1 Answers1

1

Dynamically building parameter name worked for me but could try option of unnamed parameters. Declare l variable outside loop. Really should declare all variables at top of procedure.

qry = "INSERT INTO ticket_system.ticket_weekly_snapshot " & _
      "(issue_type,key_name,summary,assignee,reporter,priority,status,resolution,created,updated,due_date,project,linked_issues,parent_link,epic_link,parent_undefined,label,comment) " & _
      "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
...
Dim l As Integer
For x = 0 To 17
    l = Len(alltix.Range("A2").Offset(y, x))
    If l = 0 Then
        l = 10
    End If
    .Parameters.Append .CreateParameter(, adLongVarChar, adParamInput, l, alltix.Range("A2").Offset(y, x))
Next

For more info review How do I use parameters in VBA in the different contexts in Microsoft Access?

June7
  • 19,874
  • 8
  • 24
  • 34
  • Thank you for the suggestion. The "l" variable defined in the loop was a quick patch for another issue I came up with, that you cannot assign a type to a parameter without also assigning its length, but you can't assign a length of 0. I did read in your link that "ADO does not support named parameters. While you can pass a name, it's not processed." I just tried the unnamed parameters and now am getting a different error: "Method 'Execute' of object '_Command' failed." Happening at the same time as the previous error was too. Any idea what could be causing this? – StephenAbbott Aug 31 '21 at 13:57
  • I got rid of the "Execute" error in my most recent comment, by not using an ADODB recordset to execute the command, and just calling adoCmd.Execute. However, upon calling the Execute function, my instance of Excel is completely crashing. No error messages at all, doesn't run to the command timeout length, just full on crash within 5 to 10 seconds. Initial research shows that it could be a driver issue, however I've tried with other drivers I have installed and get the same crash. Any ideas why this might be happening? – StephenAbbott Aug 31 '21 at 16:28
  • No idea. Cannot replicate error so cannot advise solution. However, I am testing with Access db, not MySQL. – June7 Aug 31 '21 at 17:28
  • I couldn't figure it out, no changes I made were affecting anything. Thankfully I know the restrictions on the info that's coming in so I feel relatively comfortable just using a string query and executing it from the connection. I've done a replace for both Chr*34) and Chr(39) into Chr(96) (i.e. changing both ' and " into `) so there should be no case where any SQL can get injected. Not the ideal solution but it works so far. Thank you for your help with understanding it though! – StephenAbbott Aug 31 '21 at 18:25