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

          Ascending order - Click to sort in descending order
          RemkoNolten Remko Nolten added a comment - - edited

          Not surprising, but we noted that creating an object using the JSON_OBJECT function also yields an string that is invalid according to JSON_VALID:

          The following code returns '0' (expected '1'):

          SELECT JSON_VALID(JSON_OBJECT('simple', 1,
          		 
              'wrong', JSON_OBJECT(
          		 
                  '"bar"', JSON_OBJECT(
          		 
                      "value", 1
          		 
                  )
          		 
              )
          		 
          ));

          RemkoNolten Remko Nolten added a comment - - edited Not surprising, but we noted that creating an object using the JSON_OBJECT function also yields an string that is invalid according to JSON_VALID : The following code returns '0' (expected '1'): SELECT JSON_VALID(JSON_OBJECT( 'simple' , 1, 'wrong' , JSON_OBJECT( '"bar"' , JSON_OBJECT( "value" , 1 ) ) ));
          holyfoot Alexey Botchkov added a comment -

          https://github.com/MariaDB/server/commit/6ac2a355531832db250b37e7145a3eac2293b51b

          holyfoot Alexey Botchkov added a comment - https://github.com/MariaDB/server/commit/6ac2a355531832db250b37e7145a3eac2293b51b
          holyfoot Alexey Botchkov added a comment -

          To Francisco - what you presented isn not a bug. That 'complex' json is invalid.
          To make it valid you have to add more backslashes
          SELECT '[

          {"Year":1997,"Make":"Ford","Model":"E350","Description":"ac, abs, moon","Price":3000,"UpForSale":"2017-10-01"}

          ,

          {"Year":1999,"Make":"Chevy","Model":"Venture \\\"Extended\\\" Edition","Description":null,"Price":4900,"UpForSale":"2017-10-03T12:15"}

          ]' INTO @_json;

          Try the 'SELECT @_json;' with both lines to see why.

          holyfoot Alexey Botchkov added a comment - To Francisco - what you presented isn not a bug. That 'complex' json is invalid. To make it valid you have to add more backslashes SELECT '[ {"Year":1997,"Make":"Ford","Model":"E350","Description":"ac, abs, moon","Price":3000,"UpForSale":"2017-10-01"} , {"Year":1999,"Make":"Chevy","Model":"Venture \\\"Extended\\\" Edition","Description":null,"Price":4900,"UpForSale":"2017-10-03T12:15"} ]' INTO @_json; Try the 'SELECT @_json;' with both lines to see why.

          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.