[dba-SQLServer]Changing apostrophes in string

I know it has been a long time but I finally managed to get around to 
fixing this part of my program.  I have a couple of refinements that I have 
shown below.  One is that if the apostrophe is at the end of the text then 
it would not have been picked up (needed to change the comparison to 
<=).  Second was that if there was more than 1 apostrophe then because the 
ChkText was getting longer with the extra apostrophes it might not have 
checked to the end so I deleted the lbLength variable and checked against 
the new length each loop.  Here is my amended code.

Public Function basFixSingleQuote(strChkText As String) As String

     Dim intPosition As Integer

     intPosition = InStr(1, strChkText, "'")

     Do While intPosition > 0 And intPosition <= Len(strChkText)
         strChkText = Left(strChkText, intPosition) & Mid(strChkText, 
intPosition)
         intPosition = InStr(intPosition + 2, strChkText, "'")
     Loop

     basFixSingleQuote = strChkText

End Function

Regards

David Emerson

At 17/02/2003, you wrote:
>Hi David:
>
>Here is an example that works with all versions of Access:
>
>     ....
>     With EmployeeRecord
>         .Surname = FixSingleQuote(.Surname)
>     End With
>     ....
>
>Public Function FixSingleQuote(ChkText As String) as String
>     Dim lbPosition As Integer
>     Dim lbLength As Integer
>
>     lbLength = Len(ChkText)
>     lbPosition = InStr(1, ChkText, "'")
>
>     Do While lbPosition > 0 And lbPosition < lbLength
>         ChkText = Left(ChkText, lbPosition) & Mid(ChkText, lbPosition)
>         lbPosition = InStr(lbPosition + 2, ChkText, "'")
>     Loop
>
>     FixSingleQuote = ChkText
>
>End Function
>
>HTH
>Jim
>
>-----Original Message-----
>From: dba-sqlserver-admin at databaseadvisors.com
>[mailto:dba-sqlserver-admin at databaseadvisors.com]On Behalf Of David
>Emerson
>Sent: Monday, February 17, 2003 9:58 AM
>To: dba-sqlserver at databaseadvisors.com
>Subject: Re: [dba-SQLServer]Changing apostrophes in string
>
>
>Thanks.  Does anyone have the code handy to do this (I could sit down and
>write a function that does it but am running out of time).
>
>David
>
>At 17/02/2003, you wrote:
> >Hi David,
> >
> >replcace every ' with '' (2 apostrophes, the first ' acts as an escape
> >character) before sending
> >the sql statement to SQL2000.
> >This especially gives you some (though not enough) protection in case of
> >sql injections ([Forms]![frmCustomers]!MName =
> >"test ' drop table tblCustStatement -- " etc.)
> >
> >
> >Christoph Seck
> >
> >
> >
> >-------- Original Message --------
> >Subject: [dba-SQLServer]Changing apostrophes in string (17-Feb-2003 4:21)
> >From:    davide at dalyn.co.nz
> >To:      dbaSQL.chseck at kuehne-holz.de
> >
> > > I tried the archives but couldn't get in .
> > >
> > > I have a simple sql statement to be run from and AXP ADP to SQL2000
> > >
> > > DoCmd.RunSQL "UPDATE tblCustStatement SET tblCustStatement.SMName = '" &
> > > [Forms]![frmCustomers]![MName] & "' WHERE (((tblCustStatement.CustIDNo)=
> > > " & [Forms]![frmCustomers]![txtCustomerID] & ") AND
> > > ((tblCustStatement.StatementNumber)= " &
> > > [Forms]![frmCustomers]![txtInvNumber] & "));"
> > >
> > > How do we handle the following situation where
> > [Forms]![frmCustomers]!MName
> > >
> > > includes an apostrophe?
> > >
> > >
> > > Regards
> > >
> > > David Emerson
> > > DALYN Software Ltd
> > > 25b Cunliffe St, Johnsonville
> > > Wellington, New Zealand
> > > Ph/Fax (877) 456-1205
> > >
> > > _______________________________________________
> > > dba-SQLServer mailing list
> > > dba-SQLServer at databaseadvisors.com
> > > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > > http://www.databaseadvisors.com
> > >
> >
> >_______________________________________________
> >dba-SQLServer mailing list
> >dba-SQLServer at databaseadvisors.com
> >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> >http://www.databaseadvisors.com
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com