Tedd biztonságosabbá az SQL-edet

Az SQL nagyon jó dolog, de emellett veszélyes is. Ha SQL-t használsz adatbázis-kezelésre egy alkalmazásban, aminek száz, ezer vagy akár milliónyi felhasználója van, óvatosnak kell lenned – hiszen az adatok véletlenül megsérülhetnek vagy törlődhetnek. Viszont számos módszer létezik arra, hogy az SQL-edet biztonságosabbá tedd.

Hibás módosítások/törlések elkerülése

Mielőtt kiadnál egy UPDATE-et, futtass egy SELECT-et ugyanazzal a WHERE-rel, hogy megbizonyosodj róla, hogy a megfelelő oszlopokat és sorokat módosítod.

Például mielőtt futtatod ezt:

UPDATE users SET deleted = true WHERE id = 1;

Futtasd ezt:

SELECT id, deleted FROM users WHERE id = 1;

Ha már eldöntötted, hogy futtatod a módosítást, használhatsz LIMIT operátort, így biztosíthatod, hogy nem törölsz véletlenül túl sok sort.

UPDATE users SET deleted = true WHERE id = 1 LIMIT 1;

Vagy ha törölsz:

DELETE users WHERE id = 1 LIMIT 1;

Tranzakciók használata

Amikor SQL parancsok kiadásakor megváltoztatjuk az adatbázisunkat, akkor ez egy ún. „tranzakcióval” kezdődik. A tranzakció műveletek sorozata, melyek logikai egységként kezeltek (mint a banki tranzakciók). A világ adatbázisaiban a tranzakcióknak meg kell felelniük az „ACID” alapelveknek, amivel biztosítható, hogy megbízható módon lesznek a műveletek végrehajtva.

Minden olyan parancs kiadásakor, mint a CREATE, UPDATE, INSERT, vagy a DELETE, automatikusan elkezdünk egy tranzakciót. Viszont, ha szeretnénk, mi magunk is becsomagolhatunk több parancsot egy nagyobb tranzakcióba. Lehet, hogy azt szeretnénk, hogy egy bizonyos UPDATE akkor legyen végrehajtva, ha egy másik UPDATE-et is végrehajtottunk, így ezt a kettőt például beletehetjük ugyanabba a tranzakcióba.

Ilyen esetekben ezeket a parancsokat tegyük egy BEGIN TRANSACTION és egy COMMIT közé:

BEGIN TRANSACTION;
UPDATE people SET husband = "Winston" WHERE user_id = 1;
UPDATE people SET wife = "Winnefer" WHERE user_id = 2;
COMMIT;

Ha az adatbázis valamilyen okból kifolyólag nem tudja mindkettő UPDATE parancsot végrehajtani, akkor visszavonja a tranzakciót, és a tranzakció előtti állapotra állítja vissza az adatbázist.

Akkor is használhatunk tranzakciókat, amikor biztosak szeretnénk lenni benne, hogy a parancsaink ugyanazt az adatot látják – amikor azt szeretnénk biztosítani, hogy nem hajtódnak végre más tranzakciók ugyanazon az adaton, amíg a parancsaink sora fut. Amikor parancsok sorát futtatod, tedd fel a kérdést, hogy mi történne, ha egy másik felhasználó parancsokat adna ki ez idő alatt. Lehetséges, hogy az adataid furcsa módon megváltoznának? Ha igen, mindenképp használj tranzakciót!

Például a következő parancsok egy sort hoznak létre, ami azt jelzi, hogy valamelyik felhasználó megszerzett egy jelvényt, aztán módosítja a felhasználó jelenlegi aktivitását, hogy ez látszódjon is:

INSERT INTO user_badges VALUES (1, "SQL Master", "4pm");
UPDATE user SET recent_activity = "Earned SQL Master badge" WHERE id = 1;

Ezalatt lehet, hogy egy másik felhasználó vagy folyamat egy második jelvényt ad a felhasználónak:

INSERT INTO user_badges VALUES (1, "Great Listener", "4:05pm");
UPDATE user SET recent_activity = "Earned Great Listener badge" WHERE id = 1;

Ezek a parancsok lehetséges, hogy ebben a sorrendben hajtódnak végre:

INSERT INTO user_badges VALUES (1, "SQL Master");
INSERT INTO user_badges VALUES (1, "Great Listener");
UPDATE user SET recent_activity = "Earned Great Listener badge" WHERE id = 1;
UPDATE user SET recent_activity = "Earned SQL Master badge" WHERE id = 1;

