Niezalogowany [ logowanie ]
Subskrybuj kanał ATOM Kanał ATOM    Subskrybuj kanał ATOM dla tagu sql Kanał ATOM (tag: sql)

Autor wpisu: cojack, dodany: 23.05.2010 11:15, tagi: sql

PosgreSQL Poniższy tutorial będzie wprowadzeniem do epizodu tutoriali o języku skryptowym (proceduralnym) PL/pgSQL. Język pl/sql został wymyślony przez Oracle, jego prostota i zarazem potężna siła daje nam wszystko czego potrzeba nam do pracy z postgresql. Język ten pozwala nam na wiele więcej działań niż sam SQL, począwszy od deklaracji zmiennych poprzez pętle skończywszy na triggerach (albo i nie).

Wprowadzenie do PL/pgSQL

Krótki i w miarę logicznie opisanym słowem wstępu jest artykuł na wiki o PL/pgSQL, link poniżej. Lecz ja chciałbym to omówić po swojemu, tak by każdy z Was to zrozumiał i przekonał się nad wielkością tego języka, zaczynajmy. Tak jak nam mówi manual składnia funkcji jest następująca:

CREATE FUNCTION "nazwaFunkcji"( argumenty [, ... ] ) RETURNS [typZwracanegoObiektu] AS $BODY$
  DECLARE -- blok nie obowiązkowy
    -- Możemy w tym bloku definiować zmienne, w "locie" zmiennych definiować nie możemy 
  BEGIN
    -- ciało funkcji
 
    RETURN [zwracanyObiekt] -- klauzula prawie nie obowiązkowa, o tym poniżej
  END;
$BODY$ LANGUAGE 'plpgsql';

Tak mniej więcej przedstawia się schemat konstrukcji funkcji w PL/pgSQL, Pominąłem kwestię bloków w bloku, w sensie że w bloku BEGIN … END możemy zdefiniować kolejny blok kodu. Nigdy mi to nie było jeszcze potrzebne, i wątpię by Wam było, to jest pewnego rodzaju przerost formy nad treścią. Jak widać stosuje tutaj podwójne dolary w sensie $BODY$, można się zapytać dlaczego? Otóż nie musicie stosować podwójnych dolarów zamiast nich możecie zrobić po prostu AS ‘ — i tutaj reszta kodu. Tylko nie wiem czy stać Was na nerwy stracone przy escepowaniu znaków. Otóż przy podwójnych dolarach możemy zachować taką konstrukcję:

SELECT * FROM users WHERE f_name='foobar';

Natomiast przy konstrucji z apostrofem:

SELECT * FROM users WHERE f_name=''foobar'';

Tam nie ma cudzysłowu przy foobar tylko podwójny apostrof. Także używajcie sobie czego chcecie, do woli. A i jeszcze jedno, to co sobie wstawicie pomiędzy podwójne dolarki nie ma znaczenia, ja wyniosłem z pracy nawyk wstawiania tam nazwy funkcji, co mi się osobiście trochę mało podoba, no ale nawyk pozostał. Przedstawiona przeze mnie powyżej konstrukcja nie jest oczywiście odzwierciedleniem tej z manuala, gdyż jest mało jasna, i rzadko się używa np CREATE OR REPLACE gdy co chwilę zmieniamy typy zmiennych przesyłanych w liście argumentów.

Argumenty funkcji PL/pgSQL

Lista argumentów może być podawana dwojako, a nawet trzyjako. Pierwszym z sposobów podawania argumentów do funkcji jest:

CREAT.... "nazwaFunkcji" ( INT, NUMERIC, TEXT, VARCHAR ) .... $BODY$
  DECLARE
    "_nazwaZmiennejInt" ALIAS FOR $1;
    "_nazwaZmiennejNumeric" ALIAS FOR $2;
    "_nazwaZmiennejText" ALIAS FOR $3;
    "_nazwaZmiennejVarchar" ALIAS FOR $4;
  BEGIN
   ....

drugi sposób:

CREAT.... "nazwaFunkcji" ( "_nazwaZmiennejInt" INT, "_nazwaZmiennejNumeric" NUMERIC,  "_nazwaZmiennejText" TEXT, "_nazwaZmiennejVarchar" VARCHAR ) .... $BODY$

trzeci sposób:

