Firedac Sqlite Query fails when query string contains "!"

Question

I'm trying to load records from another database into SQLite. I'm using Delphi 10.4 (Firemonkey) and a FireDAC Query component. When I pass the following query string to the Query component:

INSERT INTO BrandName (BrandName, WebSite, Email) values ("Mount It!", "", "")

it fails with the following exception:

Project MigrateData.exe raised exception class ESQLiteNativeException with message '[FireDAC][Phys][SQLite] Error: 2 values for 3 columns'.

When I remove the "!" from the record in the file, it processes normally, and if I run this query in the SQLite DB Browser it executes normally as well.

I'm using sqlite3.dll version 3.36. Here is the code I'm using, in case it helps:

  with MemDataMod.BrandTable do begin
    First;
    while not EOF do begin
      SQLStr := 'INSERT INTO BrandName (BrandName, WebSite, Email)'
            + ' values ("' + FieldByName('BrandName').AsString + '", "'
            + FieldByName('WebSite').AsString + '", "'
            + FieldByName('Email').AsString + '")';
      MySQLDB.BlankQuery.SQL.Clear;
      MySQLDB.BlankQuery.SQL.Append(SQLStr);
      MySQLDB.BlankQuery.ExecSQL;
      Next;
    end;
  end;

I can't imagine why the "!" would make a difference, but it seems to. Any help would be appreciated.

Answer 1

The ! (exclamation mark) character, and several others, require special handling in FireDAC SQL, because they have special meanings in it.

This is fully documented in the "Special Character Processing" section of the Preprocessing Command Text (FireDAC) documentation, see the ResourceOptions MacroCreate, MacroExpand, EscapeExpand properties in that section.

BTW, it's a bad idea to construct SQL by concatenating text which includes user input, or field values derived from it, because it is vulnerable to SQL Injection Attacks.