Dinamik SQL Riskleri

Bu konudaki bütün sorgular sabit metinlerdi. Uygulamalar ise sorguları çoğu zaman çalışma zamanında kurar: arama ekranında doldurulan alanlara göre koşullar eklenir, sıralama sütunu kullanıcıdan gelir, sayfa numarası her istekte değişir.

Sorgu metnini kullanıcı girdisiyle birleştirerek kurmak, ilk bakışta yalnız bir yazım kolaylığı gibi görünür. Üç ayrı sonucu vardır: sorgu sıradan girdilerde kırılır, girdinin sorgunun anlamını değiştirmesine kapı açar ve planlayıcının yaptığı işi her çağrıda tekrarlatır. Bu ders üçünü de ele alır.

Veri Kümesi

rm -f kutuphane.db
cat > kurulum.sql <<'SQL'
CREATE TABLE sube (
  sube_id INTEGER PRIMARY KEY,
  ad      TEXT NOT NULL,
  sehir   TEXT NOT NULL
);
CREATE TABLE uye (
  uye_id       INTEGER PRIMARY KEY,
  ad           TEXT NOT NULL,
  sehir        TEXT NOT NULL,
  kayit_tarihi TEXT NOT NULL
);
CREATE TABLE kitap (
  kitap_id INTEGER PRIMARY KEY,
  baslik   TEXT NOT NULL,
  yazar    TEXT NOT NULL,
  yil      INTEGER NOT NULL,
  sube_id  INTEGER NOT NULL
);
CREATE TABLE odunc (
  odunc_id    INTEGER PRIMARY KEY,
  kitap_id    INTEGER NOT NULL,
  uye_id      INTEGER NOT NULL,
  alis_tarihi TEXT NOT NULL,
  iade_tarihi TEXT
);

INSERT INTO sube (sube_id, ad, sehir) VALUES
  (1,'Merkez','Ankara'),(2,'Bahcelievler','Ankara'),(3,'Kadikoy','Istanbul'),
  (4,'Beyoglu','Istanbul'),(5,'Konak','Izmir'),(6,'Nilufer','Bursa'),
  (7,'Selcuklu','Konya'),(8,'Cankaya','Ankara');

INSERT INTO uye (uye_id, ad, sehir, kayit_tarihi)
WITH RECURSIVE sayac(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM sayac WHERE n < 120000)
SELECT n, 'Uye ' || n,
       CASE n % 5 WHEN 0 THEN 'Ankara' WHEN 1 THEN 'Istanbul' WHEN 2 THEN 'Izmir'
                  WHEN 3 THEN 'Bursa' ELSE 'Konya' END,
       date('2015-01-01', '+' || (n % 3200) || ' days')
FROM sayac;

INSERT INTO kitap (kitap_id, baslik, yazar, yil, sube_id)
WITH RECURSIVE sayac(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM sayac WHERE n < 200000)
SELECT n, 'Kitap ' || n, 'Yazar ' || (n % 4000), 1950 + (n % 75), 1 + (n % 8)
FROM sayac;

INSERT INTO odunc (odunc_id, kitap_id, uye_id, alis_tarihi, iade_tarihi)
WITH RECURSIVE sayac(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM sayac WHERE n < 2000000)
SELECT n, 1 + ((n * 7) % 200000), 1 + ((n * 13) % 120000),
       date('2018-01-01', '+' || ((n * 37) % 2437) || ' days'),
       CASE WHEN n % 9 = 0 THEN NULL
            ELSE date('2018-01-01', '+' || (((n * 37) % 2437) + 14) || ' days') END
FROM sayac;
SQL
sqlite3 kutuphane.db < kurulum.sql

Metnin İçine Karışan Girdi

Kütüphane kataloğunda başlığa göre arama yapan bir ekran düşünelim. Girdi, sorgu metnine tırnaklar arasına yerleştiriliyor. Kataloğa, başlığında kesme işareti bulunan bir kitap eklenmiş durumda.