A legutóbbi aktivitás így az „Earned SQL Master badge” lenne akkor is, ha valójában a legutóbbi jelvény a „Great listener” volt. Nem a világ vége, de nem igazán erre számítottunk.

Ehelyett ha ezeket egy tranzakcióban futtatjuk, azzal garantáljuk, hogy nem fog közbeszólni semmilyen más tranzakció:

BEGIN TRANSACTION;
INSERT INTO user_badges VALUES (1, "SQL Master");
UPDATE user SET recent_activity = "Earned SQL Master badge" WHERE id = 1;
COMMIT;

Biztonsági mentések készítése

Ha követed is a fenti tippeket, hiba bármikor történhet. Ezért a legtöbb cég biztonsági másolatokat készít az adatbázisairól – óránként, naponta vagy hetente, – ami az adatbázis méretétől, és az elérhető tárhely nagyságától függ. Amikor valami hiba történik, a régi adatbázist importtal vissza tudják állítani, bármelyik tábla sérült is meg vagy veszett el. Lehet, hogy kicsit elavult lesz az adat, de még mindig jobb, ha elavult adatunk van, mintha semmilyen.

Másodpéldány

Egy kapcsolódó módszer a másodpéldány vagy replika - amikor az adatbázis több másolatát tároljuk folyamatosan, különböző helyeken. Ha valamilyen okból az adatbázis egyik másolata nem elérhető (például villám csapott az épületbe, ahol az adatbázis található, ami egyébként velem megtörtént!), akkor a lekérdezést lefuttathatjuk az adatbázis egy másolatán, ami remélhetőleg elérhető. Ha nagyon fontos adatokról van szó, akkor valószínűleg érdemes másodpéldányokat létrehoznod, hogy biztosítsd az elérhetőséget. Például ha egy orvos szeretné lekérni a páciens allergiáinak listáját, hogy egy vészhelyzetben megfelelően tudja kezelni, akkor sürgősen szüksége lesz rá, nem várhat informatikusokra, hogy hozzájusson az adatokhoz a biztonsági másolatról.

Mivel elég sok erőforrást igényel, hogy másodpéldányokat készítünk az adatbázisokról, és ez gyakran lassítja a működést is – hiszen minden írási műveletet az összes másodpéldányon végre kell hajtani ,– ezért a cégeknek el kell dönteniük, hogy megéri-e a másodpéldányokkal járó többletköltség, és érdemes megvizsgálniuk, hogy mi a legjobb módszer a megfelelő környezet beállítására.

Privilégumok kezelése

A legtöbb adatbázis-rendszerben beépítve vannak a felhasználók és privilégiumok (jogosultságok), mivel szerveren találhatóak és több felhasználó fér hozzájuk. A Khan Academy SQL szkriptjeiben nincs koncepció a felhasználók/privilégiumok kezelésére, mivel az SQLite többnyire egy-felhasználós rendszer, így addig írhatod, amíg hozzáférésed van a meghajtóhoz, amin tárolva van.

Ellenben ha olyan adatbázis-rendszert használsz valamikor, ami megosztott szerveren van, akkor mindenképp bizonyosodj meg róla, hogy megfelelően állítottad-e be a felhasználókat és jogosultságaikat már az elején is. Általános szabály az, hogy csak néhány felhasználónak legyen teljes hozzáférése az adatbázishoz (például a backend fejlesztőknek), hiszen ez veszélyes dolog.

Például, így tudsz teljes hozzáférést adni egy adott felhasználónak:

GRANT FULL ON TABLE users TO super_admin;

És így tudsz csak SELECT hozzáférést adni egy másik felhasználónak:

GRANT SELECT ON TABLE users TO analyzing_user;

A nagy cégekben általában nem szeretnek SELECT-hozzáférést adni a felhasználónak, mert a táblában privát adatok is szerepelhetnek, például a felhasználók email címe vagy neve. Sok cégnek van anonimizált adatbázis-verziója, amit lekérdezhetnek, és mégsem kell aggódniuk, hogy valaki privát információkhoz fér hozzá.

Bónusz:: Olvasd el a híres XKCD képregényt a biztonságosabb SQL-ről (plusz ezt a magyarázat is (angolul)).

Szeretnél részt venni a beszélgetésben?

Jelentkezz be

Rendezés:

Még nincs hozzászólás.

Tudsz angolul? Kattints ide, ha meg szeretnéd nézni, milyen beszélgetések folynak a Khan Academy angol nyelvű oldalán.