Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Searching for case sensitive data


Searching for case sensitive data

Author
Message
JacekO
JacekO
SSC-Addicted
SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)

Group: General Forum Members
Points: 489 Visits: 606

By the way, my compliments on the Tally thread. It has been fascinating to follow. I haven't thought of any alternatives worth adding to the thread, but it has been thought provoking and educational all the same. I haven't seen such substantive debate going on in any forum outside SSC. Congratulations!



Do you mind posting the link to this thread. I must have missed it.
Thanks.

---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.

The Dixie Flatline
The Dixie Flatline
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3398 Visits: 6898
Somehow users managed to input some of the customer order numbers as lower case


Looks like they tried and failed, Jack. The counter-argument would be that if that column used an uppercase-only collation (or added UPPER to all procs that do inserts), then it wouldn't matter if the application code was broken. I know scrubbing is more of a load on the SQL server, but enforcing data integrity is a primary role of a database.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
The Dixie Flatline
The Dixie Flatline
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3398 Visits: 6898
Do you mind posting the link to this thread. I must have missed it.


Here you go. Flo stirred up quite a debate by identifying a situation where RBAR using CLR was outperforming the set-based solution.

Performance issue with tally solution

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
JacekO
JacekO
SSC-Addicted
SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)

Group: General Forum Members
Points: 489 Visits: 606
Thanks a bunch.

---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.

Christian Buettner-167247
Christian Buettner-167247
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2949 Visits: 3889
Jack Corbett (4/16/2009)
Just a side note. If case is that important to the application then, in my opinion, the application should be upper-casing the string in the first place. Yeah you can do it in the SQL if you need to, but that should be part of the validation.

Never thought I would ever have a different opinion than you, but never say never.
I strongly advise to move this task to the DB side. Thats why we have constraints within the DB as well. We don't trust the application by definition :-D

(It could also be that I misunderstood you and you meant to only validate the UPPER case in the DB, but UPPER the data itself in the application)

Best Regards,

Chris Büttner
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11014 Visits: 14858
Christian Buettner (4/16/2009)
Jack Corbett (4/16/2009)
Just a side note. If case is that important to the application then, in my opinion, the application should be upper-casing the string in the first place. Yeah you can do it in the SQL if you need to, but that should be part of the validation.

Never thought I would ever have a different opinion than you, but never say never.
I strongly advise to move this task to the DB side. Thats why we have constraints within the DB as well. We don't trust the application by definition :-D

(It could also be that I misunderstood you and you meant to only validate the UPPER case in the DB, but UPPER the data itself in the application)


Bob Hovious (4/16/2009)
Somehow users managed to input some of the customer order numbers as lower case


Looks like they tried and failed, Jack. The counter-argument would be that if that column used an uppercase-only collation (or added UPPER to all procs that do inserts), then it wouldn't matter if the application code was broken. I know scrubbing is more of a load on the SQL server, but enforcing data integrity is a primary role of a database.


I figured I'd hit both of these in one post.

Christian,

I'm not sure we disagree. The point I was trying to make, and obviously not clearly, is that a business rule should be enforced in the application so that you do not "waste" a round-trip to the DB with invalid data. Would I want to put some kind of validation in the database, too? Yes. I'd probably use UPPER in my insert/update procedures. If you aren't using stored procedures, but an ORM tool or ad-hoc SQL, you'd have to use an INSTEAD OF trigger in the database to make sure the data was inserted properly and apply the UPPER, otherwise it still has to be done in the application. Ideally you would use a case-sensitive collation for the column and a check constraint.

Bob,

I missed the part of the post you quoted, which causes us to assume that the application IS supposed to be validating the data. As I said above, that doesn't mean I wouldn't validate in the database as well, mainly to protect the database from some ad hoc update by me or another DBA. Is there an upper case only collation? I know you can do case-sensitive, but didn't know you could require one case.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
The Dixie Flatline
The Dixie Flatline
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3398 Visits: 6898
Hey Jack:

I think we're on the same page. I would never advocate that front-end editing be abandoned in favor of ONLY bouncing errors back from the server, but I would rather have redundant checks, and an occasional bounce, than allow data in that breaks a business rule. I've seen what happens with DBs created by developers who don't like constraints, or even foreign keys, because they "interfere with flexibility." (No, I don't think you would ever do this.)

Let me clarify the "upper case collation". Bad choice of words on my part. Every time I get in a hurry I lose precision. I assumed (possibly in error) that the order numbers coming in are validated against an orders table. Using a case-sensitive collation would make the lower-case 'cqs' strings fail. (Of course, it might also cause unexpected duplicates, depending on how the applications are written.)

The point was and is, if keeping those lower case values out is critical, steps can and should be taken to prevent it at the db level. Even if it's only changing the insert procedure to store UPPER(custOrderNo) instead of custOrderNo. In this case, it sounds as if it were more of an annoyance than a show-stopper.

Regards,

Bob

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11014 Visits: 14858
I didn't really think we disagreed, I understood what you meant, and I wanted to make sure that you, and everyone else, understood what I meant. I also was trying to be quick and did not fully explain, or probably think through thoroughly, what I meant.

You and Christian are among the folks I respect on SSC (fortunately a growing group) so I wanted to make sure I clarified. I didn't want you to think I was a fool.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
The Dixie Flatline
The Dixie Flatline
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3398 Visits: 6898
I'm not one to throw stones... especially where the word "fool" is involved ;-)

By the way, the respect is returned.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Christian Buettner-167247
Christian Buettner-167247
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2949 Visits: 3889
Jack Corbett (4/16/2009)
You and Christian are among the folks I respect on SSC (fortunately a growing group) so I wanted to make sure I clarified.

Now that makes me feel honoredWow

I didn't want you to think I was a fool.

Never would. And just to repeat - this "would have been" the first time I disagreed.
So if you were a fool, then I would be one as well (because I mostly (99,9%) agree with what you say). But now don't get the feeling that you are on the safe side, since I actually may be a fool:-D (and sometimes I definitively am - just cannot get this human behaviour out of myself)

Best Regards,

Chris Büttner
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search