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.

Remy Lebeau · Accepted Answer · 2021-07-14 16:46:31Z

6

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.

edited Jul 14, 2021 at 16:46

Remy Lebeau

609k36 gold badges516 silver badges875 bronze badges

answered Jul 14, 2021 at 16:24

MartynA

30.8k5 gold badges36 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gordon Turner Over a year ago

Thanks. I hadn't realized the special handling was a FireDAC issue. I'm not worried about SQL Injection Attacks in this particular instance. The program is a data conversion utility for a PC based product I sell - converting from one database to another. It is all the user's data so there would be no incentive to doctor your own data to break the conversion.

Collectives™ on Stack Overflow

Firedac Sqlite Query fails when query string contains "!"

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related