Databáze v geoinformatice V.

Mgr. Michal Jakl

ZS 2025

Z minula

VAŠE TÉMATA

Z minula

Z minula

Obsah lekce

1) Bezpečnost databází

2) Schématický zápis

3) Datové typy

4) Formátování kódu

​

​

​

​

​

Praktické cvičení:

Procvičování SQL na praktickém příkladu

1. Bezpečnost databází – ukládání hesel

• Hesla jako prostý text
• Hashování
• Hashovací funkce vytváří z libovolně dlouhého vstupu vytvoří řetězec fixní délky (např. u MD5 je to 32 znaků, další funkce např. SHA-256)
• "Moje heslo je super" -> "53373359a13959868b9136cdead513ba"
• I kontrola integrity souborů po jejich přenosu (stejný hash před i po).
• Sebemenší změna ve zdrojových datech způsobí změnu i na výstupu a stejný vstup bude mít vždy stejný výstup
• Výsledek: Nemáme uložené heslo v čitelné podobě, žádný programátor ho v databázi nevidí a při úniku dat útočník také hesla nezíská.
• Problém: vytváření tzv. rainbow tables - jde o "předpočítané" tabulky hesel čítající miliony záznamů pro nejčastější slovníková hesla
• Řešení: přidání soli k heslu = přidání nějakého řetězce k heslu předtím, než je použita hashovací funkce. Abychom řešení ještě vylepšili, můžeme sůl přidat opakovaně a hashovací funkci zanořovat.
• Pokud na hashování použime něco jako MD5(MD5(heslo + "adsaw") + MD5(MD5(heslo + "ae2" + email))), můžeme s poměrně velkou jistotou říci že jakékoliv předpočítané tabulky budou k ničemu.

1. Bezpečnost databází – ukládání hesel

