Udostępnienie serwera SQL za pomocą WebAPI - możliwe zabezpieczenia

Witam.
Wpadłem na bardzo głupi pomysł, aby udostępnić dane z MS SQL Server technologiom, które nie mają możliwości bezpośredniego połączenia. Pomysł wziął się z wpisu na forum electronjs - zło, którego używa się (prawie) do wszystkiego.

Ja wiem, że zaraz będzie, że "to niebezpieczne", "sql injection", "tak się nie robi". Wiem. Na razie się tym nie przejmuje. Zamysł jest taki, aby działało to tylko w sieci lokalnej.
adamwoxwozniak/optima_api

Pytania:

1. Jak zabezpieczyć appsettings.json, w którym są wszystkie dane z ConnectionStringami. Zastosować tylko logowanie po NT? Co jeśli API będzie na innym komputerze?
2. Zabezpieczenie samego API. Jak generować token bez loginu i hasła? Zależy mi na "pełnym" dostępie, bez konieczności konfigurowania tego po stronie API. W dniu wdrożenia korzysta z oprogramowania jedna osoba ale za miesiąc dojdą dwie i zależy mi żeby tylko im zainstalować "klienta" i działa.
3. W załączonym githubie jest wstępny projekt. Tam jest też IQueryGenerator. Czy z wykorzystaniem Dappera idzie to inaczej zrobić, aby zapobiec sql injection?

1. Jak zabezpieczyć appsettings.json, w którym są wszystkie dane z ConnectionStringami. Zastosować tylko logowanie po NT? Co jeśli API będzie na innym komputerze?

Chyba najlepiej nie przechowywać tam takich danych w ogóle jeśli to problem. Najlepiej zastosować do tego environment variables. Ewentualnie jakiś serwis z sekretami (np. Azure Key Vault), i jeden environment variable zawierający connection string do tego serwisu.

2. Zabezpieczenie samego API. Jak generować token bez loginu i hasła? Zależy mi na "pełnym" dostępie, bez konieczności konfigurowania tego po stronie API. W dniu wdrożenia korzysta z oprogramowania jedna osoba ale za miesiąc dojdą dwie i zależy mi żeby tylko im zainstalować "klienta" i działa.

Aby generować token nie musisz mieć w zasadzie niczego. Zakładając że ufasz źródłu requesta, po prostu generujesz token który w claimach ma np. ID sesji. Wtedy przy obsłudze requestów weryfikujesz czy możesz obsłużyć daną sesję. W tym przypadku mowa o takich "anonimowych sesjach"- nie wiesz kto konkretne wykonuje operację, ale wiesz że może ponieważ ma ważny token.

3. Czy z wykorzystaniem Dappera idzie to inaczej zrobić, aby zapobiec sql injection?

Nie patrzyłem na kod ale ogólnie jeśli będziesz pozwalał żeby klient wysyłał dowolne zapytanie SQL to bez "ręcznej" sanityzacji (?) się chyba nie obędzie.

AdamWox napisał(a):

2. Zabezpieczenie samego API. Jak generować token bez loginu i hasła? Zależy mi na "pełnym" dostępie, bez konieczności konfigurowania tego po stronie API. W dniu wdrożenia korzysta z oprogramowania jedna osoba ale za miesiąc dojdą dwie i zależy mi żeby tylko im zainstalować "klienta" i działa.>

Możesz zawsze instalować klienta z "wbudowanym" unikatowym kluczem, którego kopię będziesz trzymał na serwerze. Wtedy masz pełną kontrolę kto i jak używa twojego api

Zakładając że ufasz źródłu requesta, po prostu generujesz token który w claimach ma np. ID sesji.

No właśnie jak zabezpieczyć API żeby tylko ufał tym co "nasze". Wygenerować token podczas wdrożenia klienta i zapisać go do pliku? Przecież to tak samo jak z appsettings.json. Jak się znajdzie jakiś mądry to tam zaglądnie. Zapisywanie do bazy też odpada.

Nie patrzyłem na kod ale ogólnie jeśli będziesz pozwalał żeby klient wysyłał dowolne zapytanie SQL to bez "ręcznej" sanityzacji (?) się chyba nie obędzie.

