How to handle data with apostrope in name like O'Neil in a SQL string

[leo57]

I tried adding a chr(34) but SQL does not like it.

" Report1 = " chr(34) & Me.txtReport1.Text & chr(34) & ", " & _

'here is my original SQL string
SQLUpdateString = "Update Recognition Set CommonName = '" & Me.txtEmployeeName.Text & "', " & _
                        " EmployeeEPriseID = '" & Me.txtEmployeeEPriseID.Text & "', " & _
                        " Location = '" & Me.txtLocation.Text & "', " & _
                        " VPName = '" & Me.txtVPName.Text & "', " & _
                        " Nominator = '" & Me.txtNominator.Text & "', " & _
                        " Reporttomanager = '" & Me.txtReportToManager.Text & "', " & _
                        " Report1 = '" & Me.txtReport1.Text & "', " & _
                        " Report2 = '" & Me.txtReport2.Text & "', " & _
                        " Report3 = '" & Me.txtReport3.Text & "', " & _
                        " AwardType = '" & Me.ddlAwardTypes.Text & "', " & _
                        " [Event] = '" & Me.txtEvent.Text & "', " & _
                        " Awarded = '" & Me.ddlAwardedYN.Text & "', " & _
                        " DateAwarded = '" & Me.txtDateAwarded.Text & "', " & _
                        " NominationSummary = '" & Me.txtNominationSummary.Text & "', " & _
                        " ePrizeID = '" & Me.txtUserEPriseID.Text & "' " & _
                       "Where UniqueID = " & Session("EditSQLRecordID") & ""

 

[ChrisHirst]

Escape it by using two single quotes, as in

"It''s the the dog''s ........"

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

 

[bborissov]

use parameters


Borislav Borissov
VFP9 SP2, SQL Server

 

[leo57]

I ended up doing this.

Dim Location As String = Replace(Me.txtLocation.Text, "'", "''")

 

[gbaughma]

A stored procedure and parameters is safer... and prevents SQL injection.

The code that you're using currently is very susceptible to injection.

... something as simple as typing this:
'; TRUNCATE TABLE Recognition;

... into your txtEmployeeName field.

It's worse if you use a select statement....
Say, for example you had this:

SELECT * FROM Users WHERE UserID='" & txtUserID & "'"

... now, type into the txtUserID...
' OR '1'='1

... now your select statement just became:

SELECT * FROM Users WHERE UserID='' OR '1'='1'

... I just got your entire users table.

This is the lesson learned, and why it should be converted over to stored procedures with parameters.





Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com