SQLite Datenbank updates funktionieren nur nach connection reset

[Kokujou]

Hi Leute!

ich benutze eine SQLite Datenbank mit einem C# .NET Core Controller.
Jetzt habe ich aber bemerkt dass immer wenn ich einen Update Call mache, muss ich erst die Verbindung neu starten damit die Änderungen wirksam wäre... da ist irgendein schreckliches Caching im Spiel, dass mir die Arbeit versaut...

kann man das irgendwie ausstellen oder muss ich die Verbindung tatsächlich in jedem Request reinitialisieren?

 

[PHuV]

Ich kenne jetzt SQLite direct nicht, generell macht man aber bei Datenbanken nur einmal den init Request und meldet sich an, und die Session zur Datenbank bleibt bis zum close session immer offen. Ist vielleicht ein wichtiger Parameter am Anfang nicht gesetzt worden? Session Timeout in SQLite zu klein gewählt?

 

[BeBur]

Code?? Google??

 

[Piktogramm]

Geil, Rätsel raten!

Ich setze mal auf, "dass SQL Statement tut nicht" oder das Statement braucht einfach ewig, weil das Update zu viele Einträge anfassen muss.

 

[marcOcram]

Was heißt denn "damit die Änderung wirksam wird"? Wie prüfst du das? Wie machst du das Update? Nutzt du EF (Core)?

 

[Dalek]

Wie gesagt fehlen hier fast alle relevanten Details und der Code selbst. Wenn das ganze mit EF Core passiert würde ich tippen das du den DbContext falsch benutzt. Den DbContext soll man nicht ewig rumschleifen, den fordert man pro Vorgang an und bekommt dann einen neuen.

Ansonsten ist das ein ganz typisches Symptom das du Transaktionen nicht richtig benutzt, für andere Anfragen sind die Änderungen immer erst sichtbar wenn eine Transaktion commited ist.

 

[Hayda Ministral]

Hi Leute!

ich benutze eine SQLite Datenbank mit einem C# .NET Core Controller.
Jetzt habe ich aber bemerkt dass immer wenn ich einen Update Call mache, muss ich erst die Verbindung neu starten damit die Änderungen wirksam wäre... da ist irgendein schreckliches Caching im Spiel, dass mir die Arbeit versaut...

Was sagt denn der Debugger beim Schließen der Verbindung?

 

[Kokujou]

Geil, Rätsel raten!

Ich setze mal auf, "dass SQL Statement tut nicht" oder das Statement braucht einfach ewig, weil das Update zu viele Einträge anfassen muss.

schätze nicht... denn wie gesagt ein restart liefert die daten sofort. ohne restart der IIS application und zurücksetzen der DB Verbindung ist selbst nach 5 minuten noch kein update sichtbar

Was heißt denn "damit die Änderung wirksam wird"? Wie prüfst du das? Wie machst du das Update? Nutzt du EF (Core)?

nein ausnahmsweise nicht. da ich nur sehr primitive Sachen brauche benutze ich tatsächlich nur System.Data.SQLite System.Data.Linq und schreibe tatsächlich SQL Commands rein. ich prüfe das ganz einfach indem ich die daten mit einem GET auf meinen Controller hole nachdem ich sie geupdated habe.

Darum mutmaße ich ja dass die Daten aus irgendeinem Grund immer gecachet werden oder so... denn sobald ich die Verbdingung reinitialisiere kommt das update sofort

ich weiß nicht welche details ihr braucht, aber ich kann ja einfach mal den Code anhängen:

using System;
using System.Collections.Generic;
using System.Data.Linq;
using System.Data.SQLite;
using System.Linq;
...

    
        public void UpdateMedia(MediaModel media)
        {
            var connection = new SQLiteConnection("Data Source=database.sqlite;Cache Size=0");
            var context = new DataContext(connection);
            connection.Open();

            using var transaction = connection.BeginTransaction();
            var command = connection.CreateCommand();
            command.CommandText = "update media set ";

            if (media.Description != null)
                command.CommandText += $"Description='{media.Description.ToBase64()}',";
            if (media.GenreString != null)
                command.CommandText += $"Genres='{media.GenreString.ToBase64()}',";
            if (media.Rating > 0)
                command.CommandText += $"Rating={media.Rating},";
            if (media.Release > 0)
                command.CommandText += $"Release='{media.Release}',";
            if (media.State >= 0)
                command.CommandText += $"State='{media.State}',";
            command.CommandText = command.CommandText[..^1];

            command.CommandText += $" where Name='{media.Name.ToBase64()}' and Type='{media.Type.ToBase64()}'";
            command.ExecuteNonQuery();
            transaction.Commit();
        }
...

so sieht es aktuell aus... normalerweise würde ich aber alles bis connection.open natürlich in den Konstruktor schreiben und in der Dispose()-Methode dementsprechend Disposen. Aber als ich das getan habe kam halt der Fehler

 

