About Me

My photo
Northglenn, Colorado, United States
I'm primarily a BI Developer on the Microsoft stack. I do sometimes touch upon other Microsoft stacks ( web development, application development, and sql server development).

Friday, January 06, 2012

Removing accents for string comparisons in tsql

I've been reading The Daily WTF, and found this one interesting: http://thedailywtf.com/Articles/The-AntiSQL-Coalition-.aspx

By using the Latin1_General_CI_AI   (AI stands for Accent Insensitive)
the user was able to by-pass special characters like: É,È,Ê, and Ë for E when doing a search.
Example:

SELECT*
FROMPersons
WHEREname collate Latin1_General_CI_AI LIKE @name
AND surname collate Latin1_General_CI_AI LIKE @surname
ORDERBY name, surname

Additional references:
http://msdn.microsoft.com/en-us/library/ms187582.aspx


P.S. Funny to see other companies have Database haters too.

No comments: