Dinamik SQL: EXEC ve sp_executesql

Ne zaman gerekir?

Tablo adı, sütun listesi veya PIVOT değerleri gibi parametreyle verilemeyen kısımlar değişkense sorgu metni çalışma anında üretilir.

DECLARE @tablo SYSNAME = N'products';
DECLARE @sql NVARCHAR(MAX) = N'SELECT COUNT(*) AS satir FROM ' + QUOTENAME(@tablo) + N';';

PRINT @sql;
EXEC sp_executesql @sql;

QUOTENAME, adı köşeli parantez içine alır ve içindeki ] karakterlerini kaçışlar; tablo/sütun adı enjeksiyonuna karşı ilk savunmadır.

Değerleri her zaman parametre olarak ver

DECLARE @sql NVARCHAR(MAX) = N'
    SELECT name, price
    FROM products
    WHERE category = @kat AND price >= @min
    ORDER BY price DESC;';

EXEC sp_executesql
     @sql,
     N'@kat NVARCHAR(50), @min DECIMAL(12, 2)',
     @kat = N'Elektronik', @min = 1000;

Derinleş: enjeksiyonu önlemek ve beyaz liste

SQL enjeksiyonu, kullanıcı girdisinin veri olarak değil kod olarak yorumlanmasıdır. Dinamik SQL'de metin birleştirme yapıldığı an risk doğar. Kötü örnek (çalıştırılmaz):

-- YAPMA: girdi doğrudan metne ekleniyor
DECLARE @sehir NVARCHAR(50) = N'Bursa'' OR 1 = 1 --';
DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM customers WHERE city = ''' + @sehir + N'''';
EXEC (@sql);
-- Üretilen metin: SELECT * FROM customers WHERE city = 'Bursa' OR 1 = 1 --'
-- Sonuç: tüm müşteriler. Daha kötüsü: '; DROP TABLE customers; --

Doğru yol, değeri parametre olarak geçmektir: sp_executesql parametreyi her zaman veri olarak ele alır, içinde ne yazarsa yazsın kod olarak çalıştırmaz.

Adlar parametre olamaz: beyaz liste

Sıralama sütunu, tablo adı gibi tanımlayıcılar parametreyle verilemez. Bunlar için iki savunma birlikte kullanılır: izin verilen değerler listesi (beyaz liste) ve QUOTENAME.

DECLARE @kolon SYSNAME = N'price';   -- kullanıcıdan gelen sıralama tercihi

IF @kolon NOT IN (N'price', N'stock', N'name')
    THROW 50001, N'Bu sütuna göre sıralama yapılamaz.', 1;

DECLARE @sql NVARCHAR(MAX) =
    N'SELECT TOP 3 name, ' + QUOTENAME(@kolon) + N' AS deger
      FROM products
      ORDER BY ' + QUOTENAME(@kolon) + N' DESC, product_id;';

EXEC sp_executesql @sql;

Katalogdan sorgu üretmek

Dinamik SQL'in meşru bir kullanımı, katalogdaki tablolar için tekrar eden sorguları otomatik üretmektir; ör. tüm Lezzet Kurye tablolarının satır sayıları:

DECLARE @sql NVARCHAR(MAX);

SELECT @sql = STRING_AGG(
         CAST(N'SELECT N''' + TABLE_NAME + N''' AS tablo, COUNT(*) AS satir FROM '
              + QUOTENAME(TABLE_NAME) AS NVARCHAR(MAX)),
         N' UNION ALL ') WITHIN GROUP (ORDER BY TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'lk%';

SET @sql = @sql + N' ORDER BY tablo;';
PRINT @sql;
EXEC sp_executesql @sql;

Sessiz hata: Dinamik metindeki herhangi bir parça NULL ise birleştirmenin tamamı NULL olur ve sp_executesql hiçbir şey yapmadan, hata da vermeden döner. QUOTENAME 128 karakterden uzun girdide NULL döndürdüğü için bu durum beklenmedik anda ortaya çıkabilir. Çalıştırmadan önce IF @sql IS NULL THROW ... ile kontrol et ve geliştirirken metni PRINT ile oku.