sqlite3 kutuphane.db "INSERT INTO kitap VALUES (200001, 'Ay''in Kizi', 'Yazar 7', 1998, 3);"

aranan="Kitap 4242"
sqlite3 kutuphane.db "SELECT kitap_id, baslik FROM kitap WHERE baslik = '$aranan';"

aranan="Ay'in Kizi"
echo "--- ayni sorgu, tirnak iceren baslikla ---"
sqlite3 kutuphane.db "SELECT kitap_id, baslik FROM kitap WHERE baslik = '$aranan';"
echo "cikis kodu: $?"

4242|Kitap 4242
--- ayni sorgu, tirnak iceren baslikla ---
Error: in prepare, unrecognized token: "';"
  p_id, baslik FROM kitap WHERE baslik = 'Ay'in Kizi';
                                      error here ---^
cikis kodu: 1

Birinci arama çalışıyor. İkincisi, tamamen zararsız bir girdiyle — bir kitap başlığında bulunan kesme işaretiyle — sözdizimi hatası veriyor. Hata iletisi nedeni açıkça gösteriyor: girdideki kesme işareti, dizgiyi kapatan tırnak olarak okundu ve sorgunun geri kalanı bozuldu.

Kırılmanın kaynağı, bir tırnak karakterinin özel olması değildir. Kaynak şudur: girdi, veri olarak değil, sorgu metninin bir parçası olarak veritabanına ulaştı. Veritabanı kendisine gelen metni bir program olarak ayrıştırır ve o metinde girdinin nerede bitip programın nerede başladığını ayırt edecek bir bilgisi yoktur.

Aynı mekanizma, güvenlik açığının da tanımıdır. Girdi sorgu metninin parçasıysa, uygun biçimde hazırlanmış bir girdi sorgunun anlamını değiştirebilir: koşul her zaman doğru hale gelebilir, sorgunun kapsamı genişleyebilir, hiç istenmeyen bir tabloya erişilebilir. Buna SQL enjeksiyonu (SQL injection) denir ve kaynağı, girdinin özel karakter içermesi değil, metnin girdi ile birleştirilerek kurulmasıdır. Bu yüzden çözüm de karakter temizlemek değildir: kaçış listeleri eksik kalır, kodlama farkları listeyi atlatır, her yeni bağlam yeni bir liste ister. Çözüm, girdinin metne hiç karışmamasıdır.

Bağlı Değişken

Bağlı değişken (bound parameter), sorgu metninde değerin durması gereken yere bir yer tutucu koyar; değer sorgudan ayrı bir kanalla gönderilir. Veritabanı önce metni ayrıştırıp planlar, sonra değeri o plana yerleştirir.

aranan="Ay'in Kizi"
sqlite3 kutuphane.db <<SQL
.parameter init
.parameter set :baslik "$aranan"
SELECT kitap_id, baslik FROM kitap WHERE baslik = :baslik;
SQL

200001|Ay'in Kizi

Aynı girdi, aynı veri, farklı sonuç: kayıt bulundu. Sorgu metni :baslik yer tutucusunu içeriyor ve bu metin girdiden bağımsız olarak sabit. Girdinin içinde ne olursa olsun — kesme işareti, noktalı virgül, -- dizisi — hepsi tek bir dizgi değeri olarak kalır, çünkü ayrıştırma girdi gelmeden bitmiştir.

Bu, veri temizlemenin daha iyi bir biçimi değildir; farklı bir yaklaşımdır. Bağlı değişken kullanıldığında temizlenecek bir şey kalmaz, çünkü girdi hiçbir zaman program metni olmamıştır. Uygulama tarafında bu, sorgu metninin kod içinde sabit durması ve değerlerin sürücünün parametre arayüzüyle geçirilmesi demektir.

Ayrıştırma ve Planlama Maliyeti

Bağlı değişkenin ikinci kazancı başarım tarafındadır ve ölçülebilir. Bir sorgunun çalıştırılması iki aşamadır: metnin ayrıştırılıp planlanması ve planın yürütülmesi. Birinci aşamanın maliyeti, hiç satır döndürmeyen bir sorguda yalıtılabilir.

