Elmélet kategória bejegyzései

A nagy, a sok és a konkurencia

A fenti 3 dologban közös, hogy mindhárom ellensége a DBA-nak. Ahhoz, hogy tartósan megfelelően működjenek a szerverek, a fenti dolgokat figyelembe kell venni. Bár relatív fogalmak, mert függ a hardvertől is, hogy mikor érik el a kritikus szintet, de amelyik alkalmazás fejlődik ott figyelni kell ezekre.

Nézzük sorra, hogy melyik hol gond:

Nagy méret: (Ami nagy lesz az gond lesz, csak idő kérdése)

  • Nagy méretű adatbázisok: Minél nagyobb az adatbázis mérete, annál tovább tartanak rajta a karbantartási, mentési, helyreállítási műveletek, annál több hely kell a backup-oknak. Jó megoldás az adatbázisok elosztása. Pl.: Készül egy archív adatbázis a product adatbázis mellé, ahova az archive adatok kerülnek.
  • Nagy méretű táblák, indexek: Ezekben az esetekben is a karbantartási műveletek (pl: index rebuild, index reorganize, update statistics) kerülnek sok időbe. Célszerű darabolni a táblát (pl.: partition).

Sok: (Amiből sok lesz, az gond lesz, csak idő kérdése)

  • Sok sor: Minél több egy táblában a sor, annál mélyebbek lesznek az indexek, több lépésből tart megtalálni egy-egy sort is. A particionálás ebben az esetben jól jöhet.
  • Sok nyitott kapcsolat: Minden kapcsolat plusz erőforrásba kerül a szervernek. Amikor kezd kritikussá válni a mennyisége, akkor célszerű új szervereket beállítani a kiszolgálásba.
  • Sok utasítás egységnyi idő alatt: Amikor sok utasítás érdekezik be egységnyi idő alatt, akkor célszerű felülvizsgálni az alkalmazás(oka)t, mert lehet kódhibáról van szó, de az is lehet, hogy DML utasítások helyett a tárolt eljárás a nyerő. Egyéb esetben meg új szervereket célszerű beállítani a kiszolgálásba.

Konkurencia: (Ami konkurál egymással az össze fog akadni, csak idő kérdése)

  • Tranzakciós lock: Ezek a lock-ok a válaszidőket növelik meg. Márpedig a gyors válaszidőket jobban szeretjük. Ehhez vagy jól tervezzük az adatbázisokat és vele az alkalmazást is, vagy átnyargalunk az in-memory technológiára, ha van rá lehetőségünk.

 

A problémákra az ajánlások általános ajánlások, lehet másképp is megoldani, erősen esetfüggő.

Reklámok

Tárolt eljárás: hol és miért jobb?

A tárolt eljárásokat az SQL szerver tárolja és futtatja. Ahhoz, hogy el tudjuk dönteni szükségünk van e rá, nézzük meg milyen előnyei vannak:

  • Biztonság
  • Teljesítmény

Ha az alkalmazás tervezésekor már látszik, hogy fentiek közül valamelyik sokkal többet nyom a latba, mint az egyéb követelmények, akkor érdemes elgondolkodni rajta. Ebben a post-ban az első pontról fogok írni.

Biztonság:

A tárolt eljárás attól ad nagy biztonságot, hogy a user annak futtatására kapja a jogot és nem a táblákra (select, insert, update, delete jogokat). Ezzel a tipikus SQL injection kizárva.

Akkor is nagyon hasznos, amikor már betörtek az alkalmazás szerverekre és ellopták az adatbázis kapcsolat konfigurációs (felhasználó, jelszó, stb) fájljait. Ekkor a tárolt eljárások futtatására lesz joga a támadónak és nem pl egy “SELECT * FROM jelszavak;” parancs futtatására. A tárolt eljárás pedig mindig azt adja vissza, amit beleprogramoztunk. Eddig tudott minket védeni az SQL szerver, innen jövünk mi.

Hol tudjuk ezt a gyakorlatban nagyon jól használni? Pl.: Ott, ami most is az orrunk előtt van, a weben :). Ha van egy webalkalmazásunk, akkor az egész világnak esélyt adunk rá, hogy feltörje azt.

Legyen is egy ilyen webalkalmazásunk, ahol be tudnak login-olni a usereink. Tételezzük fel, hogy tárolt eljárásaink vannak. A támadó bejutott a webszerverre (az adatbázis másik fizikai szerveren van), ellopta az alkalmazás logikáját, tudja futtatni a tárol eljárásainkat. Idővel kitalálja, hogyan kell őket paraméterezni, hogy el tudja lopni pl.: a felhasználó / jelszó párosokat. Már ezzel is sokat nyertünk, de itt lenne a vége, ennyire futotta? Közel sem! Nézzük meg egy példán keresztül mit tehetünk még!

A rosszul megírt eljárás bekéri a user azonosítóját, visszaadja az adatait, majd az alkalmazás elvégzi a hitelesítést:

create procedure usp_user_get_by_id
	@user_id int
as
begin
	set nocount on;
	
	select
		user_name,
		password,
		create_date
	from users
	where user_id = @user_id
end
go

 

A jól megírt eljárás elvégzi a hitelesítést majd visszaadja az adatokat:

create procedure usp_user_get_by_id_password
	@user_id int,
	@password varchar(64)
as
begin
	set nocount on;
	
	select 
		user_name,
		create_date
	from users
	where user_id = @user_id
		and password = @password
	
end
go

 

