Elmélet kategória bejegyzései

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.

Amikor a memóriára várunk

Talán mindenki tudja, ha adatbázis kiszolgáló a szerver, akkor legyen minél több a memória benne. Ezt én is osztom, hiszen a memória olcsó befektetés és látványos javulás érhető el vele. Mielőtt tovább mennék nézzük meg, miért is olyan fontos a sok memória.

Az adatbázis szerver mindent a memóriában csinál (elenyésző kivétellel). Tehát, ha adott művelethez nem található meg az adat a memóriában, akkor felolvassa azt a diszkről és csak ezután kezd hozzá a feladathoz. A memóriába pedig csak akkor tud adatot felolvasni, ha van szabad. Itt már lehet sejteni, hogy akármilyen gyorsak is a diszkjeink (lehet akár SSD-is), ha kevés a memória, könnyen szűk keresztmetszet lesz. Láttam már olyat, hogy többezer request/sec-et 512 MB database cache szolgált ki. Hát nem volt gyors.

Nézzük meg egy egyszerű példával is. Legyen egy SQL Server Express-ünk, ami maximum 1 GB memóriát használhat. Van egy lekérdezésünk, aminek szüksége van mind az 1 GB memóriára és 2 perc futásra a feladat elvégzéséhez. Ebben a két percben érkeznek be újabb lekérdezések, amik gyorsan lefutnának, de nem tudnak, mert nem kapnak memóriát a feladat végrehajtásához, így várakoznak. Ilyen eseteknél simán láthatjuk, hogy a CPU nincs kihajtva, a diszkolvasás elviselhető, blokk nincs és a hálózat is csendes, a lekérdezünk és persze másoké is pedig homokórázik vagy timeout-ol. Sunyi egy dög a memória probléma, az már biztos.

Erre az esetre (is) alkottak az SQL Server fejlesztői két nagyszerű nézetet, amivel ez a hiba elég jól beazonosítható:

  1. sys.dm_exec_query_resource_semaphores
  2. sys.dm_exec_query_memory_grants

 

Nézzünk a demót.

Én SQL Server Express-t használok és a max memóriát 1 GB-ra vettem vissza (ugye tud többet is használni 🙂 ), a jól demózhatóság érdekében javaslom nektek is. Jah, és teszt környeztben csináljátok.

 

use master
go

EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max server memory (MB)', N'1024'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0'  RECONFIGURE WITH OVERRIDE
GO

 

Készítsünk teszt adatokat, most 100K sornyi adatot használunk:

use tempdb
go
if object_id('dbo.data') is not null
drop table dbo.data
go

create table dbo.data (
    id int not null identity(1,1) primary key,
    fk_1 int not null,
    fk_2 int not null,
    fk_3 int not null,
    fk_4 int not null,
    fk_5 int not null
)
go

;with cte as (
    select top (100000)
        row_number() over(order by (select null)) as rn
    from sys.[columns] c1
    cross join sys.columns c2
)

insert into dbo.data(fk_1, fk_2, fk_3, fk_4, fk_5)
select c1.rn, c1.rn, c1.rn, c1.rn, c1.rn
from cte c1
cross join cte c2
where c2.rn < 11
go

 

6 lekérdezés fog következni:
1.) 1 db query, ami alapból “gyorsan” lefut.
2.) 4 db egyforma lekérdezés, amivel leterheljük a szervert. Ha esetleg enterprise vagy developer a szerver, akkor a “merry-go-round scanning” szkennelés miatt lehet módosítani kell a lekérdezéseket. Ekkor az order by és a visszaadott oszlopok legyen eltérőek egymástól mind a 4 session-ben. Erről a szkennről az Advanced Scanning résznél olvashat az, akit érdekel.
3.) 1 db monitorozó lekérdezés, amivel beazonosítjuk, hogy memória problémával állunk szemben.

Futtassuk az 1. lekérdezést, hogy lássuk a normál futásidőt (nálam 5 mp):

use tempdb
go

;with cte as (
    select col1 from (values (1), (2), (3), (4), (5)) tbl1 (col1)
) ,

cte2 as (
    select
        c1.col1 as c1,
        c2.col1 as c2,
        c3.col1 as c3
    from cte c1
    cross join [cte] c2
    cross join [cte] c3
)

