Why is content hidden if wrapped in HTML/XML markup?

rldean1

I'm using DT and Editor.

I was testing to see if special characters like </> will break my application. If I include markup while editing a row, like this <stuff>THING</stuff>, "THING" is always displayed, but </stuff> is missing in the DataTable view. However, everything IS displayed while editing (see screenshot), either in Inline or Full Row edit modes. Our preference is to display all characters, to include </stuff>. I should note that the entire string IS sent and recorded into the database. It doesn't matter if it's an inline edit, or a full row edit.

Here's what's captured, and sent to the SQL server for processing:

{
    "DT_RowId": "79ED6880-0148-E811-8102-5CB9019B7A40",
    "PosTitle": "Administrative Assistant I <stuff>HIDDEN?</stuff>",

}

Here's what SQL server sends back (error and fieldErrors are automatically added by Editor):

{
    "data": [
        {
            "DT_RowId": "79ED6880-0148-E811-8102-5CB9019B7A40",
            "PosTitle": "Administrative Assistant <stuff>HIDDEN?</stuff>",

        }
    ],
    "error": "",
    "fieldErrors": []
}

allan

Can you show me your server-side code please?

Allan

rldean1

@allan Sure... I've tried to simplify this as pseudo-code to make it concise as possible. I've omitted columns, and I've tried to put it in order. I hope it makes sense.

I guess the main thing to consider is that our framework deals mainly in XML. I've had success with wrapping JSON strings inside XML like a burrito, and it's worked so far. If I look at what's being passed back-and-forth between SQL & JS, the JSON string fully maintains this entire character string: <stuff>HIDDEN?</stuff>. What I mean is: I feel confident that the JSON string is being maintained inside of the XML. I've even tried using FOR XML EXPLICIT and CDATA, but that didn't seem to make a difference.

SQL SERVER PROCESSING

declare
    @jsonResponse nvarchar(max) = ''
    ,@jsonRequest nvarchar(max) = ''
        ,@AppDataText nvarchar(max) = ''

    /*EXAMPLE EDIT ACTION, SQL*/

    begin

        --clear the output table
        delete from @rowAltered

        --update tblJobSpecs from source: Editor jsonRequest
        --save results to output table
        update tblJobSpecs
        set
            PosTitle = case when source.PosTitle is null then tblJobSpecs.PosTitle else source.PosTitle end,
            PosCode = case when source.PosCode is null then tblJobSpecs.PosCode else source.PosCode end
        output inserted.* into @rowAltered  /*save action to an output table later used to construct response TO Editor*/
        from openjson(@jsonRequest, '$')
        with 
        (
            DT_RowId uniqueidentifier '$.DT_RowId',
            PosTitle varchar(80) '$.PosTitle',
            PosCode varchar(5) '$.PosCode'

        ) as source
        where tblJobSpecs.id = source.DT_RowId

        --use output table to construct JSON obj for Editor
        select @jsonResponse = (
            select
                id as [DT_RowId],
                PosTitle,
                PosCode
            from @rowAltered
            for json path, root('data'), INCLUDE_NULL_VALUES
        )

        --wrap in AppDataText response
        select @AppDataText = (

            select @jsonResponse
            for xml path (''), root('jsonResponse')
        )
    end

//example response SQL
{
    "data": [
        {
            "DT_RowId": "74ED6880-0148-E811-8102-5CB9019B7A40",
            "PosTitle": "Account <stuff>HIDDEN<\/stuff>",
        }
    ]
}

/*EXAMPLE ACTION POPULATE DATATABLE*/

    begin

        select @jsonResponse = (    

            select
                id as DT_RowId,
                PosTitle,
                PosCode
            from
                dbo.tblJobSpecs
            order by
                PosTitle,
                PosCode
            for json path, INCLUDE_NULL_VALUES
        )

        select @AppDataText = (

            select @jsonResponse
            for xml path (''), root('tblJobSpecs')
        )

    end

//example response from SQL
[
     { ... },   
     {
        "DT_RowId": "79ED6880-0148-E811-8102-5CB9019B7A40",
        "PosTitle": "Administrative Assistant <stuff>HIDDEN?<\/stuff>",
     },
     { ... },
]

JS APP

//Editor AJAX interceptor
//data = stuff from Editor AJAX interceptor (item currently being edited)

else if (data.action === 'edit') {

    obj = {};

    $.each(data.data, function (i) {
        obj.DT_RowId = i;
        $.each(data.data[i], function (key, val) {
            obj[key] = val;
        });
    });

}

//example JS to SQL structure
{
    "DT_RowId": "74ED6880-0148-E811-8102-5CB9019B7A40",
    "PosTitle": "Account <stuff>FANCY</stuff>",
    "Publish": "true"
}

//doResults processor
//options = contains the successCB from Editor's AJAX interceptor
//data & data.xmldata = xmlDocument from SQL stored procedure

doResults: function (data, options) {

    //POPULATE THE DATATABLE
    case "init":

        var jsonString, aoo = [];
        var jsonString = $(data.xmldata).find("tblJobSpecs").text();
        aoo = JSON.parse(jsonString);

        // initialize table
        table = $('#example').DataTable({
            dom: "Bfrtip",
            data: aoo,
            columns: [
                { data: 'PosTitle', title: 'PosTitle' },
                { data: 'PosCode', title: 'PosCode' }
            ],
        });
    break;

    //EDIT HANDLER
    case "edit":

        if (typeof options === 'function') {
            //Editor's successCB function
            successCB = options
        }

        jsonString = $(data.xmldata).find("jsonResponse").text();
        obj = JSON.parse(jsonString);

        successCB(obj)

        break;
}

allan

Thanks! I think I'm with you now.

DataTables doesn't escape HTML entities by default when drawing the data to a cell. You can have it do that using the text renderer that is built in which it sounds like what you need in this instance.

Allan