Tak na pewno nie może być

        public string SelectQuery(GetFilter filter)
        {
            if(!string.IsNullOrEmpty(filter.Where))
            {
                if(!string.IsNullOrEmpty(filter.Sort))
                {
                    return $"select {filter.Columns} from CDN.{filter.Table} where {filter.Where} order by {filter.Sort}";
                }
                else
                {
                    return $"select {filter.Columns} from CDN.{filter.Table} where {filter.Where}";
                }
            }
            else
            {
                if (!string.IsNullOrEmpty(filter.Sort))
                {
                    return $"select {filter.Columns} from CDN.{filter.Table} order by {filter.Sort}";
                }
                else
                {
                    return $"select {filter.Columns} from CDN.{filter.Table}";
                }
            }
        }

Myślałem jeszcze czy by nie skorzystać z Build dynamic SQL queries, with confidence

@Ales
No właśnie wszystko wskazuje na to, że innego wyjścia nie ma. Generować klucz, token i na tej podstawie API wie, że tylko to oprogramowanie ma mieć dostęp. Problem jest w zapisie tego klucza. Statycznie w programie byłoby najlepiej.

Z tym budowaniem zapytania na stringu bym uważał, bo w polu where można przemycić "1=1; drop table xyz" i to się bez problemu wykona. Lepiej zdefiniować API "jednozadaniowe" z narzuconym typem danych i komunikacja z serwerem SQL przez EF + linq

Nie chce tak, ponieważ ja nie będę klepał trzech różnych API tylko dlatego, że klient potrzebuje trzech różnych programów do ogarnięcia czegoś czego nie ogarnia ERP. A już tym bardziej nie chce dopisywać jeśli już takowe u kogoś istnieje, bo ma być jedno, uniwersalne. Zwyczajnie wyciągnę sobie odpowiednie dane. Ja sobie zdaje z tego sprawę, że to nie jest dobrze ale ja mam dość specyficzne środowisko, z którym współpracuje i jeśli jestem w stanie coś "poukrywać", zablokować i tylko ja wiem jak się do tego dostać to to wdrożę i w 99% jestem pewny, że nikt tego nie złamie.

Na pewno moim zdaniem powinieneś sobie zrobić usera na tej bazce który będzie miał bardzo ograniczone uprawnienia np. read-only niektórych tabel czy może i nawet niektórych kolumn, i to on powinien wykonywać te cuda.

Osobiście szedłbym w kierunku EF Core + dynamiczne generowanie predykatów i selektów LINQ, a odpowiedzialność za zapobieganie SQL Injection bym przerzucił na EF Core.

Pisałem o tym

tutaj

i

tutaj

Jak ci bardzo zależy na czystym SQL i bezpieczeństwie, to poszedł w kierunki jakichś whitelist np. z żądanych kolumn zwracał tylko te, które są na white liście, tak samo z tabelami, a z wyrażeniami już o wiele trudniej ;) no chyba że byłoby tylko kilka dostępnych wyrażeń i jedynie argument by się zmieniał

Ten user wcale nie jest złym pomysłem. Operowałby w obrębie określonych z góry tabel i nigdzie więcej by się nikt nie wcisnął. Co do EF Core to niestety odpada. Operować muszę na istniejącej bazie. A jak ostatnio korzystałem z EF Core to nie można było robić "surowych" zapytań - dbContext.FromRawSql(), czy coś takiego było w .NET Framework. Musi być tabela (po waszemu to chyba encja) - dbContext.Documents.FromRawSql(). No i nie mam zamiaru klepać tych wszystkich klas jak nie potrzebuje. Modele wtedy będą potrzebne tylko po stronie klienta, bo tu mi przyleci czysty json z nazwami kolumn. Co lepiej, mając model filtrowania jaki jest w projekcie mogę sobie nazywać kolumny jak chce i po stronie klienta mogę mieć czysto i schludnie, a nie to bagno comarchu.

{
    "columns": "TrN_TrNId [Id], TrN_NumerPelny [Number], TrN_OdbNazwa [CustomerName]",
    "table": "TraNag",
    "where": "TrN_TypDokumentu = 302 and TrN_Korekta = 0",
    "sort": "TrN_TrNId desc"
}

Jeśli chodzi o wyjątki, błędy to to byłyby takie same jakie SQL by rzucił. Opakować w try i catch, zwrócić odpowiedni IActionResult i finito. Minusem jest sql injection i to, że jak się ktoś zorientuje jak API działa i że w ogóle takie API jest to ma "pełny" dostęp do bazy danych, bo user do sql servera będzie w konfiguracji samego API. Na tę chwilę tylko tym się martwię. Reszta wyjdzie w praniu. Mam luźne dni to mogę kombinować.

Ale wiesz, że istnieją już takie projekty i to zrobione z głową i możliwością konfiguracji security np Spring Data REST ?