Mindössze annyi történt, hogy a logika átkerült az alkalmazásból az adatbázisba. Így az érzékeny adatnak nem kellett elhagynia az adatbázis szervert. Ezzel tehát még egy védelmi falat húztunk fel. És ehhez semmi nagy varázslat nem kellett, csak szem előtt tartani, hogy a biztonság a legfontosabb az alkalmazásunkban.

Tehát ami védett: tárolt eljárás + a logika az sp-ben volt.

A logika a tárolt eljárásban egyébként is egy izgalmas téma, vannak nagyon jó felhasználási területei, ma bemutattam egyet.

LIKE és az ESCAPE

A LIKE-ot szerintem mindenki ismeri, de az ESCAPE záradékát már kevesebben, pedig nagyon hasznos tud lenni. Erről írok most egy rövid postot.

Az ESCAPE kiegészítés akkor hasznos, amikor egy foglalt karakter is szerepelne a keresésben. Ilyen karakterek jelenleg: [,],%,_,^

Tehát, ha van egy ilyen változónk: @a = ‘ertek%ertek’ és azt akarjuk tudni, hogy @a változóban található e % jel, akkor escape-elni kell. Az escape karakter bármi lehet, a lényeg, hogy az escape záradékban adjuk meg, hogy mi volt az. A továbbiakban minden szónál többet fognak érni a példák, úgyhogy jöjjön a demó.

Készítünk egy táblát, teszünk bele % jeles értékeket és szűrünk többféle variációban (Százalékjel helyett lehetne más foglalt karakter is, én most ezzel demózok).

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
DROP TABLE #tmp
GO

CREATE TABLE #tmp (name VARCHAR(16) NULL)

INSERT INTO #tmp (name)
VALUES
(NULL),
('ertek%ertek'),
('ertekertek'),
('%'),
('%ertek'),
('ertek%'),
('e%rtek'),
('erte%k')

 

Próbáljuk keresni a hagyományos módszerrel a % jelet:

-- 1.) Elso teszt
SELECT [name]
FROM #tmp
WHERE name LIKE '%%%'

01_elso_teszt

Azt látjuk, hogy visszakaptuk a teljes tábla tartalmát a NULL kivételével. De nem ezt akartuk.

Használjuk ez escape-elést.

-- 3.) Szazalekjel van valahol az ertekben
SELECT [name]
FROM #tmp
WHERE name LIKE '%!%%' ESCAPE '!'

03_szalaekjel_valahol
Egész jó, itt már azt kaptuk, amit akartunk. Bonyolítsuk néhány dologgal, a további példák már csak ráadások.

-- 4.) Szazalekjel van valahol az ertekben, de a szazalekjel elott van legalabb egy karakter
SELECT [name]
FROM #tmp
WHERE name LIKE '%_!%%' ESCAPE '!'

04_szazalekjel_valahol_plusz_elotte_1_karakter

 

-- 5.) Szazalekjel van valahol az ertekben, de a szazalekjel utan van legalabb egy karakter
SELECT [name]
FROM #tmp
WHERE name LIKE '%!%_%' ESCAPE '!'

05_szazalekjel_valahol_plusz_utana_1_karakter

 

-- 6.) Szazalekjellel kezdodik az ertek
SELECT [name]
FROM #tmp
WHERE name LIKE '!%%' ESCAPE '!'

06_szazalekjellel_kezdodik

 

-- 7.) Szazalekjelre vegzodik az ertek
SELECT [name]
FROM #tmp
WHERE name LIKE '%!%' ESCAPE '!'

07_szazalekjelre_vegzodik

 

-- 8.) Szazalekjel elott csak 1 db karakter all
SELECT [name]
FROM #tmp
WHERE name LIKE '_!%%' ESCAPE '!'

08_szazalekjel_elott_1_karakter_utana_barmennyi

 

-- 9.) Szazalekjel utan csak 1 db karakter all
SELECT [name]
FROM #tmp
WHERE name LIKE '%!%_' ESCAPE '!'

09_szazalekjel_utan_1_karakter_elotte_barmennyi

 

-- 10.) Szazalekjel utan 'e' vagy 'r' vagy 'k' aztan barmi
SELECT [name]
FROM #tmp
WHERE name LIKE '%!%[erk]%' ESCAPE '!'

10_szazalekjel_utan_erk_aztan_barmi

-- 11.) Szazalekjel utan 'e' vagy 'r' vagy 'k' aztan vege
SELECT [name]
FROM #tmp
WHERE name LIKE '%!%[erk]' ESCAPE '!'

11_szazalekjel_utan_erk_aztan_vege

 

Van még a LIKE-nak jó tulajdonsága, bővebben BOL-ban lehet utána olvasni.

Ha valakinek van kedve még ezzel játszani, akkor itt egy feladat: A meglévő táblába szúrjunk be további 2 értéket:

INSERT INTO [#tmp] ( [name] ) VALUES  ( 'ertek%[[ertek]]')
INSERT INTO [#tmp] ( [name] ) VALUES  ( 'ertek%[ertek]')

És most keressük ki azokat az értékeket, ahol:

  1. A ‘%’ jel után ‘[‘ jel áll. (Tehát ‘%[‘)
  2. A ‘%’ jel után ‘[‘ jel áll, utána pedig NEM ‘[‘ jel. (Tehát ‘%[‘ majd nem ‘[‘). Ez utóbbit a cikk alapján nem tudod megoldani, el kell olvasni a BOL-t, ha nem tudod a megoldást.

Végezetül pedig, ha nagyon bonyolult regex-es kifejezésekre van szükségünk, akkor használjunk hozzá CLR-t.