Dapper ile Parameter'lar ve SQL Injection: Güvenli Sorgunun Kuralları

Sürüm notu: Bu yazıdaki kodlar Dapper 2.1 ve .NET 10 içindir.

Önce Tahmin Et

Bir arama kutusu düşün: kullanıcı kitabın adını yazıyor, biz de veritabanında arıyoruz. En "pratik" yol, gelen değeri SQL'in içine string interpolation ile koymak gibi görünür:

string title = "The Hitchhiker's Guide to the Galaxy";

var found = connection.Query<Book>(
    $"SELECT id, title, price FROM books WHERE title = '{title}'");

Console.WriteLine(found.Count());

Kitap veritabanında var ve adı birebir aynı. Ekranda 1 mi görürsün, 0 mı, yoksa bir exception mı? İpucu: kitabın adında masum görünen bir karakter var.

Bookstore Veritabanı

Kurulum ilk yazıdakiyle aynı: .NET 10 console projesi, Dapper 2.1.89, Microsoft.Data.Sqlite 10.0.14, Id/Title/Price property'li Book class'ı ve her çalıştırmada veritabanını baştan kuran BookstoreDb.cs:

using Dapper;
using Microsoft.Data.Sqlite;

public static class BookstoreDb
{
    public const string ConnectionString = "Data Source=bookstore.db";

    public static void Reset()
    {
        using var connection = new SqliteConnection(ConnectionString);
        connection.Execute("""
            DROP TABLE IF EXISTS order_items;
            DROP TABLE IF EXISTS orders;
            DROP TABLE IF EXISTS books;
            DROP TABLE IF EXISTS authors;

            CREATE TABLE authors (
                id      INTEGER PRIMARY KEY,
                name    TEXT NOT NULL,
                country TEXT
            );
            CREATE TABLE books (
                id             INTEGER PRIMARY KEY,
                author_id      INTEGER NOT NULL REFERENCES authors(id),
                title          TEXT NOT NULL,
                price          REAL NOT NULL,
                published_year INTEGER,
                stock          INTEGER NOT NULL DEFAULT 0
            );
            CREATE TABLE orders (
                id             INTEGER PRIMARY KEY,
                customer_email TEXT NOT NULL,
                created_at     TEXT NOT NULL
            );
            CREATE TABLE order_items (
                id         INTEGER PRIMARY KEY,
                order_id   INTEGER NOT NULL REFERENCES orders(id),
                book_id    INTEGER NOT NULL REFERENCES books(id),
                quantity   INTEGER NOT NULL,
                unit_price REAL NOT NULL
            );

            INSERT INTO authors (id, name, country) VALUES
                (1, 'Ursula K. Le Guin', 'USA'),
                (2, 'Frank Herbert', 'USA'),
                (3, 'Stanislaw Lem', 'Poland'),
                (4, 'Douglas Adams', 'UK'),
                (5, 'Ted Chiang', 'USA');

            INSERT INTO books (id, author_id, title, price, published_year, stock) VALUES
                (1, 1, 'A Wizard of Earthsea', 12.50, 1968, 8),
                (2, 1, 'The Left Hand of Darkness', 14.00, 1969, 3),
                (3, 1, 'The Dispossessed', 15.25, 1974, 0),
                (4, 2, 'Dune', 18.90, 1965, 12),
                (5, 3, 'Solaris', 11.40, 1961, 5),
                (6, 4, 'The Hitchhiker''s Guide to the Galaxy', 9.99, 1979, 20),
                (7, 5, 'Exhalation', 13.20, NULL, 4);
            """);
    }
}

Aşağıdaki parçalar BookstoreDb.Reset(); sonrasında açılan tek bir connection üzerinde çalışıyor. Exception üreten örnekleri çalıştırırken her birini bir try/catch içine aldım; çıktılarda exception'ın tipini ve mesajını görüyorsun.

String Birleştirme Neden Yasak?

Tahminin cevabı:

Microsoft.Data.Sqlite.SqliteException: SQLite Error 1: 'near "s": syntax error'.

Kitabın adındaki kesme işareti, SQL'deki string'i erkenden kapattı. Veritabanına giden metin WHERE title = 'The Hitchhiker' ile bitip ardından anlamsız bir s Guide to the Galaxy' parçası içeren bozuk bir SQL oldu. Bu masum bir hata gibi duruyor, ama aynı kapı kötü niyetli birine de açık. Kullanıcı arama kutusuna kitap adı yerine şunu yazarsa ne olur?