[marcOcram]

Das wichtigste zuerst: Hast du jemals von einer SQL Injection gehört? Wenn ja, warum nutzt du keinen parametrisierten Query? Wenn nein, einlesen und nutzen!

Die Verbindung wird nach dem Update nicht geschlossen. Ich würde die Benutzung von using(...) empfehlen, damit die Verbindung automatisch geschlossen wird.

Das Öffnen der Verbindung an dieser Stelle ist schon richtig. Das gehört nicht in den Konstruktor außer es ist ein wirklich kleines Objekt mit einer sehr kleinen Lebenszeit. Ansonsten gilt: Verbindung aufmachen, DML ausführen, Verbindung schließen.

Was macht der DataContext da mit der Verbindung? Wie ist es ohne diesen DataContext? Dieser scheint ja nicht benötigt zu werden. Öffnet der z. B. eine weitere Transaktion? (EDIT: Das macht er wohl nicht, da die Verbindung erst danach geöffnet wird)

Die Transaktion ist an dieser Stelle nicht unbedingt nötig, da du nur ein Statement ausführst braucht es keine Transaktion nur für dieses eine Statement.

Hier mal ein Minimalbeispiel, dass es funktioniert: https://dotnetfiddle.net/KvPCwg

 

[Dalek]

Sind die ganzen Base64 Sachen zur Vermeidung von SQL Injektion? Ich vermute das funktioniert sogar, aber wieso würde man eine so umständliche Methode nehmen statt die Queries einfach richtig mit Parametern zu schreiben?

Du erstellst einen DataContext, benutzt ihn aber nicht. Ein DataContext macht Caching, wobei das nicht benutzt werden sollte so wie du das verwendest. Wenn du den DataContext verwendest dann muss der pro Request erstellt werden.

Was du da machst ist schon sehr umständlich, ich würde nicht unbedingt die low-level ADO.NET Methoden verwenden sondern entweder EF Core oder Dapper (wenn du plain SQL magst). Und schau dir wirklich mal an was SQL injections sind und wie man parametrisierte Queries macht. Benutzereingaben direkt in SQL strings einbauen ist extrem gefährlich, das macht man einfach nicht. Und die richtigen Methoden sind noch nichmal mehr Arbeit.

 

[Kokujou]

Stimmt Injection wäre eine Möglichkeit aber ganz ehrlich? Der Code wird damit immer so sperrig und lang wenn ich jeder mal nen Parameter erstellen muss, ihm einen Wert geben muss und ihn dann dem Kommando zuweisen muss... nicht sonderlich schön bei vielen Parametern...

Vielleicht sollte ich ja doch Entity Framework nutzen aber auch hier schien mir das für mein kleines Projektchen ja schon ein Overhead zu sein... Ehrlich gesagt bin ich noch am austüfteln was wirklich Sinn macht... Ich hab auch überlegt selbst mit Reflections die Parameters dynamisch zu übergeben...

Aber gut die Antwort hat mir ja im Prinzip marcOcram gegeben... Ich finde es komisch, denn z.b. bei CosmosDB wird die Verbindung zur Datenbank im Konstruktor gemacht und gehalten, damit man sie nicht dauernd wiederaufbauen muss und damit einen unnötigen Overhead hat..

aber wenn ich so drüber nachdenke ist sqlite ja eigentlich nur File Lese und Schreibvorgänge, das ist vermutlich kein großer Gewinn da die Verbindung zu halten, eher ein verlust...

Aber so ganz versteh ich es immernoch nicht... denn selbst wenn das ganze File in den Arbeitsspeicher geladen werdn würde, dann würde doch der update-request auf demselben objekt, derselben verbindung, demselben dataContext laufen... und damit dürften die daten auch geändert werden...

aber gut. Wenn ihr sagt das muss so, dann muss das so. Danke für die Antworten!

 

[Dalek]

Du hast nur den Code gezeigt der funktioniert, nicht den Code der nicht funktioniert. Wir wissen nicht was der Fehler wirklich ist. Ich hab mit SQLite in C# keine Erfahrung, und das ist ja tatsächlich etwas anders als ein DB Server bei dem man generell mit einem Connection Pool die Verbindungen zentral managt. Aber ich sehe noch nicht wieso die DB Connection irgendwas cachen würde, außer der DataContext funkt wirklich dazwischen, das würde ich einfach mal ausprobieren.

Ich hab nie wirklich mit ADO.NET gearbeitet, aber was ich gelesen habe war eigentlich immer das das eine etwas unnötig umständliche Methode ist. Wenn man ein ORM will nimmt man EF Core, und wenn man mehr Performance braucht oder reines SQL schreiben will dann Dapper.

 

[Kokujou]

