<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 7,2000
»
Strategies
»
Apostrophes and Double Quotes - Should They...
16 posts, Page 1 of 2
1
2
»»
Apostrophes and Double Quotes - Should They be Allowed in table Text-Type Columns?
Rate Topic
Display Mode
Topic Options
Author
Message
mtillman-921105
mtillman-921105
Posted Monday, January 30, 2012 1:01 PM
Right there with Babe
Group: General Forum Members
Last Login: Friday, May 10, 2013 11:54 AM
Points: 749,
Visits: 3,767
As a best practice, should apostrophes and double quotes be removed from text in SQL tables?
I can see how it should be allowed. For example, names such as "O'Hare" contain the apostrophe, so that is the best way to store them. The apostrophe is part of their name, after all.
But if you allow it, will it trip up programming, not just in SQL, but 3rd party software using the data? And if they are allowed, do third party applications have to run each text string through a function to avoid errors?
I'd also like to know if your data entry (or other data input) is validated so that apostrophes and/or double quotes are prohibited. Why or why not?
______________________________________________________________________
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
Post #1243868
Sean Lange
Sean Lange
Posted Monday, January 30, 2012 1:08 PM
SSCrazy Eights
Group: General Forum Members
Last Login: Yesterday @ 2:45 PM
Points: 8,567,
Visits: 8,218
Yes they should be allowed. The system should NEVER NEVER NEVER change the data. The job of sql is store and retrieve data. If there are challenges to retrieving that data then it should be handled by sql.
_______________________________________________________________
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
Post #1243874
mtillman-921105
mtillman-921105
Posted Monday, January 30, 2012 1:23 PM
Right there with Babe
Group: General Forum Members
Last Login: Friday, May 10, 2013 11:54 AM
Points: 749,
Visits: 3,767
Sean Lange (1/30/2012)
Yes they should be allowed. The system should NEVER NEVER NEVER change the data. The job of sql is store and retrieve data. If there are challenges to retrieving that data then it should be handled by sql.
Isn't allowing them just asking for trouble? I recently witnessed a 3rd party application throw an error, simply because of a quote. So it is the 3rd party's developer's fault then? I'm playnig a devil's advocate here, but I can see both sides of this.
______________________________________________________________________
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
Post #1243887
GSquared
GSquared
Posted Monday, January 30, 2012 1:26 PM
SSCoach
Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439,
Visits: 9,569
mtillman-921105 (1/30/2012)
Sean Lange (1/30/2012)
Yes they should be allowed. The system should NEVER NEVER NEVER change the data. The job of sql is store and retrieve data. If there are challenges to retrieving that data then it should be handled by sql.
Isn't allowing them just asking for trouble? I recently witnessed a 3rd party application throw an error, simply because of a quote. So it is the 3rd party's developer's fault then? I'm playnig a devil's advocate here, but I can see both sides of this.
Yes, that's a problem with the software.
It almost certainly means they are using some very weak methods of preventing SQL injection attacks, using string manipulation where they should be using query parameterization.
Those methods of preventing injection attacks have been obsolete for over a decade, but there are people who still use them without understanding that they don't actually work properly, never have, and never will.
- 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 #1243889
Sean Lange
Sean Lange
Posted Monday, January 30, 2012 1:27 PM
SSCrazy Eights
Group: General Forum Members
Last Login: Yesterday @ 2:45 PM
Points: 8,567,
Visits: 8,218
If some 3rd party app can't handle a single quote in the data then the third party vendor should deal with it. It really boils down to what sql is supposed to do. It should store and retrieve data, not change it so some developers can be lazy.
_______________________________________________________________
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
Post #1243890
mtillman-921105
mtillman-921105
Posted Monday, January 30, 2012 1:44 PM
Right there with Babe
Group: General Forum Members
Last Login: Friday, May 10, 2013 11:54 AM
Points: 749,
Visits: 3,767
Sean Lange (1/30/2012)
If some 3rd party app can't handle a single quote in the data then the third party vendor should deal with it. It really boils down to what sql is supposed to do. It should store and retrieve data, not change it so some developers can be lazy.
I was also thinking about performance. So I wonder how the developers get around the issue - do they have to run every text field though a function, stripping out the quotes in case there are any there? Wouldn't that cause a performance hit?
______________________________________________________________________
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
Post #1243905
Lowell
Lowell
Posted Monday, January 30, 2012 1:57 PM
SSChampion
Group: General Forum Members
Last Login: Yesterday @ 6:56 PM
Points: 11,613,
Visits: 27,669
if the software uses parameters, quotes are never a problem, performance could increase, as the query would benefit from the ability to be cached, because the parameters allow it to be typed for other values.
Lowell
--
There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1243914
GSquared
GSquared
Posted Monday, January 30, 2012 1:58 PM
SSCoach
Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439,
Visits: 9,569
Stripping that kind of thing from a string does, obviously, take some CPU cycles, but it's usually minimal. Might make a difference on a server that was close to hardware overload already, but it's a "straw that broke the cammel's back" kind of thing. If it's not already overloaded, you'll probably never see the difference in performance.
- 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 #1243915
mtillman-921105
mtillman-921105
Posted Monday, January 30, 2012 2:16 PM
Right there with Babe
Group: General Forum Members
Last Login: Friday, May 10, 2013 11:54 AM
Points: 749,
Visits: 3,767
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.
______________________________________________________________________
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
Post #1243927
GSquared
GSquared
Posted Tuesday, January 31, 2012 6:56 AM
SSCoach
Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439,
Visits: 9,569
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.
Any standard character on the keyboard is a liability for this kind of thing. But well-written code doesn't have problems with it.
- 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 #1244344
« Prev Topic
|
Next Topic »
16 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