#EDIT nie doczytałem, że jest to w dziale .NET xD

Do autoryzacji możesz używać Authorize w C#

https://docs.microsoft.com/pl-pl/aspnet/core/security/authorization/roles?view=aspnetcore-5.0

Generalnie jest cały standard open api do autoryzacji itd.

https://oauth.net/2/

https://swagger.io/specification/

Co do SQL'a to jeżeli to mają być tylko selecty robione to robisz użytkownika na bazie danych SQL tylko z uprawnieniami select i zawsze jakieś to jest zabezpieczenie + dobra praktyka mówi że same Query powinno generować się na podstawie add.property

https://docs.microsoft.com/pl-pl/dotnet/api/system.data.sqlclient.sqlcommand.parameters?view=dotnet-plat-ext-5.0

Według mnie ten problem jest niestandardowy. Metodami "out of the box" chyba nie uda się tej sprawy rozwiązać. Authorize i Roles musi być gdzieś zdefiniowane, ja nie mam gdzie. Czy OAuth przypadkiem nie jest standardem do logowania za pomocą innych serwisów? W jaki sposób miałoby to działać u mnie?

Gdziekolwiek nie wrzucisz danych zamiast appsettings.json to każdy kto będzie chciał to je w jakiś sposób wyciągnie.
Przed kim się chcesz zabezpieczyć?
Postaw api na maszynie do której klienci nie będą mieć dostępu. Jeśli nie chcesz jakiejś bardzo podstawowej autentykacji to wygeneruj sobie listę tokenów, każdemu klientowi przypisz osobny token oraz określ adres IP z jakiego może się łączyć z api i chyba tyle wystarczy.

nie wiem na ile to będzie pomocne
ale jest takie narzędzie do przechowywania sekertów przeróżnych

https://www.vaultproject.io/docs/secrets

@var:
Wstępnie przed nikim. Ogólnie skoro my obsługujemy informatycznie firmę to taka firma swojego informatyka nie ma. Są firmy które pracują na serwerze, poprzez RDP, a są firmy, które nie wiedzą, że taka maszyna istnieje i to wszystko działa w magiczny sposób u nich na komputerach. Jeśli jest problem to dzwonią do nas. Staram się to zabezpieczyć w jakiś minimalny sposób. Patrząc na to jak "hakują" Facebooki, WhatsAppy, zaszyfrowali Acera to, aż takim fanatykiem bezpieczeństwa nie jestem. Próbuje tylko spowolnić dojście do celu ciekawskim. Sam jak jeszcze nie pracowałem w branży grzebałem w miejscach, w których nie powinienem.

@AdamWox: Wypowiem się tylko w kwestii zabezpieczeń, ponieważ coś podobnego ostatnio ogarniałem (również środowisko ERP Comarchu). Miało być prosto z punktu zarządzania dostępem.
Żadnych OAuth, AD, zewnętrznych dostawców, tożsamości użytkowników itd. Więc zrealizowałem to tak, że stworzyłem tabele, która przechowuje API Key, nazwę systemu (u Ciebie może być to nazwa klienta, komputera, dla którego został wydany klucz), czy jest 'enabled' itp.
Następnie w Web API dodałem własną warstwę uwierzytelniania opartą o przesyłany w nagłówku klucz:

public class ApiKeyAuthenticationHandler : AuthenticationHandler<ApiKeyAuthenticationOptions>
{
    protected override async Task<AuthenticateResult> HandleAuthenticateAsync()
    {
          // tutaj pobranie api key z nagłówka, weryfikacja w DB itd.
         // jeśli jest coś nie tak zwracasz
         return AuthenticateResult.Fail("brak nagłówka autoryzacjnego itp");

        // jeśli jest ok tworzysz identity, principal'a i ticket
        return AuthenticateResult.Success(ticket);

    }
}

Kilka uwag:

1. Komunikacja do API tylko po https
2. Za rotowanie kluczy odpowiedzialny jest admin. Jeśli uzna, że klucz został wykradziony, udostępniają sobie nawzajem itd. może go po prostu wywalić i apka przestaje działać
3. W appsettings nie przechowujesz connection string do DB tylko API Key Jest to nadal problem. Ale mniejszy :)
4. Generowanie tokenu możesz ogarnać zwykłą funkcją SQL NEWID()

Są firmy, środowiska, które ciężko przekonać do integracji swoich rozwiązań z AD czy czymś z zewnątrz. Zostały z infrastrukturą 20 lat temu i jakoś trzeba se radzić :)