HotXLS Unsafe Formula Callbacks: Gating CALL and WEBSERVICE

Spreadsheet engines evaluate arguments before they call the function that receives them. A cell holding =WEBSERVICE(AUDIT_TOKEN()) therefore runs AUDIT_TOKEN first, and if the host application answers AUDIT_TOKEN through a user-defined function handler, that handler has already written its log line, bumped its counter or opened its connection by the time anything looks at the word WEBSERVICE. A blocklist inside the handler is too late. So is one that inspects the outer name after the arguments come back. The refusal has to be decided on the outer name, before any argument is touched and before any code the host wrote gets a turn

The setting where this bites is mundane. A service accepts uploaded XLS or XLSX files, recalculates them on the server and reads a few totals back. Years ago someone registered a user-function handler for two business functions, and along the way it grew a catch-all branch that forwards any unrecognised name to a plugin table. Nobody on the team ever typed =WEBSERVICE(...) into a cell. The uploader did. Keeping that formula intact through open, recalc and save is a file-fidelity job; letting it reach host code that can open sockets or write files is an authorisation decision, and the two need different owners

How a preserved formula reaches your Delphi callbacks

In the HotXLS formula engine, any call the calculator cannot evaluate natively falls into one shared dispatch chain, and dangerous names arrive there by two roads. One road is built-in functions that the parser recognises but the calculator has no implementation for: CALL and REGISTER.ID are real Excel function names, so they parse cleanly and then land in the same fallback as your DISCOUNT or REGIONRATE. The other road is names the parser does not know at all, such as WEBSERVICE or DDE, which can match a same-named entry in a registry or be claimed by a catch-all event handler. The mechanics of that fallback are covered in how HotXLS resolves custom functions through OnUserFunction

The order of the chain decides what "unknown" means. A name goes to the LAMBDA machinery before anything else: a closure bound by LET, or a defined name whose formula is a LAMBDA, both described in the closure support in the HotXLS formula engine. After that come workbook-local functions registered with RegisterUserFunction, followed by process-wide functions from the class method TXLSWorkbook.RegisterGlobalUserFunction, and last the events. OnUserFunctionEx runs when it is assigned; OnUserFunction runs only when OnUserFunctionEx is not. A truly unknown name becomes #NAME? once every stage has declined, while a recognised but unimplemented built-in reports an unsupported function instead

Every stage after the LAMBDA lookup hands control to code you wrote. That is why HotXLS puts its safety check in front of every stage that reaches your code rather than inside any one of them, and why the check sits in the calculator itself instead of in the three file readers. A formula loaded from Classic BIFF tokens, from XLSX text or from an ODS msoxl: formula ends up as the same parsed expression, so one check covers all three formats and a converted workbook behaves the same as the original

Which formula names does HotXLS deny by default?

HotXLS denies a fixed set of 20 names unless callbacks are explicitly allowed: DDE, CALL, REGISTER, REGISTER.ID, WEBSERVICE, RTD, SQL.REQUEST, EXEC, RUN, CREATE.OBJECT, APP.ACTIVATE, SEND.KEYS, OPEN, SAVE, SAVE.AS, FOPEN, FWRITE, FWRITELN, FCLOSE and FILE.DELETE. In Excel or its macro language these are the names that load native code, reach the network, talk to other processes or touch the file system. Before comparing, the engine trims surrounding whitespace, uppercases the name and strips a single _XLFN. or _XLWS. prefix. A file written by a newer Excel build that stores _xlfn.webservice is caught exactly like the bare spelling

The match is exact, which cuts both ways. A handler you register as MYWEBSERVICE is unaffected. An in-house UDF that happens to be called OPEN or RUN is denied by default, and the tempting fix is to set AllowUnsafeFormulaCallbacks to True on the workbook so that one name works again. Rename the function instead. Opting the whole workbook in to rescue one in-house name also re-enables CALL, DDE and FILE.DELETE for every formula in that file, including formulas you did not write

The deny set also does nothing to fence in your own handlers. If a catch-all branch executes arbitrary plugin names, the gate stops the 20 well-known dangerous ones and lets everything else through to your code. The durable answer is to stop writing catch-all handlers. Registering each function by name with RegisterUserFunction turns the registry itself into an allowlist, and it keeps the formula entry check meaningful: when OnUserFunction or OnUserFunctionEx is assigned, the engine cannot know which names the event will claim, so entry validation stops flagging unknown function names altogether

