0

working on a small tool, and I want to store information on a per-app basis, so I'm using SQLite.

I have the code implemented in order to get the record in the database, and pull it from the database, etc.

On my insert however, I'm getting an error stating

Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'unrecognized token: "60ca7952"'. at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db) at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader() at Microsoft.Data.Sqlite.SqliteCommand.ExecuteNonQuery()

for an insert that looks like this:

cmd.CommandText = $"INSERT INTO FILES(Id, Path, DeleteOnExpiry, ExpirationDate, CreationTime) values({(string) f.Id}, {f.Path}, {f.DeleteOnExpiry}, {f.ExpirationDate}, {f.CreationTime})";

The ID field is a GUID, as I wanted a long, always unique key.

How can I "fix" the GUID to insert safely into the SQLite db file?

Chris Rutherford
  • 1,592
  • 3
  • 22
  • 58
  • 2
    Use parameters. The problem will automatically fix and you have the added benefit of preventing SQL injections – Camilo Terevinto Sep 10 '18 at 17:18
  • Um what? Could you give an example? I'm using a template string and it inserts correctly with the sqlite gui – Chris Rutherford Sep 10 '18 at 17:22
  • 3
    @ChrisRutherford using string interpolation to make your SQL query is a bad idea, its unsafe and its hard to debug. Use SqlParameters. – maccettura Sep 10 '18 at 17:23
  • There are thousands and thousands of posts here on SQL Parameters – Ňɏssa Pøngjǣrdenlarp Sep 10 '18 at 17:23
  • 1
    String operations (concatenation / formatting) is vulnerable to SQL injections. Please read https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection – Camilo Terevinto Sep 10 '18 at 17:23
  • In your table is the ID field a varchar? – Any Moose Sep 10 '18 at 17:25
  • Sorry, I didn't know of that paradigm it sounded nebulous. I'll look into it. Just didn't know what he was talking about – Chris Rutherford Sep 10 '18 at 17:26
  • @ChrisRutherford if you switch to using parameters you will likely find that this will just work. The crux of your problem is that the string interpolation you are doing is adding a value that does not make sense given the 'type' of the column. If you use parameterized queries it handles _a lot_ of this for you. – maccettura Sep 10 '18 at 17:27
  • @maccettura. I'll change it. Thanks – Chris Rutherford Sep 10 '18 at 17:28
  • Passing strings with SQLite is particularly problematic because it is typeless so it is easy to trash the DB when passing the wring type - such as a GUID which would be Binary – Ňɏssa Pøngjǣrdenlarp Sep 10 '18 at 17:31
  • 2
    Note that `new Guid()` returns a GUID containing all zeroes. If you want an initialized GUID use `Guid.NewGuid()`. – Dour High Arch Sep 10 '18 at 17:33
  • Possible duplicate of [What are good ways to prevent SQL injection?](https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection) – Chris Rutherford Sep 10 '18 at 17:37
  • With sqlite, you're better off using an [INTEGER PRIMARY KEY](https://www.sqlite.org/lang_createtable.html#rowid) instead of a UUID as your primary key. And if you want it to be always unique and never get reused, that's the use case for [AUTOINCREMENT](https://www.sqlite.org/autoinc.html). – Shawn Sep 10 '18 at 18:45

0 Answers0