Apostrophes in mySQL INSERT Statement

[BarryCambridge]

Hi there. My first post, so please be gentle!

I have a module in one of my databases which writes changes to an audit table. It works fine, but has always had an issue if there is an apostrophe in the field that's changed. The database is being used by more and more users, so it's time to sort it out. I think(!) I can use the replace() function to change the apostrophe to \' escape, but I can't get it in the right place in the code.

I'd be very grateful if anyone could advise me on this or if anyone has other suggestions. The code is:

Function WriteAudit(MyForm As Form)

Dim f As Form
Dim c As Control
Dim subject_id As Long
Dim frm As String
Dim user As String
Dim sql As String
Dim changes As String
Dim db As DAO.Database

Set f = MyForm
Set db = CurrentDb

subject_id = f.subject_id
frm = f.Name
user = Environ$("Username")
changes = ""

sql = "INSERT INTO tbl_audit " & _
"([subject_id], [form_name], [user], [change]) " & _
"VALUES ('" & subject_id & "', '" & frm & "', '" & user & "', "

For Each c In f.Controls

Select Case c.ControlType
Case acTextBox, acComboBox, acListBox, acOptionGroup
If IsNull(c.OldValue) And Not IsNull(c.Value) Then
changes = changes & _
c.Name & "--" & "BLANK" & "--" & c.Value & _
vbCrLf
ElseIf IsNull(c.Value) And Not IsNull(c.OldValue) Then
changes = changes & _
c.Name & "--" & c.OldValue & "--" & "BLANK" & _
vbCrLf
ElseIf c.Value <> c.OldValue Then
changes = changes & _
c.Name & "--" & c.OldValue & "--" & c.Value & _
vbCrLf
End If
End Select

Next c

sql = sql & "'" & changes & "');"

db.Execute sql, dbFailOnError

Set f = Nothing
Set db = Nothing

End Function

Many thanks, Barry

 

[sneuberg]

What I do is create a function to do the replace, e.g.,

'escapes single quotes
Private Function SQE(fd As Variant) As Variant
If IsNull(fd) Then
    SQE = Null
    Exit Function
End If
SQE = Replace(fd, "'", "''")

End Function

Then it's easy to put in the string, e.g.

SQL = "INSERT INTO tbl_audit " & _
"([subject_id], [form_name], [user], [change]) " & _
"VALUES ('" & SQE(subject_id) & "', '" & SQE(frm) & "', '" & SQE(User) & "', "

If you do that a lot you could make the function public and put it in a standard module.

 

[pbaldy]

This should help:

http://www.baldyweb.com/BuildSQL.htm

specifically the use of Chr(34)

 

[MarkK]

What I would do is use a temp QueryDef, which handles delimiters automatically, and allows you to handle strings that contain embedded delimiters--like 5'9", if that's what the user enters into the control--no problem. Consider code like...

Function WriteAudit(MyForm As Form)
    Const SQL_INSERT As String = _
        "INSERT INTO tbl_audit " & _
            "( [subject_id], [form_name], [user], [change]) " & _
        "VALUES " & _
            "( p0, p1, p2, p3 )"
            
    With CurrentDb.CreateQueryDef("", SQL_INSERT)
        .Parameters(0) = MyForm.subject_id
        .Parameters(1) = MyForm.Name
        .Parameters(2) = Environ$("Username")
        .Parameters(3) = GetChanges(MyForm)
        .Execute dbFailOnError
        .Close
    End With

End Function

Private Function GetChanges(frm As Form) As String
    Dim c As Access.Control
    Dim tmp As String

    For Each c In frm.Controls
        Select Case c.ControlType
            Case acTextBox, acComboBox, acListBox, acOptionGroup
            If IsNull(c.OldValue) And Not IsNull(c.Value) Then
                tmp = tmp & c.Name & "--" & "BLANK" & "--" & c.Value & vbCrLf
            ElseIf IsNull(c.Value) And Not IsNull(c.OldValue) Then
                tmp = tmp & c.Name & "--" & c.OldValue & "--" & "BLANK" & vbCrLf
            ElseIf c.Value <> c.OldValue Then
                tmp = tmp & c.Name & "--" & c.OldValue & "--" & c.Value & vbCrLf
            End If
        End Select
    Next c
    
    GetChanges = tmp