string input = "x' OR 1=1 --";

var leaked = connection.Query<Book>(
    $"SELECT id, title, price FROM books WHERE title = '{input}'");

Console.WriteLine(leaked.Count());

7

Bütün tablo döndü. Oluşan SQL WHERE title = 'x' OR 1=1 --'; OR 1=1 her satır için doğru, -- da satırın geri kalanını yorum satırına çevirip sondaki kesme işaretini etkisiz bırakıyor. Bu SQL injection. Burada sadece kitap listesi sızdı, ama aynı teknikle başka tablolardan veri okunabilir, kayıtlar silinebilir, bir login kontrolü atlatılabilir.

Sorunun kökü şu: değeri SQL metninin içine koyduğunda veritabanı neyin komut, neyin veri olduğunu ayırt edemez. Bir benzetmeyle, bir çalışana "şu formdaki adı listede ara" demekle, formdaki yazıyı yüksek sesle talimat gibi okumasını istemek arasındaki fark. İkincisinde formu dolduran kişi sana ne yaptıracağını seçer.

Anonymous Object ile Parameter

Çözüm, değeri SQL'den ayrı göndermek. SQL'de değerin yerine bir isim (@Title) koyarsın, değeri de Dapper'a bir object olarak verirsin:

var safe = connection.Query<Book>(
    "SELECT id, title, price FROM books WHERE title = @Title",
    new { Title = title });

var stillSafe = connection.Query<Book>(
    "SELECT id, title, price FROM books WHERE title = @Title",
    new { Title = input });

Console.WriteLine($"{safe.Count()} | {stillSafe.Count()}");

1 | 0

Kesme işaretli başlık sorunsuz bulundu, saldırı metni de sadece adı x' OR 1=1 -- olan bir kitap arandığı için hiçbir şey döndürmedi. Dapper anonymous object'in property'lerinden SQL metninde geçenleri bulur ve her biri için command'a bir DbParameter ekler; SQL'de kullanmadığın property'ler command'a hiç girmez. SQL metni ve değerler veritabanına ayrı ayrı gider; değer ne içerirse içersin hiçbir zaman SQL olarak yorumlanmaz. Kaçış karakteri eklemek, tırnakları ikiye katlamak gibi elle yapılan "temizlik" yöntemlerine de gerek kalmaz.

Bir yan kazanç daha var: SQL metni her çağrıda aynı kaldığı için hem Dapper'ın kendi önbelleği hem de veritabanının sorgu planı önbelleği bu metni tekrar kullanabilir. String birleştirme ile her farklı değer yeni bir SQL metni demektir. Anonymous object dışında Dictionary<string, object> da verebilirsin, ama çoğu durumda anonymous object en okunaklısı.

Parameter Adları: SQLite'ın Katılığı

SQL'deki adı property'den farklı yazarsan ne olur? Sadece harf büyüklüğü farklı olsun:

var wrongCase = connection.Query<Book>(
    "SELECT id, title, price FROM books WHERE title = @title",
    new { Title = title });

System.InvalidOperationException: Must add values for the following parameters: @title

Mesajı veren Dapper değil, Microsoft.Data.Sqlite. Dapper SQL'de @title'ı görüp Title property'sini harf büyüklüğüne bakmadan eşleştirdi ve parameter'ı Title adıyla ekledi; SQLite'ta ise parameter adları büyük-küçük harfe duyarlı ve @title ile Title buluşamadı. Kendine tek bir kural koy: SQL'deki adı property adıyla birebir aynı yaz.

Öneke gelince, Dapper 2.1'den beri @ yanında $ önekini de tanıyor. Bu kurulumda WHERE title = $Title de çalışıyor; SQLite provider'ı :Title'ı da kabul ediyor. Ben seri boyunca @ kullanacağım, çünkü SQL Server, PostgreSQL (Npgsql) ve SQLite'ta ortak payda o.

DynamicParameters: Parameter'ları Adım Adım Kurmak

