Lekce 3 - Databáze v C++ a Qt - Základy Qt SQL

V minulé lekci, Databáze v C++ a Qt - Připojení k databázi a nová tabulka, jsme si v C++ a Qt připravili projekt s SQLite databází.

Dnes do ní konečně vložíme data a naučíme se provádět základní databázové operace. Čeká nás toho opravdu hodně, tak pojďme na věc

Vložení dat

Prázdná tabulka je samozřejmě o ničem. Pojďme si do ní vložit nějaká data. K tomu slouží příkaz INSERT INTO.

INSERT INTO

Pro vložení Richarda do databáze bychom použili SQL příkaz:

INSERT INTO people (name) VALUES ('Richard');

Za příkazem INSERT INTO následuje název tabulky, do které se nový řádek chystáme vložit. Dále následuje závorka s výčtem sloupců, které budeme vyplňovat. Dále máme klíčové slovo VALUES (hodnoty) a pak v závorce následují hodnoty ve stejném pořadí jako sloupce, které jsme uvedli dříve.

Vložení více záznamů najednou

My budeme chtít vložit hned několik záznamů najednou, určitě se nespokojíme jen s Richardem. Nejprve se ale podívejme na to, jak to nepůjde:

//...
if (query.exec("INSERT INTO people(name) VALUES('Lev');"
               "INSERT INTO people(name) VALUES('Richard');"
               "INSERT INTO people(name) VALUES('Raduška')"))  {...

Pokud si někdo všímavý říká, že takto se řetězce nevytvářejí, tak má pravdu. Ovšem Qt Creator takto rozděluje dlouhý text na několik řádků a překladač si je spojí do jednoho.

query.exec() opravdu neumí pracovat se složeným příkazem a program skončí s chybou:

Konzolová aplikace
...
ERROR:  "not an error Unable to execute multiple statements at a time"

Budeme tedy zadávat příkazy jednotlivě a abychom byli stručnější, vynecháme testy, zda se příkazy povedly:

query.exec("INSERT INTO people(name) VALUES('Lev')");
query.exec("INSERT INTO people(name) VALUES('Richard')");
query.exec("INSERT INTO people(name) VALUES('Raduška')");

Hotovo, v databázi máme nyní 3 osoby.

Berte v úvahu, že nyní pro testovací účely data ukládáme pouze do paměti. Když tedy program skončí, data se nezachovají a při dalším spuštění se objeví opět ta původní. Proto v aplikaci tento kód vkládající data ponecháme, abychom je tam vždy měli.

Získání všech dat z tabulky

Nastal čas si ukázat způsob, jakým se lze k údajům v tabulce zas dostat. Nejprve si je vypíšeme všechny, což bude nejjednodušší.

SELECT

K získání dat z databáze slouží příkaz SELECT ... FROM ...:

SELECT name FROM people

Jako první zadáváme sloupce, které nás zajímají. Pokud jich je více, oddělili bychom je čárkou. Pokud nás zajímají všechny, můžeme zapsat *, ale to není příliš dobrá praktika, protože nás téměř nikdy nezajímají všechny a každý přenos dat z databáze něco stojí (v našem případě čas, ale pokud bychom databázi hostovali např. na cloudu, tak i peníze). Nás nyní zajímají pouze jména a ne id. Nakonec uvedeme i název tabulky, ze které data čteme, v našem případě people.

value()

Na hodnotu výsledku se následně zeptáme pomocí metody value(). Nás zajímá první sloupec výsledku, proto uvedeme parametr 0:

if (!query.exec("SELECT name FROM people"))
    qWarning() << "ERROR: " << query.lastError().text();

while (query.next()) {
    qDebug() << query.value(0).toString();
}

Výsledky načítáme v cyklu while dokud tam nějaké jsou. Na další výsledek se přesuneme zavoláním next(). Výsledek:

Konzolová aplikace
...
"Lev"
"Richard"
"Raduška"

Získání dat

Na závěr si ještě ukážeme, jak načíst údaje o jednom uživateli podle jeho ID.

WHERE

Použijeme k tomu SQL klauzuli WHERE, kde podmínkou určíme jaké záznamy nás zajímají:

SELECT name FROM people WHERE id = 1

SQL injekce

Samozřejmě můžeme SQL příkaz SELECT včetně parametru id zadat přímo jako řetězec do exec(), vypadalo by to takto:

query.exec("SELECT name FROM people WHERE id = 1");

Tato metoda však nepatří mezi zcela bezpečné. ID totiž typicky pochází z nějaké proměnné, kterou mohl zadat uživatel a kód pak vypadá takto:

query.exec("SELECT name FROM people WHERE id = " + idCoZadalUzivatel);

A kdybychom ji vložili přímo do řetězce, koledujeme si o útok SQL injection. Představte si, že by uživatel jako id zadal 1; DROP TABLE people. Výsledný SQL dotaz by rázem byl:

query.exec("SELECT name FROM people WHERE id = 1; DROP TABLE people");

A teď záleží, zda driver podporuje spouštět více příkazů najednou, pokud ano, máme po databázi, protože příkaz DROP TABLE celou tabulku vymaže. I pokud by ale tato podpora nebyla, útočník si může vypsat třeba uživatelské jméno admina a to zadáním id jako 1 OR admin = 1. Pokud by tabulka měla sloupeček admin, zjistí tím kdo je admin a má polovinu práce hotovou. Záškodností lze udělat celou řadu, ale jejich výčet není předmětem našeho kurzu.

SQL injection je typ útoku, kdy uživatel zadá přes nějaký vstup aplikace SQL kód, který se ve špatně navržené aplikaci poté na databázi opravdu spustí. Šikovný útočník si takto s databázi může dělat v podstatě co chce a např. nám data smazat nebo se přihlásit za administrátora a získat přístup k citlivým datům. Do SQL řetězců proto nikdy nevkládáme proměnné spojováním řetězců!!!

Prepared statements

Nyní si ukážeme, jak do SQL dotazu vložit parametr správně. Do příkazu si prvně připravíme značky v podobě otazníků ? a necháme databázi, aby na tato místa sama parametry dotazu vložila. Databáze si pak sama ošetří dané hodnoty a i kdyby obsahovaly nějaký SQL kód, bude brán jako obyčejný text. Tato možnost vám také umožní si SQL dotazy nachystat v obecné formě a ve vhodný čas je použít.

Hodnoty, které se mají použít místo otazníků, předáváme pomocí metody addBindValue():

query.prepare("SELECT name FROM people WHERE id = ?");
query.addBindValue(1);

Dotaz máme nyní připravený, ale ještě jej musíme vykonat. To uděláme zas pomocí metody exec():

// Vykonat dotaz a zároveň test na chybu
if(!query.exec()) {
    qWarning() << "ERROR: " << query.lastError().text();
}
// Přečíst první záznam, tedy pokud existuje
if (query.first()) {
   qDebug() << query.value(0).toString();
}

A výsledek:

Konzolová aplikace
SQLite is ok
Databázi se zdařilo otevřít
Tabulka vytvořena
"Lev"

Odstranění dat

Poslední ze 4 základních operací s daty je jejich odstranění. Stačí smazat jeden záznam nebo vše? Zkusíme si obojí. Nejprve odstraníme osobu podle jejího id.

DELETE FROM

SQL příkaz na odstranění záznamů je DELETE FROM ..., následovaný názvem tabulky a klauzulí WHERE s podmínkou určující jaké záznamy chceme vymazat:

query.prepare("DELETE FROM people WHERE id = ?");
query.addBindValue(1);

A pokud vás celý obsah tabulky již nebaví, můžete použít:

query.exec("DELETE FROM people");

Zde pozor. Určitě jste si všimli, že když v příkazu DELETE zapomenete na část WHERE, nezpůsobí to chybu, nýbrž dojde k odstranění všech řádků v dané tabulce!

QtSql nabízí dosti možností, jak se s databází vypořádat.

 

Měl jsi s čímkoli problém? Stáhni si vzorovou aplikaci níže a porovnej ji se svým projektem, chybu tak snadno najdeš.