Tipicamente nelle nostre query di ricerca non ci preoccupiamo delle maiuscole
e minuscole, difatti nella maggior parte delle situazioni la ricerca case sensitive del tutto inutile.
A volte però capita di dover gestire dei campi il cui significato cambia in
modo enorme a seconda che i caratteri siano minuscoli oppure maiuscoli, pensiamo
alle login sui sistemi UNIX o LIKE-UNIX dove le password e gli username sono generalmente case sensitive.
Come fare
Esaminiamo queste due stringhe, "La gatta sul tetto che scotta" e
confrontiamola con la sua gemella "La Gatta sul tetto che scotta".
L'apparenza inganna, le stringhe sono del tutto identiche, ed in effetti è vero tranne che per un piccolo particolare la G maiuscola di Gatta presente nella seconda stringa.
Esaminiamo ora i corrispondenti binari e vediamo come le differenze tra le
due questa volta siano più nette (relativamente), la prima stringa in binario:
0x4C612067617474612073756C20746574746F206368652073636F747461
la seconda:
0x4C612047617474612073756C20746574746F206368652073636F747461
Come potete vedere la differenza consiste in un 6 al posto di un 4, la parte
restante invece è totalmente identica.
Questa osservazione ci da il là alla implementazione di una ricerca CASE
SENSITIVE su un campo di testo in SQL Server, come sempre il Transact SQL ci
verrà in aiuto e ci permetterà di realizzare questa piccola "magia".
Prima di iniziare apriamo il nostro QA e cominciamo a capire bene come inpostare una SELECT case sensitive:
USE tempdb
GO
declare @str1 varchar(30), @str2 varchar(30)
SELECT @str1='La gatta sul tetto che scotta',
@str2='La Gatta sul tetto che scotta'
print 'Confronto normale' + char(13)
if @str1 = @str2
print '>> Le stringhe sono identiche' + char(10)
else
print '>> Le stringhe sono differenti' + char(10)
print 'Confronto binario' + char(13)
if convert(varbinary,@str1) = convert(varbinary,@str2)
print '>> Le stringhe sono identiche' + char(10)
else
print '>> Le stringhe sono differenti' + char(10)
Premete F5 ed il result panel dovrebbe darci un responso simile a questo:
Confronto normale
>> Le stringhe sono identiche
Confronto binario
>> Le stringhe sono differenti
Ecco che SQL Server ha riconosciuto la differenza tra le due stringhe grazie
al confronto binario.
Avrete già capito che il trucchetto consiste nell'effettuare il confronto
tra le stringhe trasformando il tipo dati varchar in varbinary, il suo corrispondente binario tramite la funzione convert() ed il gioco è fatto!
Cerchiamo ora di creare una login case sensitive, lo script potrebbe essere
una traccia interessante per potenziare i vostri script di autenticazione a siti
web protetti:
/*
SCRIPT PER UNA LOGIN CASE SENSITIVE
BY LUCA MILAN PER FREEASP.IT
*/
USE tempdb
SET NOCOUNT ON
GO
--se esiste la tabella la elimino
if object_id('#login')>0
drop table #login
--se esiste la procedura la elimino
if object_id('#p_login')>0
drop procedure #p_login
--creo la tabella temporanea
create table #login (id int identity, username char(10), [password] char(10))
--inserisco alcuni valori di prova
insert into #login values ('pippo','pluto');
insert into #login values ('Pippo','pluto');
insert into #login values ('paperino','paperone');
insert into #login values ('paperino','Paperone');
GO
--creo la procedure temporanea
create procedure #p_login
@username char(10)
, @password char(10)
as
select * from #login where
convert(binary(10),username) = convert(binary(10),@username)
and
convert(binary(10),[password]) = convert(binary(10),@password)
GO
--eseguiamo la procedura
--non riconosce 'Pippo','pluto'
exec #p_login 'pippo','pluto'
--non riconosce 'pippo','pluto'
exec #p_login 'Pippo','pluto'
Per eseguire la query non dovete fare altro che caricare il file scripts.sql
allegato nel file zip nel vostro query analyzer e premere come al solito il tasto
F5, oppure cut & paste del codice da questa pagina. Per informazioni o dubbi
scrivetemi pure.
