[dba-SQLServer]Changing apostrophes in string

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