newtonsoft. json serializeObject string contains fraction value doesn't seem to escape properly

Question

Having an issue where my object is being serialized using Newton where one of the properties is a string that contains values that are fractions such as 1/2", 1/4", etc...

After serializing I'm passing the variable to a SQL Server Stored Procedure that is using OPENJSON.

The double quote in the fraction value doesn't seem to be escaping properly as it fails as invalid format for JSON. When debugging I see below which would appear the " in the fraction value is not being escaped properly.

A little new to some of the serialization so could use a little help.

string strJson = JsonConvert.SerializeObject(myobject);



DECLARE @json nvarchar(max) = '{"Number":64260,"Notes":"1/2\\" testing"}';
SELECT *
    FROM OPENJSON (@json, '$')
    WITH(
        [Number] int   '$.Number'
        ,[Notes] nvarchar(max)   '$.Notes'
    ) AS myDat
Msg 13609, Level 16, State 4, Line 2 JSON text is not properly formatted. Unexpected character 't' is found at position 32.

If I remove the second "\" from the fraction value it works fine.

 public partial class TblEcr
{


    public int Number { get; set; }
    public string Notes { get; set; }

}

    public JsonResult OnPostUpdate([DataSourceRequest] DataSourceRequest request, TblEcr ecr)
    {
        _context.TblEcr.Where(x => x.Number == ecr.Number).Select(x => ecr);
        try
        {
            if (ModelState.IsValid)
            {
                string ecrJson = JsonConvert.SerializeObject(ecr);

                var param = new SqlParameter[] {
                    new SqlParameter() {
                        ParameterName = "@json",
                        SqlDbType =  System.Data.SqlDbType.VarChar,
                        Size = 8000,
                        Direction = System.Data.ParameterDirection.Input,
                        Value = ecrJson
                    },
                    new SqlParameter() {
                        ParameterName = "@Status",
                        SqlDbType =  System.Data.SqlDbType.Bit,
                        Direction = System.Data.ParameterDirection.Output
                        //,Value = 10
                    },
                    new SqlParameter() {
                        ParameterName = "@ErrorDetails",
                        SqlDbType =  System.Data.SqlDbType.VarChar,
                        Size =8000,
                        Direction = System.Data.ParameterDirection.Output,
                    }};
                int affectedRows = _context.Database.ExecuteSqlCommand("dbo.usp_UpdateECR @json, @Status, @ErrorDetails out", param);
            }
        }
        catch (Exception ex)
        {
            return new JsonResult(ex.Message);
        }

        return new JsonResult(new[] { ecr }.ToDataSourceResult(request, ModelState));
    }

Could you add enough C# code to your question to generate that string as the result of a JSON.NET serialization? — David Browne - Microsoft, May 06 '20 at 21:35
Updated question. Let me know if this is enough. The stored procedure contains the SQL statement used in the example — SKing605, May 07 '20 at 12:39

score 0 · Answer 1 · answered May 07 '20 at 13:48

Doesn't repro for me.

using Microsoft.Data.SqlClient;
using Newtonsoft.Json;
using System;

namespace ConsoleApp8
{
    class Program
    {
        public partial class TblEcr
        {
            public int Number { get; set; }
            public string Notes { get; set; }
        }
        static void Main(string[] args)
        {
            var ecr = new TblEcr() { Number = 1, Notes = @"1/2"" testing" };

            string ecrJson = JsonConvert.SerializeObject(ecr);
            Console.WriteLine(ecrJson);

        }
    }
}

outputs

{"Number":1,"Notes":"1/2\" testing"}

score 0 · Answer 2 · answered May 07 '20 at 17:58

0

Thank you for looking at it.

After putting more thought in to it, I simplified a record I was attempting to update and found my issue. It is the stored procedure in SQL Server.

There was a string manipulation in the procedure that was causing the issue. Basically a simple replace for another purpose.

Set @json = REPLACE(@json,'\"','"'); -- BOOM, this caused it.

answered May 07 '20 at 17:58

SKing605

1
1

Glad you found the problem. Doing a Replace on a JSON string is always a red flag for causing these kinds of issues. – Brian Rogers May 08 '20 at 15:07

newtonsoft. json serializeObject string contains fraction value doesn't seem to escape properly

2 Answers2