"SQL Command Preprocessor" pitfall

[Stéphane Wierzbicki 45]

Hi,

 

I'm having a problem with the "SQL Command Preprocessor". Whenever I enter values containing special characters such as '{' ,'!', '&', '}' , Firedac tries to treat this value as a new macro.

I played with ResourceOptions.MacroCreate, ResourceOptions.MacroExpand and ResourceOptions.EscapeExpand but I was not able to get a correct password. The only thing I found was to "duplcate" any of these '{' ,'!', '&', '}'.

 

Password 'Let me out !!" will becomes 'Let me out !!!!'.

 

Some Delphi code:

 

    FDQuery2.Macros.MacroByName('USER').AsRaw := 'Blablabla';
    FDQuery2.Macros.MacroByName('USER_PASSWORD').AsRaw := 'Let me out !!!!';
    FDQuery2.ExecSQL;

MS SQL Script

ALTER LOGIN [&USER] WITH PASSWORD = N'&USER_PASSWORD',
   DEFAULT_DATABASE = [&Database],
   DEFAULT_LANGUAGE = [us_english],
   CHECK_EXPIRATION = OFF,
   CHECK_POLICY = OFF;  

Is there not a way to tell the "SQL Command Preprocessor" not to preprocess value passed thanks to FDQuery2.Macros.MacroByName method call ?

 

Thank you.

 

Stéphane

Edited July 8, 2020 by Stéphane Wierzbicki

[Uwe Raabe 2064]

If the password is a string, which I think is what N'...' implies, can you change the script to take only the macro and use AsString in the code? 

    FDQuery2.Macros.MacroByName('USER').AsRaw := 'Blablabla';
    FDQuery2.Macros.MacroByName('USER_PASSWORD').AsString := 'Let me out !!';
    FDQuery2.ExecSQL;

ALTER LOGIN [&USER] WITH PASSWORD = &USER_PASSWORD,
   DEFAULT_DATABASE = [&Database],
   DEFAULT_LANGUAGE = [us_english],
   CHECK_EXPIRATION = OFF,
   CHECK_POLICY = OFF;