Solved

Apostrophy in SQL string

Posted on 2002-04-05
12
179 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +3
12 Comments
 
LVL 51

Expert Comment

by:Ryan Chong
ID: 6920390
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
ID: 6920395
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
ID: 6920457
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Expert Comment

by:kcm76
ID: 6920726
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
ID: 6920731
"?" is for one character substitution. Thus it can match both Pray and Play for 'P?ay'.




0
 
LVL 18

Expert Comment

by:mdougan
ID: 6920812
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
 

Author Comment

by:nickwoolley
ID: 6921054
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
ID: 6921062
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
ID: 6921931
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
ID: 6924922
I've tested characters %,?,*,# and there was no problem. Forgot to check \ though.
0
 
LVL 18

Expert Comment

by:mdougan
ID: 6926357
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
ID: 6931074
mdougan: No I haven't tried the '_' but I'll watch out for it, thanks.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

735 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