Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-19628

JSON with starting double quotes key is not valid

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.3.8, 10.3.15, 10.4.4
    • 10.3.19, 10.4.9
    • JSON
    • None

    Description

      Tests were done on these versions, but more versions may be affected:

      • 10.3.8 (server version)
      • 10.3.15 (Docker image)
      • 10.4.4 (Docker image)

      We have found some issues with JSON_VALID and JSON_MERGE functions that occur when the keys of the JSON objects start with a double quote.

      For a JSON object to be valid, the double quote needs to be escaped with a backslash because the key is a string. Given the backslash is a reserved character in SQL, we need to add an additional one for the escaping, hence the double backslash seen in the following test sets.

      JSON_VALID Test Set

      select JSON_VALID('{"admin\\"": null}'), '{"admin\\"": null}'
      		 
      UNION
      		 
      select JSON_VALID('{"\\"admin": null}'), '{"\\"admin": null}'
      		 
      UNION
      		 
      select JSON_VALID('{"\\"": null}'), '{"\\"": null}';

      Results
      JSON_VALID() json
      TRUE {"admin\"": null}
      FALSE {"\"admin": null}
      FALSE {"\"": null}
      Expected Result
      JSON_VALID() json
      TRUE {"admin\"": null}
      TRUE {"\"admin": null}
      TRUE {"\"": null}

      JSON_MERGE Test Set

      create table Tags(
      		 
          id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      		 
          tj JSON
      		 
      );
      		 
       
      		 
      insert into Tags(tj) values ('{"admin":null}');
      		 
      insert into Tags(tj) values ('{"\\"":null}');
      		 
      insert into Tags(tj) values ('{"admin":null, "\\"":null}');
      		 
      insert into Tags(tj) values ('{"\\"":null, "admin":null}');
      		 
      insert into Tags(tj) values ('{"admin":null, "\\"":null, "admin2":null }');
      		 
      insert into Tags(tj) values ('{"admin":null, "admin2":null ,"\\"":null }');
      		 
      insert into Tags(tj) values ('{"abc\\"":null}');
      		 
      insert into Tags(tj) values ('{"admin":null, "abc\\"":null}');
      		 
      insert into Tags(tj) values ('{"abc\\"":null, "admin":null}');
      		 
       
      		 
      select tj, JSON_VALID(tj), 
      JSON_MERGE(tj,'{"admin":null}'),
      		 
      JSON_MERGE('{"admin":null}', tj), 
       
      		 
      JSON_MERGE(tj,'{"\\"":null}'),
      		 
      JSON_MERGE('{"\\"":null}', tj), 
       
      		 
      JSON_MERGE(tj,'{"admin": null, "\\"":null}'),
      		 
      JSON_MERGE('{"admin": null, "\\"":null}', tj), 
       
      		 
      JSON_MERGE(tj,'{"\\"":null, "admin":null}'),
      		 
      JSON_MERGE('{"\\"":null, "admin":null}', tj), 
       
      		 
      JSON_MERGE(tj,'{"admin":null, "\\"":null, "admin2":null }'),
      		 
      JSON_MERGE('{"admin":null, "\\"":null, "admin2":null }', tj),
      		 
       
      		 
      JSON_MERGE(tj,'{"admin":null, "admin2":null ,"\\"":null }'),
      		 
      JSON_MERGE('{"admin":null, "admin2":null ,"\\"":null }', tj),
      		 
       
      		 
      JSON_MERGE(tj,'{"abc\\"":null}'),
      		 
      JSON_MERGE('{"abc\\"":null}', tj),
      		 
       
      		 
      JSON_MERGE(tj,'{"admin":null, "abc\\"":null}'),
      		 
      JSON_MERGE('{"admin":null, "abc\\"":null}', tj),
      		 
       
      		 
      JSON_MERGE(tj,'{"abc\\"":null, "admin":null}'),
      		 
      JSON_MERGE('{"abc\\"":null, "admin":null}', tj) 
      FROM Tags;

      Issues found

      1. Order of JSON_MERGE parameters affects the result.
      Check differences between:

      JSON_MERGE(tj,'{"admin":null}')
      		 
      JSON_MERGE('{"admin":null}', tj)

      2. Number of JSON keys affects the result.
      Check differences between:

      JSON_MERGE('{"admin": null, "\\"":null}', tj)
      		 
      JSON_MERGE('{"\\"":null}', tj)

      3. Order of JSON keys within a JSON Object affects the result.
      Check differences between:

      JSON_MERGE('{"admin": null, "\\"":null}', tj)
      		 
      JSON_MERGE('{"\\"":null, "admin":null}', tj)

      Expected Results

      JSON_MERGE should produce a valid output regardless of the order and size of parameters (assuming the parameters have JSON_VALID as 1).

      Note: We suspect the issue in JSON_MERGE is related with the first issue with JSON_VALID.

      Attachments

        Activity

          People

            holyfoot Alexey Botchkov
            dmbento Diogo
            Votes:
            3 Vote for this issue
            Watchers:
            6 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.