CREAT.... "nazwaFunkcji" ( "_nazwaZmiennejInt" INT, OUT "_nazwZwracanejZmiennej" INT, OUT "_nazwaZwracanejZmiennej2" INT ) .... $BODY$

Dzięki tej trzeciej metodzie możemy zwracać więcej niż jeden argument. Możemy też definiować typy zmiennych i też je zwracać o tym zraz.

Zwracane typy

Tak jak wyżej napisałem funkcje PostgreSQL mogą zwracać typy danych które też sami zdefiniujemy, metody mogą zwracać zarówno typ VOID, jeżeli nie potrzebujemy niczego zwrócić to po prostu ustawiamy RETURNS VOID i dzięki takiej konstrukcji klauzula RETURN w ciele kodu jest niewymagana, proste nie? PostgreSQL to nie PHP, źle mu prześlesz typ danych zdefiniowany jako INT a prześlesz jako ‘1′ i będzie to string, w PHP to przejdzie ale w postgre nie. Więc tutaj musimy się pilnować jeżeli chodzi o przesyłanie zmiennych. To samo tyczy się wszystkich typów oraz tych zwracanych, jeżeli w końcowej wersji uzyskamy inny typ niż ten który zdefiniowaliśmy to dostaniemy błędem po oczach próbując przypisać np NUMERIC do INT i w sumie funkcja się nie wykona, nic nie zwróci a weź to debuguj :>

Czytaj dalej tutaj (rozwija treść wpisu)
Czytaj dalej na blogu autora...

Autor wpisu: JoShiMa, dodany: 21.05.2010 02:00, tagi: sql

W przypadku najprostszej implementacji struktury drzewiastej w bazie, jaką opisałam w pierwszym artykule tej serii, przeniesienie całej gałęzi do nowej lokalizacji jest zagadnieniem trywialnym. Wystarczy zmienić wartość parametru ‘parentID’ odpowiedniego rekordu i już. Jeśli chodzi o drzewo typu nested set, nie jest to już takie proste. Przeniesienie gałęzi wymaga przeliczenia parametrów ‘lft’ i ‘rgt’ wielu [...]

Autor wpisu: JoShiMa, dodany: 17.05.2010 01:00, tagi: sql

Skoro pokazałam już jak wygląda struktura drzewa nested set oraz jak wczytać całe drzewo a także jak pobrać wybraną gałąź, pora zająć się dodawaniem rekordów do omawianej struktury. W przypadku najprostszej implementacji struktury drzewiastej w bazie, jaką opisałam w pierwszym artykule tej serii, dodanie kolejnego rekordu jest proste i wymaga wykonania jednego zapytania. Wystarczy tylko aby [...]

Autor wpisu: JoShiMa, dodany: 12.05.2010 01:45, tagi: php, sql

W poprzednich artykułach tej serii pokazałam jak wygląda konstrukcja drzewa typu nested set a także jak wczytać zawartość drzewa tego typu. W tym odcinku pokażę, że wystarczą niewielkie modyfikacje omówionych wcześniej zapytań, by bez trudu odczytać dowolnie wybraną gałąź naszej struktury. Dla przypomnienia Na początek przypomnę tabelę zawierająca strukturę drzewa, którą wykorzystywałam w poprzednich artykułach oraz [...]

Autor wpisu: cojack, dodany: 21.03.2010 18:20, tagi: sql

Ostatnimi czasy coś nie mam weny by napisać cokolwiek na blogu, także postanowiłem tym razem coś może o postgresql napisać i jego własnościach tudzież mowa o widokach w postgresql. Rzecz biorąc czym jest widok? Widok jest niczym innym jak zapytaniem kryjącym się pod krótką nazwą, jak pisałem w wcześniejszej notce o RBAC cz.3 z pewnego zapytania można było utworzyć sobie widok. Może więcej szczegółów technicznych, składnia widoku ma się tak:

CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] VIEW name [ ( column_name [, ...] ) ]
    AS query

Sama składnia może się wydawać prosta choć nie musi ;] składnia podana w nawiasach kwadratowych jest opcjonalna. Prze zemnie rzadko wykorzystywana, bo nie tworzę jakiś tymczasowych widoków, nie zapisuję widoku do innego schematu niż ten w którym się znajduję ani też nie używam OR REPLACE gdyż jak instaluje na nowo to drop db i amba.

Teraz może co do samej składni

