X36WWW Přístup k databázi Martin Klíma
Tvorba Webu 2 1
DATABÁZE ÚVOD
Tvorba Webu 2 2
Relační databáze - pojmy
Databázový systém – program pro práci a správu DB Databáze – souhrn datových struktur obsahující data Tabulky – databáze se skládá z tabulek, tabulka má sloupce a řádky Sloupec – popis vlastnosti objektu Řádek, záznam – konkrétní data uložená v tabulce Primární klíč – množina sloupců, které jednoznačně identifikují záznam Relace (podmnožina kartézského součinu)
Tvorba Webu 2 3
Databáze - relace Relace 1:1 ID_Objednavky
Mnozstvi
Produkt
ID_Transakce
ID_Objednavky
Datum
1
10
Okurky
33
1
1.1.2005
2
15
Papriky
34
2
5.1.2005
Relace 1:N ID_Objednavky
Mnozstvi
Produkt
ID_Zakaz nika
ID_Zakaznika
Jmeno
1
10
Okurky
102
102
Novák
2
15
Papriky
102
103
Omáčka
Tvorba Webu 2 4
Databáze - relace Relace M:N ID_reky
Jmeno
ID_Zeme
Nazev
1
Labe
102
CR
2
Vltava
103
Nemecko
Převod na 1:N
ID_reky
Jmeno
ID_reky
ID_Zeme
ID_Zeme
Nazev
1
Labe
1
102
102
CR
2
Vltava
1
103
103
Nemecko
2
102
Tvorba Webu 2 5
Manipulace s databází Jazyk SQL (Simple Query Language) – DDL = Data Definition Language – DML = Data Manipulation Language
DDL
– Create – Alter – Drop
DML – – – –
Tvorba Webu 2 6
Select INSERT Update Delete
Příklad použití databáze
Databáze zboží
Kategorie zboží – Kategorie může mít N podkategorií – Kazdá podkategorie patří do jedné kategorie (strom)
Každé zboží může patřit do více kategorií
Tvorba Webu 2 7
DB - příklad kategorie
zbozi ID
Jmeno
ID
Nazev
Nadkategorie
1
Jablka č. 35
1
Ovoce
null
2
Jablka z. 38
2
Zelenina
null
3
Okurky
103
4
Potraviny null
Cena
zbozi_kategorie ID
IDZbozi
IDKategorie
1
1
1
2
2
1
3
1
4
4
2
4
Tvorba Webu 2 8
MYSQL
Tvorba Webu 2 9
MySQL
Velice úspěšný RDBMS systém Open source Poskytovaný skoro všude Vlastnosti – několik různých engine – rychlé i pomalé – transakční i netransakční – podpora různých kódování – uložené procedury – kninovny, PHP podpora od vznikuk PHP
Tvorba Webu 2 10
Databázové enginy – – – –
MyISAM InnoDB MERGE MEMORY
– – – –
EXAMPLE FEDERATED ARCHIVE NDB
Zjistit aktuálně instalované enginy můžeme takto: SHOW ENGINES\G
Tvorba Webu 2 11
MyISAM Engine
Velmi rychlý Není transakční (proto je také rychlý) Nepodporuje ref. integritu – Syntakticky ano, fakticky ne Každá tabulka je v samostatném souboru – soubor se jmenuje podle jména tabulky
Tvorba Webu 2 12
Innodb engine Přehled vlastností
Plně transakční zpracování – ACID kompatibilní = commit, rollback, zotavení Zamykání záznamů (po řádcích) Podporuje cizí klíče (FOREIGN KEY) Je součástí základní distribuce Rychlý engine, ale ne tak rychlý jako MyISAM GNU GPL License Version 2
Tvorba Webu 2 13
INNODB vlastnosti
Tabulky jsou uloženy ve společném souboru (souborech) a to včetně indexů Tabulky mohou být uloženy ve více souborech i na více různých discích Tabulky mohou být uloženy i separátně, každá v jednom souboru – pozor, chová se to jinak než MyISAM – nelze jen tak kopírovat
Tvorba Webu 2 14
PHP A DATABÁZE
Tvorba Webu 2 15
PHP a DB
PHP má nativní podporu pro většinu existujících DB MySQL v první řadě
Tři způsoby, jak může DB podporovat
6. Nativní podpora pomocí dynamické knihovny – např. mysql.dll 7. Nativní podpora v jádře PHP – je nutné zkompilovat 8. Pomocí ODBC – je nutné mít podporu ODBC
Tvorba Webu 2 16
My se zaměříme na nativní podporu
Naše DB CREATE TABLE `zbozi` ( `ID` int(11) NOT NULL auto_increment, `Nazev` varchar(100) NOT NULL, `Popis` text, `ObrazekURL` varchar(50) default NULL, `Cena` double NOT NULL default '0', PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci; GRANT ALL PRIVILEGES ON x36www.* TO x36www_user@localhost IDENTIFIED BY "x36heslo"; FLUSH PRIVILEGES;
Tvorba Webu 2 17
Nutné kroky při práci s DB 1.
2.
3. 4. 5. 6.
Připojení k databázovému stroji – URL databáze – ověření uživatele Výběr databáze – body 1 a 2 lze sloučit Sestavení a poslání dotazu Čtení resultsetu (pokud ho daný dotaz vrací) Uvolnění resultsetu Uzavření spojení
Pozor!!! Každá operace může skončit chybou, musím na to správně reagovat Tvorba Webu 2 18
Implementace define define define define
("DB_HOST", "localhost"); ("DB_NAME", "x36www"); ("DB_USER", "x36www_user"); ("DB_PASSWD", "x36heslo");
// pokusim se pripojit k DB stroji $link = mysqli_connect(DB_HOST, DB_USER, DB_PASSWD); if (!$link) { echo "Nepodařilo se spojit s DB.
"; echo mysqli_connect_error(); exit(); } // pokusim se vybrat si spravnou databazi $success = mysqli_select_db($link, DB_NAME); if (!$success) { echo "Nepodařilo se přepnout na správnou databázi"; exit(); } Tvorba Webu 2 19
Implementace // sestavim si dotaz $sql = "SELECT * FROM zbozi WHERE zbozi.Cena <=100 ORDER BY zbozi.Cena, zbozi.Nazev"; // provedu dotaz $result = mysqli_query($link, $sql); if ($result) { // iteruj vysledek a vypis ho na obrazovku while ($row = mysqli_fetch_assoc($result)) { echo "\n
"; echo htmlspecialchars($row['Nazev']); echo ": "; echo $row['Cena']; echo "
"; } // uvoni resultset mysqli_free_result($result); } // uzavri spojeni s db mysqli_client_encoding($link); Tvorba Webu 2 20
Co vrací mysqli_query Pro dotazy SELECT, SHOW, DESCRIBE a EXPLAIN vrací mysqli_query() výsledek (objekt s tabulkou dat), který musí být zpracován dalšími funkcemi. Pro UPDATE, INSERT, DELETE, DROP a další dotazy pro manipulaci s DB vrací mysqli_query() hodnotu typu Boolean.
Tvorba Webu 2 21
Zpracování výsledku z mysqli_query Mnoho funkcí na zpracování $result mysqli_fetch_array — Fetch a result row as an associative, a numeric array, or both mysqli_fetch_assoc — Fetch a result row as an associative array mysqli_fetch_field_direct — Fetch meta-data for a single field mysqli_fetch_field — Returns the next field in the result set mysqli_fetch_fields — Returns an array of objects representing the fields in a result set mysqli_fetch_lengths — Returns the lengths of the columns of the current row in the result set mysqli_fetch_object — Returns the current row of a result set as an object mysqli_fetch_row — Get a result row as an enumerated array
Tvorba Webu 2 22
SQL injection Často sestavujeme dotaz z parametru zadanych uživatelem. Musíme počítat s tím, že uživatel nemusí zadat "pěkná" data. Uživatel může zničit sql dotaz. Uživatel může pozměnit dotaz tak, že vrátí jiny výsledek.
Tvorba Webu 2 23
SQL Injection zkuste url: sql_injection.php?max=aaa // sestavim sql dotaz z $_GET parametru // predpokladam, ze uzivatel ve formulari zadal max cenu zbozi // parametr max // spatne sestaveny sql dotaz $sql_spatne = "SELECT * FROM zbozi WHERE Cena <= ". $_GET['max']." ORDER BY Nazev"; $cena_max = intval($_GET['max']); $sql_spravne = "SELECT * FROM zbozi WHERE Cena <= ". $cena_max." ORDER BY Nazev"; echo echo echo echo
htmlspecialchars("Spatne: ".$sql_spatne); "
"; htmlspecialchars("Spravne: ".$sql_spravne); "
"; Tvorba Webu 2 24
SQL Injection zkuste URL: sql_injection.php?search=dd'%20OR%20true%20OR%20Nazev%20like %20'% // predpokladam, ze uzivatel zadal vyhledavaci retezec nazvu // param search // rekneme, ze v systemu jsou dve role: 1: admin, 2: obyc uzivak $sql_spatne2 = "SELECT * FROM zbozi WHERE Priv = 2 AND Nazev like '%".$_GET['search']."'"; $search = addslashes($_GET['search']); $sql_spravne2 = "SELECT * FROM zbozi WHERE Priv = 2 AND Nazev like '%".$search."'"; echo htmlspecialchars("Spatne 2: ".$sql_spatne2); echo "
"; echo htmlspecialchars("Spravne 2: ".$sql_spravne2); Tvorba Webu 2 25
Veškeré skripty musí být ošetřeny na SQL Injection ..je to zápočtová podmínka
Tvorba Webu 2 26
Databáze
PHP podporuje nejrůznější databáze MySQL PosgreSQL Oracle Sybase .....
Při přístupu k databázi je třeba používat db-specific funkcí Obtížná migrace na jiný typ databáze
Tvorba Webu 2 27
Jak to obejít
Udělat jednotné databázové rozhraní Abstraktní vrstva pro všechny databáze
Aplikační logika
DB abstraktní vrstva
MySQL
PosgreSQL
Oracle Tvorba Webu 2 28
Sybase
....
Pear DB package
OO API jednoduchý přechod na jiný typ databáze používá standardní DSN(data source name) pro specifikaci parametrů DB vrací objekt s výsledkem každého dotazu přenositelné chybové hlášky sekvenční a nesekvenční načítání řádků a hromadné načítání načtený řádek může být vrácen jako asociativní pole, pole s číselnými indexy nebo objekt podpora pro omezení počtu řádků podpora transakcí informace o tabulkách ... ... Tvorba Webu 2 29
DB package DB staví na existujících PHP rozšířeních (kompilovaných) Tyto databáze procházejí testy: fbsql, ibase, informix, msql, mssql, mysql, mysqli, oci8, odbc, pgsql, sqlite, sybase. DB je kompatibilní s PHP 4 a PHP 5
Tvorba Webu 2 30
DSN - data source name Před připojením k DB je třeba vytvořit platný DSN obecný tvar:
phptype(dbsyntax)://jmeno:heslo@protokol+host/databázee?volba=hodnota
příklady Připojení k db přes soket mysql://user@unix(/path/to/socket)/pear Připojení k db na nestandardním portu pgsql://user:pass@tcp(localhost:5555)/pear Připojení k SQLite na Unixu s použitím voleb sqlite:////full/unix/path/to/file.db?mode=0666 Připojení k SQLite na Unixu s použitím voleb sqlite:///c:/full/windows/path/to/file.db?mode=0666 Připojení k MySQLi přes SSL mysqli://user:pass@localhost/pear?key=client-key.pem&cert=clientcert.pem Připojení k MS Access někdy vyžaduje admin jako uživatelské jméno odbc(access)://admin@/datasourcename Tvorba Webu 2 31
Metody connect, disconnect Připojuje (odpojuje) se k databázi. Používá DSN a další parametry. 2, 'portability' => DB_PORTABILITY_ALL ); $db =& DB::connect($dsn, $options); if (PEAR::isError($db)) { die($db->getMessage()); } // ... $db->disconnect(); ?> Tvorba Webu 2 32
Někomu je milejší specifikovat DSN jako pole 'pgsql', 'username' => 'someuser', 'password' => 'apasswd', 'hostspec' => 'localhost', 'database' => 'thedb' ); $options = array( 'debug' => 2, 'portability' => DB_PORTABILITY_ALL ); $db =& DB::connect($dsn, $options); if (PEAR::isError($db)) { die($db->getMessage()); } ?> Tvorba Webu 2 33
Metoda query Odesílá dotaz do db a vrací výsledek Může být parametrizována
require_once 'DB.php';
require_once 'DB.php'; $db =& DB::connect('pgsql://usr:pw@localhost/dbnam');
if (PEAR::isError($db)) {
$db =& DB::connect('pgsql://usr:pw@localhost/dbnam');
$sql = 'select * from clients where clientid = ?'; $data = 53; $res =& $db->query($sql, $data);
die($db->getMessage());
if (PEAR::isError($res)) {
}
die($res->getMessage());
$res =& $db->query('SELECT * FROM clients'); if (PEAR::isError($res)) {
} ?>
die($res->getMessage()); } ?>
Proveď parametrizovan dotaz a vrať ho do proměnné $res
Proveď dotaz a vrať ho do proměnné $res Tvorba Webu 2 34
Načítání výsledků
Tři typy datových struktur DB_FETCHMODE_ORDERED (default) Array ( [0] => 28 [1] => hi )
DB_FETCHMODE_ASSOC Array ( [a] => 28 [b] => hi ) DB_FETCHMODE_OBJECT stdClass Object ( [a] => 28 [b] => hi ) Tvorba Webu 2 35
Příklad načtení výsledků query('SELECT * FROM users'); while ($res->fetchInto($row, DB_FETCHMODE_ASSOC)) { echo $row['id'] . "\n"; } ?>
Tvorba Webu 2 36
Hlavní metody – prepare, execute prepare, execute Problém rozdílné syntaxe u různých databází. Příklad: db1: INSERT INTO tbl_name (col1, col2) VALUES (expr1, expr2) db2: INSERT INTO tbl_name SET col1=expr1, col2=expr2
$statement['db1']['INSERT_PERSON'] = 'INSERT INTO person (surname, name, age) VALUES (?, ?, ?)'; $statement['db2']['INSERT_PERSON'] = 'INSERT INTO person SET surname=?, name=?, age=?'; ?>
Tvorba Webu 2 37
Metody prepare, execute Před vykonáním dotazu je tento nejprve zpracován metodou prepare. prepare('INSERT INTO numbers VALUES (?, ?, ?)'); $data = array(1, 'one', 'en'); $db->execute($sth, $data); ?> sestaví a provede dotaz: INSERT INTO numbers VALUES (1, ‘one’, ‘en’)
Tvorba Webu 2 38
Metoda executeMultiple prepare('INSERT INTO numbers VALUES (?, ?, ?)'); foreach ($alldata as $row) {
$db->execute($sth, $row);
}
Zbytečná práce
$db->executeMultiple($sth, $alldata);
?>
Tvorba Webu 2 39
Metody autoPrepare, autoExecute
Snaha oprostit se od psaní UPDATE a INSERT dotazů Jejich syntaxe se navíc liší pro různé databáze Nechme tyto dotazy vygenerovat automaticky Chci postupovat stejným parametrickým způsobem jako pro prepare, execute
Tvorba Webu 2 40
Metoda autoPrepare INSERT INTO table (id, name, country) VALUES (?, ?, ?)
Vytvoř INSERT dotaz s těmito parametry.
autoPrepare($table_name, $table_fields,
$db-> DB_AUTOQUERY_INSERT);
if (PEAR::isError($sth)) { die($sth->getMessage()); } ?>
Tvorba Webu 2 41
Dotaz je vytvořen pro konkrétní DB (instance v proměnné $db
Metody autoPrepare UPDATE table SET id=?, name=?, country=? WHERE ... autoPrepare($table_name, $table_fields, DB_AUTOQUERY_UPDATE, 'id = 1'); if (PEAR::isError($sth)) { die($sth->getMessage()); } $res =& $db->execute($sth, $table_values); if (PEAR::isError($res)) { die($res->getMessage()); } ?>
Tvorba Webu 2 42
Vytvoř UPDATE dotaz s těmito parametry.
Dotaz je vytvořen pro konkrétní DB (instance v proměnné $db
Metoda autoExecute Ještě větší zjednodušení. Kombinuje metody autoPrepare a execute 'Fabien', 'country' => 'France' ); $res = $db->autoExecute($table_name, $fields_values, DB_AUTOQUERY_UPDATE, 'id = 1'); if (PEAR::isError($res)) { die($res->getMessage()); } ?>
Tvorba Webu 2 43
Vše v jednom, vytvoří dotaz a provede ho
Děkuji za pozornost
MARTIN KLÍMA,
[email protected] Tvorba Webu 2 44
