SQL Server ASP.NET C# Kitabı

SQL Server’da yapacağımız aramalar, herhangi özel bir ayar uygulamamışsak default olarak case insensitive çalışır yani kayıtlar üzerinde arama yaparken deÄŸerler, büyük-küçük harf duyarlı deÄŸildir. Bu yüzden SQL Server üzerinde SELECT … FROM .. WHERE UserPwd=’abc’ ile SELECT … FROM .. WHERE UserPwd=’ABC’ ifadesi aynı sonucu getirir. Özellikle kullanıcı ile ilgili güvenlik bilgilerinin sorgulandığı durumlarda iÅŸlemlerin büyük-küçük harf duyarlı olmasına ihtiyaç duyabiliriz. Bunu saÄŸlamanın birkaç yöntemi bulunmaktadır. Burada en çok kullanılan yöntemleri örneklendirmeye çalışacağız.

SQL Serverin, deÄŸerleri sıralama düzeni(sort order), SQL 2000 ile birlikte collation olarak tanımlanır. Daha önce yazdığımız bir yazıda SQL Server’daki collation ve language tanımlarıyla ilgili detaylı bilgi alabilirsiniz.
SQL Server’i kurarken eÄŸer sistemin collation deÄŸerini deÄŸiÅŸtirmezsek case insensitive collation olarak kurulur. SQL Server’in güzel yanlarından biri de tüm sistemin collation deÄŸerini deÄŸiÅŸtirmek yerine sadece bir tablonun belli kolonu için collation ayarlaması yaptırmasıdır. ÖrneÄŸin kullanıcı güvenlik bilgilerinin tutulduÄŸu tablodaki ÅŸifre kolonu bu ÅŸekilde set edilebilir.
1.Yöntem
BilindiÄŸi gibi “A” ve “a” deÄŸerleri, günlük konuÅŸma ve yazmada aynı deÄŸerler gibi görünsede iÅŸlemci tarafından farklı deÄŸerler olarak yorumlanır. CPU sadece 1 ve 0′lardan oluÅŸan ikili sistemden (binary) anladığı için bir karakteri yazıldığı gibi deÄŸil onu binary koda çevirerek iÅŸler. “Bütün karakterlerin binary kodları birbirinden farklı olduÄŸu için SQL Server’a bir deÄŸer gönderdiÄŸimiz zaman onu olduÄŸu gibi deÄŸil binary koda çevirerek yorumlanmasını saÄŸlarsak büyük-küçük harf duyarlılığı oluÅŸturmuÅŸ oluruz. Bunu yapmanın yolu da SQL Server’da CAST fonksiyonunu kullanarak karşılaÅŸtırılacak deÄŸerleri binary veya varbinary veri tipine dönüştürmektir. Member tablosu ve içerisinde MemberPwd isimli bir kolonun olduÄŸunu düşünelim. Bu kolonda “abc” ve “ABC” deÄŸerlerinin olduÄŸunu varsayarak aÅŸağıdaki sorguları çalıştıralım.

DECLARE @GonderilenSifre varchar(10)
SET @GonderilenSifre='abc'
SELECT * FROM Member
WHERE MemberPwd='abc'

Bu sorgunun sonucunda hem “abc” hem de “ABC” ÅŸifresine sahip üyeler gelir. Oysa biz sadece küçük harflerle yazılmış “abc” ÅŸifresini arıyoruz. Kodumuzu aÅŸağıdaki gibi düzeltirsek istediÄŸimiz sonuca varırız.

DECLARE @GonderilenSifre varchar(10)
SET @GonderilenSifre='abc'
SELECT * FROM Member
WHERE CAST(MemberPwd as binary)=CAST(@GonderilenSifre as binary)

Gerçekten de örnek olarak “abc” ve “ABC” deÄŸerleri üzerinde gidecek olursak ikisinin binary kodunun farklı olduÄŸunu görürüz.

SELECT CAST('abc' as varbinary)--0x616263
SELECT CAST('ABC' as varbinary)--0x414243

Burada vyaskn.tripod.com’da okuduÄŸum, dikkate alınması gereken bir not var. EÄŸer MemberPwd tablosunda herhangi bir index varsa CAST iÅŸleminin olduÄŸu Query’de sözkonusu index kullanılmayacak ve Table Scan iÅŸlemi yapılacaktır. Bu da istenmeyen bir durum olduÄŸu için Query Optimizer’ın (QP) indexi kullanabilmesi için kolonu bu ÅŸekilde sadece CAST fonksiyonu içerisinde deÄŸil ayrıca olduÄŸu gibi belirtmemizin faydası olacaktır. Querymizi aÅŸağıdaki gibi düzeltelim.

DECLARE @GonderilenSifre varchar(10)
SET @GonderilenSifre='abc'
SELECT * FROM Member
WHERE CAST(MemberPwd as binary)=CAST(@GonderilenSifre as binary)
AND MemberPwd = @GonderilenSifre

2.Yöntem
Ä°kinci yöntem olarak daha önce yazdığımız COLLATE ifadesini kullanabiliriz. COLLATE ifadesi, verdiÄŸimizin deÄŸerin SQL SERVER tarafından belirtilmiÅŸ collation türünde yorumlanmasını saÄŸlar. Collation mekanizması, büyük veya küçük karakter setini ayrı ayrı içerdiÄŸi için query esnasında dilimize uygun olarak ilgili collation tipini belirtmemiz de bizi amacına ulaÅŸtırır. SQL_Latin1_General_CP1254_CI_AS collation serisi Türkçe karakter setinin de bulunduÄŸu gibi deÄŸerlerin case-insensitive durumunu temsil eder. Sorgularda bunun case-sensitive türünü kullanacağız(SQL_Latin1_General_CP1254_CS_AS). SQL Server’in default collation deÄŸerini okumak için sp_helpsort prosedürü kullanılabilir.

DECLARE @GonderilenSifre varchar(10)
SET @GonderilenSifre='abc'
SELECT * FROM Member
WHERE MemberPwd Collate SQL_Latin1_General_CP1254_CS_AS = @GonderilenSifre
AND MemberPwd =@GonderilenSifre --INDEX kullanması için

3.Yöntem
SQL Server’da önceki yöntemlerden farklı olarak BINARY_CHECKSUM fonksiyonuyla da bu iÅŸ yapılabilmektedir. Ancak uzmanlar bunun doÄŸru bir yöntem olmadığını, her zaman doÄŸru sonucu vermeyeceÄŸini ifade etmiÅŸtir.
Üçüncü yöntem olarak bu iÅŸi SQL Server’da deÄŸil business logic katmanında halledebiliriz. SQL Server’dan klasik yöntemle uyuÅŸan ÅŸifreleri çektikten sonra uygulamamızı geliÅŸtirdiÄŸimiz dilin(C#, VB.NET, Delphi . . .) fonksiyonlarını kullanarak büyük-küçük harf uyumluluÄŸunu kontrol edebiliriz. Hatta bazı programlama dilleri default olarak case sensitive karşılaÅŸtırma yapmaktadır.