0

I want to make a json request with jquery to populate a wysihtml5 textarea but it doesn't work

$("#calendarAnchor").click(function(event) {
            $.getJSON( "php/databaseHandler.php?action=GET_DATE_DETAILS&day=1&month=11&year=2014&username=bd107a66ba", function( json ) {
                alert( "JSON Data: " + json ); // not executed
            });             
        });

When i put my json in jsonlint then it doesn't validate

({'id': '1124','day': '1','month': '11','year': '2014','red_day': 'ja','name_day': ['Allhelgona'],'images':[{'id': '2','url': 'svala_mini15.png','description': 'Idag är det soligt','category_id': '1','slot_id': '0'},{'id': '1','url': 'img/arstider/host/Flyttfagel_sadesarla_mini15.png','description': 'Idag regnar det, men vi har kul ändå!','category_id': '1','slot_id': '1'}],'holidays':[{'id': '1','name': 'Allhelgon','description': 'Nu firar man!'}],'dateDescription':[{'description': 'Idag!'}]})

WWhy is the json not valid? Should I change the output or use jquery some other way to read the fields? The php that outputs the json is

function getDateDetails($day, $month, $year, $username, $db){
$sql = <<<SQL
SELECT calendar_dates.id, 
calendar_dates.day, 
calendar_dates.month, 
calendar_dates.year,
calendar_dates.name_day,
calendar_dates.red_day 
FROM calendar_dates 
WHERE calendar_dates.day=$day 
AND calendar_dates.month=$month 
AND calendar_dates.year=$year
SQL;

    $ret = "{";
    $isFirst = true;

    if(!$result = $db->query($sql)){
        die('There was an error running the query [' . $db->error . ']');
    }

    while($row = $result->fetch_assoc()){
        if($isFirst){
            $names = join("','", array_filter(explode(";", "'" . $row['name_day'] . "'")));
            $imagesJson = getImagesJson($row['id'], $username, $db);
            $holidaysJson = getHolidaysJson($row['id'], $username, $db);
            $dateDescriptionJson = getDateDescriptionJson($row['id'], $username, $db);

            $ret .= "'id': '" . $row['id'] . "',";
            $ret .= "'day': '" . $row['day'] . "',";
            $ret .= "'month': '" . $row['month'] . "',";
            $ret .= "'year': '" . $row['year'] . "',";
            $ret .= "'red_day': '" . $row['red_day'] . "',";
            $ret .= "'name_day': [";


            $ret .= $names;             
            $ret .= "],";
            $ret .= "'images':";
            $ret .= $imagesJson . ",";
            $ret .= "'holidays':";
            $ret .= $holidaysJson . ",";
            $ret .= "'dateDescription':";
            $ret .= $dateDescriptionJson . ",";
            $isFirst = false;
        }

    }

    $ret = rtrim($ret, ",");
    $ret .= "}";

    return($ret);
}

Update

With help from Dave, this is valid json

{
    "id": "1124",
    "day": "1",
    "month": "11",
    "year": "2014",
    "red_day": "ja",
    "name_day": [
        "Allhelgon "
    ],
    "images": [
        {
            "id": "2",
            "url": "mini15.png",
            "description": "Idag är det soligt!",
            "category_id": "1",
            "slot_id": "0"
        },
        {
            "id": "1",
            "url": "sadesarla_mini15.png",
            "description": "Idag regnar det!",
            "category_id": "1",
            "slot_id": "1"
        }
    ],
    "holidays": [
        {
            "id": "1",
            "name": "Allhelgon",
            "description": "Nu!"
        }
    ],
    "dateDescription": [
        {
            "description": "Idag!"
        }
    ]
}

My new php (still injection vulnerable from users.username = '$username' ?) is

function getDateDescription($dateId, $username, $db){
$sql = <<<SQL
SELECT calendar_date_description.description
FROM calendar_date_description
INNER JOIN calendar_users
ON calendar_users.username = '$username'
WHERE calendar_date_description.user_id= calendar_users.id
AND calendar_date_description.date_id = $dateId
SQL;

$ret = "[";
$description ="";
    if(!$result = $db->query($sql)){
        die('There was an error running the query [' . $db->error . ']');
    }

    while($row = $result->fetch_assoc()){
        $description = $row['description'];
    }

    return($description);   
}