OR REPLACE – co dziwnego o tym piszą w manualu, to się można dowiedzieć na stronie podanej poniżej. Ale przytoczę fragment.

CREATE OR REPLACE VIEW is similar, but if a view of the same name already exists, it is replaced. The new query must generate the same columns that were generated by the existing view query (that is, the same column names in the same order and with the same data types), but it may add additional columns to the end of the list. The calculations giving rise to the output columns may be completely different.

I teraz pytanie, po czorta w takim razie OR REPLACE jeżeli zmienimy sobie całkowicie widok a pozostawimy tą samą nazwę, trochę dziwna akcja, chociaż może nas to też z jednej strony zabezpieczać przed nałogowym wstawianiu OR REPLACE i nie daj Bóg strzelimy gdzieś dwa takie same nazwy widoków, hmmm w sumie może i dobrze.

Przykład jakiegoś widoku? Proszę bardzo, z naszego RBAC’a z zapytania sprawdzającego uprawnienia, zróbmy sobie teraz widok:

CREATE VIEW "checkRightView" AS
SELECT 
  "allow",
  "u2g"."idUser",
  "m"."moduleName",
  "a"."actionName"
FROM 
  "rbac_privilages" p 
LEFT JOIN 
  "rbac_module" m 
ON 
  ( p."idModule" = m."idModule" ) 
LEFT JOIN 
  "rbac_action" a 
ON 
  ( p."idAction" = a."idAction" ) 
LEFT JOIN 
  "rbac_user_to_group" u2g 
ON 
  ( p."idGroup" = u2g."idGroup" );

Dodaliśmy tylko nagłówek create view i ucięliśmy klauzule where, a teraz przykład jak takiego widoku użyć:

SELECT 
  "cRV"."allow"
FROM 
  "checkRightView" AS "cRV"  -- prawie jak honda ^^
WHERE 
  "cRV"."idUser" = 2 
AND 
  "cRV"."moduleName" = 'music' 
AND 
  "cRV"."actionName" = 'index';

Tadam, proste prawda? Tworzenie widoków można by porównać do polimorfizmu, lecz nim nie jest. Co nam to daje? Na pewno przejrzyste zapytania sql, co do debugowania to w sql zawsze był z tym problem, dlatego przed utworzeniem widoku pierw lepiej sprawdzić czy zapytanie śmiga.

Co do ograniczeń widoku, wiążą się z tym pewne niedogodności, gdyż jak widać by mieć dostęp do tabel łączonych w zapytaniu, trzeba pobrać ich elementy w samym zapytaniu (w widoku) by mieć do nich dostęp przy wywoływaniu widoku. Cóż jak to uważam za pewne ograniczenia, czy nim jest teoretycznie? Nie mam takiej wiedzy by to stwierdzić.

Czytaj dalej tutaj (rozwija treść wpisu)
Czytaj dalej na blogu autora...

Autor wpisu: stormfly, dodany: 09.03.2010 14:48, tagi: php, sql

Mechanizm bardzo przydatny, umożliwiający przechodzenie do następnego lub poprzedniego rekordu z poziomu wybranego już rekordu. Dzięki temu użytkownik nie musi wracać do listy czy też z poziomu listy otwierać dziesiątek stron w osobnych zakładkach by je wszystkie...

Autor wpisu: Michał Środek, dodany: 13.02.2010 22:33, tagi: php, sql

Ostatnio pracowałem nad pewnym projektem wraz z innym(„troszkę“ mniej doświadczonym) programistą. Starałem się przymykać oko na wiele jego błędów(niepotrzebne zmienne, brak obiektowości itp.) jednak jeden był niewybaczalny — brak filtracji danych przychodzących w zapytaniach SQL. Dzisiaj postaram się wytłumaczyć dlaczego to jest tak bardzo ważne pokazując jak haker w prosty sposób może wykraść loginy i hasła użytkowników ze słabo zabezpieczonej witryny.

Stwórzmy przykładową bazę danych „hack“ aby pokazać jak to wygląda od drugiej strony.

CREATE TABLE articles(
id int(10) AUTO_INCREMENT,
title varchar(80) NOT NULL,
content text NOT NULL,
author int(10),
category int(10),
PRIMARY KEY(id)
);
 
CREATE TABLE users(
id int(10) AUTO_INCREMENT,
login varchar(40) NOT NULL,
email varchar(60) NOT NULL,
password varchar(32) NOT NULL,
PRIMARY KEY(id)
);