procedure TImportService.WireFunctions;
begin
  // One registration per name: the registry is the allowlist
  FBook.RegisterUserFunction('DISCOUNT', HandleDiscount);
  FBook.RegisterUserFunction('REGIONRATE', HandleRegionRate);
  // Leave both events unassigned so unknown names are still
  // rejected at entry and end up as #NAME? at evaluation
  FBook.OnUserFunction := nil;
  FBook.OnUserFunctionEx := nil;
end;

procedure TImportService.HandleDiscount(Sender: TObject;
  const FunctionName: WideString; const Args: Variant;
  var Value: Variant; var Handled: Boolean);
begin
  // Args is a zero-based variant array of already evaluated arguments
  if VarIsArray(Args) and (VarArrayHighBound(Args, 1) = 1) then
  begin
    Value := Args[0] * (1 - Args[1]);
    Handled := True;
  end;
end;

Why must the gate run before argument evaluation?

HotXLS checks the function name first and refuses the call before it builds the argument array, before it consults a resolver or either registry, and even before it notices that no handler is assigned at all. That ordering is what defeats the nested formula from the opening: the outer WEBSERVICE is refused while AUDIT_TOKEN is still an unevaluated child node, so the host handler never runs. The last part of the ordering matters for logging. A workbook with no handlers and a workbook with five both report the same status for =WEBSERVICE(...), so a rejection log does not change meaning when someone later wires up a handler

procedure TImportService.HandleUdf(Sender: TObject;
  const FunctionName: WideString; const Args: Variant;
  var Value: Variant; var Handled: Boolean);
begin
  if SameText(FunctionName, 'AUDIT_TOKEN') then
  begin
    FAuditLog.Add('AUDIT_TOKEN evaluated');   // side effect in host code
    Value := 'token-42';
    Handled := True;
  end;
end;

procedure TImportService.ProbeNestedCall;
var
  Sheet: TXLSWorksheet;
  Eval: TXLSFormulaEvaluationResult;
begin
  FBook.OnUserFunction := HandleUdf;
  Sheet := FBook.Sheets[1];                  // worksheet indexes start at 1
  Eval := Sheet.EvaluateFormulaAt(1, 1, '=WEBSERVICE(AUDIT_TOKEN())');
  // Expected: Eval.Status = xlfeUnsafeFunctionDenied, Eval.Value = Null,
  // Eval.Issue.NativeCode = -106 and FAuditLog.Count = 0
end;

The native code -106 is the public constant lxErrorUnsafeFunctionDenied, and Eval.Issue.Message reads "The formula callback is blocked by the safety policy". xlfeUnsafeFunctionDenied is the last member of TXLSFormulaEvaluationStatus, after xlfeFailed. Code that maps statuses with a case and an else branch for generic failure will file a denial under "evaluation failed" unless it names the status explicitly, and for an upload pipeline that is the one outcome worth reporting back to the uploader

Workbook default versus per-call TXLSFormulaEvaluationOptions

The workbook flag sets the default and the per-call option has the final word. TXLSWorkbook.AllowUnsafeFormulaCallbacks and TXLSXWorkbook.AllowUnsafeFormulaCallbacks start at False and govern ordinary recalculation, Calculate, the three-argument EvaluateFormulaAt, EvaluateAt on an evaluation template, data validation list formulas, read-only views and, on XLSX, every worker in the parallel recalculation pool. Any entry point that takes an explicit TXLSFormulaEvaluationOptions record uses Options.AllowUnsafeFormulaCallbacks as the verdict for that call and does not OR it with the workbook property

That asymmetry is deliberate, and it is the right default. A trusted internal job can authorise one RTD lookup without flipping the whole workbook, and a workbook that is globally opted in can still force a sensitive evaluation back to deny. XLSDefaultFormulaEvaluationOptions returns the flag as False, so building the record from that function means denial unless you set it on purpose. One path ignores the flag entirely: when Classic conditional formatting rules are evaluated for rendering, callbacks are denied whatever the workbook says