Anonymous object derleme anında şekli belli olan durumlar için ideal. Filtreleri kullanıcı seçimine göre ekleyen bir arama ekranında ise parameter'ları koşullara göre eklemen gerekir. Bunun için DynamicParameters var:

string? country = "USA";
decimal? maxPrice = 14m;

var sql = "SELECT b.title FROM books b JOIN authors a ON a.id = b.author_id WHERE 1 = 1";
var parameters = new DynamicParameters();

// SQL'e sadece sabit parçalar ekleniyor, değerler parameter olarak gidiyor
if (country is not null)
{
    sql += " AND a.country = @Country";
    parameters.Add("Country", country);
}
if (maxPrice is not null)
{
    sql += " AND b.price < @MaxPrice";
    parameters.Add("MaxPrice", maxPrice, DbType.Decimal);
}

var titles = connection.Query<string>(sql + " ORDER BY b.title", parameters);
Console.WriteLine(string.Join(", ", titles));

A Wizard of Earthsea, Exhalation

Burada da string birleştirme var, ama arada büyük bir fark var: SQL'e eklenen her şey kodun içinde yazılı sabit parçalar. Kullanıcıdan gelen değerler hiçbir zaman SQL metnine girmiyor, hep parameters.Add ile gidiyor. Add'in isteğe bağlı parametreleriyle DbType, yön (ParameterDirection), boyut ve hassasiyet de verebilirsin; output parameter'ları stored procedure yazısında kullanacağız. System.Data namespace'ini eklemeyi unutma.

DynamicParameters bir anonymous object'ten başlayıp üzerine eklemeye de izin verir:

var merged = new DynamicParameters(new { AuthorId = 1 });
merged.AddDynamicParams(new { MinStock = 1 });

var inStock = connection.Query<string>(
    "SELECT title FROM books WHERE author_id = @AuthorId AND stock >= @MinStock",
    merged);

Console.WriteLine($"{string.Join(", ", merged.ParameterNames)} -> {string.Join(", ", inStock)}");

AuthorId, MinStock -> A Wizard of Earthsea, The Left Hand of Darkness

Dinamik filtreler büyüdükçe bu kodu düzenli tutmak zorlaşır; 14. yazıda Dapper.SqlBuilder ile aynı işi daha temiz yapacağız.

IN @Ids ile Liste Genişletme

Birden fazla id ile arama yapmak klasik bir ihtiyaç. Dapper'da bir koleksiyonu parameter olarak verirsin ve IN'den sonra parantez yazmazsın:

int[] ids = [1, 4, 6];

var picked = connection.Query<string>(
    "SELECT title FROM books WHERE id IN @Ids ORDER BY id",
    new { Ids = ids });

Console.WriteLine(string.Join(", ", picked));

A Wizard of Earthsea, Dune, The Hitchhiker's Guide to the Galaxy

Dapper @Ids'i gördüğünde onu koleksiyondaki element sayısı kadar parameter'a açar; README'deki örneğe göre SQL in (@Ids1, @Ids2, @Ids3) hâline gelir ve her değer ayrı bir parameter olarak gider. Parantezi yazmayı unutmak değil, yazmak hatadır. SQL alışkanlığıyla IN (@Ids) yazarsan çift parantez oluşur ve SQLite bunu bir row value sanar:

var withParens = connection.Query<string>(
    "SELECT title FROM books WHERE id IN (@Ids)",
    new { Ids = ids });

Microsoft.Data.Sqlite.SqliteException: SQLite Error 1: 'row value misused'.

Boş liste de güvenli. Dapper boş bir koleksiyon için IN @Ids'in yerine hiçbir satırla eşleşmeyen (SELECT @Ids WHERE 1 = 0) ifadesini koyar, sorgu exception fırlatmadan sıfır satır döndürür:

var nothing = connection.Query<string>(
    "SELECT title FROM books WHERE id IN @Ids",
    new { Ids = Array.Empty<int>() });

Console.WriteLine(nothing.Count());

0

Bu genişletmenin bir yan etkisi var: listedeki element sayısı değiştikçe SQL metni de değişir. SQL Server'da bu, liste uzunluğu başına ayrı bir sorgu planı demek; buna 13. yazıda, SQL Server'a özgü konular arasında döneceğiz.

DbString: ANSI mi, Unicode mu?

