Cześć,
Czy jest jakiś prosty sposób (np. jakaś wbudowana funkcja), aby ustalić czy dane zapytanie SQL ma poprawną składnię a jeżeli nie to jakiego typu zawiera błąd, ale bez wywoływania tego zapytania? Chodzi mi głównie o PostgreSQL.
0
4
4
Po prostu spreparuj to zapytanie, to się dowiesz czy jest poprawne.
To się zazwyczaj robi po stronie klienta (aplikacji), a nie napisałeś w czym piszesz.
Ale można i po stronie serwera:
https://www.postgresql.org/docs/current/sql-prepare.html
1
@Kofcio: SELECT * FROM FOOBARBLABLABLA - zapytanie jest składniowo poprawne, ale czy dla Ciebie jest również poprawne? (FOOBARBLABLABLA może nie istnieć, możesz nie mieć uprawnień select). Czy metoda ma weryfikować poprawność składniową, czy coś więcej?
2
Może transakcja bez commitowania z rollbackiem?
1
wloochacz napisał(a):
Po prostu spreparuj to zapytanie, to się dowiesz czy jest poprawne.
To się zazwyczaj robi po stronie klienta (aplikacji), a nie napisałeś w czym piszesz.
Jak mowa o preparowaniu, użyłbym nieistniejących wartości parametrów, aby robiła "skutek zero"
W ogóle selecty się względnie łatwo sprawdza, dać jakiś limit i kilka sekund CPU
Dużo niewiadomych w twoim pytaniu, np kto miałby być beneficjentem tego
a) enduser (power enduser, np piszący podręczne raporty księgowy), w tym, co podniósł @yarel, na konkretnym loginie i uprawnieniach
b) wdrożeniowiec (semi-developer w terenie, na produkcji)
c) developer na środowisku developerskim
W konsekwencji odpowiedź byłaby zróżnicowana, c) jest niewyobrażalne dla a), b) nie te same skile jak c)
Np kwerenda z argumentami pod preparowanie
Nie powiedziałeś też jasne, czy selekty ("zapytanie SQL" by na to wskazywało, ale nasz język jest tu nieścisły), czy aktualizacje (niewyobrażalne dla a) i z migającym czerwonym dla b)
Kolejne: czy "wykańczanie" kwerendy w parametry będzie przez sklejanie stringów (select ... where name = + 'O'Hara'), czy argumenty, a to tez wpływa na wysadzenie w powietrze (uczynienie martwą a nawet szkodliwą) nie całkiem złej kwerendy
I jeszcze: poprawna formalnie, ale śmiertelnie niewydajna - żarówki w serwerowni gasną
0
Dziękuję bardzo wszystkim za udział w dyskusji. Faktycznie mało informacji podałem więc już tłumaczę o co chodzi:
Piszę sobie apkę webową (w blazorze). Wszystkie bazy danych będą na moim serwerze i nikt nie będzie miał do nich bezpośredniego dostępu.
Celem Apki będzie m.in. wyświetlanie danych z programu księgowego (taki pulpit managera).
Każda firma podłączona pod platformę ma swoją niezależną bazę danych.
Na stronie będą dwa podstawowe typy użytkowników: Admin oraz zwykły User. Zarówno Admini jak User'y będą mogli mieć dostęp do wszystkich lub tylko do wybranych baz (ale raczej będą to wybrane bazy). Czyli Admin może mieć dostęp do firmy A, ale nie będzie miał dostępu do firmy B (która będzie w oddzielnej bazie danych).
Admin sam wybiera które kolumny z których tabel mają być udostępniane User-om. W każdej bazie danych może to być inny zestaw danych.
Jednak Admin nie ma bezpośredniego dostępu do bazy danych. On może jedynie napisać odpowiednie zapytania SQL w swoim panelu administracyjnym i to zapytanie będzie następnie wykonywane przez API z odpowiednimi filtrami.
Dlatego, przed zapisaniem takiego zapytania SQL muszę je zwalidować (ustalić, czy nie jest wadliwe).
Oczywiście API będzie się łączyć z bazą danych na użytkowniku, który będzie miał prawa tylko do tej bazy danych do której dostęp ma User - dlatego zakładam, że Admin, który ma dostęp do bazy A nie wydobędzie swoim SELECT-em danych z bazy B :).
Dla pewności: Mam bazę danych firmy A i bazę firmy B -> dla każdej bazy tworzę użytkownika / rolę UserA, który ma dostęp tylko do firmy A oraz UserB, który ma dostęp do firmy B. Jeśli ktoś jest zalogowany do firmy A i prosi API o dane to API loguje się do bazy danych na użytkowniku UserA.
Jest jeszcze kilka sytuacji, gdzie Admin będzie mógł pisać swoje zapytania, ale ogólny problem jest ten sam: Admin nie ma bezpośredniego dostępu do bazy danych a przed zapisaniem odpowiedniego zapytania SQL muszę je zwalidować. Jeśli zapytanie zawiera błąd składniowy to chciałbym wyświetlić Adminowi jak najwięcej szczegółów, aby mógł łatwo zlokalizować gdzie jest problem i go poprawić.
0
- Wyczuwam niezbyt zdrowy pomysł:
zestawy tabel są inne w bazach - a jednak wiara we wspólne sterowanie ?
Kupę ścieżki zawodowej mam związane z "customizacją bazy". Czasem idzie to metodą odmiennych tabel, ale to "one way ticekt", myślenie żegna się ze wspólnym mianownikiem
Żaden poważny producent programu o targecie > 3 klientów nie pójdzie na to - choć do własnie 3, albo 1 (i pół ewe przyszłego)
Bardzo duzo "customizacji bazy" można uzyskać na DOBREJ stabilnej strukturze tabel. A jeszcze piszesz o systemie ksiegowym ... toż o czasów Fenicjan nic sie nie zmieniło.
2
Systemy tej klasy - jeśli wprowadzają zróżnicowanie - nie pozstawiaja tematu "sprawdz sobie tabele w SQLu", ale maja jakie repozytorium, czy inną ()w szerokim sensie) bazę wiedzy o strukturze
3
W dostatecznei złożonym wspóczesnym systemie (innym niż na jednego klienta i hojny dział IT-manów) chyba niemal wcale się róznicuje prawami na tabelach. Np awykonalne jest "widzieć oceny tylko swoich klientów / swojego oddziału" a nie innych handlowców / innych oddzialow. Albo "dostać opiekę nad klientem na czas choroby kolegi"
To sie pilnuje na serwerze aplikacyjnym
Mam mocne wrażenie tłuczesz sie z konsekwencjami nieoptymalnych decyzji projektowym (nazwij XY problem)
0
Krótko mówiąc, istnieje tylko jeden prawidłowy sposób sprawdzenia zapytania sql bez jego uruchamiania - czy zawiera ono przynajmniej jedno z głównych słów kluczowych (SELECT, UPDATE, INSERT, DELETE). W zależności od dialektu sql może zaistnieć potrzeba sprawdzenia dodatkowego słowa kluczowego FROM, ale nie wszystkie typy baz danych wymagają tego jako obowiązkowe (możemy uruchomić „SELECT 123 AS t1” i będzie ono poprawne bez FROM w wielu typach baz danych). Dodatkowo samo posiadanie głównego słowa kluczowego (SELECT, UPDATE, INSERT, DELETE) nie wystarczy, powinien istnieć jakiś inny symbol inny niż spacja, przynajmniej „SELECT 1”. Podsumowując, nie ma możliwości pełnego sprawdzenia poprawności zapytania SQL bez jego uruchomienia. Sugerowany EXPLAIN PLAN lub utworzenie transakcji bez COMMIT jest również uruchomieniem.
0
frz napisał(a):
Krótko mówiąc, istnieje tylko jeden prawidłowy sposób sprawdzenia zapytania sql bez jego uruchamiania - czy zawiera ono przynajmniej jedno z głównych słów kluczowych (SELECT, UPDATE, INSERT, DELETE). W zależności od dialektu sql może zaistnieć potrzeba sprawdzenia dodatkowego słowa kluczowego FROM, ... ciach
Duży optymizm
Twórcy paserów SQL się pocą nie mniej od C++/C. Jezyk nieregularny
(sądzę, ze w pewnych punktach / dialektach i słowo kluczowe nagle może legalnie wystąpić jako nazwa pola / tabeli)
0
@AnyKtokolwiek chyba źle mnie zrozumiałeś :)
Ad 1,2) Nie planuję różnicować baz danych. Chodzi jedynie o wydobywanie danych z tabel. Czyli struktura bazy jest niezmienna - żaden Admin nie może dodać nowej tabeli lub wstawić nowej kolumny, może jedynie wybrać tabele i kolumny z których te dane pochodzą a czasami napisać własnego bardziej skomplikowanego SELECT-a.
Dodatkowo system ma mieć swoje dane oraz dane pochodzące z innych baz danych (innych programów - głównie księgowych). Czyli założenie jest takie, że można sobie podpiąć dowolną bazę a następnie zdefiniować widoki w celach analitycznych.
Dodawanie danych do bazy będzie odbywać się tylko na jasno określonych zasadach (nie zakładam customizacji sposobu wprowadzania danych do systemu).
Ad 3) nie jestem pewny czy dobrze zrozumiałem, ale tak dla pewności: każda firma to jedna lub kilka baz danych do których userzy mają mieć dostęp (oczywiście ograniczony przez Adminów). Użytkownik może mieć dostęp do jednej firmy lub do wielu. Ale jednocześnie user może mieć zakaz wglądu do danych kadrowych, które są dostępne w ramach udostępnionych baz.
Czy moje decyzje projektowe są optymalne czy nie to inna kwestia - tak sobie to wymyśliłem i chcę to zrealizować :).
@frz dzięki, ale chodziło mi o weryfikację składni SQL w kontekście aktualnej struktury bazy danych. Weryfikacja, czy zapytanie zawiera słowo SELECT nic mi nie daje. Dodatkowo na pewno nie ma potrzeby wywołania zapytania aby ustalić czy jest ono prawidłowe (zgodne ze strukturą bazy).
0
Jeśli chodzi tylko o polecenia select, to może opakowywać je do testu w select 1 from (...) t where 1=0. Nie siedzę w PostgreSQL, ale chyba będzie na tyle sensowny, żeby wykonanie tego było błyskawiczne i bez marnowania zasobów. Pozycję ewentualnego błędu będzie łatwo przeliczyć, żeby pasowała do samego zapytania ze środka.
1
Kofcio napisał(a):
@AnyKtokolwiek chyba źle mnie zrozumiałeś :)
Ad 1,2) Nie planuję różnicować baz danych. Chodzi jedynie o wydobywanie danych z tabel. Czyli struktura bazy jest niezmienna - żaden Admin nie może dodać nowej tabeli lub wstawić nowej kolumny, może jedynie wybrać tabele i kolumny z których te dane pochodzą a czasami napisać własnego bardziej skomplikowanego SELECT-a.
Dodatkowo system ma mieć swoje dane oraz dane pochodzące z innych baz danych (innych programów - głównie księgowych). Czyli założenie jest takie, że można sobie podpiąć dowolną bazę a następnie zdefiniować widoki w celach analitycznych.
1 Baza obca mogła dostać upgrade i stać sie niekompatybilną (mało prawodpodbne, ale mozliwe)
- Nad własnym produktem masz jakas wiedzę o tabelach i polach? Warstwa obiektowo - relacyjna, te sprawy ? Czy wszystklo na partizana ?
Typowe kwerendy wyklikiwane ze słowników, a zaawansowane "na własną odpowiedzialność"
3 Syntaktyczna poprawnosc to jest bardzo odległa od "realnej poprawności", np kwerenda syntaktycznie poprawna która zwraca miliard rekordów (drobna pomyła w join). Poświecił bym energię (gdybym robił podobny tool) na profesjonalne zabezpeicznei na runtime.
2
Dlaczego po prostu nie napiszesz w kodzie jakiegoś walidatora, do którego przekażesz zapytanie wpisane przez admina i które zostanie wykonane na bazie i zwrocisz/obsluzysz ewentualny komunikat błędu? Jesli chodzi o operacje modyfikujące dane to bez commitowania transakcji?
Najszybszym sposobem na sprawdzenie poprawności zapytania jest jego wykonanie. Nie widzę powodu dla którego miałbyś pisać jakieś specjalne parsery/analizatory do tego.
0
@AnyKtokolwiek
Ad 1) Z tego co wiem z bazy raczej nie usuwa się kolumn, tylko dodaje się nowe (co do zasady). Ale biorę to pod uwagę, jednak w przypadku SELECT-ów nie powinno się nic strasznego stać - nawet, gdy jakaś kolumna przestanie istnieć, w najgorszym wypadku trzeba będzie zaktualizować zapytanie.
Ad 2) Nie rozumiem pytania
Ad 3) Zgadza się, ale to również biorę pod uwagę. Zakładam jednak, że modyfikacja zapytań nie będzie zbyt powszechna, więc raczej nic się nie stanie, gdy od czasu do czasu ktoś zrobi coś głupiego. Rozważałem również wprowadzenie pewnych limitów na ilość zwracanych rekordów, czas wykonania zapytania a nawet weryfikację duplikacji klucza głównego (w sensie czy zwracany wynik nie ma zdublowanych wartości w kluczu - co sugerowałoby błędne połączenie tabel)
@markone_dev zaczynam się przekonywać, że faktycznie bezpieczniej będzie wywołanie zapytania, które wprowadził użytkownik.
Co do braku commit-owania to skąd taki wniosek?
0
Co do braku commit-owania to skąd taki wniosek?
Jeżeli jesteś pewien że admin systemu za każdym razem testując zapytanie wprowadza poprawne dane to commituj.
0
Commitować SELECT-a? A w jakim celu?
0
Kofcio napisał(a):
Commitować SELECT-a? A w jakim celu?
Czytanie ze zrozumieniem się kłania. Napisałem Jesli chodzi o operacje modyfikujące dane to bez commitowania transakcji? Czy SELECT jest operacją modyfikującą dane?
0
Kofcio napisał(a):
Commitować SELECT-a? A w jakim celu?
jedno z niedopowiedzeń tej akcji / tego projektu. O czym naprawdę mówimy.
Nie tylko tu w tym wątku,choć oczywiście też, ale nigdy sam sobie nie przedstawiłeś jasno czym ten projekt jest. Wyklikajmy bazę i niech się produkt stworzy
W związku z tym sądzę, że jakimiś randomowymi działaniami nad bazą, bez wartsw / podziału logiki / architektury
Czy mowa tylko o raportach (=select), czy w kodzie usera update / insert / a dlaczego bym nie alter ?
0
@AnyKtokolwiek nie do końca zrozumiem co napisałeś.
Update / Insert będzie tylko za pośrednictwem API
Alter będzie robione tylko przeze mnie (w mojej bazie danych) lub przez producenta systemu (jeśli mowa o podłączonej bazie jakiegoś systemu)
User ma mieć prawo tylko wybrać sobie dane, które chce sobie wyświetlać na ekranie (SELECT).