RE: case sensitivity in stored procedures
I missed the beginning of this thread but by looking at the last mail I
assume that you want to check the cases sensitivity of a string in Transact
SQL? Here is some code I worte to log a user into a site ensuring each
character of the submitted password is the correct case:
SELECT @endstring = ''
SELECT @passlen = LEN(@password)
SELECT @loop = 1
SELECT @startstring = "SELECT * FROM Login WHERE Username = '" + @username +
"' AND Password = '" + @password + "'"
WHILE @loop <= @passlen
BEGIN
SELECT @endstring = @endstring + " AND ascii(Substring(Password," +
CONVERT(char,@loop) + ",1)) = ascii(Substring('" + @Password + "'," +
CONVERT(char,@loop) + ",1))"
SELECT @loop = @loop + 1
END
--
Andrew Ewings
Project Manager
Thoughtbubble Ltd
http://www.thoughtbubble.net
--
United Kingdom
http://www.thoughtbubble.co.uk/
Tel: +44 (0) 20 7387 8890
--
New Zealand
http://www.thoughtbubble.co.nz/
Tel: +64 (0) 9 419 4235
--
The information in this email and in any attachments is confidential and
intended solely for the attention and use of the named addressee(s). Any
views or opinions presented are solely those of the author and do not
necessarily represent those of Thoughtbubble. This information may be
subject to legal, professional or other privilege and further distribution
of it is strictly prohibited without our authority. If you are not the
intended recipient, you are not authorised to disclose, copy, distribute, or
retain this message. Please notify us on +44 (0)207 387 8890.
-Original Message-
From: Paul Johnston [mailto:[EMAIL PROTECTED]]
Sent: 24 January 2001 13:03
To: CF-Talk
Subject: RE: case sensitivity in stored procedures
It can be done! You can bypass the case-insensitivity, although it's a bit
long winded (but it works).
What you need to do is use the ASCII character number to make a string case
sensitive (ie convert it into ASCII characters and test against those!).
there are two functions, ASCII and CHAR. Here's an example (from the
Transact-SQL Help) and note that D and d have different numbers:
SET TEXTSIZE 0
SET NOCOUNT ON
-- Create the variables for the current position of the character string
-- and for the character string.
DECLARE @position int, @string char(15)
-- Initialize the variables.
SET @position = 1
SET @string = 'Du monde entier'
WHILE @position <= DATALENGTH(@string)
BEGIN
SELECT ASCII(SUBSTRING(@string, @position, 1)),
CHAR(ASCII(SUBSTRING(@string, @position, 1)))
SET @position = @position + 1
END
SET NOCOUNT OFF
GO
Here is the result set:
--- -
68 D
--- -
117 u
--- -
32
--- -
109 m
--- -
111 o
--- -
110 n
--- -
100 d
--- -
101 e
--- -
32
--- -
101 e
--- -
110 n
--- -
116 t
--- -
105 i
--- -
101 e
--- -
114 r
Enjoy!
Paul
> -Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, January 24, 2001 12:55 PM
> To: CF-Talk
> Subject: RE: case sensitivity in stored procedures
>
>
>
> Nick, this was helpful, thanks - I didn't realize it was an installation
> option, and I've never seen it installed except with the
> default setting. I
> looked it up in BOL and it gives pretty good details. I'm sure
> I've been
> lazy enough with object names, etc. that I wouldn't want
> case-sensitivity
> ON - half my queries or code might fail. Well maybe not half, but some.
>
> I had been trying out some code with tests of exact
> (case-sensitive) words,
> such as "WHERE Password LIKE '[S][e][c][R][e][t]', but so far it seems
> that the case-sensitivity setting relates to this too. As a
> result, SEcret,
> seCRET, SeCrEt, etc. all come up as LIKE the above expression.
>
> So it might be better to say that SQL Server is case-insensitive by
> default, period.
>
>
> Mark
> [EMAIL PROTECTED]
>
>
>
> Sebastian
>
> Apologies, not a very helpful answer.
>
> What I meant was SQL Server is case-sensitive, period.
>
> But actually that's not always true, only if SQL Server is
> installed with a case sensitive sort order.
>
> http://msdn.microsoft.com/library/default.asp?URL=/library/psdk/
sql/8_ar_da_
10.htm
Nick
~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: case sensitivity in stored procedures
Hi I'm not sure if you got this yesterday so I'm sending again Select * from MyTbl where convert(varbinary, column1) = convert(varbinary, 'ABCD') Justin >-Original Message- >From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] >Sent: Wednesday, January 24, 2001 12:55 PM >To: CF-Talk >Subject: RE: case sensitivity in stored procedures > > > >Nick, this was helpful, thanks - I didn't realize it was an installation >option, and I've never seen it installed except with the default setting. I >looked it up in BOL and it gives pretty good details. I'm sure I've been >lazy enough with object names, etc. that I wouldn't want case-sensitivity >ON - half my queries or code might fail. Well maybe not half, but some. > >I had been trying out some code with tests of exact (case-sensitive) words, >such as "WHERE Password LIKE '[S][e][c][R][e][t]', but so far it seems >that the case-sensitivity setting relates to this too. As a result, SEcret, >seCRET, SeCrEt, etc. all come up as LIKE the above expression. > >So it might be better to say that SQL Server is case-insensitive by >default, period. > > >Mark >[EMAIL PROTECTED] > > > >Sebastian > >Apologies, not a very helpful answer. > >What I meant was SQL Server is case-sensitive, period. > >But actually that's not always true, only if SQL Server is >installed with a case sensitive sort order. > >http://msdn.microsoft.com/library/default.asp?URL=/library/psdk/sql /8_ar_da_ 10.htm Nick ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: case sensitivity in stored procedures
If you're talking about passwords, you may consider using CFX_HASH to create the passwords. Its hashes are case sensitive so "A" is not the same as "a" and it would probably end up being more secure. HTH Stephen -Original Message- From: Paul Johnston [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 24, 2001 8:03 AM To: CF-Talk Subject: RE: case sensitivity in stored procedures It can be done! You can bypass the case-insensitivity, although it's a bit long winded (but it works). What you need to do is use the ASCII character number to make a string case sensitive (ie convert it into ASCII characters and test against those!). there are two functions, ASCII and CHAR. Here's an example (from the Transact-SQL Help) and note that D and d have different numbers: SET TEXTSIZE 0 SET NOCOUNT ON -- Create the variables for the current position of the character string -- and for the character string. DECLARE @position int, @string char(15) -- Initialize the variables. SET @position = 1 SET @string = 'Du monde entier' WHILE @position <= DATALENGTH(@string) BEGIN SELECT ASCII(SUBSTRING(@string, @position, 1)), CHAR(ASCII(SUBSTRING(@string, @position, 1))) SET @position = @position + 1 END SET NOCOUNT OFF GO Here is the result set: --- - 68 D --- - 117 u --- - 32 --- - 109 m --- - 111 o --- - 110 n --- - 100 d --- - 101 e --- - 32 --- - 101 e --- - 110 n --- - 116 t --- - 105 i --- - 101 e --- - 114 r Enjoy! Paul > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, January 24, 2001 12:55 PM > To: CF-Talk > Subject: RE: case sensitivity in stored procedures > > > > Nick, this was helpful, thanks - I didn't realize it was an installation > option, and I've never seen it installed except with the > default setting. I > looked it up in BOL and it gives pretty good details. I'm sure > I've been > lazy enough with object names, etc. that I wouldn't want > case-sensitivity > ON - half my queries or code might fail. Well maybe not half, but some. > > I had been trying out some code with tests of exact > (case-sensitive) words, > such as "WHERE Password LIKE '[S][e][c][R][e][t]', but so far it seems > that the case-sensitivity setting relates to this too. As a > result, SEcret, > seCRET, SeCrEt, etc. all come up as LIKE the above expression. > > So it might be better to say that SQL Server is case-insensitive by > default, period. > > > Mark > [EMAIL PROTECTED] > > > > Sebastian > > Apologies, not a very helpful answer. > > What I meant was SQL Server is case-sensitive, period. > > But actually that's not always true, only if SQL Server is > installed with a case sensitive sort order. > > http://msdn.microsoft.com/library/default.asp?URL=/library/psdk/ sql/8_ar_da_ 10.htm Nick ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: case sensitivity in stored procedures
There's another reason you don't want case-sensitivity turned on for the whole server... the Merant database drivers have a bug in them: they have internal calls to a stored procedure named sp_SeRvEr_InFo when the actual stored procedure is named sp_server_info. With case sensitivity turned on every SQL query will infrequently crash with an error about not finding sp_SeRvEr_InFo. If you *do* need all comparisons to be case-sensitive, make sure you make a copy of that stored procedure under the crazy capitalization. At 07:54 AM 1/24/01 -0500, [EMAIL PROTECTED] wrote: >Nick, this was helpful, thanks - I didn't realize it was an installation >option, and I've never seen it installed except with the default setting. I >looked it up in BOL and it gives pretty good details. I'm sure I've been >lazy enough with object names, etc. that I wouldn't want case-sensitivity >ON - half my queries or code might fail. Well maybe not half, but some. > >I had been trying out some code with tests of exact (case-sensitive) words, >such as "WHERE Password LIKE '[S][e][c][R][e][t]', but so far it seems >that the case-sensitivity setting relates to this too. As a result, SEcret, >seCRET, SeCrEt, etc. all come up as LIKE the above expression. > >So it might be better to say that SQL Server is case-insensitive by >default, period. > > >Mark >[EMAIL PROTECTED] > > > >Sebastian > >Apologies, not a very helpful answer. > >What I meant was SQL Server is case-sensitive, period. > >But actually that's not always true, only if SQL Server is >installed with a case sensitive sort order. > >http://msdn.microsoft.com/library/default.asp?URL=/library/psdk/sql/8_ar_da_ > >10.htm > >Nick > > > > > > > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: case sensitivity in stored procedures
It can be done! You can bypass the case-insensitivity, although it's a bit long winded (but it works). What you need to do is use the ASCII character number to make a string case sensitive (ie convert it into ASCII characters and test against those!). there are two functions, ASCII and CHAR. Here's an example (from the Transact-SQL Help) and note that D and d have different numbers: SET TEXTSIZE 0 SET NOCOUNT ON -- Create the variables for the current position of the character string -- and for the character string. DECLARE @position int, @string char(15) -- Initialize the variables. SET @position = 1 SET @string = 'Du monde entier' WHILE @position <= DATALENGTH(@string) BEGIN SELECT ASCII(SUBSTRING(@string, @position, 1)), CHAR(ASCII(SUBSTRING(@string, @position, 1))) SET @position = @position + 1 END SET NOCOUNT OFF GO Here is the result set: --- - 68 D --- - 117 u --- - 32 --- - 109 m --- - 111 o --- - 110 n --- - 100 d --- - 101 e --- - 32 --- - 101 e --- - 110 n --- - 116 t --- - 105 i --- - 101 e --- - 114 r Enjoy! Paul > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, January 24, 2001 12:55 PM > To: CF-Talk > Subject: RE: case sensitivity in stored procedures > > > > Nick, this was helpful, thanks - I didn't realize it was an installation > option, and I've never seen it installed except with the > default setting. I > looked it up in BOL and it gives pretty good details. I'm sure > I've been > lazy enough with object names, etc. that I wouldn't want > case-sensitivity > ON - half my queries or code might fail. Well maybe not half, but some. > > I had been trying out some code with tests of exact > (case-sensitive) words, > such as "WHERE Password LIKE '[S][e][c][R][e][t]', but so far it seems > that the case-sensitivity setting relates to this too. As a > result, SEcret, > seCRET, SeCrEt, etc. all come up as LIKE the above expression. > > So it might be better to say that SQL Server is case-insensitive by > default, period. > > > Mark > [EMAIL PROTECTED] > > > > Sebastian > > Apologies, not a very helpful answer. > > What I meant was SQL Server is case-sensitive, period. > > But actually that's not always true, only if SQL Server is > installed with a case sensitive sort order. > > http://msdn.microsoft.com/library/default.asp?URL=/library/psdk/ sql/8_ar_da_ 10.htm Nick ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: case sensitivity in stored procedures
Nick, this was helpful, thanks - I didn't realize it was an installation option, and I've never seen it installed except with the default setting. I looked it up in BOL and it gives pretty good details. I'm sure I've been lazy enough with object names, etc. that I wouldn't want case-sensitivity ON - half my queries or code might fail. Well maybe not half, but some. I had been trying out some code with tests of exact (case-sensitive) words, such as "WHERE Password LIKE '[S][e][c][R][e][t]', but so far it seems that the case-sensitivity setting relates to this too. As a result, SEcret, seCRET, SeCrEt, etc. all come up as LIKE the above expression. So it might be better to say that SQL Server is case-insensitive by default, period. Mark [EMAIL PROTECTED] Sebastian Apologies, not a very helpful answer. What I meant was SQL Server is case-sensitive, period. But actually that's not always true, only if SQL Server is installed with a case sensitive sort order. http://msdn.microsoft.com/library/default.asp?URL=/library/psdk/sql/8_ar_da_ 10.htm Nick ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: case sensitivity in stored procedures
Sebastian Apologies, not a very helpful answer. What I meant was SQL Server is case-sensitive, period. But actually that's not always true, only if SQL Server is installed with a case sensitive sort order. http://msdn.microsoft.com/library/default.asp?URL=/library/psdk/sql/8_ar_da_ 10.htm Nick -Original Message- From: sebastian palmigiani [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 24, 2001 4:01 AM To: CF-Talk Subject: Re: case sensitivity in stored procedures can you give me a hint? Sebastian on 1/23/01 11:34 AM, DeVoil, Nick at [EMAIL PROTECTED] wrote: >> Can you do a case sensitivity comparison within a SQL Server stored > procedure? > > Yes. > > > ** > Information in this email is confidential and may be privileged. > It is intended for the addressee only. If you have received it in error, > please notify the sender immediately and delete it from your system. > You should not otherwise copy it, retransmit it or use or disclose its > contents to anyone. > Thank you for your co-operation. > ** > > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: case sensitivity in stored procedures
can you give me a hint? Sebastian on 1/23/01 11:34 AM, DeVoil, Nick at [EMAIL PROTECTED] wrote: >> Can you do a case sensitivity comparison within a SQL Server stored > procedure? > > Yes. > > > ** > Information in this email is confidential and may be privileged. > It is intended for the addressee only. If you have received it in error, > please notify the sender immediately and delete it from your system. > You should not otherwise copy it, retransmit it or use or disclose its > contents to anyone. > Thank you for your co-operation. > ** > > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: case sensitivity in stored procedures
I would certainly also like to see if people have easy ways of doing this (short of evaluating each character using CHR). If one allows two different users with passwords 'sEcreT' and 'SecrEt', for example, how to distinguish these? Mark [EMAIL PROTECTED] > Can you do a case sensitivity comparison within a SQL Server stored procedure? Yes. ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: case sensitivity in stored procedures
Yes, check out the SQL faq http://www.swynk.com/faq/sql/sqlserverfaq.asp Justin >-Original Message- >From: DeVoil, Nick [mailto:[EMAIL PROTECTED]] >Sent: Tuesday, January 23, 2001 5:34 PM >To: CF-Talk >Subject: RE: case sensitivity in stored procedures > > >> Can you do a case sensitivity comparison within a SQL Server stored >procedure? > >Yes. > > >** >Information in this email is confidential and may be privileged. >It is intended for the addressee only. If you have received it in error, >please notify the sender immediately and delete it from your system. >You should not otherwise copy it, retransmit it or use or disclose its >contents to anyone. >Thank you for your co-operation. >** > > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: case sensitivity in stored procedures
> Can you do a case sensitivity comparison within a SQL Server stored procedure? Yes. ** Information in this email is confidential and may be privileged. It is intended for the addressee only. If you have received it in error, please notify the sender immediately and delete it from your system. You should not otherwise copy it, retransmit it or use or disclose its contents to anyone. Thank you for your co-operation. ** ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
