Server Side Processing Seems Returns Invalid JSON If Double Quotes Are Returned From The DB

gbyrd

Hello,

After a lot of testing and attempting to nail down where my problem is coming from, I have pretty much narrowed it down to he following:

I am using the server side processing to handle a large and growing database currently about 30k entries. The database contains Titles of News Articles and the URL to that article.

IF I display ONLY the url to the article, then everything works beautifully as expected.
IF I include the Title of article then things start to break (not work). I will get the error about "Invalid JSON Response".

NOTE: The Invalid JSON message ONLY appears when I include the title and not with any other columns in the table and it seems to only happen when a title has double quotes in the title. Single quotes works fine, but if the title has a double quote then I get the invalid response.

My question is, is there a work-around for this in DataTables? or Do I need to consider replacing all double quotes with single quotes in the database?

allan

It sounds like however you are doing the JSON stringify is broken. How is that being done at the moment? Are you using a library to do it, or are you building the JSON string yourself?

Allan

gbyrd

I am using the example files from this page: https://datatables.net/examples/server_side/simple.html with nothing changed except for my login credentials and column name(s).

I also figured out that there are other characters that the script will get hung up on as well.
I think it has to do with smart quotes and "smart" apostrophes and a few other characters. (Note image)

After I went through and did a "Find/Replace" for all these characters then the script would not throw the JSON error. This is not an ideal solution though because I would have to manually do this for each entry going forward so I am hoping that there is something in the server side script that I can add/edit to accommodate for all these characters.

tangerine

Couldn't you prevent those characters from getting entered into the database in the first place?

gbyrd

Hi,

No, There is no way I can do that. The process is as follows:

1. Users find an article they want to share
2. They go to my website to shorten the url and then share it

They database, retrieves the name of the article (or title of webpage) along with the url and stores it in a database.
I am trying to now to implement a search and sort feature but unruly characters keep messing the process up.

tangerine

So whose code is responsible for updating the database?

allan

Smart quotes are perfectly valid in JSON. Can you show me your serverside script please?

Allan

gbyrd

Sure ... It's too long to paste in a comment so I will link to the specific page ...

First one:
https://github.com/DataTables/DataTablesSrc/blob/master/examples/server_side/scripts/ssp.class.php

Second one here:

<?php

/*
 * DataTables example server-side processing script.
 *
 * Please note that this script is intentionally extremely simple to show how
 * server-side processing can be implemented, and probably shouldn't be used as
 * the basis for a large complex system. It is suitable for simple use cases as
 * for learning.
 *
 * See http://datatables.net/usage/server-side for full details on the server-
 * side processing requirements of DataTables.
 *
 * @license MIT - http://datatables.net/license_mit
 */

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * Easy set variables
 */

// DB table to use
$table = 'links';

// Table's primary key
$primaryKey = 'id';

// Array of database columns which should be read and sent back to DataTables.
// The `db` parameter represents the column name in the database, while the `dt`
// parameter represents the DataTables column identifier. In this case simple
// indexes
$columns = array(
    array( 'db' => 'title', 'dt' => 0 )

);

// SQL server connection information
$sql_details = array(
    'user' => 'xxxx',
    'pass' => '*xxxx',
    'db'   => 'xxxx',
    'host' => '108.xxx.xxx.xxx'
);


/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * If you just want to use the basic configuration for DataTables with PHP
 * server-side, there is no need to edit below this line.
 */

require( 'ssp.class.php' );

echo json_encode(
    SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
);

allan

The fact that you are using json_encode() is good. It makes me assume that the problem is going to be in the database / PHP character encoding bridge.

On line 416 of the demo SSP script, could you add:

$db->exec("set names utf8");

That will hopefully fix it.

Allan

gbyrd

That did the trick ...
Many thanks!