End Function

When the strings are handled by a parameterized QueryDef it doesn't matter if the strings contain delimiters, which is a major advantage.

 

[sneuberg]

When the strings are handled by a parameterized QueryDef it doesn't matter if the strings contain delimiters, which is a major advantage.

Thanks for post that. I've been looking for that method. I suspect this method would also solve the problem of having non US format literal dates in queries.

 

[MarkK]

I suspect this method would also solve the problem of having non US format literal dates in queries.

Steve, I expect this would still depend on how the date supplied to the parameter is formatted. For instance, you could supply the date as a string, but in that case I expect the parameter would honor the Windows regional setting, so...

   const SQL as string = _
      "INSERT INTO tDateTestTable ( datField1, datField2 ) " & _
      "VALUES ( p0, p1 ) "

   with currentdb.createquerydef("", SQL)
[COLOR="Green"]      'I expect both of these would work, but...[/COLOR]
      .parameters(0) = "3/10/17" [COLOR="Green"]'...this is still ambiguous, could be 3-Nov-2017...[/COLOR]
      .parameters(1) = #3/10/17# [COLOR="Green"]'...and this is still ambiguous...[/COLOR]
      .execute
      .close
   end with

...so I don't think it completely solves the date format problem. What it does solve is that you no longer need to construct a string like...

dim sql as string
sql = _
   "INSERT INTO tDateTestTable ( datField1, datField2 ) " & _
   "VALUES ( #" & me.dat1 & "#, #" & me.dat2 & "#)"

...because those tediously constructed literal parameters in the SQL query text are handled automatically. That's the part I like. This kind of thing always looks like such a mess to me....

"... ( #" & me.dat1 & "#, #" & me.dat2 & "#)"

...but for strings like <O'Leary>, or <2x 8'-2"x4">, or <6'2">, this method is a silver bullet.

 

[sneuberg]

Mark,

Test results of your code:

These are shown with my regional settings set to US. The first record was inserted with US settings and the second with UK settings.

 

[MarkK]

It seems odd to me that with UK settings the date string "3/10/17" and the date literal #3/10/17# should be parsed differently. I don't really understand this result. It seems to indicate that it handles the date string better than date literal, which seems counter-intuitive.

 

[sneuberg]

It seems odd to me that with UK settings the date string "3/10/17" and the date literal #3/10/17# should be parsed differently. I don't really understand this result. It seems to indicate that it handles the date string better than date literal, which seems counter-intuitive.

With UK settings if you put #3/10/17# in the criteria of a query in design view and switch to SQL view it will be

