EF Core'da Raw SQL: FromSqlRaw, FromSqlInterpolated, Keyless Entity ve SQL Injection

Sürüm notu: Bu yazıdaki kodlar EF Core 6 ile yazılmıştır ve EF Core 10'da da değişmeden çalışır.

Önce Tahmin Et: Bu SQL Veritabanına Nasıl Gider?

Elle yazılmış bir SQL ile başlayıp üzerine LINQ ekliyoruz:

var query = db.Books
    .FromSqlRaw("SELECT * FROM Books")
    .Where(b => b.Stock > 2)
    .OrderBy(b => b.Title);

Console.WriteLine(query.ToQueryString());

Üç ihtimal düşün. EF Core senin SQL'ini olduğu gibi gönderip filtrelemeyi ve sıralamayı bellekte yapar. Ya da SQL'ini bir kenara bırakıp kendi sorgusunu üretir. Ya da ikisini bir şekilde birleştirir. Hangisi? Cevap birazdan, ama önce bu yazının sorusu: LINQ'in yetmediği ya da elinde zaten hazır bir SQL olduğu durumlarda EF Core'a SQL'i nasıl verirsin ve bunu yaparken kendini nasıl yakmazsın?

Örnekler için Bookstore modelinin tanıdık iki entity'sini kullanıyoruz:

public class Author
{
    public int Id { get; set; }
    public string Name { get; set; } = "";
    public List<Book> Books { get; set; } = new();
}

public class Book
{
    public int Id { get; set; }
    public string Title { get; set; } = "";
    public decimal Price { get; set; }
    public int Stock { get; set; }
    public int AuthorId { get; set; }
    public Author Author { get; set; } = null!;
}

FromSqlRaw ve Üzerine LINQ

Açılıştaki sorgunun ToQueryString() çıktısı şu:

SELECT "b"."Id", "b"."AuthorId", "b"."Price", "b"."Stock", "b"."Title"
FROM (
    SELECT * FROM Books
) AS "b"
WHERE "b"."Stock" > 2
ORDER BY "b"."Title"

Doğru cevap üçüncüsü. EF Core senin SQL'ini bir subquery olarak sarıyor ve Where ile OrderBy'ı dışarıdaki SELECT'e ekliyor. Filtre veritabanında çalışıyor, bellekte değil. Bu çıktı EF Core 6 ve EF Core 10'da birebir aynı.

Bu birleştirmenin bir bedeli var: EF Core, senin SQL'inin sonucunu bir Book gibi okuyabilmeli. Yani sorgu entity'nin bütün kolonlarını, model ile aynı adlarla döndürmeli. Eksik bir kolon olursa sorgu çalışır ama sonucu okurken patlar:

var titles = await db.Books
    .FromSqlRaw("SELECT Id, Title FROM Books")
    .ToListAsync();

InvalidOperationException: The required column 'AuthorId' was not present in the results of a 'FromSql' operation.

Sadece birkaç kolon lazımsa ya sorguda hepsini döndür ve LINQ'te Select ile projection yap ya da sonucu ayrı bir keyless entity'ye eşle. Composition'ın güzel tarafı, ilişkili veriyi de getirebilmen. Include da aynı şekilde subquery'nin üzerine bir JOIN olarak ekleniyor:

var withAuthors = db.Books
    .FromSqlRaw("SELECT * FROM Books")
    .Include(b => b.Author);

Console.WriteLine(withAuthors.ToQueryString());

SELECT "b"."Id", "b"."AuthorId", "b"."Price", "b"."Stock", "b"."Title", "a"."Id", "a"."Name"
FROM (
    SELECT * FROM Books
) AS "b"
INNER JOIN "Authors" AS "a" ON "b"."AuthorId" = "a"."Id"

Parametreler: FromSqlRaw mı, FromSqlInterpolated mı?

Sorguya dışarıdan değer verdiğin anda iş ciddileşir. İki yol var:

var authorId = 1;

var raw = db.Books.FromSqlRaw("SELECT * FROM Books WHERE AuthorId = {0}", authorId);
var interpolated = db.Books.FromSqlInterpolated($"SELECT * FROM Books WHERE AuthorId = {authorId}");

Console.WriteLine(raw.ToQueryString());
Console.WriteLine();
Console.WriteLine(interpolated.ToQueryString());

İki sorgu da aynı SQL'i üretiyor:

.param set p0 1

SELECT * FROM Books WHERE AuthorId = @p0

SQLite provider'ında ToQueryString(), parametreleri .param set satırlarıyla gösteriyor; SQL Server'da bunlar DECLARE satırları olur. Önemli olan, 1 değerinin SQL metnine yazılmaması, ayrı bir @p0 parametresi olarak gitmesi.

FromSqlRaw'da yer tutucular {0}, {1} şeklinde. Bu string.Format'a benziyor ama değildir; EF Core bu yerlere değeri yazmaz, parametre koyar. FromSqlInterpolated ise bir FormattableString alır. Dolar işaretli string'i normal bir string'e çevirmeden, parçaları ve değerleri ayrı ayrı yakalar ve her {authorId} için bir parametre üretir. İkisi de güvenli. Kendi parametre object'ini vermek istersen, örneğin adlandırılmış parametre ya da tip ayarı için, provider'ın DbParameter'ını da kullanabilirsin:

var parameter = new SqliteParameter("authorId", authorId);
var books = await db.Books
    .FromSqlRaw("SELECT * FROM Books WHERE AuthorId = @authorId", parameter)
    .ToListAsync();

EF Core 7 ile FromSql adında üçüncü bir method geldi. İşi FromSqlInterpolated ile aynı, adı daha kısa. EF Core 6'da yok; sürüm yazılarında göreceğiz.

SQL Injection: Aynı Satır, İki Farklı Sonuç

Tehlike, iki method'un adının ve görüntüsünün bu kadar birbirine benzemesinde. Şu koda bak:

// title kullanıcıdan geliyor: "x' OR '1'='1"
var books = await db.Books
    .FromSqlRaw($"SELECT * FROM Books WHERE Title = '{title}'")
    .ToListAsync();

Console.WriteLine(books.Count);

3

title adında hiç kitap yok ama sorgu üç kitabı da döndürdü. Sebebi şu: FromSqlRaw bir string parametresi bekliyor. Önündeki $ yüzünden C# bu string'i method'a girmeden önce birleştirdi ve EF Core'a hazır bir SQL metni geldi: ... WHERE Title = 'x' OR '1'='1'. EF Core'un burada parametre üretme şansı yoktu, çünkü değeri hiç görmedi. Kullanıcının yazdığı metin artık SQL'in bir parçası. Bu sefer koşulu bozduk; bir dahakine '; DROP TABLE Books; -- olabilir.

Aynı satırı FromSqlInterpolated ile yazınca:

var safe = await db.Books
    .FromSqlInterpolated($"SELECT * FROM Books WHERE Title = {title}")
    .ToListAsync();

Console.WriteLine(safe.Count);

0

Değer parametre olarak gitti, veritabanı x' OR '1'='1 adında bir kitap aradı ve bulamadı. Doğru sonuç.

EF Core 6 bu tehlikeli satır için hiçbir uyarı vermiyor. Aynı kod EF Core 10 ile derlenince şu uyarı çıkıyor:

warning EF1002: Method 'FromSqlRaw' inserts interpolated strings directly into the SQL, without any protection against SQL injection. Consider using 'FromSql' instead, which protects against SQL injection, or make sure that the value is sanitized and suppress the warning.

Bu analyzer EF Core 8'de geldi; EF Core 10 bir adım daha atıp "..." + title + "..." gibi string birleştirmeleri için de EF1003 uyarısı veriyor. Uyarılar yardımcı ama kural basit: kullanıcıdan gelen hiçbir değeri SQL metnine sen yazma. FromSqlRaw'ı ancak SQL'in kendisini dinamik kurman gerektiğinde, örneğin bir kolon adını seçmen gerekiyorsa, kullan ve o parçayı kendi izin listenden seç.

ExecuteSqlRaw ve ExecuteSqlInterpolated

Satır döndürmeyen komutlar için DbSet değil Database üzerinden gidilir. Bir yazarın bütün kitaplarına stok ekleyelim:

var dune = await db.Books.SingleAsync(b => b.Title == "Dune"); // Stock = 5, artık track ediliyor

var authorId = 1;
var amount = 10;
var affected = await db.Database.ExecuteSqlInterpolatedAsync(
    $"UPDATE Books SET Stock = Stock + {amount} WHERE AuthorId = {authorId}");

Console.WriteLine($"Affected rows: {affected}");
Console.WriteLine($"Tracked stock: {dune.Stock}");

await db.Entry(dune).ReloadAsync();
Console.WriteLine($"After reload: {dune.Stock}");

Affected rows: 2
Tracked stock: 5
After reload: 15

Method etkilenen satır sayısını döndürüyor: iki kitap güncellendi. Ama dikkat et: önceden track ettiğimiz dune hâlâ 5 diyor. ExecuteSql ailesi change tracker'ı tamamen atlar; veritabanında ne değiştiğinden context'in haberi olmaz. Aynı context'te bu entity'yi kullanmaya devam edeceksen Reload ile tazele ya da hiç track etme. Bir de şunu hatırla: bu method'lar kendi başına transaction açmaz. Birden fazla komutu bir arada tutman gerekiyorsa önceki yazılarda gördüğümüz BeginTransaction'ı kullan. Concurrency token'ları da bu komutlarda güncellenmez.

Bu tür toplu güncellemeler için EF Core 7 ile ExecuteUpdate ve ExecuteDelete geldi; SQL yazmadan LINQ ile aynı işi yapıyorlar. EF Core 6'da yoklar, sürüm yazılarında göreceğiz.

Keyless Entity'ler ve View Eşleme

Raporlama sorguları çoğu zaman bir entity'ye karşılık gelmez. Yazar başına kitap sayısı ve toplam stok gibi bir sonucu bir view'da tutalım:

await db.Database.ExecuteSqlRawAsync(@"
    CREATE VIEW AuthorStats AS
    SELECT a.Name AS AuthorName, COUNT(b.Id) AS BookCount, COALESCE(SUM(b.Stock), 0) AS TotalStock
    FROM Authors AS a
    LEFT JOIN Books AS b ON b.AuthorId = a.Id
    GROUP BY a.Id, a.Name");

Burada view'ı EnsureCreated'ten sonra elle oluşturduk. Gerçek bir projede bu SQL'i bir migration'ın Up method'una migrationBuilder.Sql(...) ile koyarsın. Sonucu taşıyacak class'ın bir key'i yok:

public class AuthorStats
{
    public string AuthorName { get; set; } = "";
    public int BookCount { get; set; }
    public int TotalStock { get; set; }
}

Context'te bunu keyless entity olarak tanıtıyor ve view'a eşliyoruz:

public DbSet<Author> Authors => Set<Author>();
public DbSet<Book> Books => Set<Book>();
public DbSet<AuthorStats> AuthorStats => Set<AuthorStats>();

protected override void OnConfiguring(DbContextOptionsBuilder options)
    => options.UseSqlite("Data Source=bookstore.db");

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<AuthorStats>()
        .HasNoKey()
        .ToView("AuthorStats");
}

HasNoKey(), EF Core'a "bu tipin primary key'i yok, satırları ayırt etmeye çalışma" der; data annotation karşılığı [Keyless]. ToView("AuthorStats") ise iki iş yapar: sorgularda bu view'ı kullanır ve EnsureCreated ile migration'lara bu tip için tablo oluşturmamasını söyler. Nitekim GenerateCreateScript() çıktısında yalnız Authors ve Books tabloları var. Artık view'a normal bir DbSet gibi LINQ yazabilirsin:

var busyAuthors = db.AuthorStats
    .Where(s => s.BookCount > 1)
    .OrderBy(s => s.AuthorName);

Console.WriteLine(busyAuthors.ToQueryString());

foreach (var stats in await busyAuthors.ToListAsync())
    Console.WriteLine($"{stats.AuthorName}: {stats.BookCount} books, {stats.TotalStock} in stock");

SELECT "a"."AuthorName", "a"."BookCount", "a"."TotalStock"
FROM "AuthorStats" AS "a"
WHERE "a"."BookCount" > 1
ORDER BY "a"."AuthorName"
Frank Herbert: 2 books, 7 in stock

Keyless entity'ler salt okunurdur. Change tracker onları takip edemez, çünkü bir satırı diğerinden ayıracak bir key yok. Eklemeye kalkarsan:

db.AuthorStats.Add(new AuthorStats { AuthorName = "Grace" });

InvalidOperationException: Unable to track an instance of type 'AuthorStats' because it does not have a primary key. Only entity types with a primary key may be tracked.

Keyless entity'yi bir view'a bağlamak zorunda değilsin. ToView vermeden db.Set<AuthorStats>().FromSqlRaw(...) ile tek seferlik bir SQL'in sonucunu da aynı tipe eşleyebilirsin. Hiç modelde olmayan tipler için raw SQL sorgusu (SqlQuery<T>) ise EF Core 8 ile geldi.

Stored Procedure Çağırmak (SQL Server)