var
  Sheet: TXLSWorksheet;
  Options: TXLSFormulaEvaluationOptions;
  Eval: TXLSFormulaEvaluationResult;
begin
  Sheet := FBook.Sheets[1];

  // workbook stays locked down, one trusted call is allowed through
  FBook.AllowUnsafeFormulaCallbacks := False;
  Options := XLSDefaultFormulaEvaluationOptions;
  Options.AllowUnsafeFormulaCallbacks := True;
  Eval := Sheet.EvaluateFormulaAt(4, 2, '=WEBSERVICE(B1)', xlfrsA1, Options);

  // workbook opted in, but this evaluation of uploaded text is not
  FBook.AllowUnsafeFormulaCallbacks := True;
  Options := XLSDefaultFormulaEvaluationOptions;   // flag is False again
  Eval := Sheet.EvaluateFormulaAt(4, 2, UploadedFormula, xlfrsA1, Options);
  if Eval.Status = xlfeUnsafeFunctionDenied then
    LogRejected(Eval.Issue.Message);   // LogRejected is your own routine
end;

Changing the workbook property also marks every formula in the dependency graph dirty, on both workbook classes. Without that, a value cached while callbacks were allowed could be served after they were revoked, or a cached denial could outlive an opt-in. Setting the property to the value it already holds is a no-op and leaves the cache alone, so toggling it inside a loop only costs a full recalculation when the value actually changes

What happens to unknown and unsafe formula text on save?

Keeping a formula and running it are two separate questions, and the entry policy only answers the first. FormulaEntryPolicy on either workbook class carries UnknownFunctionMode and UnknownNameMode, both defaulting to xlfusmReject. Assigning a formula with an unknown call through the normal Formula property raises EArgumentException with "Formula entry rejected: unknown function" before the cell value, formula cache or dependencies change. ValidateFormulaEntry returns the same decision as a record, without side effects. Trusted paths such as loading a file, copying and format conversion do not go through the user-entry policy, because a strict default must never reject symbols already present in a file you are merely opening

var
  Sheet: TXLSWorksheet;
  Policy: TXLSFormulaEntryPolicy;
  Check: TXLSFormulaEntryValidationResult;
begin
  Sheet := FBook.Sheets[1];
  Check := Sheet.ValidateFormulaEntry(3, 1, '=ACME_RATE(B3)');
  // default policy, no handler for ACME_RATE:
  // Check.Issue = xlfeiUnknownFunction, Check.Action = xlfeaReject

  Policy := FBook.FormulaEntryPolicy;
  Policy.UnknownFunctionMode := xlfusmPreserve;   // compatibility entry
  FBook.FormulaEntryPolicy := Policy;
  Sheet.Cells[3, 1].Formula := '=ACME_RATE(B3)';  // stored, not authorised
  FBook.SaveAs('rates.xls');
end;

Classic BIFF8 has no token for an arbitrary function name, so HotXLS writes an unknown call the way Excel writes add-in functions. The formula gets a PtgNameX token ($59) whose XTI entry points at the add-in SUPBOOK with both sheet indexes set to $FFFE, followed by the argument tokens and a PtgFuncVar carrying function number 255 and an argument count that includes the name slot. The backing ExternName body is six zero bytes, a length byte and the Unicode flag, the UTF-16 function name, then a two-byte formula of $1C $17, a PtgErr holding #REF!. The writer accepts names of 1 to 255 characters with at most 29 arguments, and it targets BIFF8 only; a BIFF5 save cannot carry the call. How these add-in SUPBOOK entries sit next to external workbook links is explained in the SUPBOOK and XTI classification rules for BIFF external links

XLSX keeps the raw function text and ODS keeps its msoxl: formula. In all three formats a file that saved =WEBSERVICE(...) reopens with the name intact, and its default evaluation still returns xlfeUnsafeFunctionDenied without calling the host. Saving never upgrades a formula's permissions: the same text that was denied before the round trip is denied after it

For a server that recalculates workbooks it did not author, the setup that holds up is short: leave AllowUnsafeFormulaCallbacks at False, register functions by name instead of assigning a catch-all event, and pass per-call options only where the formula text comes from your own code. The callback, entry-policy and evaluation reference ships with the HotXLS Delphi spreadsheet component documentation