3. diel - Databázy v C ++ a Qt - Základy Qt SQL

V minulej lekcii, Databázy v C ++ a Qt - Pripojenie k databáze a nová tabuľka , sme si v C ++ a Qt pripravili projekt s SQLite databáz. Dnes do nej konečne vložíme dáta a naučíme sa vykonávať základné databázové operácie. Čaká nás toho naozaj veľa, tak poďme na vec:)

Vloženie dát

Prázdna tabuľka je samozrejme o ničom. Poďme si do nej vložiť nejaké dáta. K tomu slúži príkaz INSERT INTO.

Insert INTO

Pre vloženie Richarda do databázy by sme použili SQL príkaz:

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

Za príkazom INSERT INTO nasleduje názov tabuľky, do ktorej sa nový riadok chystáme vložiť. Ďalej nasleduje zátvorka so zoznamom stĺpcov, ktoré budeme vypĺňať. Ďalej máme kľúčové slovo VALUES (hodnoty) a potom v zátvorke nasledujú hodnoty v rovnakom poradí ako stĺpce, ktoré sme uviedli skôr.

Vloženie viac záznamov naraz

My budeme chcieť vložiť hneď niekoľko záznamov naraz, určite sa neuspokojíme len s Richardom. Najprv sa ale pozrime na to, ako 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')"))  {...

Pokiaľ si niekto všímavý hovorí, že takto sa reťazca nevytvárajú, tak má pravdu. Ovšem Qt Creator takto rozdeľuje dlhý text na niekoľko riadkov a prekladač si ich spojí do jedného.

query.exec() naozaj nevie pracovať so zloženým príkazom a program skončí s chybou:

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

Budeme teda zadávať príkazy jednotlivo a aby sme boli stručnejšie, vynecháme testy, či sa príkazy podarili:

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áze máme teraz 3 osoby.

Berte do úvahy, že teraz pre testovacie účely dáta ukladáme iba do pamäti. Keď teda program skončí, dáta sa nezachovajú a pri ďalšom spustení sa objaví opäť tá pôvodná. Preto v aplikácii tento kód vkladajúce dáta ponecháme, aby sme ich tam vždy mali.

Získanie všetkých dát z tabuľky

Nastal čas si ukázať spôsob, akým sa dá k údajom v tabuľke zas dostať. Najprv si ich vypíšeme všetky, čo bude najjednoduchšie.

Selecta

Na získanie dát z databázy slúžia príkaz SELECT ... FROM ...:

SELECT name FROM people

Ako prvý zadávame stĺpce, ktoré nás zaujímajú. Ak ich je viac, oddelili by sme ich čiarkou. Ak nás zaujímajú všetkých, môžeme zapísať *, ale to nie je príliš dobrá praktika, pretože nás takmer nikdy nezaujímajú všetky a každý prenos dát z databázy niečo stojí (v našom prípade čas, ale ak by sme databázu hosťovali napr. Na cloude, tak i peniaze) . Nás teraz zaujímajú iba mená a nie id. Nakoniec uvedieme aj názov tabuľky, z ktorej dáta čítame, v našom prípade people.

Value ()

Na hodnotu výsledku sa následne opýtame pomocou metódy value(). Nás zaujíma prvá stĺpec výsledku, preto uvedieme parameter 0:

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

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

Výsledky načítame v cykle while kým tam nejaké sú. Na ďalší výsledok sa presunieme zavolaním next(). výsledok:

Konzolová aplikácia
...
"Lev"
"Richard"
"Raduška"

Získanie dát

Na záver si ešte ukážeme, ako načítať údaje o jednom používateľovi podľa jeho ID.

Where

Použijeme na to SQL klauzulu WHERE, kde podmienkou určíme aké záznamy nás zaujímajú:

SELECT name FROM people WHERE id = 1

Sql injekcie

Samozrejme môžeme SQL príkaz SELECT vrátane parametra id zadať priamo ako reťazec do exec(), vyzeralo by to takto:

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

Táto metóda však nepatrí medzi úplne bezpečné. ID totiž typicky pochádza z nejakej premennej, ktorú mohol zadať užívateľ a kód potom vyzerá takto:

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

A keby sme ju vložili priamo do reťazca, koledujeme si o útok SQL injection. Predstavte si, že by užívateľ ako id zadal 1; DROP TABLE people. Výsledný SQL dotaz by razom bol:

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

A teraz záleží, či driver podporuje spúšťať viac príkazov naraz, ak áno, máme po databázu, pretože príkaz DROP TABLE celú tabuľku vymaže. Aj ak by ale táto podpora nebola, útočník si môže vypísať napríklad užívateľské meno admina a to zadaním id ako 1 OR admin = 1. Ak by tabuľka mala stĺpček admin, zistí tým kto je admin a má polovicu práce hotovú. Záškodností možno urobiť celý rad, ale ich výpočet nie je predmetom nášho kurzu.

SQL injection je typ útoku, kedy užívateľ zadá cez nejaký vstup aplikácie SQL kód, ktorý sa v zle navrhnuté aplikácii potom na databázu naozaj spustí. Šikovný útočník si takto s databázu môže robiť v podstate čo chce a napr. Nám dáta zmazať alebo sa prihlásiť za administrátora a získať prístup k citlivým dátam. Do SQL reťazcov preto nikdy nevkladáme premenné spájaním reťazcov !!!

Prepared statements

Teraz si ukážeme, ako do SQL dotazu vložiť parameter správne. Do príkazu si prvýkrát pripravíme značky v podobe otáznikov ? a necháme databázu, aby na tieto miesta sama parametre dotazu vložila. Databáza si potom sama ošetrí danej hodnoty a aj keby obsahovali nejaký SQL kód, bude braný ako obyčajný text. Táto možnosť vám tiež umožní si SQL dotazy nachystať vo všeobecnej forme a vo vhodný čas ich použiť.

Hodnoty, ktoré sa majú použiť namiesto otáznikov, odovzdávame pomocou metódy addBindValue():

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

Otázka máme teraz pripravený, ale ešte ho musíme vykonať. To urobíme zas pomocou metódy 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ýsledok:

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

Odstránenie dát

Posledný zo 4 základných operácií s dátami je ich odstránenie. Stačí zmazať jeden záznam alebo všetko? Skúsime si oboje. Najprv odstránime osobu podľa jej id.

Dele FROM

SQL príkaz na odstránenie záznamov je DELETE FROM ..., nasledovaný názvom tabuľky a klauzulou WHERE s podmienkou určujúci aké záznamy chceme vymazať:

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

A ak vás celý obsah tabuľky už nebaví, môžete použiť:

query.exec("DELETE FROM people");

Tu pozor. Určite ste si všimli, že keď v príkaze DELETE zabudnete na časť WHERE, nespôsobí to chybu, ale dôjde k odstráneniu všetkých riadkov v danej tabuľke!

QtSql ponúka dosť možností, ako sa s databázou vysporiadať. Na ďalšie sa pozrieme nabudúce, v lekcii .

 

Mal si s čímkoľvek problém? Stiahni si vzorovú aplikáciu nižšie a porovnaj ju so svojím projektom, chybu tak ľahko nájdeš.