• BCRYPT
• = hashovací algoritmus, v případě PHP funkce password_hash((
• Automaticky používá náhodnou sůl a kvalitnější algoritmus pro hashovaní.
• Neplatí také pravidla uváděná výše. Pro stejný vstup po použití funkce password_hash nedostaneme vždy stejný výstup.
• Pokud tedy dva uživatelé mají stejné heslo, z uloženého hashe to není poznat. Výstupem je řetězec dlouhý 60 znaků, který začíná znakem $ a má 3 části.
• První určujte typ použitého algoritmu, druhá salt a třetí samotný hash. Výsledek vypadá nějak takto:
• $2y$10$.vGA1O9wmRjrwAVXD98HNOgsNpDczlqm3Jq7KnEd1rVAGv3Fykk1a
• K porovnání nemůžeme použít klasicky operátor rovnosti, ale opět speciální funkci, v případě PHP jde o password_verify().
• Další výhodou tohoto řešení je jeho pomalost.
• Pomocí md5 vygenerujeme na běžném počítači tisíce hashů během jediné sekundy.
• BCRYPTu trvá hashování řádově 100ms, díky tomu není tak snadné snažit se rozlousknout hesla silou.
• Pokud tedy stavíte novou aplikaci, kde musí být uložena uživatelská hesla, tento způsob je jednoznačně ten, který byste měli zvolit.

1. Bezpečnost databází – ukládání hesel

Při úniku hesel či dalších citlivých dat povinnost informovat patřičné úřady!

1. Bezpečnost databází – SQL injection

• = technika napadení databázové vrstvy programu vsunutím (odtud „injection“) kódu přes neošetřený vstup a vykonání vlastního pozměňujícího poškozujícího SQL příkazu (dotazu DELETE, UPDATE, ALTER atp.)
• Toto nezamýšlené potenciálně nebezpečné chování je umožněno při neošetřeném propojení aplikační vrstvy s databázovou vrstvou.
• V klasickém případě se jedná o útok na internetové stránky prováděný přes neošetřený formulář, manipulací s URL nebo třeba i podstrčením zákeřně upravené cookie.
• Na internetu je stále velké množství webů, spravovaných převážně nezkušenými programátory, kteří o této technice útoku vůbec neví a tuto kritickou chybu opomíjejí.

Zdroj: https://cs.wikipedia.org/wiki/SQL_injection

1. Bezpečnost databází – SQL injection

Zdroj: https://cs.wikipedia.org/wiki/SQL_injection

1. Bezpečnost databází – SQL injection

1. Bezpečnost databází – SQL injection – příklad 1

Zdroj: https://cs.wikipedia.org/wiki/SQL_injection

"SELECT * FROM uzivatele WHERE jmeno = '" + zadaneJmeno + "';"

zadaneJmeno = 'a' or 'b'='b

"SELECT * FROM uzivatele WHERE jmeno = 'a' or 'b'='b';"

Protože 'b' = 'b' je vždy pravda, klientská vrstva aplikace vypíše všechny uživatele (nejen s jménem 'a'), tj. ne stránku vlastností jednoho záznamu se jménem 'a'.

1. Bezpečnost databází – SQL injection – příklad 2

Zdroj: https://cs.wikipedia.org/wiki/SQL_injection

"SELECT * FROM uzivatele WHERE jmeno = '" + zadaneJmeno + "';"

zadaneJmeno = a';DROP TABLE uzivatele; --

"SELECT * FROM uzivatele WHERE jmeno = 'a';DROP TABLE uzivatele; --';"

Tímto by hacker by celou tabulku uživatelů (pokud dotaz proběhne pod oprávněním uživatele s právem mazat databázové objekty)! Poslední apostrof se pomocí sekvence dvou pomlček stane poznámkou a nemá žádný vliv.

​

Podobných průniků je samozřejmě celá řada. Dokonce díky klauzulím UNION a JOIN nejsme ani vázáni na tabulku předepsanou v části FROM a můžeme vypisovat data odkudkoliv z databáze.

1. SQL injection – příklad

1. SQL injection – obrana na straně aplikace

• Parametrizace / prepared statements
• Co: oddělí SQL kód od dat, vstup nikdy není vykonatelný kód.
• Prak.: prepare("SELECT * FROM users WHERE name = ?").execute([$name]);
• Whitelist validace vstupů
• Co: povolíš jen přesně to, co čekáš (např. čísla, e-mail, 8 číslic).
• Prak.: if (!preg_match('/^\d{8}$/', $ico)) reject();
• Typování a délkové limity
• Co: přetypuj nebo omez délku polí — menší prostor pro škodlivý vstup.
• Prak.: $id = (int)$_GET['id']; if (strlen($name)>50) reject();
• Zakázat multi-statements
• Co: nedovolíš v jednom požadavku více SQL příkazů (např. ; DROP ...).
• Prak.: nepoužívej multi_query(); povol multi-statements = false v driveru.
• ORM / query builder
• Co: používej ověřenou knihovnu, která automaticky parametrizuje dotazy.
• Prak.: User::where('email', $email)->first(); (ne ruční string concat)
• Rate limiting, WAF a logging
• Co: blokuje podezřelé opakované pokusy a zaznamenává je pro vyšetření.
• Prak.: nastavit limit 10 požadavků/min na login, zapnout WAF pravidla.

1. SQL injection – obrana na straně DTB

• Least privilege (nejmenší práva)
• Co: účet, kterým aplikace mluví s DB, má jen nutná práva (např. SELECT/INSERT), ne DROP/ALTER.
• Prak.: GRANT SELECT, INSERT ON appdb.* TO 'appuser'@'app';
• Oddělené účty pro administraci
• Co: změny schématu a zálohy dělá jen admin účet, ne aplikace.
• Prak.: admins používají samostatný login s vyššími právy.
• Disable dangerous features / nastavení DB
• Co: vypnout nebo omezit funkce, které mohou spouštět nebezpečný kód (multi-statements, xp_cmdshell...).
• Prak.: v MySQL mysqli->multi_query nepovolovat; v MSSQL vypnout xp_cmdshell.
• Auditing / DB logy + alerty
• Co: logovat podezřelé DDL/DML operace a posílat alarmy.
• Prak.: zapnout audit log nebo triggery, které hlásí DROP/ALTER.
• Zálohy a otestované obnovení
• Co: pokud se něco zničí, máš čerstvou zálohu a ověřený postup obnovy.
• Prak.: denní záloha + obnovení test měsíc/kvartál.

1. SQL injection – obrana - příklad

Jak bar (praktická obrana) postupuje — a co to znamená v kódu:

​

1. Uzavřená, zapečetěná láhev (nikdo nemůže nic přidat)

→ Prepared statements / parametrizace (aplikace odděluje data od příkazu).

​

• Barman kontroluje původ ingrediencí (nebere cizí kostky)

→ Validace / whitelist (povolit jen očekávané formáty; např. IČO = 8 čísel).

​

• Barman má omezené pravomoci (smí používat jen označené suroviny z vybraného regálu)

→ Least privilege (DB účet aplikace nemá právo DROP TABLE).

​

• CCTV + pozorný personál (sledují podezřelé chování)

→ Logging + WAF / monitoring (detekce opakovaných škodlivých vzorů).

​

• Pokud se stane otrava, rychlé omezení zásob (izolace)

→ Rychlá revokace klíčů / změna přístupů, obnova ze zálohy.

1. Bezpečnost databází – obecně

• Silná hesla
• Zálohování, duplikace
• Logování
• Přístupová práva
• Šifrovaný přenos, šifrované uložení
• Aktualizace (OS, firewall atd.)
• Transakce
• Záložní zdroje energie
• Integrita dat

Zdroj: http://uhk.mikmik.cz/1/obdai.pdf

2. Schématický zápis

Tabulka

Název tabulky

Jednotlivé atributy

Primární klíč

Cizí klíč

2. Schématický zápis

Tabulka

Název tabulky

Jednotlivé atributy

Primární klíč

Cizí klíč

2. Schématický zápis

Tabulka

Název tabulky

Jednotlivé atributy

Primární klíč

Cizí klíč

2. Schématický zápis - drawsql.app

3. Datové typy

Datový typ definuje v programování druh nebo význam hodnot, kterých smí nabývat proměnná (nebo konstanta). Datový typ je určen oborem hodnot a zároveň výpočetními operacemi, které lze s hodnotami tohoto typu provádět.

3. Datové typy - význam a přínos

Proč vůbec datové typy existují?

​

📦 Říkají databázi, co přesně očekáváme – číslo, text, datum, logickou hodnotu...

🧮 Umožňují správné výpočty a porovnávání – s číslem můžeš sčítat, s textem ne.

🧱 Pomáhají hlídat chyby – databáze odmítne uložit text tam, kde má být číslo.

⚡ Šetří místo a zrychlují práci – číslo zabere méně paměti než text „12345“.

🔒 Zvyšují bezpečnost a spolehlivost – data jsou přesně taková, jaká mají být.

🔍 Zlepšují vyhledávání a indexy – porovnání čísel nebo dat je rychlejší než textů.

3. Datové typy - čísla

Zdroj: http://books.fs.vsb.cz/sqlreference/sadovski/sql-prvn.htm + https://www.journaldev.com/16774/sql-data-types

3. Datové typy - serial

SERIAL je speciální číselný datový typ, který databáze automaticky zvyšuje o 1 při každém novém záznamu. Používá se nejčastěji pro primární klíče (unikátní ID každého řádku).

3. Datové typy – textové řetězce

Zdroj: http://books.fs.vsb.cz/sqlreference/sadovski/sql-prvn.htm + https://www.journaldev.com/16774/sql-data-types

3. Datové typy – datum a čas

Zdroj: https://www.journaldev.com/16774/sql-data-types

3. Datové typy – ostatní

Zdroj: https://www.journaldev.com/16774/sql-data-types

4. SQL – formátování kódu

Zdroj: https://biportal.cz/sqlprehledne-zformatovany-kod/

4. SQL – formátování kódu

• Pravidla ohledně formátování dokáží značně zjednodušit a zefektivnit práci
• Kód/dotaz je během svého životního cyklu mnohokrát revidován, upravován a analyzován různými lidmi

-> Je velmi časově náročně luštit po někom kód, který je napsán způsobem, že se v něm nelze vůbec vyznat

-> Často je to dokonce tak náročné, že je jednodušší kód napsat znovu = jednotky i desítky hodin práce navíc

Zdroj: https://biportal.cz/sqlprehledne-zformatovany-kod/

1. Jednotlivé atributy SELECT klauzule na samostatném řádku
2. Hlavní klauzule by z hlediska přehlednosti neměly být na stejném řádku
3. Atributy v select a join odsazujeme
4. Aliasy tabulek nepojmenováváme a,b,c,d ale alespoň trošku popisně, ne však příliš dlouze
5. Klauzule, operátory, joiny, funkce píšeme uppercasem
6. Používáme komentáře – samozřejmě v tomto případě jednoduchého dotazu to není nutné, ale pokud skript obsahuje nějakou složitější logiku je více než vhodné tuto transformaci okomentovat.

Praktické cvičení

Vaše farma je spravována farmářem Janem. Ten čelí několika výzvám a potřebuje pomoc se správou zvířat, zaměstnanců a odběratelů. Vy tak v roli analytiků nebo správců databáze můžete pomoci farmáři pomocí SQL dotazů.

Praktické cvičení - 1

Farmář Jan chce zjistit, kolik nových zvířat se narodilo za poslední rok. Potřebuje znát jejich druh a také výběh, ve kterém jsou umístěna.

Praktické cvičení

Farmář Jan chce zjistit, kolik nových zvířat se narodilo za poslední rok. Potřebuje znát jejich druh a také výběh, ve kterém jsou umístěna.

SELECT d."Nazev" AS "Druh", v."Nazev" AS "Vybeh", COUNT(*) as pocet

FROM public.zvirata z

JOIN public.druhy_zvirat d ON z."Typ" = d."ID"

LEFT JOIN public.vybehy v ON z."Vybeh" = v."ID"

WHERE z."Narozeni" > '2023-01-01' GROUP BY d."Nazev", v."Nazev";

​

​

Praktické cvičení - 2

Jan se obává, že některé výběhy mohou být přetížené. Chtěl by zjistit, kolik zvířat je v každém výběhu, a zároveň vidět název výběhu.

Praktické cvičení

Jan se obává, že některé výběhy mohou být přetížené. Chtěl by zjistit, kolik zvířat je v každém výběhu, a zároveň vidět název výběhu.

​

SELECT v."Nazev", COUNT(z."ID") AS "Pocet_zvirat"

FROM public.zvirata z

JOIN public.vybehy v ON z."Vybeh" = v."ID"

GROUP BY v."Nazev";

Praktické cvičení - 3

Jan má zájem zjistit celkovou hodnotu zvířat na farmě na základě jejich druhu. Rád by věděl, jakou hodnotu představují skot, slepice a vepři.

Praktické cvičení

Jan má zájem zjistit celkovou hodnotu zvířat na farmě na základě jejich druhu. Rád by věděl, jakou hodnotu představují skot, slepice a vepři.

​

SELECT d."Nazev", SUM(d."Cena") AS "Celkova_hodnota"

FROM public.zvirata z

JOIN public.druhy_zvirat d ON z."Typ" = d."ID"

GROUP BY d."Nazev";

Praktické cvičení - 4

Farmář chce zkontrolovat, jak daleko jsou od farmy jeho odběratelé a kdo z nich se nachází do 100 km, protože ti dostávají speciální slevu.

Praktické cvičení

Farmář chce zkontrolovat, jak daleko jsou od farmy jeho odběratelé a kdo z nich se nachází do 100 km, protože ti dostávají speciální slevu.

​

SELECT "Nazev", "Vzdalenost"

FROM public.odberatele

WHERE "Vzdalenost" < 100;

Praktické cvičení - 5

Jan plánuje najímat nové zaměstnance, ale potřebuje zkontrolovat, jestli jsou jeho současní zaměstnanci rozděleni rovnoměrně podle měst, kde bydlí. Chce vypsat počet zaměstnanců v každém městě.

Praktické cvičení

Jan plánuje najímat nové zaměstnance, ale potřebuje zkontrolovat, jestli jsou jeho současní zaměstnanci rozděleni rovnoměrně podle měst, kde bydlí. Chce vypsat počet zaměstnanců v každém městě.

​

SELECT m."Nazev", COUNT(z."ID") AS "Pocet_zamestnancu"

FROM public.zamestnanci z

JOIN public.mesta m ON z."mesto" = m."ID"

GROUP BY m."Nazev";

Praktické cvičení - 6

Některá zvířata, která jsou již příliš stará nebo byla prodána, je třeba odstranit z databáze. Jan chce vymazat všechna zvířata narozená před rokem 2019.

Praktické cvičení

Některá zvířata, která jsou již příliš stará nebo byla prodána, je třeba odstranit z databáze. Jan chce vymazat všechna zvířata narozená před rokem 2019.

​

DELETE FROM public.zvirata

WHERE "Narozeni" < '2019-01-01';

Praktické cvičení - 7

Jan si všiml, že někteří odběratelé preferují starší zvířata kvůli kvalitnímu masu. Rád by věděl, která zvířata jsou na farmě nejstarší a ke kterému druhu a výběhu patří.

Praktické cvičení

Jan si všiml, že někteří odběratelé preferují starší zvířata kvůli kvalitnímu masu. Rád by věděl, která zvířata jsou na farmě nejstarší a ke kterému druhu a výběhu patří.

​

SELECT z."ID", d."Nazev" AS "Druh", z."Narozeni", v."Nazev" AS "Vybeh"

FROM public.zvirata z

JOIN public.druhy_zvirat d ON z."Typ" = d."ID"

LEFT JOIN public.vybehy v ON z."Vybeh" = v."ID"

WHERE z."Narozeni" = (

SELECT MIN(z2."Narozeni")

FROM public.zvirata z2

)

ORDER BY z."Narozeni" ASC;

Praktické cvičení - 8

Jan plánuje zavést rychlé dodávky čerstvých produktů. Chce zjistit, který odběratel je nejblíže farmě, aby mohl zahájit spolupráci pro rychlé dodávky.

Praktické cvičení

Jan plánuje zavést rychlé dodávky čerstvých produktů. Chce zjistit, který odběratel je nejblíže farmě, aby mohl zahájit spolupráci pro rychlé dodávky.

​

SELECT "Nazev", "Vzdalenost"

FROM public.odberatele

ORDER BY "Vzdalenost" ASC

LIMIT 1;

Praktické cvičení - 9

Některá zvířata na farmě nemají přidělené výběhy. Jan si toho všiml a chce zjistit, která zvířata výběh postrádají, aby mohl tuto chybu opravit.

Praktické cvičení

Některá zvířata na farmě nemají přidělené výběhy. Jan si toho všiml a chce zjistit, která zvířata výběh postrádají, aby mohl tuto chybu opravit.

​

SELECT z."ID", d."Nazev" AS "Druh", z."Narozeni"

FROM public.zvirata z

JOIN public.druhy_zvirat d ON z."Typ" = d."ID"

WHERE z."Vybeh" IS NULL;

Praktické cvičení - 10

Jan chce vědět, kolik zaměstnanců pracuje na každé pozici na farmě, aby mohl lépe plánovat směny.

Praktické cvičení

Jan chce vědět, kolik zaměstnanců pracuje na každé pozici na farmě, aby mohl lépe plánovat směny.

​

SELECT p."Nazev", COUNT(z."ID") AS "Pocet_zamestnancu"

FROM public.zamestnanci z

JOIN public.pozice p ON z."Pozice" = p."ID"

GROUP BY p."Nazev";

Praktické cvičení - 11

Jan se zajímá o to, jaký je průměrný věk zvířat jednotlivých druhů na farmě. Potřebuje zjistit, jestli některý druh nemá nadměrně stará zvířata.

Praktické cvičení

Jan se zajímá o to, jaký je průměrný věk zvířat jednotlivých druhů na farmě. Potřebuje zjistit, jestli některý druh nemá nadměrně stará zvířata.

​

SELECT d."Nazev", AVG(EXTRACT(YEAR FROM AGE(z."Narozeni"))) AS "Prumerny_vek"

FROM public.zvirata z

JOIN public.druhy_zvirat d ON z."Typ" = d."ID"

GROUP BY d."Nazev";

Praktické cvičení - 12

Jan chce zjistit, který druh zvířat má na farmě největší počet. Potřebuje zjistit, jestli některý druh není na farmě nadměrně zastoupený.

Praktické cvičení

Jan chce zjistit, který druh zvířat má na farmě největší počet. Potřebuje zjistit, jestli některý druh není na farmě nadměrně zastoupený.

​

SELECT d."Nazev", COUNT(z."ID") AS "Pocet_zvirat"

FROM public.zvirata z

JOIN public.druhy_zvirat d ON z."Typ" = d."ID"

GROUP BY d."Nazev"

ORDER BY COUNT(z."ID") DESC

LIMIT 1;

Praktické cvičení - 13

Jan by rád zjistil, kolik nových zvířat se narodilo za poslední dva roky. Potřebuje tento přehled, aby mohl sledovat růst populace na farmě.

Praktické cvičení

Jan by rád zjistil, kolik nových zvířat se narodilo za poslední dva roky. Potřebuje tento přehled, aby mohl sledovat růst populace na farmě.

​

SELECT d."Nazev" AS "Druh", COUNT(z."ID") AS "Pocet_zvirat"

FROM public.zvirata z

JOIN public.druhy_zvirat d ON z."Typ" = d."ID"

WHERE z."Narozeni" > CURRENT_DATE - interval '2 years'

GROUP BY d."Nazev";

Praktické cvičení - 14

Jan si všiml, že ceny všech druhů zvířat jsou podhodnocené, a rozhodl se je zvýšit o 10 %. Potřebuje, aby byla tato změna provedena v databázi.

Praktické cvičení

Jan si všiml, že ceny všech druhů zvířat jsou podhodnocené, a rozhodl se je zvýšit o 10 %. Potřebuje, aby byla tato změna provedena v databázi.

UPDATE public.druhy_zvirat

SET "Cena" = "Cena" * 1.10;

Praktické cvičení - 15

Jan by rád přesunul nejstarší zvířata z výběhu 3 do výběhu 4.

Praktické cvičení

Jan by rád přesunul nejstarší zvířata z výběhu 3 do výběhu 4.

UPDATE public.zvirata

SET "Vybeh" = 4

WHERE "Vybeh" = 3

AND "Narozeni" = (

SELECT MIN(z2."Narozeni")

FROM public.zvirata z2

WHERE z2."Vybeh" = 3

);

Na doma

Vypracujte a nahrajte do Moodle schéma své databáze pro semestrální projekt! Příští lekci proběhne kontrola.

Děkuji za pozornost a těším se příště!��Prostor pro zpětnou vazbu

mail@mjakl.cz

Tel. 608 544 839

Web: mjakl.cz

​