nickwoolley
asked on
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?
eg:
CurrentCustomer = "John's"
WHERE Account = '" & CurrentCustomer & "'
eg:
CurrentCustomer = "John's"
WHERE Account = '" & CurrentCustomer & "'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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
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
"?" 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.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
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
ASKER
gondesiravi: doesn't ryancys method do the same thing using far less code?
However, I'm wanting to retain the apostrophe, so thanks Gunsen.
However, I'm wanting to retain the apostrophe, so thanks Gunsen.
ASKER
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
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
ASKER
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:
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....
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....
ASKER
mdougan: No I haven't tried the '_' but I'll watch out for it, thanks.
Try to use Replace function in VB:
Example:
CurrentCustomer = Replace$(CurrentCustomer,"
WHERE Account = '" & CurrentCustomer & "'
Cheers