mORMot Open Source

ma64

Member

Registered: 2011-01-27

Posts: 12

Invalid JSON from ISqlDBStatement.ExecutePreparedAndFetchAllAsJson()

Hello Arnaud, there is a small bug inside the method TSqlDBPostgresStatement.ColumnToJson() (unit mormot.db.sql.postgres; latest version of mORMot2). Values from PostgreSQL columns of type boolean are getting written as t and f instead of true and false, leading to invalid JSON.
An example of a call to the method ISqlDBStatement.ExecutePreparedAndFetchAllAsJson(), which internally calls ColumnToJson(), that shows this bug is

'{"fieldCount":1,"values":["autolinked",f,f,f,f,f,f],"rowCount":6}'

whereas it should be

'{"fieldCount":1,"values":["autolinked",false,false,false,false,false,false],"rowCount":6}'

The problem results from the fact, that the boolean column is seen as a column of type ftInt64 inside of ColumnToJson(),
so that the PostgreSQL value is wrongly written verbatim into the JSON. This can be fixed quickly by checking the real column type (as shown below), but maybe this is not an optimal solution, since this check is repeated for every row:

case ColumnType of
  ...
  
  ftInt64,
  ftDouble,
  ftCurrency:
    begin
    if PQ.ftype(fRes, Col) = BOOLOID then begin
      if putf8char(P)^ = 't' then
        W.AddNoJsonEscapeUtf8('true')
      else
        W.AddNoJsonEscapeUtf8('false');
    end else
      // note: StrLen is slightly faster than PQ.GetLength for small content
      W.AddNoJsonEscape(P, StrLen(P));
    end;

  ...

Kind regards
Michael

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,270

Website

Re: Invalid JSON from ISqlDBStatement.ExecutePreparedAndFetchAllAsJson()

Hello

Thanks for the detailed feedback.

Please try https://github.com/synopse/mORMot2/commit/30b1bdbe

ma64

Member

Registered: 2011-01-27

Posts: 12

Re: Invalid JSON from ISqlDBStatement.ExecutePreparedAndFetchAllAsJson()

Thank you Arnaud, your fix works perfectly.