Saving CR/LF in json data
Solution 1
Don't use JSON.parse
, the data is already JSON and Javascript can work with it.
You only need it when passing a string, imagine JSON.parse()
beeing like string2json()
.
I think this might already be a solution to your problem, I've never had issues with new line characters.
As Luis said, the problem is not your client (Javascript, jQuery), besides the JSON.parse
, but the providing site is wrong.
Example for PHP:
<?php
echo json_encode(array("test" => "
x"));
PHP properly escapes the characters:
{"test":"\r\n\r\n\r\nx"}
But the source of your data is providing malformed JSON.
To fix the JSON issue, either use prepared statements or use:
$notes = str_replace('\', '\\', json_encode($notes)); // in SaveTaskNotes
Solution 2
Well, the error is on the input data (showed in question). You can't have an CR or LF inside a literal in a JSON string. What you can have are that chars escaped as \r
\n
. The problem is on other side, where escaped codes are replaced by actual chars and therefore the full JSON string becomes invalid.
marky
I work as a database migration/conversion programmer for a medical software company in rural central Minnesota. Tools used include SQL Server 2005, 2008 and 2012, VS2013, Tortoise SVN.
Updated on June 28, 2022Comments
-
marky almost 2 years
I am saving table data to a json object. The table data is coming from txt inputs and textareas in the table cells.
I'm running into a problem with CR/LF characters in the JSON elements holding the textarea data. The JSON data gets saved to the database fine, but when I pass it back to the jQuery function that populates the table using that data, I get this:
SyntaxError: JSON.parse: bad control character in string literal at line 1 column 67 of the JSON data var array = JSON.parse(notes),
in the console.
I put the JSON data in Notepad++ with Show All Characters on and the CR/LF was at column 67.
Here's a sample of JSON data that I'm working with:
[["","","",""],["","9/23/14","",""],["","30789 detail, x_vendor_no**CR/LF HERE** 20597 header","",""],["","99 del invalid x_vendor_no","",""],["","30780","",""],["","","",""],["","","",""],["","","",""]]
Is there a way to allow CR/LF in the data?
UPDATE 11684's suggestion to use replace to remove the \r part of the CRLF won't work. Here's why:
Here's the complete function that uses the JSON data: (Updated to work with Update #2 code below)
function PopulateTaskTableWithNotes(tableID,notesArray) { // JSON parse removed per answer suggestion var r, c, note; for (r = 0; r < notesArray.length; ++r) { for (c = 0; c < notesArray[r].length; ++c) { note = notesArray[r][c]; $('#' + tableID + ' tr:nth-child(' + (r + 1) + ') td:nth-child(' + (c + 1) + ')').children(':first-child').val(note); } } }
I still get the error on the line that tries to parse the JSON data. The replace function apparently can't "find" characters within an array element.
UPDATE #2 Here's how I am creating the array:
var siteID = $('#ddlUserSites option:selected').val(), numRows = $('#' + tableID + ' tr').length, numCols = $('#' + tableID).find('tr:first th').length, notesArray = new Array(numRows), rowNum = 1, note = '', colNum; while (rowNum <= numRows) { notesArray[rowNum] = new Array(numCols); // Reset colNum for next row iteration colNum = 1; while (colNum <= numCols) { note = ''; if ($('#' + tableID + ' tr:nth-child(' + rowNum + ') td:nth-child(' + colNum + ')').children(':first-child').is('input,textarea')) { note = $('#' + tableID + ' tr:nth-child(' + rowNum + ') td:nth-child(' + colNum + ')').children(':first-child').val(); } notesArray[rowNum][colNum] = note; //console.log('Note for rowNum ' + rowNum + ', colNum ' + colNum + ': ' + note); colNum++; } // Remove first element in current row array notesArray[rowNum].shift(); rowNum++; } // Remove first element in array notesArray.shift(); JSON.stringify(notesArray); // Added per an answer here console.log('Final notesArray: ' + $.toJSON(notesArray)); $.ajax({ data: {saveTaskNotes: 'true', userID:userID, siteID:siteID, taskTable:tableID, notes:notesArray}, success: function(data) { console.log('Save task notes data: ' + data); } });
The "Final notesArray" console output looks fine, but now, with stringify added, the function above (PopulateTaskTableWithNotes) console output shows that it's reading through every character in the array as a separate element!
Maybe this will help too, as far as what's happening to the data between the creating and reading functions: the array is being saved to a single MySQL database field and then retrieved for the PopulateTable function via $.ajax() (on both ends).
Having said that, do I need to look at what I'm doing with/to the array in the PHP code?
UPDATE #3 Here's the PHP function that takes the data in and writes to the MySQL db:
function SaveTaskNotes($userID,$siteID,$taskTable,$notes) { $notes = json_encode($notes); $insertUpdateTaskNotesResult = ''; $insertTaskNotes = "INSERT INTO userProgress (userProgressUserID,userProgressSiteID,userProgressNotesTable,userProgressNotes) values ($userID,$siteID,'" . $taskTable . "','" . $notes . "')"; $log->lwrite('$insertTaskNotes: ' . $insertTaskNotes); $resultInsertTaskNotes = @mysqli_query($dbc,$insertTaskNotes); if ($resultInsertTaskNotes) { $insertUpdateTaskNotesResult = 'insertTaskNotesSuccess'; } else { if (mysqli_error($dbc) != '') { $log->lwrite('INSERT TASK NOTES: An error occurred while attempting to add the task notes. Query: ' . $insertTaskNotes . ', mysqli_error: ' . mysqli_error($dbc)); } $insertUpdateTaskNotesResult = 'insertTaskNotesFail'; } echo $insertUpdateTaskNotesResult; }
And here's the function that gets the data from the db and sends it to the above $.ajax function:
function GetUserTaskNotes($userID,$siteID,$taskTableID) { $queryGetUserTaskNotes = "SELECT userProgressNotes FROM userProgress WHERE userProgressUserID = $userID AND userProgressSiteID = $siteID AND userProgressNotesTable = '" . $taskTableID . "'"; $log->lwrite('$queryGetUserTaskNotes: ' . $queryGetUserTaskNotes); $resultGetUserTaskNotes = @mysqli_query($dbc,$queryGetUserTaskNotes); if ($resultGetUserTaskNotes) { $taskNotes = mysqli_fetch_assoc($resultGetUserTaskNotes); $log->lwrite('Retrieved $taskNotes[\'userProgressNotes\']: ' . $taskNotes['userProgressNotes']); echo $taskNotes['userProgressNotes']; } else { if (mysqli_error($dbc) != '') { $log->lwrite('GET TASK NOTES: An error occurred while attempting to retrieve the task notes. Query: ' . $queryGetUserTaskNotes . ', mysqli_error: ' . mysqli_error($dbc)); } echo 'getTaskNotesFail'; } }
In both the save and get functions the $log output shows that the array never changes (with the above js/php code) and pasting the array in to notepad++ shows that the CR/LF is still there throughout.