sqlite3 kutuphane.db 'CREATE INDEX kitap_yazar ON kitap(yazar);
CREATE INDEX odunc_kitap ON odunc(kitap_id);'

i=1
: > basit.sql
: > karmasik.sql
while [ "$i" -le 20000 ]; do
  printf 'SELECT 1;\n' >> basit.sql
  printf "SELECT s.ad, count(*) FROM odunc o JOIN kitap k ON k.kitap_id = o.kitap_id JOIN uye u ON u.uye_id = o.uye_id JOIN sube s ON s.sube_id = k.sube_id WHERE k.yazar = 'Yok %s' AND u.sehir = 'Bursa' GROUP BY s.ad ORDER BY 2 DESC;\n" "$i" >> karmasik.sql
  i=$((i + 1))
done

printf 'basit    : '; { time sqlite3 kutuphane.db < basit.sql > /dev/null ; } 2>&1 | tr '\n' ' '; echo
printf 'karmasik : '; { time sqlite3 kutuphane.db < karmasik.sql > /dev/null ; } 2>&1 | tr '\n' ' '; echo

basit    :  real	0m0.022s user	0m0.013s sys	0m0.007s 
karmasik :  real	0m0.220s user	0m0.159s sys	0m0.060s 

İki dosya da yirmi bin deyim içeriyor. Karmaşık dosyadaki sorgular hiçbir satır döndürmüyor: yazar koşulu hiçbir kayıtla eşleşmiyor ve dizin sayesinde yürütme neredeyse sıfır. Aradaki fark — bu ortamda yaklaşık 0,19 saniye, deyim başına on mikrosaniye civarı — dört tablolu birleştirmenin ayrıştırılması ve planlanmasıdır. Süreler ortama bağlıdır; anlamlı olan, planlamanın ölçülebilir bir maliyeti olduğudur.

Veritabanları bu maliyeti plan önbelleği (plan cache) ile öderler: derlenmiş plan saklanır ve aynı sorgu yeniden geldiğinde yeniden planlanmaz. Önbelleğin anahtarı sorgu metnidir. Bağlı değişken kullanıldığında metin her çağrıda birebir aynıdır ve önbellek isabet eder. Dizgi birleştirmeyle kurulan sorguda ise her farklı değer farklı bir metin üretir: önbellek hiç isabet etmez ve tek kullanımlık girdilerle dolarak işe yarar planları dışarı atar.

Bağlı değişkenin bir ödünleşimi de vardır. Plan tek bir değere göre değil, bütün değerler için ortaklaşa seçilir; çarpık dağılımlı bir sütunda aynı plan sık geçen değer için de seyrek geçen değer için de kullanılır. Önceki dersteki ortalama tartışmasının aynısıdır bu. Bazı motorlar ilk çağrıların değerlerine bakıp bu durumu ele alan yöntemler uygular.

Değişken Yapıdaki Sorgular

Değerler bağlı değişkenle geçirilebilir; sütun ve tablo adları geçirilemez, çünkü onlar sorgunun yapısına aittir. Sıralama sütunu kullanıcıdan geliyorsa yapılacak şey, gelen adı sabit bir listeye karşı doğrulamak ve yalnız listedeki adı metne koymaktır.

sutun_girdi="yil"
aranan="Ay'in Kizi"

case "$sutun_girdi" in
  baslik|yazar|yil) sutun="$sutun_girdi" ;;
  *) echo "gecersiz siralama sutunu"; exit 1 ;;
esac

sqlite3 kutuphane.db <<SQL
.parameter init
.parameter set :aranan "$aranan"
SELECT kitap_id, baslik, yil FROM kitap
WHERE baslik = :aranan OR yazar = 'Yazar 7'
ORDER BY $sutun LIMIT 3;
SQL

7|Kitap 7|1957
12007|Kitap 12007|1957
24007|Kitap 24007|1957

