The FireDAC components fix one of my longest standing frustrations with using Delphi with Firebird and Interbase databases.
As a security aware developer, I have always known the advantages of using parameters in my queries, not only do these make queries a little faster to run (as they can be prepared and reused multiple times), but they can assist with defending against SQL injection caused by incorrect formatting of values passed to the database.
Sometimes you may wish to use a SQL script (using execute block in Firebird), the problem here is that Firebird prefixes variables with the : character – the same character you would use to define a parameter in Delphi.
FireDAC has a little known feature however. If you declare an execute block statement, all of the : identifiers before the first BEGIN statement are treated as parameters, all other : identifiers are ignored, and left as variables.
This means, for the first time, you can use one of the ‘out of the box’ data access controls to mix parameters and variables in Firebird queries.
EXECUTE BLOCK (
MY_PARAM INTEGER = :PARAM
) RETURNS (
DECLARE VARIABLE MY_VARIABLE INTEGER;
MY_VARIABLE = 100;
MY_RESULT = :MY_VARIABLE * :MY_PARAM;
In the code above :PARAM identifies a parameter we can pass from Delphi using ParamByName, :MY_VARIABLE is left untouched, and is used by Firebird as a variable.