Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Apostrophes and Double Quotes - Should They be Allowed in table Text-Type Columns? Expand / Collapse
Author
Message
Posted Tuesday, January 31, 2012 7:40 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 3:46 PM
Points: 598, Visits: 3,821
GSquared (1/31/2012)

Any standard character on the keyboard is a liability for this kind of thing. But well-written code doesn't have problems with it.


If I understand correctly, I think that having to wrap every text field in a function, just in case there are quotes in it, is a design flaw. Ideally, that would be unnecessary.

But thanks for all the information Gus, I want to look into this further.


______________________________________________________________________
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
Post #1244386
Posted Tuesday, January 31, 2012 7:57 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 13,325, Visits: 12,811
You don't have to wrap every field in a function. If your queries are parameterized, which they should be anyway, then it is a non-issue.



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1244409
Posted Tuesday, January 31, 2012 8:03 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:58 AM
Points: 13,872, Visits: 9,600
mtillman-921105 (1/31/2012)
GSquared (1/31/2012)

Any standard character on the keyboard is a liability for this kind of thing. But well-written code doesn't have problems with it.


If I understand correctly, I think that having to wrap every text field in a function, just in case there are quotes in it, is a design flaw. Ideally, that would be unnecessary.

But thanks for all the information Gus, I want to look into this further.


What I'm saying is, you don't need to wrap them in a function unless there's something wrong with the code. The reason people strip these things out is to prevent SQL injection, and it's the wrong way to do that. It's completely unnecessary if you do it the right way.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1244414
Posted Wednesday, February 1, 2012 2:51 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:36 AM
Points: 893, Visits: 2,476
mtillman-921105 (1/30/2012)

Well, if they're not already doing so, I hope that new programming languages start using another delimiter for strings other than quotes. Even brackets "[]" would have been better characters to use, at least for English, since those characters aren't normally necessary.


1) The best delimiter for separating units of ASCII data is the Unit Separator, ASCII 31 (0x1F). Record Separator is ASCII 30 (0x1E), Group Separator is ASCII 29 (0x1D), and File Separator is ASCII 29 (0x1C); these have been defined since ASCII was defined in the early 1960's, though it's fallen out of use.

2) Delimiters shouldn't be relevant if you have good field to field mappings between app and database; regrettably, some of us don't have that luxury.

In response to the original post, as everyone else said, it's the app's problem; the database is there to take exactly what it's given, whether it's a Q, a š, a ©, or an ', and return that upon request.

As far as CPU hit, with current hardware, in general, properly escaping each character in a string is not likely to cost more CPU than checking on the database connection and authentication, formatting the data for the connection type, transmitting the data, getting a result, validating the result, and other database connection overhead tasks.
Post #1245352
Posted Friday, February 3, 2012 6:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:58 AM
Points: 13,872, Visits: 9,600
Nadrek (2/1/2012)
mtillman-921105 (1/30/2012)

Well, if they're not already doing so, I hope that new programming languages start using another delimiter for strings other than quotes. Even brackets "[]" would have been better characters to use, at least for English, since those characters aren't normally necessary.


1) The best delimiter for separating units of ASCII data is the Unit Separator, ASCII 31 (0x1F). Record Separator is ASCII 30 (0x1E), Group Separator is ASCII 29 (0x1D), and File Separator is ASCII 29 (0x1C); these have been defined since ASCII was defined in the early 1960's, though it's fallen out of use.

2) Delimiters shouldn't be relevant if you have good field to field mappings between app and database; regrettably, some of us don't have that luxury.

In response to the original post, as everyone else said, it's the app's problem; the database is there to take exactly what it's given, whether it's a Q, a š, a ©, or an ', and return that upon request.

As far as CPU hit, with current hardware, in general, properly escaping each character in a string is not likely to cost more CPU than checking on the database connection and authentication, formatting the data for the connection type, transmitting the data, getting a result, validating the result, and other database connection overhead tasks.


The ASCII escape characters aren't human-visible and don't have keys on a regular keyboard, so, while they work beatifully for computers, they don't work at all well for people. That's almost certainly why they've fallen out of use. Same reason we don't program in Assembler.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1246469
Posted Friday, February 3, 2012 10:11 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 3:46 PM
Points: 598, Visits: 3,821
Thanks for the information.

I haven't commented much lately, but I'm extra busy with work these days. But I appreciate your time and insights.


______________________________________________________________________
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
Post #1246624
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse