-- SAVUNMASIZ — prod ortaminda BOYLE YAZMA
DECLARE @username NVARCHAR(50) = N'admin';
DECLARE @password NVARCHAR(50) = N'pwd123';
DECLARE @sql NVARCHAR(MAX);
SET @sql = N'SELECT user_id FROM Users '
         + N'WHERE username = ''' + @username + ''' '
         + N'AND password_hash = HASHBYTES(''SHA2_256'', '''
         + @password + ''')';
EXEC sp_executesql @sql;
Atakçı @username yerine `admin' OR '1'='1` koyarsa, son SQL şuna dönüşür:
SELECT user_id FROM Users
WHERE username = 'admin' OR '1'='1'
  AND password_hash = HASHBYTES('SHA2_256', 'whatever')

/* ============================================================
   SQL INJECTION DEMO — Klasik Atak Vektörleri
   UNION-based veri sizdirma + Time-based blind injection
   Yazar: Yavuz Filizlibay | yavuzfilizlibay.com
   Onkosul: Adim 1 (dbo.Users) ve Adim 5 (dbo.Products) mevcut.
   Veritabani: AdventureWorks2025
   ============================================================ */
SET NOCOUNT ON;
USE AdventureWorks2025;
GO
-- Products tablosuna 'description' kolonu ekle (UNION demosu icin) ---
IF COL_LENGTH('dbo.Products', 'description') IS NULL
    ALTER TABLE dbo.Products ADD description NVARCHAR(200) NULL;
GO
UPDATE dbo.Products SET description = N'Aciklama: ' + name WHERE description IS NULL;
GO
/* ############################################################
   BOLUM A — UNION-BASED
   ############################################################ */
-- ZAFIYETLI arama proseduru (string concatenation)
CREATE OR ALTER PROCEDURE dbo.spSearchProducts_Vuln
    @search NVARCHAR(200)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @sql NVARCHAR(MAX) =
        N'SELECT name, description FROM dbo.Products WHERE name LIKE ''' + @search + N'''';
    PRINT N'-- Calisan sorgu: ' + @sql;
    EXEC (@sql);
END
GO
PRINT N'=== A1) Mesru arama ===';
EXEC dbo.spSearchProducts_Vuln @search = N'%Klavye%';
-- Sonuc: sadece eslesen urunler
PRINT N'';
PRINT N'=== A2) UNION saldirisi — Users tablosundan hash sizdir ===';
EXEC dbo.spSearchProducts_Vuln
    @search = N'x'' UNION SELECT username, CONVERT(NVARCHAR(64), password_hash, 2) FROM dbo.Users--';
-- Sonuc: urun kolonlari + TUM kullanicilarin adi ve parola hash''i akar
GO
/* ############################################################
   BOLUM B — TIME-BASED BLIND
   Dogru teknik: IF (kosul) WAITFOR DELAY  (stacked query)
   ############################################################ */
-- ZAFIYETLI login arama proseduru
CREATE OR ALTER PROCEDURE dbo.spFindUser_Vuln
    @username NVARCHAR(200)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @sql NVARCHAR(MAX) =
        N'SELECT user_id, username FROM dbo.Users WHERE username = ''' + @username + N'''';
    EXEC (@sql);
END
GO
PRINT N'=== B1) Kosul YANLIS -> gecikme YOK (aninda doner) ===';
DECLARE @t1 DATETIME2 = SYSDATETIME();
EXEC dbo.spFindUser_Vuln
    @username = N'zzz''; IF (SELECT is_admin FROM dbo.Users WHERE username=''admin'')=0 WAITFOR DELAY ''0:0:05''--';
SELECT DATEDIFF(SECOND, @t1, SYSDATETIME()) AS gecen_saniye_YANLIS;   -- ~0
GO
PRINT N'=== B2) Kosul DOGRU -> 5 saniye gecikme (oracle: bilgi sizdi) ===';
DECLARE @t2 DATETIME2 = SYSDATETIME();
EXEC dbo.spFindUser_Vuln
    @username = N'zzz''; IF (SELECT is_admin FROM dbo.Users WHERE username=''admin'')=1 WAITFOR DELAY ''0:0:05''--';
SELECT DATEDIFF(SECOND, @t2, SYSDATETIME()) AS gecen_saniye_DOGRU;    -- ~5
GO
-- Gercek saldiri ayni mantikla parolayi harf harf cikarir:
--   ... IF (SUBSTRING((SELECT password_hash FROM dbo.Users WHERE user_id=1),1,1)=0x61) WAITFOR DELAY '0:0:05'--
-- 5 sn geldi mi -> ilk byte 0x61 ('a'); gelmedi mi -> sonraki tahmin. Byte byte tum hash.
/* ############################################################
   BOLUM C — GUVENLI SURUM (her iki saldiri da notr)
   ############################################################ */
CREATE OR ALTER PROCEDURE dbo.spSearchProducts_Safe
    @search NVARCHAR(200)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT name, description FROM dbo.Products WHERE name LIKE @search;  -- parametreli
END
GO
CREATE OR ALTER PROCEDURE dbo.spFindUser_Safe
    @username NVARCHAR(200)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT user_id, username FROM dbo.Users WHERE username = @username;  -- parametreli
END
GO
PRINT N'=== C1) Guvenli surume UNION saldirisi -> 0 satir (girdi DEGER) ===';
EXEC dbo.spSearchProducts_Safe
    @search = N'x'' UNION SELECT username, CONVERT(NVARCHAR(64), password_hash, 2) FROM dbo.Users--';
-- Sonuc: 0 satir. Girdi LIKE deseni olarak arandi, UNION calismadi.
PRINT N'';
PRINT N'=== C2) Guvenli surume time-based saldiri -> gecikme YOK ===';
DECLARE @t3 DATETIME2 = SYSDATETIME();
EXEC dbo.spFindUser_Safe
    @username = N'zzz''; IF (SELECT is_admin FROM dbo.Users WHERE username=''admin'')=1 WAITFOR DELAY ''0:0:05''--';
SELECT DATEDIFF(SECOND, @t3, SYSDATETIME()) AS gecen_saniye_GUVENLI;   -- ~0, injection notr
GO

/* ============================================================
   SQL INJECTION DEMO — Regex Input Kontrolü
   E-posta ve kullanici adi FORMAT dogrulama (REGEXP_LIKE)
   Yazar: Yavuz Filizlibay | yavuzfilizlibay.com
   NOT 1: REGEXP_LIKE Boolean doner -> SELECT'te IIF/CASE ile sarilir.
   NOT 2: T-SQL string'leri TEK tirnakla yazilir; icteki ' -> '' yapilir.
   Onkosul: veritabani compatibility level 170+.
   ============================================================ */
SET NOCOUNT ON;
-- Tek grid: gecerli + gecersiz ornekler bir arada (ekran goruntusu icin)
SELECT
    v.tip,
    v.deger,
    IIF(REGEXP_LIKE(v.deger, v.pattern), 1, 0) AS is_valid
FROM (VALUES
    -- E-posta dogrulama
    (N'E-posta (gecerli)',  N'yavuz@example.com',
        N'^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
    (N'E-posta (gecersiz)', N'invalid-email',
        N'^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
    -- Kullanici adi dogrulama (sadece harf+rakam)
    (N'Username (gecerli)',  N'admin42',
        N'^[A-Za-z0-9]{3,20}$'),
    -- DIKKAT: tek tirnak + icteki apostroflar ikilendi
    (N'Username (injection)', N'admin'' OR ''1''=''1',
        N'^[A-Za-z0-9]{3,20}$')
) AS v(tip, deger, pattern);
-- Beklenen: gecerli satirlar 1, gecersiz/injection satirlari 0

/* ============================================================
   SQL INJECTION DEMO
   Tam savunma: input validation gateway + parametreli sorgu
   Yazar: Yavuz Filizlibay | yavuzfilizlibay.com
   DUZELTME: IF REGEXP_LIKE(...) = 0  ->  IF NOT REGEXP_LIKE(...)
             (REGEXP_LIKE Boolean doner, = 0 ile karsilastirilmaz)
   Onkosul: dbo.Users mevcut, veritabani compat level 170+.
   ============================================================ */
CREATE OR ALTER PROCEDURE dbo.spLogin
    @username NVARCHAR(50),
    @password NVARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;
    -- Input validation gateway (allowlist)
    IF NOT REGEXP_LIKE(@username, N'^[A-Za-z0-9._-]{3,30}$')
    BEGIN
        RAISERROR(N'Invalid username format', 16, 1);
        RETURN;
    END
    IF LEN(@password) < 8 OR LEN(@password) > 100
    BEGIN
        RAISERROR(N'Invalid password length', 16, 1);
        RETURN;
    END
    -- Parametreli sorgu — asil guvenlik siniri
    SELECT user_id
    FROM dbo.Users
    WHERE username = @username
      AND password_hash = HASHBYTES('SHA2_256', @password);
END
GO
/* --- Hizli test --------------------------------------------- */
PRINT N'=== Saldiri girdisi (format kapisi durdurur) ===';
EXEC dbo.spLogin @username = N'admin'' OR ''1''=''1''--', @password = N'WhateverPass';
-- Beklenen: "Invalid username format"
PRINT N'';
PRINT N'=== Mesru giris (kapidan gecer, satir doner) ===';
EXEC dbo.spLogin @username = N'admin', @password = N'S3cret!Admin';
-- Beklenen: admin'in user_id'si
GO

-- Test database ve tablo
CREATE DATABASE InjectionLab;
GO
USE InjectionLab;
GO
CREATE TABLE dbo.Users
(
    user_id       INT IDENTITY PRIMARY KEY,
    username      NVARCHAR(50) UNIQUE,
    password_hash VARBINARY(64),
    is_admin      BIT NOT NULL DEFAULT 0
);
INSERT INTO dbo.Users (username, password_hash, is_admin)
VALUES
    (N'admin', HASHBYTES('SHA2_256', N'SuperSecret#123'), 1),
    (N'yavuz', HASHBYTES('SHA2_256', N'password1'), 0);
-- SAVUNMASIZ prosedur (anti-pattern)
CREATE OR ALTER PROCEDURE dbo.spLogin_Vulnerable
    @username NVARCHAR(100),
    @password NVARCHAR(100)
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX) =
        N'SELECT user_id, username, is_admin FROM Users '
      + N'WHERE username = ''' + @username + ''' '
      + N'AND password_hash = HASHBYTES(''SHA2_256'', N'''
      + @password + ''')';
    PRINT @sql;  -- debug amacli
    EXEC sp_executesql @sql;
END
GO
-- Normal kullanim — calisir
EXEC dbo.spLogin_Vulnerable @username = N'admin', @password = N'SuperSecret#123';
-- 1 satir doner: admin
-- ATAK — bypass
EXEC dbo.spLogin_Vulnerable
    @username = N'admin'' OR ''1''=''1''--',
    @password = N'whatever';
-- Hala 1 satir doner — admin! Sifre kontrolu yorumla devre disi
Generated SQL'de PRINT ile gösterdiğimiz çıktıya bakın: WHERE clause'tan sonra `OR '1'='1'--` enjeksiyonu eklendi, ardından `--` comment ile gerisi iptal oldu. Atak başarılı, admin satırını döndü.
Aynı yaklaşımla UNION-based veri sızdırma da mümkün. Bir filter veya search endpoint'i gibi davranan prosedürde:
-- UNION-based exfiltration ornegi
EXEC dbo.spLogin_Vulnerable
    @username = N'x'' UNION SELECT user_id, password_hash, is_admin '
             + N'FROM Users WHERE 1=1--',
    @password = N'whatever';
-- Tum kullaniclilarin password_hash'leri ekrana doker

/* ============================================================
   SQL INJECTION DEMO  —  Zafiyetli vs Guvenli
   Yazar: Yavuz Filizlibay  |  yavuzfilizlibay.com
   Ortam: SQL Server (SSMS 22) — local
   ============================================================ */
SET NOCOUNT ON;
-- 0) Temiz baslangic: demo tablosunu kur ----------------------
IF OBJECT_ID('dbo.Users', 'U') IS NOT NULL
    DROP TABLE dbo.Users;
GO
CREATE TABLE dbo.Users
(
    user_id       INT IDENTITY(1,1) PRIMARY KEY,
    username      NVARCHAR(100)  NOT NULL,
    password_hash VARBINARY(32)  NOT NULL,   -- SHA2_256 = 32 byte
    is_admin      BIT            NOT NULL DEFAULT 0
);
GO
-- Ornek kullanicilar (parolalar hash'lenerek saklaniyor)
INSERT INTO dbo.Users (username, password_hash, is_admin)
VALUES
    (N'admin', HASHBYTES('SHA2_256', N'S3cret!Admin'), 1),
    (N'ayse',  HASHBYTES('SHA2_256', N'ayse1234'),     0),
    (N'mehmet',HASHBYTES('SHA2_256', N'mehmet1234'),   0);
GO
/* ============================================================
   1) ZAFIYETLI VERSIYON  (kotu ornek — sadece gosterim icin)
   Girdi, string birlestirme (concatenation) ile sorguya gomuluyor.
   ============================================================ */
CREATE OR ALTER PROCEDURE dbo.spLogin_Vulnerable
    @username NVARCHAR(100),
    @password NVARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @sql NVARCHAR(MAX);
    -- TEHLIKE: kullanici girdisi dogrudan SQL metnine yapistiriliyor
    SET @sql = N'
        SELECT user_id, username, is_admin
        FROM dbo.Users
        WHERE username = ''' + @username + N'''
          AND password_hash = HASHBYTES(''SHA2_256'', ''' + @password + N''')';
    PRINT N'-- Calisan sorgu metni:';
    PRINT @sql;
    EXEC sp_executesql @sql;
END
GO
/* ============================================================
   2) GUVENLI VERSIYON  (dogru ornek)
   Parametreli sorgu — girdi SQL syntax'ina katilamaz.
   ============================================================ */
CREATE OR ALTER PROCEDURE dbo.spLogin_Safe
    @username NVARCHAR(100),
    @password NVARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;
    -- Parametreli: @username bir DEGER, sorgu KODU degil
    SELECT user_id, username, is_admin
    FROM dbo.Users
    WHERE username = @username
      AND password_hash = HASHBYTES('SHA2_256', @password);
END
GO
/* ============================================================
   3) ATAK — ayni saldiri girdisini iki versiyona da uygula
   ============================================================ */
PRINT N'';
PRINT N'==================================================';
PRINT N'  ZAFIYETLI versiyona saldiri (BASARILI olur)';
PRINT N'==================================================';
-- Klasik bypass: yorum (--) ile parola kontrolu devre disi
EXEC dbo.spLogin_Vulnerable
    @username = N'admin'' --',
    @password = N'herhangibirsey';
-- Sonuc: admin satiri doner. Saldirgan parolayi bilmeden giris yapti.
PRINT N'';
PRINT N'==================================================';
PRINT N'  GUVENLI versiyona AYNI saldiri (BASARISIZ olur)';
PRINT N'==================================================';
EXEC dbo.spLogin_Safe
    @username = N'admin'' OR ''1''=''1''--',
    @password = N'herhangibirsey';
-- Sonuc: 0 satir. Cunku boyle bir username yok; girdi DEGER olarak arandi.
PRINT N'';
PRINT N'==================================================';
PRINT N'  GUVENLI versiyon — gercek/dogru giris (CALISIR)';
PRINT N'==================================================';
EXEC dbo.spLogin_Safe
    @username = N'admin',
    @password = N'S3cret!Admin';
-- Sonuc: admin satiri doner. Mesru kullanici normal sekilde giris yapti.
GO

/* ============================================================
   SQL INJECTION DEMO — Adim 2
   Dinamik SQL'i GUVENLI yazmak: sp_executesql + parametre
   Yazar: Yavuz Filizlibay | yavuzfilizlibay.com
   Onkosul: Adim 1'deki dbo.Users tablosu mevcut olmali.
   ============================================================ */
SET NOCOUNT ON;
-- 0) Ornek icin 'active' kolonu yoksa ekle (idempotent) -------
IF COL_LENGTH('dbo.Users', 'active') IS NULL
BEGIN
    ALTER TABLE dbo.Users ADD active BIT NOT NULL DEFAULT 1;
END
GO
-- Herkes aktif olsun (ornek veri)
UPDATE dbo.Users SET active = 1;
GO
/* ============================================================
   1) SALDIRI denemesi — kotu niyetli girdi parametre olarak gecer
   ============================================================ */
PRINT N'==================================================';
PRINT N'  Dinamik SQL + parametre — SALDIRI girdisi';
PRINT N'==================================================';
DECLARE @sql NVARCHAR(MAX) =
    N'SELECT user_id, username FROM dbo.Users
      WHERE username = @u AND active = @a';
EXEC sp_executesql
    @stmt   = @sql,
    @params = N'@u NVARCHAR(50), @a BIT',
    @u      = N'admin'' OR ''1''=''1',   -- klasik injection denemesi
    @a      = 1;
-- Sonuc: 0 satir. @u bir DEGER olarak arandi; boyle bir kullanici adi yok.
-- Girdi SQL syntax'ina KARISMADI — injection notr hale geldi.
GO
/* ============================================================
   2) MESRU giris — ayni guvenli yapi normal kullaniciyi dondurur
   ============================================================ */
PRINT N'';
PRINT N'==================================================';
PRINT N'  Dinamik SQL + parametre — MESRU girdi';
PRINT N'==================================================';
DECLARE @sql2 NVARCHAR(MAX) =
    N'SELECT user_id, username FROM dbo.Users
      WHERE username = @u AND active = @a';
EXEC sp_executesql
    @stmt   = @sql2,
    @params = N'@u NVARCHAR(50), @a BIT',
    @u      = N'admin',
    @a      = 1;
-- Sonuc: admin satiri doner. Guvenlik mesru kullaniciyi engellemez.
GO

/* ============================================================
   SQL INJECTION DEMO — Adim 3
   Defense-in-depth: Allowlist REGEX format kapisi + parametreli sorgu
   Yazar: Yavuz Filizlibay | yavuzfilizlibay.com
   Onkosul: Adim 1'deki dbo.Users tablosu mevcut olmali.
   DIKKAT: REGEXP_LIKE, veritabani compatibility level 170+ ister.
   ============================================================ */
SET NOCOUNT ON;
-- 0) Compatibility level kontrolu (net hata mesaji icin) ------
DECLARE @compat INT =
    (SELECT compatibility_level FROM sys.databases WHERE database_id = DB_ID());
IF @compat < 170
BEGIN
    DECLARE @msg NVARCHAR(400) =
        N'Bu veritabani (' + DB_NAME() + N') compatibility level ' +
        CAST(@compat AS NVARCHAR(10)) +
        N'. REGEXP_LIKE icin 170 gerekli. Su komutu calistir:' + CHAR(13) +
        N'   ALTER DATABASE [' + DB_NAME() + N'] SET COMPATIBILITY_LEVEL = 170;';
    RAISERROR(@msg, 16, 1);
    RETURN;
END
GO
/* ============================================================
   Prosedur: once format kapisi, sonra parametreli lookup
   ============================================================ */
CREATE OR ALTER PROCEDURE dbo.spLogin_WithRegex
    @username NVARCHAR(100),
    @password NVARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;
    -- 1. Format kapisi (allowlist) — REGEXP_LIKE Boolean doner,
    --    bu yuzden 'NOT' ile kontrol edilir (= 0 DEGIL).
    IF NOT REGEXP_LIKE(@username, N'^[A-Za-z0-9._-]{3,30}$')
    BEGIN
        RAISERROR(N'Invalid username format', 16, 1);
        RETURN;
    END
    IF LEN(@password) < 8 OR LEN(@password) > 100
    BEGIN
        RAISERROR(N'Invalid password length', 16, 1);
        RETURN;
    END
    -- 2. Asil guvenlik siniri: parametreli sorgu
    SELECT user_id, username, is_admin
    FROM dbo.Users
    WHERE username = @username
      AND password_hash = HASHBYTES('SHA2_256', @password);
END
GO
/* ============================================================
   TEST 1) Saldiri girdisi — format kapisina TAKILIR
   ============================================================ */
PRINT N'==================================================';
PRINT N'  TEST 1: Saldiri girdisi (format kapisi durdurur)';
PRINT N'==================================================';
EXEC dbo.spLogin_WithRegex
    @username = N'admin'' OR ''1''=''1''--',   -- bosluk + tirnak = regex'e uymaz
    @password = N'WhateverPassword';
-- Sonuc: "Invalid username format" hatasi. Sorgu hic calismadi.
GO
/* ============================================================
   TEST 2) Mesru giris — kapidan gecer, admin satiri doner
   ============================================================ */
PRINT N'';
PRINT N'==================================================';
PRINT N'  TEST 2: Mesru giris (kapidan gecer, sorgu calisir)';
PRINT N'==================================================';
EXEC dbo.spLogin_WithRegex
    @username = N'admin',
    @password = N'S3cret!Admin';
-- Sonuc: admin satiri doner. Gecerli format + dogru parola.
GO

/* ============================================================
   SQL INJECTION DEMO — Adim 4
   REGEX ile FORMAT dogrulama (TR telefon, TC, IBAN, tarih)
   Yazar: Yavuz Filizlibay | yavuzfilizlibay.com
   NOT: REGEXP_LIKE Boolean doner -> SELECT'te IIF/CASE ile sarilir.
   Onkosul: veritabani compatibility level 170+.
   ============================================================ */
SET NOCOUNT ON;
/* ------------------------------------------------------------
   1) GECERLI ornekler — hepsi is_valid = 1 dondurur
   ------------------------------------------------------------ */
SELECT
    v.tip,
    v.deger,
    IIF(REGEXP_LIKE(v.deger, v.pattern), 1, 0) AS is_valid
FROM (VALUES
    (N'TR Telefon',   N'+905321234567',               N'^\+90[0-9]{10}$'),
    (N'TC Kimlik No', N'12345678901',                 N'^[1-9][0-9]{10}$'),
    (N'IBAN (TR)',    N'TR330006100519786457841326',  N'^TR[0-9]{24}$'),
    (N'Tarih (ISO)',  N'2025-05-05',                  N'^[0-9]{4}-[0-9]{2}-[0-9]{2}$')
) AS v(tip, deger, pattern);
/* ------------------------------------------------------------
   2) GECERSIZ ornekler — format bozuk, is_valid = 0 dondurur
   ------------------------------------------------------------ */
SELECT
    v.tip,
    v.deger,
    IIF(REGEXP_LIKE(v.deger, v.pattern), 1, 0) AS is_valid
FROM (VALUES
    (N'TR Telefon',   N'0532 123 45 67',              N'^\+90[0-9]{10}$'),   -- +90 yok, bosluk var
    (N'TC Kimlik No', N'01234567890',                 N'^[1-9][0-9]{10}$'),   -- 0 ile basliyor
    (N'IBAN (TR)',    N'TR33 0006 1005 1978 6457 84', N'^TR[0-9]{24}$'),      -- bosluklu + eksik
    (N'Tarih (ISO)',  N'05/05/2025',                  N'^[0-9]{4}-[0-9]{2}-[0-9]{2}$') -- yanlis format
) AS v(tip, deger, pattern);

/* ============================================================
   SQL INJECTION DEMO — Adim 5
   En az yetki (least privilege): app_user yalnizca EXECUTE alir,
   tabloya DOGRUDAN erisemez. Ownership chaining ile prosedur calisir.
   Yazar: Yavuz Filizlibay | yavuzfilizlibay.com
   DIKKAT: Bu script, prosedurlerin BULUNDUGU veritabaninda calismali.
   Ekran goruntulerine gore o veritabani AdventureWorks2025.
   Farkliysa asagidaki USE satirini degistir.
   ============================================================ */
SET NOCOUNT ON;
/* ------------------------------------------------------------
   1) Sunucu seviyesinde LOGIN (master) — idempotent
   ------------------------------------------------------------ */
USE master;
GO
IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = N'app_login')
BEGIN
    -- !!! Bu parolayi kendi guclu parolanla degistir !!!
    CREATE LOGIN app_login WITH PASSWORD = N'P@ssw0rd!Demo_2025';
END
GO
/* ------------------------------------------------------------
   2) Veritabani kullanicisi + ornek nesneler
   ------------------------------------------------------------ */
USE AdventureWorks2025;   -- <-- prosedurlerin bulundugu DB
GO
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = N'app_user')
BEGIN
    CREATE USER app_user FOR LOGIN app_login;
END
GO
-- Demoyu tamamlamak icin kucuk bir Products tablosu + prosedur
IF OBJECT_ID('dbo.Products', 'U') IS NULL
BEGIN
    CREATE TABLE dbo.Products
    (
        product_id INT IDENTITY(1,1) PRIMARY KEY,
        name       NVARCHAR(100) NOT NULL,
        price      DECIMAL(10,2) NOT NULL
    );
    INSERT INTO dbo.Products (name, price) VALUES
        (N'Klavye', 450.00), (N'Mouse', 250.00), (N'Monitor', 3200.00);
END
GO
CREATE OR ALTER PROCEDURE dbo.spListProducts
AS
BEGIN
    SET NOCOUNT ON;
    SELECT product_id, name, price FROM dbo.Products;
END
GO
/* ------------------------------------------------------------
   3) SADECE prosedurlere EXECUTE ver (tabloya yetki YOK)
   ------------------------------------------------------------ */
GRANT EXECUTE ON dbo.spLogin_WithRegex TO app_user;
GRANT EXECUTE ON dbo.spListProducts    TO app_user;
GO
/* ============================================================
   TEST — app_user kimligine burunup dene (ayri baglanti gerekmez)
   ============================================================ */
PRINT N'==================================================';
PRINT N'  app_user olarak: PROSEDUR cagrisi (CALISIR)';
PRINT N'==================================================';
EXECUTE AS USER = N'app_user';
EXEC dbo.spListProducts;   -- ownership chaining sayesinde calisir
REVERT;
GO
PRINT N'';
PRINT N'==================================================';
PRINT N'  app_user olarak: DOGRUDAN tablo erisimi (REDDEDILIR)';
PRINT N'==================================================';
EXECUTE AS USER = N'app_user';
BEGIN TRY
    SELECT * FROM dbo.Products;   -- dogrudan SELECT -> permission denied
END TRY
BEGIN CATCH
    PRINT N'Beklenen hata yakalandi:';
    PRINT N'   ' + ERROR_MESSAGE();
END CATCH
REVERT;
GO

/* ============================================================
   SQL INJECTION DEMO — Adim 6
   WITH EXECUTE AS OWNER — ne zaman gerekir, ne zaman gereksiz?
   Yazar: Yavuz Filizlibay | yavuzfilizlibay.com
   Onkosul: Adim 5 calistirilmis olmali (app_user + Products mevcut).
   Veritabani: AdventureWorks2025 (prosedurlerin bulundugu DB).
   ============================================================ */
SET NOCOUNT ON;
USE AdventureWorks2025;
GO
/* ============================================================
   BOLUM A) Senin verdigin prosedur — tam govdesiyle
   NOT: Bu prosedur STATIK SQL kullanir ve dbo.Users ile ayni
        sahibe sahiptir => ownership chaining zaten yeterli.
        Buradaki EXECUTE AS OWNER teknik olarak FAZLALIKTIR,
        ama zararsizdir ve niyeti acikca belgeler.
   ============================================================ */
ALTER PROCEDURE dbo.spLogin_WithRegex
    @username NVARCHAR(100),
    @password NVARCHAR(100)
WITH EXECUTE AS OWNER          -- prosedur sahibi (dbo) yetkisiyle calisir
AS
BEGIN
    SET NOCOUNT ON;
    -- 1. Format kapisi (allowlist)
    IF NOT REGEXP_LIKE(@username, N'^[A-Za-z0-9._-]{3,30}$')
    BEGIN
        RAISERROR(N'Invalid username format', 16, 1);
        RETURN;
    END
    IF LEN(@password) < 8 OR LEN(@password) > 100
    BEGIN
        RAISERROR(N'Invalid password length', 16, 1);
        RETURN;
    END
    -- 2. Parametreli lookup (asil guvenlik siniri)
    SELECT user_id, username, is_admin
    FROM dbo.Users
    WHERE username = @username
      AND password_hash = HASHBYTES('SHA2_256', @password);
END
GO
/* ============================================================
   BOLUM B) EXECUTE AS OWNER'in GERCEKTEN fark ettigi durum:
            DINAMIK SQL (ownership chaining burada KIRILIR)
   ============================================================ */
-- B1) EXECUTE AS OWNER YOK -> dinamik SQL CAGIRAN kimligiyle calisir
CREATE OR ALTER PROCEDURE dbo.spProducts_Dynamic
AS
BEGIN
    SET NOCOUNT ON;
    -- Parametreli + dinamik (guvenli), ama chaining dinamikte kirilir
    DECLARE @sql NVARCHAR(MAX) = N'SELECT product_id, name, price FROM dbo.Products';
    EXEC sp_executesql @sql;
END
GO
-- B2) EXECUTE AS OWNER VAR -> dinamik SQL sahip (dbo) kimligiyle calisir
CREATE OR ALTER PROCEDURE dbo.spProducts_DynamicAsOwner
WITH EXECUTE AS OWNER
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @sql NVARCHAR(MAX) = N'SELECT product_id, name, price FROM dbo.Products';
    EXEC sp_executesql @sql;
END
GO
-- app_user'a iki yeni prosedur icin de EXECUTE ver
GRANT EXECUTE ON dbo.spProducts_Dynamic        TO app_user;
GRANT EXECUTE ON dbo.spProducts_DynamicAsOwner TO app_user;
GO
/* ============================================================
   TEST — app_user kimligiyle iki dinamik proseduru karsilastir
   ============================================================ */
PRINT N'==================================================';
PRINT N'  app_user: DINAMIK + EXECUTE AS OWNER YOK (REDDEDILIR)';
PRINT N'==================================================';
EXECUTE AS USER = N'app_user';
BEGIN TRY
    EXEC dbo.spProducts_Dynamic;   -- chaining kirildi, app_user'in SELECT yetkisi yok
END TRY
BEGIN CATCH
    PRINT N'Beklenen hata: ' + ERROR_MESSAGE();
END CATCH
REVERT;
GO
PRINT N'';
PRINT N'==================================================';
PRINT N'  app_user: DINAMIK + EXECUTE AS OWNER VAR (CALISIR)';
PRINT N'==================================================';
EXECUTE AS USER = N'app_user';
EXEC dbo.spProducts_DynamicAsOwner;   -- sahip (dbo) yetkisiyle calisti
REVERT;
GO

/* ============================================================
   SQL INJECTION DEMO — Adim 7
   MINI GUVENLIK TARAMASI (T-SQL)
   SSMS'in kaldirilan "Vulnerability Assessment" aracinin yerine
   yaygin yanlis yapilandirmalari LOCAL olarak kontrol eder.
   Yazar: Yavuz Filizlibay | yavuzfilizlibay.com
   NOT: Bu, Microsoft Defender for SQL'in yerini TUTMAZ; egitim
        amacli hafif bir kontrol listesidir. sysadmin yetkisi ister.
   Calistirma: master uzerinde calistir (sunucu seviyesi kontroller).
   ============================================================ */
SET NOCOUNT ON;
USE master;
GO
PRINT N'################ MINI GUVENLIK TARAMASI ################';
PRINT N'';
/* --- 1) Kimlik dogrulama modu ------------------------------- */
PRINT N'--- 1) Sunucu kimlik dogrulama modu ---';
SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly')
            WHEN 1 THEN N'Windows Authentication (daha guvenli)'
            WHEN 0 THEN N'Mixed Mode (SQL + Windows) - gozden gecir'
       END AS auth_mode;
/* --- 2) 'sa' hesabinin durumu ------------------------------- */
PRINT N'--- 2) sa hesabi (devre disi + yeniden adlandirilmis olmali) ---';
SELECT name AS login_adi,
       is_disabled,
       IIF(name = N'sa', N'YENIDEN ADLANDIR onerilir', N'-') AS oneri
FROM sys.sql_logins
WHERE principal_id = 1;   -- sa her zaman principal_id = 1
/* --- 3) Parola politikasi KAPALI olan aktif SQL login'leri --- */
PRINT N'--- 3) Zayif parola ayarli aktif SQL login''leri ---';
SELECT name AS login_adi, is_policy_checked, is_expiration_checked
FROM sys.sql_logins
WHERE is_disabled = 0
  AND (is_policy_checked = 0 OR is_expiration_checked = 0);
/* --- 4) sysadmin rolu uyeleri (asiri yetki) ----------------- */
PRINT N'--- 4) sysadmin rolundeki principal''ler ---';
SELECT m.name AS uye, m.type_desc AS tip, m.is_disabled
FROM sys.server_role_members rm
JOIN sys.server_principals r ON rm.role_principal_id   = r.principal_id
JOIN sys.server_principals m ON rm.member_principal_id = m.principal_id
WHERE r.name = N'sysadmin'
ORDER BY m.name;
/* --- 5) Tehlikeli surface-area ayarlari --------------------- */
PRINT N'--- 5) Riskli sunucu yapilandirmalari ---';
SELECT name AS ayar,
       CAST(value_in_use AS INT) AS deger,
       IIF(value_in_use = 1, N'ACIK - gozden gecir', N'kapali (iyi)') AS durum
FROM sys.configurations
WHERE name IN (N'xp_cmdshell',
               N'Ole Automation Procedures',
               N'clr enabled',
               N'Ad Hoc Distributed Queries',
               N'remote access',
               N'scan for startup procs')
ORDER BY name;
/* --- 6) Kullanici veritabanlarinda TDE (sifreleme) durumu ---- */
PRINT N'--- 6) Veritabani sifreleme (TDE) durumu ---';
SELECT name AS veritabani,
       is_encrypted,
       IIF(is_encrypted = 1, N'TDE acik', N'TDE KAPALI') AS durum
FROM sys.databases
WHERE database_id > 4          -- sistem DB'lerini atla
ORDER BY name;
/* --- 7) Veritabani sahipleri -------------------------------- */
PRINT N'--- 7) Veritabani sahipleri ---';
SELECT name AS veritabani,
       SUSER_SNAME(owner_sid) AS sahip
FROM sys.databases
ORDER BY name;
PRINT N'';
PRINT N'################ TARAMA TAMAMLANDI ################';
GO