Apostrophy in SQL string

My sql string includes a field in the WHERE clause which contains descriptions which include apostrophies. The apostrophies are causing errors because they are interpreted by sql as a termination. Apart from changing the structure and using an internal code in the WHERE clause, if I detect the apostrophies how can I then allow for them in the sql?

CurrentCustomer = "John's"
WHERE Account = '" & CurrentCustomer & "'
Who is Participating?
GunsenConnect With a Mentor Commented:
Use "
... WHERE Account ="John's"

If InStr(CurrentCustomer,"'") Then SQL = "WHERE Account=" + Chr$(34) + CurrentCustomer + Chr$(34)
Else SQL = "WHERE Account='" + CurrentCustomer + "'"
Ryan ChongCommented:

Try to use Replace function in VB:


CurrentCustomer = Replace$(CurrentCustomer,"'","''")
WHERE Account = '" & CurrentCustomer & "'

Before passing the data to SQL call this function for detecting ' and replace it with '' and then transfer it to SQL

function FormatText(StrFieldVal as string) as String
' Format Apostrophes For SQL Statement
Dim ChrPos , PosFound
Dim WrkStr
For ChrPos = 1 To Len(StrFieldVal)
           PosFound = InStr(ChrPos, StrFieldVal, "'")
        If PosFound > 0 Then
        WrkStr = WrkStr & Mid(StrFieldVal, ChrPos, PosFound - ChrPos + 1) & "'"
                 ChrPos = PosFound
                 WrkStr = WrkStr & Mid(StrFieldVal, ChrPos, Len(StrFieldVal))
                 ChrPos = Len(StrFieldVal)
             End If
         FormatText= WrkStr
End Function
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Chandramouli kArchitectCommented:
Assume that u have a FieldValue John's.
In oracle "?" replaces any one character
and ur sql would be "John?s". but u cannot use "=" Operator u have to only use "Like".

Select * from ....... Where CurrentCustomer like 'John?s'

Using Instr or Other functions u can build ur query in vb.

Try this.

Chandramouli kArchitectCommented:
"?" is for one character substitution. Thus it can match both Pray and Play for 'P?ay'.

This points to a larger issue though.  The single quote, while the most common, is not the only character that will give you problems.  The only way I've found to totally eliminate concerns about embedded characters in field values in SQL is through using ADODB Command Objects with Parameters.  It takes a bit more code, but you can create procedures to hide most of the extra code.  Here is what your example would look like (assuming you've already opened CN the connection object)

Dim CMD as ADODB.Command
Dim Parm1 As ADODB.Parameter
Dim RS as ADODB.Recordset

Set Parm1 = New ADODB.Parameter
Parm1.Direction = adParamInput

' needs to match column definition in database
Parm1.Type = adVarChar
Parm1.Size = 50

Parm1.Value = Text1.Text ' could include John's

Set CMD = New ADODB.Command
CMD.ActiveConnection = CN
CMD.CommandType = adCmdText

CMD.Parameters.Append Parm1

Set RS = CMD.Execute

(or is that just RS = CMD.Execute)

If RS.State = adStateOpen
   If Not RS.EOF and Not RS.BOF then
      While Not RS.EOF.....
      msgbox "No records were found"
   End if
   msgbox "An error occured while running the query"
End if
nickwoolleyAuthor Commented:
gondesiravi: doesn't ryancys method do the same thing using far less code?

However, I'm wanting to retain the apostrophe, so thanks Gunsen.
nickwoolleyAuthor Commented:
mdougan: Thanks but this app is using dao. What other characters will cause me problems? I'm trapping for double quotes and not allowing them in the field.
OK, under DAO you are right, you don't have too many options.  If you are going against Access, I think that you can use Parameters as part of a QueryDef object for the same effect...

I don't have the list handy, but any SQL special character will give you problems (and that depends on your DBMS).  In SQL Server, you'll probably have strange results with any of the characters reserved for wildcards, like _ or % or ? along with double and single quotes.  You might also have trouble with forward or back-slashes, particularly if they are just preceding certain characters which I think can be interpreted as an escape sequence.

In Access you had * and # signs to give you trouble
nickwoolleyAuthor Commented:
I've tested characters %,?,*,# and there was no problem. Forgot to check \ though.
Did you try the underscore (_) ?  Some of the characters may not give you an error, but they may not give you the results you expected.  For example, let's say that you had a query that said:


You might be expecting only rows with 'Form_Load' in the MYEVENT column to be returned, however, because the underscore is a valid wildcard character for the LIKE clause, then you might actually return rows with values like the following:

Form Load

I guess my point is that it's a real headache to try to keep in mind all of the various places that some special character might hose your SQL, and it's really nice to work with parameters which can eliminate the need to worry about that....
nickwoolleyAuthor Commented:
mdougan: No I haven't tried the '_' but I'll watch out for it, thanks.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.