Solved

Dealing with Apostrophe's and Pipe symbols

Posted on 1998-11-04
9
992 Views
Last Modified: 2009-07-29
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
0
Comment
Question by:brewman
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 1

Expert Comment

by:TheGrinch
ID: 1091038
Here is a function that replaces each apostrophe "'" with a pair of apostrophe "'" characters. It is in C++, but you didn't say what you needed...

void DoubleUp ( CString& text )
{
  CString orig = text;
  text = "";
  for ( int i = 0; i < orig.GetLength(); i++ )
      {
        text += orig.GetAt(i);
        if ( orig.GetAt(i) == '\'' )
           text += orig.GetAt(i);
      }
  return;
}

NOTE: Two apostrophes ('') are not the same as a double quote(").

0
 

Author Comment

by:brewman
ID: 1091039
TheGrinch,

I need to include this in a Active Server Page so I would assume that it will need to be written in VBScript.  Can you still help me?

0
 
LVL 1

Expert Comment

by:TheGrinch
ID: 1091040
No, sorry. Maybe you could follow the pattern of my example and do something yourself? Or maybe someone else can help.
0
 

Author Comment

by:brewman
ID: 1091041
TheGrinch,

I need to include this in a Active Server Page so I would assume that it will need to be written in VBScript.  Can you still help me?

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 2

Expert Comment

by:odessa
ID: 1091042
you can use same procedure is ASP if you set script language to JavaScript
0
 
LVL 2

Expert Comment

by:aliciaam
ID: 1091043


BUG #: 17353 (sqlbug_65)
 
SYMPTOMS
========
 
When you insert a character through an ODBC data source with either OEM-to-
ANSI translation enabled or a code page translation set, some characters
are translated to an apostrophe (ANSI 39). When the Generate Stored
Procedures option is disabled and a parameter is passed containing these
translated characters, SQL Server returns an error 105:
 
   Unclosed quote before the character string ')'.
 
NOTE: The apostrophe is also referred to as a single quotation mark or
single quote character.
 
CAUSE
=====
 
If a single quotation mark is passed in the parameter, this is replaced
by two single quotation marks and the insert executes correctly.
 
All of the following conditions must be met:
 
 - The Generate Stored Procedure for Prepared Statement option must be
   disabled for the data source.
 
   -and-
 
 - OEM-to-ANSI conversion must be enabled or a code page translator
   selected for the data source.
 
   -and-
 
 - The client application prepares the statement with parameter markers.
 
   -and-
 
 - One of the parameters contains a string which includes an extended
   character which in the translation enabled for the data source is
   converted to the single quotation mark (ANSI 39).
 
When a Transact-SQL statement is sent to the server, the string type
parameters are delimited by single quotation marks, and the translated
single quotation mark character in the parameter makes the statement
ambiguous.
 
WORKAROUND
==========
 
Use any of the following methods to work around the problem:
 
 - Enable Generate Stored Procedure for Prepared Statement. The stored
   procedure is created and the parameters to the stored procedure are
   passed delimited by double quotation marks so that the problem does
   not occur.
 
   -or-
 
 - Disable translation if you don't need this feature. The character is
   not translated and will be stored in the server code page as the
   relevant character.
 
   -or-
 
 - In the client application, parse the parameters before binding them to
   the prepared statement and make appropriate substitutions for characters
   which are causing the problem. Substituting the single quotation mark
   before binding the parameter works as this will be replaced by two
   single quotation marks before being passed to SQL Server.
 
STATUS
======
 
Microsoft has confirmed this to be a problem in Microsoft SQL Server
version 6.5. We are researching this problem and will post new information
here in the Microsoft Knowledge Base as it becomes available.
 

0
 

Author Comment

by:brewman
ID: 1091044
I have seen this but am looking for a someone to give me some code for the workaround in VBScript.  Thanks for your input!
0
 
LVL 2

Accepted Solution

by:
aliciaam earned 100 total points
ID: 1091045
Have you seen this?

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'
 

0
 
LVL 2

Expert Comment

by:aliciaam
ID: 1091046
you can use SET QUOTED_IDENTIFIER ON to have
SQL Server start enforcing the ANSI SQL rules regarding quotation marks

0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

707 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

16 Experts available now in Live!

Get 1:1 Help Now