Apostrophe in search string

[Michael T]

I have a simple SQL string that is run from a form when a field is updated:

strSQL = "UPDATE tblDesign " & _
"SET " & strFieldNameForUpdate & " = '" & strDM & "' " & _
"WHERE DesignName = '" & strName & "' ;"

It works fine except for where there is (rarely) an apostrophe in the
strName field data. I cannot work out how many extra quotes to put into the
sql build string to get round this.

A debug.print of the string that causes a syntax problem is:

UPDATE tblDesign SET DesignMonarchSide = 'M' WHERE DesignName = 'Sailor's
uniform' ;

Help would be appreciated.

All the best,

Michael.

 

[Stefan Hoffmann]

hi Michael,

It works fine except for where there is (rarely) an apostrophe in the
strName field data. I cannot work out how many extra quotes to put into the
sql build string to get round this.

This is the reason for the so called SQL Injection attacks. Always check
your input first.

In your case this can be simply done by avoiding single quotes. When you
need them, you have to escape them by doubling them (paste it into a
standard module:

Public Function SQLEscape(AStatement As String) As String

SQLEscape = Replace(AStatement, "'", "''")

End Function


strSQL = "UPDATE tblDesign " & _
"SET [" & strFieldNameForUpdate & "] = '" & _
SQLEscape(strDM) & "' " & _
"WHERE DesignName = '" & _
SQLEscape(strName) & "' ;"

while you are using dynamic field names, I would strongly recommend the
usage of square brackets.


mfG
--> stefan <--

 

[J_Goddard via AccessMonster.com]

Hi -

Try changing your strSQL= statement to :

strSQL = "UPDATE tblDesign " & _
"SET " & strFieldNameForUpdate & " = '" & strDM & "' " & _
"WHERE DesignName = """ & strName & """ ;"

Which will result in:

UPDATE tblDesign SET DesignMonarchSide = 'M' WHERE DesignName = "Sailor's
uniform" ;

Of course, if strName should ever be something like Grade "A" Design......
A classic case of "You can't have it both ways", which I have had to deal
with!

HTH


John

 

[Lythandra]

I use the Replace command to just get rid of them in text data.

Chr(39) is '
Chr(34) is "

I'm in the construction industry so I have to change em to "ft" and "in" all
the time.

 

[Powderfinger]

? matches any single alphabetic character.B?ll finds ball, bell, and bill

If the user types in Land'oLakes , this would find that without that
annoying error screen but would also return other values such as
..Land"oLakes and LandXoLakes

Just an FYI, but it probably wouldn't fit whatr you want to do

strSQL = "UPDATE tblDesign " & _
"SET " & strFieldNameForUpdate & " = '" & strDM & "' " & _
"WHERE DesignName Like '" & Replace(strName,"'","?") & "' ;"