.NET'te bir string parameter varsayılan olarak Unicode gönderilir. Bazı veritabanlarında bu önemli bir ayrım. Dapper bunu kontrol etmen için DbString tipini sunar:

var duneId = connection.QuerySingle<int>(
    "SELECT id FROM books WHERE title = @Title",
    new { Title = new DbString { Value = "Dune", IsAnsi = true, Length = 200 } });

Console.WriteLine(duneId);

4

IsAnsi = true parameter'ı Unicode olmayan bir string (DbType.AnsiString) olarak, Length da sabit bir boyutla gönderir; IsFixedLength ile char gibi sabit uzunluklu kolonlara da uyum sağlayabilirsin. SQLite'ta tek bir metin tipi olduğu için burada sonuç değişmiyor. SQL Server'da ise varchar bir kolonu nvarchar bir parameter ile karşılaştırmak örtük bir tip dönüşümüne, bu da indeks kullanılamamasına yol açabilir. README de SQL Server için açık konuşuyor: Unicode kolonu Unicode, ANSI kolonu ANSI parameter ile sorgula. Bunu SQL Server yazısında gerçek bir sorgu planı üzerinde göreceğiz.

Sık Düşülen Tuzaklar

En yaygın tuzak, kolon adını parameter yapmaya çalışmak. Kullanıcı sıralama kolonunu seçiyor ve sen bunu ORDER BY @Column ile vermeye çalışıyorsun:

var notSorted = connection.Query<string>(
    "SELECT title FROM books ORDER BY @Column LIMIT 3",
    new { Column = "price" });

Console.WriteLine(string.Join(", ", notSorted));

A Wizard of Earthsea, The Left Hand of Darkness, The Dispossessed

Exception yok, ama sonuç fiyata göre sıralı da değil. Parameter'lar değer taşır, kolon ya da tablo adı değil; veritabanı her satır için sabit 'price' string'ine göre sıraladı, yani hiç sıralamadı. Kolon adları, sıralama yönü gibi SQL parçaları dışarıdan geliyorsa onları bir beyaz listeden geçir:

static string ToOrderColumn(string sortBy) => sortBy switch
{
    "price" => "price",
    "title" => "title",
    "year" => "published_year",
    _ => "id"
};

var sorted = connection.Query<string>(
    $"SELECT title FROM books ORDER BY {ToOrderColumn("price")} LIMIT 3");

Console.WriteLine(string.Join(", ", sorted));

The Hitchhiker's Guide to the Galaxy, Solaris, A Wizard of Earthsea

Burada interpolation güvenli, çünkü SQL'e giren değer kullanıcının yazdığı metin değil, switch'in döndürdüğü sabitlerden biri.

İkincisi LIKE. % joker karakterlerini SQL'e değil değere koy: WHERE title LIKE @Pattern ve new { Pattern = "%" + term + "%" }. '%@Term%' yazarsan parameter tırnak içinde kalır ve düz metin olarak aranır.

Üçüncüsü yukarıda gördüğün IN (@Ids) parantezi ve dördüncüsü SQLite'taki harf büyüklüğü. İkisi de ilk denemede fark edilir; asıl tehlikeli olanlar, ORDER BY @Column gibi sessizce yanlış sonuç verenler.

Küçük Bir Deneme

★☆☆ Başlığında verilen kelime geçen kitapları bulan bir arama yaz. % karakterlerini parameter değerine ekle ve "the" ile dene.

★★☆ DynamicParameters örneğine int? minYear filtresi ekle. published_year'ı NULL olan Exhalation bu filtreyle ne oluyor? Tahmin et, sonra çalıştır.

★★★ Sıralama için hem kolon hem yön ("asc"/"desc") alan bir method yaz. İkisini de beyaz listeden geçir; listede olmayan bir değer gelirse varsayılana dönmek yerine ArgumentException fırlat. Hangisi daha doğru davranış, neden?

Artık güvenli şekilde okuyabiliyoruz. Bir sonraki yazıda yazma tarafına geçiyoruz: Execute ile INSERT, UPDATE ve DELETE, etkilenen satır sayısı, yeni eklenen satırın id'sini almak ve bir listeyle Execute çağırmanın neden bir bulk işlem olmadığı. Seri burada bitmiyor; Dapper'ın yeni sürümleri çıktıkça yeni yazılarla devam edeceğiz.