ich hab nicht viel geändert nur die 3 ersten Zeilen standen halt vorher im Konstruktor und haben in ne readonly variable geschrieben. Alles kein Hexenwerk. Also statt pro request habe ich sowohl Connection als auch DbContext nur im Startup des Servers aufgebaut... und das ist der Grund des ganzen Problems...

Wenn es nur am Kontext liegt das könnte ich demnächst nochmal ausprobierne, das würde ich noch machen...
ansonsten... kA ob und wie Entity Framework Core mit SQLite funktioniert, ich werds vielleicht mal ausprobierne, hatte aber in eine früheren Projekt bittere Erfahrungen gemacht... was aber zugegeben auch daran lag dass die Kollegen da richtig heftig Mist gebaut haben >.<

 

[Dalek]

Was mich noch wirklich interessieren würde ist diese Base64 Sache? Ist das eine improvisierte Lösung gegen SQL Injections? Oder was ist der Hintergrund? Ich bin mir ziemlich sicher das das nicht wirklich sinnvoll ist, und auch eine Sache die du auf jeden Fall anders machen solltest.

 

[Hayda Ministral]

Aber so ganz versteh ich es immernoch nicht... denn selbst wenn das ganze File in den Arbeitsspeicher geladen werdn würde, dann würde doch der update-request auf demselben objekt, derselben verbindung, demselben dataContext laufen...

nach meinem Verständnis: Würde er ja auch. Nur das öffnen der bereits geöffneten Verbindung macht Aua.

 

[Kokujou]

Was mich noch wirklich interessieren würde ist diese Base64 Sache? Ist das eine improvisierte Lösung gegen SQL Injections? Oder was ist der Hintergrund? Ich bin mir ziemlich sicher das das nicht wirklich sinnvoll ist, und auch eine Sache die du auf jeden Fall anders machen solltest.

wenn SQL Injections dieses Parameter-Zeug sind womit man sich um Escaping keine Sorgen mehr machen muss: ja. nur darum gehts. Wie gesagt ich bin noch am Anfang und hab noch kein klares Bild und weiß nicht wie es später mal aussehen soll. Vielleicht steig ich ja doch wieder auf Parameter um... ist schon komisch base64 Werte in der DB zu haben...

nach meinem Verständnis: Würde er ja auch. Nur das öffnen der bereits geöffneten Verbindung macht Aua.

das hab ich jetzt nich verstanden. falls es wegen dem aktuellen code ist: wie gesagt das ist jetzt die improvisierte funktionierende Lösung. Da er noch nicht gemeckert hat nehme ich einfach mal an dass die Variablen ob mit oder ohne using am ende des Requests disposet werden...

vorher hatte ich, wie bereits gesagt, die ersten 3 Zeilen im Konstruktor des Controllers und einen Dispose-call der sich der Connection entledigt.

 

[Fombu]

Stimmt Injection wäre eine Möglichkeit aber ganz ehrlich? Der Code wird damit immer so sperrig und lang wenn ich jeder mal nen Parameter erstellen muss, ihm einen Wert geben muss und ihn dann dem Kommando zuweisen muss... nicht sonderlich schön bei vielen Parametern...

Lol! Da fällt mir echt nix mehr ein...
Das Schloss an der Haustür macht den Schlüsselbund immer so sperrig, ich lass das Schloss einfach weg.

 

[Kokujou]

Lol! Da fällt mir echt nix mehr ein...
Das Schloss an der Haustür macht den Schlüsselbund immer so sperrig, ich lass das Schloss einfach weg.

Es ist ja kein Sicherheitsproblem.
Ich hatte ein ähnliches Problem auf Arbeit. Ich hab ehrlich gesagt keine Ahnung was wir da für die Datenbank benutzen, die Daten in die DB zu kriegen wird vermutlich über INjection gemacht. aber wir hatten ein Problem beim holen der Daten. denn dann standen die ganzen Werte im Arbeitsspeicher und müssten als Response ans Frontend geschickt werden. Und in dem Moment hat das fehlende Escaping unser JSON-Pattern zerstört und zu fehlern geführt.

Also haben wir auch da einfach base64 kodierung eingefügt.

 

[Dalek]

Die korrekte Version dieses Codes mit EF Core oder Dapper wäre viel kürzer und einfacher zu schreiben. Du benutzt die mit Abstand umständlichste Library für den Datenbankzugriff und entscheidest dann das weil diese Library so umständlich ist eine extreme Frickelei zu betreiben mit Parametern.

In EF Core wären das zwei Zeilen (evtl. noch Code fürs mapping oben drauf), in Dapper ist es ein bißchen länger. Der Code den du gepostet hast ist unnötig lang, sehr umständlich und anfällig für Fehler. Einmal "ToBase64" vergessen und die Datenbank ist kompromittiert. Und das handling von ungültigen Parametern ist problematisch da es Probleme lautlos verschluckt anstatt einen Fehler auszugeben.