Solved

Apostrophy in SQL string

Posted on 2002-04-05
12
175 Views
Last Modified: 2010-05-02
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?

eg:
CurrentCustomer = "John's"
WHERE Account = '" & CurrentCustomer & "'
0
Comment
Question by:nickwoolley
  • 4
  • 3
  • 2
  • +3
12 Comments
 
LVL 49

Expert Comment

by:Ryan Chong
Comment Utility
Hi,

Try to use Replace function in VB:

Example:

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

Cheers
0
 
LVL 3

Accepted Solution

by:
Gunsen earned 50 total points
Comment Utility
Use "
... WHERE Account ="John's"

If InStr(CurrentCustomer,"'") Then SQL = "WHERE Account=" + Chr$(34) + CurrentCustomer + Chr$(34)
Else SQL = "WHERE Account='" + CurrentCustomer + "'"
0
 
LVL 1

Expert Comment

by:gondesiravi
Comment Utility
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
             Else
                 WrkStr = WrkStr & Mid(StrFieldVal, ChrPos, Len(StrFieldVal))
                 ChrPos = Len(StrFieldVal)
             End If
         Next
         FormatText= WrkStr
End Function
0
 
LVL 5

Expert Comment

by:kcm76
Comment Utility
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".

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


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

Try this.

ALL THE BEST
KCM
0
 
LVL 5

Expert Comment

by:kcm76
Comment Utility
"?" is for one character substitution. Thus it can match both Pray and Play for 'P?ay'.




0
 
LVL 18

Expert Comment

by:mdougan
Comment Utility
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.CommandText = "SELECT * FROM CUSTOMERS WHERE CUSTOMERS.ACCOUNT = ?"

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.....
      WEnd
   Else
      msgbox "No records were found"
   End if
Else
   msgbox "An error occured while running the query"
End if
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:nickwoolley
Comment Utility
gondesiravi: doesn't ryancys method do the same thing using far less code?

However, I'm wanting to retain the apostrophe, so thanks Gunsen.
0
 

Author Comment

by:nickwoolley
Comment Utility
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.
0
 
LVL 18

Expert Comment

by:mdougan
Comment Utility
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
0
 

Author Comment

by:nickwoolley
Comment Utility
I've tested characters %,?,*,# and there was no problem. Forgot to check \ though.
0
 
LVL 18

Expert Comment

by:mdougan
Comment Utility
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:

SELECT * FROM MYTABLE WHERE MYTABLE.MYEVENT LIKE 'Form_Load'


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:

Form1Load
Form Load
FormmLoad

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....
0
 

Author Comment

by:nickwoolley
Comment Utility
mdougan: No I haven't tried the '_' but I'll watch out for it, thanks.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now