View Single Post
  #9 (permalink)  
Old September 15th, 2006, 07:47 AM
Imar's Avatar
Imar Imar is offline
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

 
Quote:
quote:Come on
Quote:

How can you say that it is possible to insert an apostrophe using text command........ In any scenario, you won't be able to insert that single quote to the database except procedure.

The only alternate is to replace the single quote with space or something other character.
What?? How can you say that? It *is* possible, but you just didn't know how to do it.... ;)

Replacing an apostrophe with a space or another character sounds like a bad thing to do. If you need to present the data later again, how do you distinguish between real spaces and the one you added to replace the apostrophe? You can't, so you end up with corrupt data.

But since database vendors got sick and tired of complaints from all the O'Brien families, they came up with a simple and successful solution: replace every ' with two single ' characters:

INSERT INTO MyTable(Name) VALUES('O''Brien')

successfully inserts a single apostrophe in the database.

So the code example could be rewritten as this:
Code:
Dim comments As String
comments = txtComments.Text.Replace("'", "''")
Dim strSQL as string = "INSERT INTO dbo.myTable(comments, name) VALUES('myName', '" & comments & "')"
That said, I agree that it's much better to use parameterized queries as they protect you better against "SQL injection".

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
While typing this post, I was listening to: Insight by Joy Division (Track 6 from the album: Heart And Soul (CD 1)) What's This?