All,
I need someone's help with dealing with apostrophe's and pipe symbol's. I am trying to insert text from a HTML FORM to a SQL statement using the UPDATE and INSERT methods. My issue is whenever one of these symbols get put into the SQL statement in my ASP file, it chokes!!
I understand that you need to get a double '' say to keep the quote within a literal but I can't figure how to get this done. I have read to try and use the Replace but it does not work for me. I was also advised to build a function that would search for a ' and replace it with '' before the INSERT or UPDATE.
The fact is that I don't know how to do it! Can anyone provide me with some code and explanation on how to resolve this issue....
Thanks
Mike
When building concatenated SQL statements, you can run into the following
problems based on incorporating user-typed text into the SQL statement:
User Types the Delimiter Character
--------------------------
If the user types the same character you use to delimit the text field,
such as:
LName contains: O'Brien
SQL = "SELECT * FROM Employees WHERE LastName='" & LName & "'"
SQL now contains:
SELECT * FROM Employees WHERE LastName='O'Brien'
this can result in the following error messages when you execute the SQL
statement:
Run-time error 3075
Syntax error in query expression '...'
One solution is to replace the apostrophe delimiter with quotes ("), such
as:
SQL = "SELECT * FROM Employees WHERE LastName=""" & LName & """"
However, the user could easily type O"Brien by mistake (forgetting to
release the SHIFT key when typing the apostrophe) and the problem
reappears. In addition, SQL Server uses " to delimit table and field names.
If the user-supplied value exceeds the maximum length of an identifier
name, SQL Server will return a syntax error.
The solution is to replace the apostrophe in the variable with two
apostrophes so that SQL contains:
SELECT * FROM Employees WHERE LastName='O''Brien'
User Types the Pipe Symbol
--------------------------
If the user types the pipe symbol (|), such as:
Password contains: A2|45
SQL = "SELECT * FROM SecurityLevel WHERE UID='" & UserID & "'"
SQL = SQL & " AND PWD='" & Password & "'"
SQL now contains:
SELECT * FROM SecurityLevel WHERE UID='JohnDoe'
AND PWD='A2|45'
and you are querying a Jet database, it can cause either the "Syntax Error"
given above or the following error:
Run-time error 3061
Too few parameters. Expected n.
The pipe symbol causes problems because Jet uses pipe symbols to delimit
field or parameter names embedded in a literal string, such as:
SELECT "|LastName|, |FirstName|" FROM Employees
This was considered easier for beginner users to learn than concatenation
when building ad hoc queries through the Access Query designer. However,
when used inadvertently in building a SQL statement, it can result in an
error.
The solution is to replace the pipe symbol with a concatenated expression
so that SQL contains:
SELECT * FROM SecurityLevel WHERE UID='JohnDoe'
AND PWD='A2' & chr(124) & '45'
Implementing the Solution
-------------------------
The solution to both these problems can be addressed via substring
replacement. The sample functions, ReplaceStr, SQLFixup and JetSQLFixup,
are provided below to illustrate the technique.
WARNING:
Microsoft provides code examples for illustration only, without warranty
either expressed or implied, including but not limited to the implied
warranties of merchantability and/or fitness for a particular purpose. This
code is provided 'as is' and Microsoft does not guarantee that the
following code can be used in all situations. Microsoft does not support
modifications of the code to suit customer requirements for a particular
purpose.
NOTE: In the following sample code, an underscore (_) at the end of a line
is used as a line-continuation character. For product versions that don't
not support the line-continuation character, remove the underscore and
combine that line with the next lines as a single statement when re-
creating this code.
Function ReplaceStr (TextIn, ByVal SearchStr As String, _
ByVal Replacement As String, _
ByVal CompMode As Integer)
Dim WorkText As String, Pointer As Integer
If IsNull(TextIn) Then
ReplaceStr = Null
Else
WorkText = TextIn
Pointer = InStr(1, WorkText, SearchStr, CompMode)
Do While Pointer > 0
WorkText = Left(WorkText, Pointer - 1) & Replacement & _
Mid(WorkText, Pointer + Len(SearchStr))
Pointer = InStr(Pointer + Len(Replacement), WorkText, _
SearchStr, CompMode)
Loop
ReplaceStr = WorkText
End If
End Function
Function SQLFixup(TextIn)
SQLFixup = ReplaceStr(TextIn, "'", "''", 0)
End Function
Function JetSQLFixup(TextIn)
Dim Temp
Temp = ReplaceStr(TextIn, "'", "''", 0)
SQLFixup = ReplaceStr(Temp, "|", "' & chr(124) & '", 0)
End Function
SQLFixup should be used if your SQL statement is going to be used with Jet
SQL pass-through queries or with ODBCDirect, RDO, or ADO to a non-Jet back-
end database:
LName contains: O'Brien
SQL = "SELECT * FROM Employees WHERE LastName='" & _
SQLFixup(LName) & "'"
SQL now contains:
SELECT * FROM Employees WHERE LastName='O''Brien'
JetSQLFixup should be used if Jet is your database back-end, or if doing a
non-Pass-through query to an ODBC datasource:
UserID cntains: JohnDoe
Password contains: A2|4'5
SQL = "SELECT * FROM SecurityLevel WHERE UID='" & _
JetSQLFixup(UserID) & "' AND PWD='" & JetSQLFixup(Password) & "'"
SQL now contains:
SELECT * FROM SecurityLevel WHERE UID='JohnDoe'
AND PWD='A2' & chr(124) & '4''5'