JSON path is not properly formatted. Unexpected character ‘#’ is found at position 2

SQL Server has a pretty good support for JSON. If there is a special character in JSON, then it will throw this error;

JSON path is not properly formatted. Unexpected character ‘#’ is found at position 2

To produce this, here is an example

DECLARE @data NVARCHAR(50)='{"#Name":"Shahzad"}'

-- Print the current JSON
PRINT @data

-- Rename the key (by copying the value to a new key, then deleting the old one)
SET @data=
 JSON_MODIFY(
  JSON_MODIFY(@data,'$.Contractor', JSON_VALUE(@data,'$.#Name')),
  '$.#Name',
  NULL
 )
-- Print the new JSON
PRINT @data

I didn’t find any built-in support to handle these characters. The workaround I found is to simply replace special characters. Here is how;

DECLARE @data NVARCHAR(50)='{"#Name":"Shahzad"}'
DECLARE @cleanData NVARCHAR(50) = REPLACE(@data, '#Name', 'Name');
-- Print the current JSON
PRINT @data

-- Rename the key (by copying the value to a new key, then deleting the old one)
SET @cleanData=
 JSON_MODIFY(
  JSON_MODIFY(@cleanData,'$.Contractor', JSON_VALUE(@cleanData,'$.Name')),
  '$.Name',
  NULL
 )
-- Print the new JSON
PRINT @cleanData

And the output is;

{“#Name”:”Shahzad”}
{“Contractor”:”Shahzad”}

References

https://learn.microsoft.com/en-us/sql/t-sql/functions/json-modify-transact-sql?source=recommendations&view=sql-server-ver16

Database Guides

Json object from Razor model in Javascript

Let’s begin with a model.

public class RoleModel
{ 
     public int RoleId { get; set; }
     public string RoleName { get; set; }
}

Add some data to the model;

List<RoleModel> roles = new List<RoleModel> {
     new RoleModel{
                     RoleId= 1,
                     RoleName = "Administrator"
                },
     new RoleModel
                {
                     RoleId = 2,
                     RoleName = "Manager"
                }
            };

Pass this to razor view;

@model List<RoleModel>

Declare Javascript Sections in your razor view;

<end of HTML here>
@section Scripts
{
    //go with jQuery ready function
    $(document).ready(function () {
       //get model data here
       var roles = @Html.Raw(Json.Serialize(Model.Roles));

       //iterate through model data

       $.each(roles, function(i, role){
            console.log(role.roleName);
      });
  }
}

This will output this data;

AspNetCore

AspNetCore uses Json.Serialize intead of Json.Encode

var json = @Html.Raw(Json.Serialize(@Model.Roles));

MVC 5/6

You can use Newtonsoft for this:

    @Html.Raw(Newtonsoft.Json.JsonConvert.SerializeObject(Model, 
Newtonsoft.Json.Formatting.Indented))

This gives you more control of the json formatting i.e. indenting as above, camelcasing etc.

what @Html.Raw() and Json.Encode() does

What this code does?

@model CourseVM
<script type="text/javascript">
    var model = @Html.Raw(Json.Encode(Model));
    // go ahead and use the model javascript variable to bind with ko
</script>

Json.Encode serialises the Model to a JSON string. Html.Raw ensures that it is rendered verbatim and isn’t HTML-encoded by Razor. If it is Html-encoded (which Razor does by default) special characters will be converted to their HTML entity representations (e.g. & becomes &amp;). Then the JSON string might not be valid JSON.

There are arguments that encoding protects against script injection and Html.Raw removes that protection.

Html encode() is a built-in feature in MVC so we shouldn’t be worried about script injection in MVC.

URL Matching in C#

To start, let’s define what the internals of a URL looks like:

For our purposes, we care about the scheme, authority, path, query, and fragment. You can think of the scheme as the protocol, i.e., HTTP or HTTPS. The authority is the root or domain, for example, mycompany.com. The path, query, and fragment make up the rest of the URL. The URL spec defines each segment in this specific order. For example, the scheme always comes before the authority. The path comes after the scheme and authority. The query and fragment come after the path if there is one in the URL.

Read this, if thinking about using fragment in URL.

Alice runs a web site, Bob visits it, authenticates and receives a session cookie. (Some time might pass here, Bob might even close his browser.) Charlie sends Bob a mail saying “check out this cool link!”. Bob opens the link, which leads to a site controlled by Charlie. The page redirects Bob’s browser to a page on Alice’s site with an attack payload in the hash. The payload is executed, and since the browser still remembers the cookies, it can just send them to Charlie

https://stackoverflow.com/questions/1822598/getting-url-hash-location-and-using-it-in-jquery

Read more here

Quick layout using pre-defined Json Data

Let’s say we are manually creating Json Data and use ASP.NET Core to quickly come up with a layout. This is the data (JSON output) and final outcome that we expect;

# JSON output

[ { "text": "Head Office", "nodes": [ { "text": "Finance Division", "href": "#parent1", "tags": [ "4" ], "nodes": [ { "text": "Accounting functions", "href": "#child1", "tags": [ "2" ], "nodes": [ { "text": "205", "href": "#grandchild1", "tags": [ "0" ] }, { "text": "206", "href": "#grandchild2", "tags": [ "0" ] } ] }, { "text": "Customer Invoicing", "nodes": [ { "text": 205 }, { "text": 206 } ] }, { "text": "Vendor Invoicing", "nodes": [ { "text": 205 }, { "text": 206 } ] }, { "text": "Banking relationship", "nodes": [ { "text": 205 }, { "text": 206 } ] } ] } ] } ]

Create your test data and drop it under Models, call it DummyData.json. Make sure it’s a valid JSON. These are the lines that we need in controller action method;

var folderDetails = Path.Combine(Directory.GetCurrentDirectory(), $"Models\\{"DummyData.json"}");
var JSON = System.IO.File.ReadAllText(folderDetails);
ViewBag.defaultData = JSON;

return View(result);

In the view, we are rendering ViewBag values in a div HTML tag;

<div id="layoutData">@(ViewBag.defaultData)</div>

We are using jQuery to read this data and use it in any control;

var layoutData = $('#layoutData').text();

You can use layoutData variable anywhere you want. Make sure you are not vulnerable to XSS attacks. For XSS attacks, read this