@rvk: I think I will take that road indeed. No need to get myself in more trouble than necessary.
Actually, thinking about this further and seeing the link Lutz posted you might also go another route.
In the link Lutz posted you see that SQLite accepts double-quotes. Also, IT NEEDS double quotes for table- and fieldnames which also act as keywords.
If you let your users choose the table and fieldnames that means that the user could also pick UPDATE or TABLE als field- or tablename.
And you get in trouble again.If you just
double quote ALL table and fieldnames, you can let the user pick what they like. And it doesn't even matter if it contains spaces (although I would never use those in real-live applications).
For instance this works correctly (at least it doesn't give me an error). The table- and fieldnames should ALL be double quoted so you never get into trouble with user-defined names. You can even leave ParseSQL set to true.
(Tested it with values and it does work with those fieldnames)
So like Lutz and taazz suggested, always use double quotes
FOR ALL your table and fieldnames (because they are userdefined you need to double quote them ALL) and use single quotes for string-literals during inserts.
program project1;
uses sqlite3conn, sqldb, DB, SysUtils;
var
SQLite3Connection1: TSQLite3Connection;
SQLTransaction1: TSQLTransaction;
SQLQuery1: TSQLQuery;
SQL: String;
begin
Deletefile('test.db'); // remove if you want to keep de db
SQLite3Connection1 := TSQLite3Connection.Create(nil);
SQLite3Connection1.DatabaseName := 'test.db';
SQLTransaction1 := TSQLTransaction.Create(nil);
SQLite3Connection1.Transaction := SQLTransaction1;
if not FileExists('test.db') then
begin
SQL := 'CREATE TABLE "My Addresses"(ID INTEGER PRIMARY KEY AUTOINCREMENT, "UPDATE" VARCHAR(25), "TABLE" VARCHAR(25));';
SQLite3Connection1.ExecuteDirect(SQL);
SQL := 'INSERT INTO "My Addresses" VALUES(1, ''a'', ''b'');';
SQLite3Connection1.ExecuteDirect(SQL);
SQL := 'INSERT INTO "My Addresses" VALUES(2, ''d'', ''e'');';
SQLite3Connection1.ExecuteDirect(SQL);
SQLTransaction1.Commit;
end;
SQLite3Connection1.Connected:=true;
SQLQuery1 := TSQLQuery.Create(nil);
SQLQuery1.Database := SQLite3Connection1;
SQLQuery1.UpdateMode := upWhereKeyOnly;
SQLQuery1.Transaction := SQLTransaction1;
SQL := 'SELECT "UPDATE", "TABLE" FROM "My Addresses" WHERE 1=1';
SQLQuery1.SQL.Text := SQL;
SQLQuery1.ParseSQL := true; // You can even leave this at true
SQLQuery1.Open;
while not SQLQuery1.EOF do
begin
writeln(SQLQuery1.Fieldbyname('TABLE').asString);
SQLQuery1.Next;
end;
readln;
end.