SQLite'ta stored procedure yok; bu bölüm SQL Server içindir ve gerçekten çalıştırmak için bir SQL Server'a, örneğin Docker'daki mcr.microsoft.com/mssql/server imajına ihtiyacın var. Ama SQL'in nasıl üretildiğini görmek için veritabanına gerek yok. ToQueryString() bağlantı açmıyor:

var authorId = 1;

var books = db.Books
    .FromSqlInterpolated($"EXECUTE dbo.GetBooksByAuthor {authorId}");

Console.WriteLine(books.ToQueryString());

DECLARE p0 int = 1;

EXECUTE dbo.GetBooksByAuthor @p0

Procedure entity'nin bütün kolonlarını döndürüyorsa bu sorgu doğrudan Book listesi verir. Yalnız, bir EXECUTE komutu subquery olarak sarılamaz. Üzerine LINQ eklemeye kalkarsan EF Core bunu sorguyu çalıştırmadan önce yakalıyor:

var cheapBooks = db.Books
    .FromSqlInterpolated($"EXECUTE dbo.GetBooksByAuthor {authorId}")
    .Where(b => b.Price < 10);

Console.WriteLine(cheapBooks.ToQueryString());

InvalidOperationException: 'FromSqlRaw' or 'FromSqlInterpolated' was called with non-composable SQL and with a query composing over it. Consider calling 'AsEnumerable' after the method to perform the composition on the client side.

Bu mesaj EF Core 6'dan. EF Core 10'da aynı mesaj 'FromSql' or 'SqlQuery' diye başlıyor. EF Core, EXECUTE ile başlayan bir komutu subquery'ye sarmayı daha veritabanına gitmeden reddediyor. Çözüm mesajın kendisinde: FromSqlInterpolated(...)'dan hemen sonra AsEnumerable() çağır, gerisi bellekte çalışsın. Satır döndürmeyen procedure'ler için de ExecuteSqlInterpolated($"EXECUTE dbo.RestockBooks {authorId}, {amount}") yeterli.

Sık Düşülen Tuzaklar

FromSqlRaw ile $. En tehlikelisi ve en kolay yapılanı bu; yukarıda gördük. Kod incelemesinde FromSqlRaw($ ve ExecuteSqlRaw($ görürsen dur ve sor.

SELECT * ile kırılgan sorgular. Örneklerde kısalık için SELECT * kullandık. Ama tabloya model dışında bir kolon eklendiğinde ya da kolon sırası değiştiğinde ne olacağını düşün. Gerçek kodda kolonları açıkça yaz.

Provider'a özgü SQL. Raw SQL yazdığın anda veritabanından bağımsızlığı kaybedersin. SQLite'ta çalışan bir sorgu SQL Server'da çalışmayabilir. Testlerini başka bir provider ile çalıştırıyorsan bu farkları orada fark edersin; test yazısında bu konuya döneceğiz.

Tracked veri ile raw komutları karıştırmak. ExecuteSqlInterpolated sonrası context'teki entity'ler eski kalır ve ardından gelen bir SaveChanges eski değerleri geri yazabilir. Raw komutu ya ayrı bir context'te çalıştır ya da ilgili entity'leri yeniden yükle.

Küçük Bir Deneme

★☆☆ FromSqlRaw("SELECT * FROM Books WHERE Stock > {0}", 2) sorgusunu yaz, üzerine Select(b => b.Title) ekle ve ToQueryString() çıktısında subquery'nin nasıl sarıldığına bak.

★★☆ Injection örneğini EF Core 10 projesinde derle ve EF1002 uyarısını gör. Sonra satırı "... WHERE Title = '" + title + "'" şeklinde birleştirmeye çevir ve gelen uyarının kodunu not et. Son olarak her ikisini de FromSqlInterpolated ile düzelt.

★★★ OutOfStockBooks adında, stoğu 0 olan kitapları yazar adıyla birlikte döndüren bir view yaz. Bunu keyless bir entity'ye eşle ve AuthorName'e göre sıralanmış ilk 5 kaydı LINQ ile al. Üretilen SQL'de LIMIT'in nerede durduğuna bak.

Bu yazıya kadar context'leri hep new BookstoreContext() ile kendimiz oluşturduk. Bir web uygulamasında bunu kimin, ne zaman yaptığı ve context'in ne kadar yaşadığı ayrı bir konu. Bir sonraki yazıda EF Core'u ASP.NET Core'a bağlıyoruz: AddDbContext, scoped lifetime, AddDbContextFactory, AddDbContextPool ve background service'ler.