select top 100000 *
from [cte2] c1
cross join [cte2] c2
cross join [cte2] c3
cross join [cte2] c4
cross join [cte2] c5
cross join [cte2] c6
cross join [cte2] c7
order by 1,2,3,4
option (maxdop 1)

A 2. query-t indítsuk el 4 db session-ben, garantáltan sokáig fognak futni.

 
use tempdb
go

select 
	d1.[id],
	d2.[fk_2],
	d2.[id]
from [dbo].[data] d1
cross join [dbo].[data] d2
order by 
	d1.[fk_1], 
	d1.[fk_2], 
	d1.[fk_3], 
	d1.[fk_4], 
	d1.[fk_5], 
	d2.[fk_1],
	d2.[fk_2],
	d2.[fk_3],
	d2.[fk_4]
option (maxdop 1)

 

A 3. (monitorozó) lekérdezést futtassuk a 6. session-ben.

select 
    case when [granted_memory_kb] = [used_memory_kb] then 1 else 0 end as flag, 
    * 
FROM sys.dm_exec_query_resource_semaphores

SELECT * FROM sys.dm_exec_query_memory_grants

 

Ha nálatok is úgy alakul a memória használat, mint nálam, akkor a következőt láthatjátok a monitorozó session-ben:

052_wait_for_memory_1

Az összetartozó adatokat közös színnel jelöltem. A zöldeknél látjuk, hogy a felső táblázatban hárman megkapták a szükséges memórát, míg az alsó táblázatban azt is, hogy mikor és mennyit, illetve milyen session_id-kal. A pirosnál láthatjuk a felső táblázatban, hogy egy valaki várakozik memóriára, az alsóban pedig, hogy ki az (session_id). Itt grant_time mező értéke NULL. Ha esetleg nálatok nem lenne olyan, aki memóriára vár, akkor indítsatok még néhány session-ben a 2-es query-ből.

 

Most ismét indítsuk el az 1. lekérdezést, majd futtassuk a monitorozó (3.) lekérdezésünket. Ezt az eredményt láthatjuk:

052_wait_for_memory_2

Várakozik az a lekérdezésünk is, ami eddig pár másodperc alatt lefutott. Sőt, még sokáig fog várakozni. Ha kilőnénk a 2. query-ket, akkor ismét gyorsan lefutna az 1-es lekérdezésünk, de nem tesszük. Helyette inkább vizsgálódunk még.

 

Kérdezzük le, hogy milyen blocking_session-jeink vannak:

select * from sys.dm_os_waiting_tasks 
where [blocking_session_id] > 0 

Az eredmény:

052_wait_for_memory_3

Nézzük meg a counterek sql-ből lekérdezve:

SELECT [counter_name], cntr_value as value
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME like '%Memory Manager%'
    AND counter_name like 'Memory Grants%'

 

Végül, ha monitorozni akarjuk ezt típusú hibát, akkor legjobban a perfmon.exe-n keresztül tehetjük meg, szerintem. Aki nem tudja hogyan kell, annak itt egy step-by-step útmutató:

1.) Írjuk a Start menü keresőjébe, hogy perfmon.exe, majd nyomjunk enter.

2.) Válasszuk a bal oldali fában a “Teljesítményfigyelő” lehetőséget, majd a jobb oldali részen a lenyíló ikonnál válasszuk a “Jelentés” menüpontot.

052_wait_for_memory_4

3.) Jobb oldalon alapból a processzor információ már látszik. Ezen a részen, a fehér területen jobb gomb, majd “Számláló hozzáadása…” lehetőség.

052_wait_for_memory_5

4.) Itt keressük ki, majd nyissuk le az “MSSQL$<instance név>:Memory Manager” lehetőséget, majd jelöljük ki az egér bal gombjával rákattintva a “Memory Grants Outstanding” és “Memory Grants Pending” számlálókat, alul kattintsunk a “Hozzáadás” gombra, majd “OK” gomb.

052_wait_for_memory_6

Ha mindent jól csináltunk, akkor ezt láthatjuk:

052_wait_for_memory_7

 

Arra figyeljetek, hogy tempdb nagyon hízni fog.