function getDateDetails($day, $month, $year, $username, $db){

$sql = <<<SQL
SELECT calendar_dates.id, 
calendar_dates.day, 
calendar_dates.month, 
calendar_dates.year,
calendar_dates.name_day,
calendar_dates.red_day 
FROM calendar_dates 
WHERE calendar_dates.day=$day 
AND calendar_dates.month=$month 
AND calendar_dates.year=$year
SQL;

    //$ret = "{";
    $isFirst = true;

    if(!$result = $db->query($sql)){
        die('There was an error running the query [' . $db->error . ']');
    }

$ret = array();

while ($row = $result->fetch_assoc()) {
    $names               = array_filter(explode(";", $row['name_day']));
    $dateDescriptionJson = getDateDescription($row['id'], $username, $db);

    $ret = array(
        'id'              => $row['id'],
        'day'             => $row['day'],
        'month'           => $row['month'],
        'year'            => $row['year'],
        'red_day'         => $row['red_day'],
        'name_day'        => $names,
        'dateDescription' => $dateDescriptionJson
    );
}

$ret = json_encode($ret);

return ($ret);
}
Niklas Rosencrantz
  • 25,640
  • 75
  • 229
  • 424
  • 1
    JSON requires double quotes for property names and values. That's why it's better to use appropriate JSON libraries to create JSON strings, namely json_encode. – Dave Chen Oct 20 '14 at 08:10
  • 1
    see: http://stackoverflow.com/questions/14355655/jquery-parsejson-single-quote-vs-double-quote – secelite Oct 20 '14 at 08:11
  • Thank you. Now I could replace `'` with `"` and then it validates. – Niklas Rosencrantz Oct 20 '14 at 08:13
  • 1
    Also it can only start with [] for arrays or {} for an object, it can't start with () – Tomer Oct 20 '14 at 08:13
  • 2
    injection vulnerabilities detected + why would anyone string together JSON manually, when there is such a thing as `json_encode`? – Elias Van Ootegem Oct 20 '14 at 08:13
  • 1
    @fatman: And it doesn't, the `()` in the `return` statement are just redundant, but not part of the returned string – Elias Van Ootegem Oct 20 '14 at 08:14
  • Thanks for the help. I inherited the code. Now I changed it and I get valid json using the code from @DaveChen in the answer. My new problem is that foreign chars don't work ("åäö...") but that is a different question. – Niklas Rosencrantz Oct 20 '14 at 09:07

1 Answers1

1

I'm glad to have helped but I was actually recommending json_encode. The problem is from single quotes but the underlying issue is that you're constructing this complex JSON string by hand. Sometimes I do this, but only in extremely rare cases such as one level JSON strings, {"error":"true"}, but this is not the case for you.

Please try this method instead:

$ret = array();

while ($row = $result->fetch_assoc()) {
    $names               = array_filter(explode(";", $row['name_day']));
    $imagesJson          = getImages($row['id'], $username, $db);
    $holidaysJson        = getHolidays($row['id'], $username, $db);
    $dateDescriptionJson = getDateDescription($row['id'], $username, $db);

    $ret = array(
        'id'              => $row['id'],
        'day'             => $row['day'],
        'month'           => $row['month'],
        'year'            => $row['year'],
        'red_day'         => $row['red_day'],
        'name_day'        => $names,
        'images'          => $imagesJson,
        'holidays'        => $holidaysJson,
        'dateDescription' => $dateDescriptionJson
    );
}

$ret = json_encode($ret);

return ($ret);

I would highly recommend keeping everything in the PHP array format, then when the data is ready to be outputted to the client, then do a final json_encode.

So this would mean:

getImagesJson       -> getImages
getHolidaysJson     -> getHolidays
dateDescriptionJson -> dateDescription

These functions will then return PHP arrays instead of JSON. This will allow you to manipulate the data across functions easier. Simply apply the same techniques to those functions and this will make your life so much easier.

Dave Chen
  • 10,887
  • 8
  • 39
  • 67