WHERE (((tDateTestTable.datField1)=#10/3/2017#));

and #10/03/2017# it becomes

WHERE (((tDateTestTable.datField1)=#3/10/2017#));

With US settings the literal doesn't change. So it appears Access is treating the parameter the same way.

Anyway in the cases I normally encounter control values are being concatenated into a query. So I did some testing for that case. I've attached the test database in case you'd like to test this or some variation yourself. The code is:

Private Sub InsertRecords_Click()

   Const SQL As String = _
      "INSERT INTO tDateTestTable ( datField1,InsertMethod ,CountryName ) " & _
      "VALUES ( p0, p1, p2)"
Debug.Print Len(SQL)

   With CurrentDb.CreateQueryDef("", SQL)
      .Parameters(0) = Me.txtDate
      .Parameters(1) = "QueryDef Parameters"
      .Parameters(2) = CountryName
      .Execute
      .Close
   End With

CurrentDb.Execute "INSERT INTO tDateTestTable ( datField1,InsertMethod ,CountryName ) VALUES (#" & Me.txtDate & "#,'Concatenated' ,'" & CountryName & "')"

End Sub

I ran the code twice; both times with the date being the 10th of March. Viewed with US settings the result are:

I think this demonstrates this method solves the date problem too at least for controls. I'm totally sold on this method. You shouldn't catch me telling a forum member

'but if it's a text field then you have to put it in single quotes and if there is is any possibility of a single quote occurring in the data .................."

ever again.

 

[MarkK]

OK, I see what you're getting at. I never deal with UK dates so I've never been concerned with this, or observed what changes when I change regional settings, so, another advantage of the QueryDef method!
Thanks Steve,
Have a good weekend!

 

[Gasman]

Steve,
That is what catches a lot of us out in the UK.
We can put #10/03/2017# in a design window and get results for 10th March, but if we construct a string for SQL in VBA we need to have USA format.

With UK settings if you put #3/10/17# in the criteria of a query in design view and switch to SQL view it will be

WHERE (((tDateTestTable.datField1)=#10/3/2017#));

and #10/03/2017# it becomes

WHERE (((tDateTestTable.datField1)=#3/10/2017#));

With US settings the literal doesn't change. So it appears Access is treating the parameter the same way.

Anyway in the cases I normally encounter control values are being concatenated into a query. So I did some testing for that case. I've attached the test database in case you'd like to test this or some variation yourself. The code is:

Private Sub InsertRecords_Click()

   Const SQL As String = _
      "INSERT INTO tDateTestTable ( datField1,InsertMethod ,CountryName ) " & _
      "VALUES ( p0, p1, p2)"
Debug.Print Len(SQL)

   With CurrentDb.CreateQueryDef("", SQL)
      .Parameters(0) = Me.txtDate
      .Parameters(1) = "QueryDef Parameters"
      .Parameters(2) = CountryName
      .Execute
      .Close
   End With

CurrentDb.Execute "INSERT INTO tDateTestTable ( datField1,InsertMethod ,CountryName ) VALUES (#" & Me.txtDate & "#,'Concatenated' ,'" & CountryName & "')"

End Sub

I ran the code twice; both times with the date being the 10th of March. Viewed with US settings the result are:

I think this demonstrates this method solves the date problem too at least for controls. I'm totally sold on this method. You shouldn't catch me telling a forum member


ever again.

 

[BarryCambridge]

Thanks for all the suggestions guys! I'll have a proper read thorough and see what I can do

Barry

 

[BarryCambridge]

Mark, your code worked perfectly first time. Thanks again, Barry

 

[MarkK]

You're welcome Barry, all the best.

 

[BarryCambridge]

Hi again. The audit module is performing well, other than there is now a problem with longer text changes. the underlying tables are MySQL linked via odbc with the fields as "Text", so there's plenty of room there.

From my investigations, I think the problem stems from the fact that there is a limit of 255 characters being imposed on the declared string variable. I know strings can be a lot longer, but read that some functions will limit their length. If I keep the changed text to under 255 characters, it works, but exceeding this causes an error.

Function WriteAudit(MyForm As Form)
Const SQL_INSERT As String = _
"INSERT INTO tbl_audit " & _
"( [subject_id], [form_name], [user], [change]) " & _
"VALUES " & _
"( p0, p1, p2, p3 )"

With CurrentDb.CreateQueryDef("", SQL_INSERT)
.Parameters(0) = MyForm.subject_id
.Parameters(1) = MyForm.Name
.Parameters(2) = Environ$("Username")
.Parameters(3) = GetChanges(MyForm)
.Execute dbFailOnError
.Close
End With

End Function

Private Function GetChanges(frm As Form) As String
Dim c As Access.Control
Dim tmp As String

For Each c In frm.Controls
Select Case c.ControlType
Case acTextBox, acComboBox, acListBox, acOptionGroup
If IsNull(c.OldValue) And Not IsNull(c.Value) Then
tmp = tmp & c.Name & "--" & "BLANK" & "--" & c.Value & vbCrLf
ElseIf IsNull(c.Value) And Not IsNull(c.OldValue) Then
tmp = tmp & c.Name & "--" & c.OldValue & "--" & "BLANK" & vbCrLf
ElseIf c.Value <> c.OldValue Then
tmp = tmp & c.Name & "--" & c.OldValue & "--" & c.Value & vbCrLf
End If
End Select
Next c

GetChanges = tmp

End Function

Any ideas much appreciated

Barry