Wypełnijmy je danymi:

mysql> select * from articles;
+----+--------------+----------+--------+----------+
| id | title        | content  | author | category |
+----+--------------+----------+--------+----------+
|  1 | Artykul nr 1 | tresc... |      1 |        2 |
|  2 | Artykul nr 2 | tresc... |      3 |        1 |
|  3 | Artykul nr 3 | tresc... |      2 |        1 |
|  4 | Artykul nr 4 | tresc... |      3 |        2 |
+----+--------------+----------+--------+----------+
mysql> select * from users;
+----+--------+--------------------+----------------------------------+
| id | login  | email              | password                         |
+----+--------+--------------------+----------------------------------+
|  1 | Michal | michal@example.com | 1660fe5c81c4ce64a2611494c439e1ba |
|  2 | Magda  | romek@example.com  | 9d0250b24620c2056516e5d2d79eed4a |
|  3 | Romek  | romek@example.com  | 944278ab01f435bfc369fa038130f25b |
+----+--------+--------------------+----------------------------------+

Czas na kod PHP odpowiedzialny za pobieranie artykułów. Pomijam filtrowanie zmiennych przychodzących. Wygląda to mniej więcej tak:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<?php
$mysqli = new mysqli('localhost', 'root', '', 'hack');
 
if(isset($_GET['id']))
  $result = $mysqli->query('SELECT a.*, u.login FROM articles a, users u WHERE a.author=u.id AND a.id='.$_GET['id']);
else
  $result = $mysqli->query('SELECT a.*, u.login FROM articles a, users u WHERE a.author=u.id');
 
while($row = $result->fetch_array())
{
  echo '<h2>'.$row['title'].'</h2>';
  echo '<strong>Author: '.$row['author'].'</strong>';
  echo '<p>'.$row['content'].'</p>';
}
?>

Wystarczy mała nieuwaga aby skrypt był dziurawy jak ser szwajcarski. Co więcej, dziurę taką da się bardzo łatwo wykryć — wystarczy apostrof lub cudzysłów.

http://localhost/hack/sql/?id=1'

Naszym oczom ukaże się błąd:

Fatal error: Call to a member function fetch_array() on a non-object in /home/hellson/public_html/hack/sql/index.php on line 27 

Jest to informacja, że użytkownik może wpływać na treść zapytania SQL. Dlaczego tak się dzieje? PHP bezmyslnie umieszcza zmienną $_GET[’id’] do zapytania SQL które teraz wygląda tak:

SELECT a.*, u.login FROM articles a, users u WHERE a.author=u.id AND a.id=1'

Jest to niepoprawne zapytanie więc php zwraca błąd metody fetch_array(). Tym samym jest to informacja, że haker może spreparować własne zapytania, które zwrócą mu loginy i hasła. Oczywiście w sytuacji gdy nie zna on struktury bazy danych jest to troszkę utrudnione lecz wciąż możliwe. Aby uświadomić o jakie aspekty należy zadbać warto dowiedzieć się jak to robi przeciętny włamywacz.

Od strony hakera

Pierwszym krokiem jest sprawdzenie czy rzeczywiście możemy wpływać na zapytanie. Najlepiej jest dopisać nic nie znaczące „OR 1=1″

http://localhost/hack/sql/?id=1 OR 1=1
SELECT a.*, u.login FROM articles a, users u WHERE a.author=u.id AND a.id=1 OR 1=1

Naszym oczom powinna ukazać się pełna lista artykułów zamiast tylko jednego lub jeśli na stronie moduł pobiera tylko pierwszy rekord otrzymamy inny niż jest sprecyzowany w zmiennej id(oczywiście należy wziąć pod uwagę sortowanie). Kolejnym krokiem jest wprowadzenie własnego zapytania. Najczęściej używa się UNION ponieważ jest to najprostsza technika. UNION połączy 2 zapytania tylko wtedy gdy będą one posiadać taką samą ilość kolumn. Warto więc najpierw to sprawdzić metodą prób i błędów pobierając kolejno 2 NULL-e, 3 NULL-e itd. W naszym przykładzie wystarczy sześć.

Czytaj dalej tutaj (rozwija treść wpisu)
Czytaj dalej na blogu autora...

Wszystkie wpisy należą do ich twórców. PHP.pl nie ponosi odpowiedzialności za treść wpisów.