Ayrım nettir: :aranan bir değerdir ve bağlanır; $sutun bir tanımlayıcıdır ve metne girer, bu yüzden değeri kullanıcının verdiği dizgi değil, listede yer alan sabitlerden biridir. Gelen ad listede yoksa sorgu hiç kurulmaz. Aynı yöntem koşul sayısı değişen aramalar için de geçerlidir: koşul parçaları kodun içinde sabit metinler olarak durur, hangilerinin ekleneceğine kod karar verir, değerlerin hepsi bağlanır.

Bu düzenin başarım tarafındaki yan etkisi, farklı koşul birleşimlerinin farklı sorgu metinleri üretmesidir. Sayı sınırlı kaldığı sürece — birkaç düzine olası birleşim — plan önbelleği bunların hepsini tutabilir. Sınırın kaybolduğu yer, metne değer karıştığı yerdir.

Özet

• Sorgu metnini girdiyle birleştirerek kurmak, girdinin program metnine karışması demektir; kesme işareti içeren sıradan bir başlık bile sorguyu sözdizimi hatasıyla kırar.
• Aynı mekanizma SQL enjeksiyonunun kaynağıdır; çözüm karakter temizlemek değil, girdiyi metnin dışında tutmaktır.
• Bağlı değişken, metni girdiden bağımsız kılar: ayrıştırma girdi gelmeden biter, değer plana sonradan yerleşir ve aynı girdi doğru çalışır.
• Ayrıştırma ve planlama ölçülebilir bir maliyettir; bu ortamda dört tablolu bir sorgu için deyim başına on mikrosaniye civarında ölçüldü.
• Plan önbelleği sorgu metnine göre çalışır; bağlı değişken metni sabitlediği için isabet eder, dizgi birleştirme her çağrıda yeni metin üreterek önbelleği işlevsiz kılar.
• Tanımlayıcılar bağlanamaz; sütun ve tablo adları sabit bir listeye karşı doğrulanıp öyle kullanılır.

Kurs Kapanışı

İleri SQL kursu üç konuda ilerledi. Bileşik Sorgular, tek bir deyimle ifade edilebilecek soruların sınırını genişletti: alt sorgular, ortak tablo ifadeleri, özyinelemeli sorgular ve pencere işlevleri. İşlemler, birden çok deyimin bir arada doğru davranmasını sağlayan düzeni kurdu: kesinleştirme sınırları, yalıtım düzeyleri, kilitlenme ve tetikleyicilerin örtük yan etkileri. Sorgu Başarımı konusu ise doğru sonucu veren sorgunun ne kadar işle verildiğini sordu.

Bu son konunun yöntemi, cevaplarından daha kalıcıdır. Her iddia iki ölçüyle sınandı: plan çıktısı kararı gösterdi, adım sayısı ve süre bedeli gösterdi; süre ortama bağlı olduğu için yorumlar oranlar üzerinden yapıldı. Plan çıktısının biçimi ve düğüm adları motora göre değişir — okunacak olan biçim değil, plandaki dört karardır: tarama mı arama mı, hangi tablo sürüyor, tabloya dönmek gerekiyor mu, ayrıca sıralama yapılıyor mu.

Buradaki bütün eniyilemeler sorgu düzeyindeydi: dizin tanımı, koşul yazımı, birleştirme sırası, alt sorgu biçimi, istatistik tazeliği ve parametre kullanımı. Sıra motorun kendisindedir. İlişkisel Veritabanı Yönetimi kursu, buraya kadar kara kutu olarak kullanılan motoru açar: süreç ve bellek mimarisi, verinin sayfalar halinde diske yazılma biçimi, yazma öncesi günlük ve denetim noktaları, dizinlerin bakımı ve şişmesi, bölümleme, yedekleme ve zaman noktasına kurtarma, çoğaltma ve devralma. Sorgunun ne kadar işle çalıştığını ölçmeyi bilen biri, o işin motorun içinde nereye düştüğünü de öğrenmeye hazır demektir.