SyntaxError: "JSON.parse: unexpected non-whitespace ..." when returning JSON from PHP

Question

I'm running into an issue where the JSON returned from a PHP query is not valid and I'm not really sure why; I'm still learning. When the datatype is excluded the below code returns:

{"Customer_ID":"0", "FirstName":"John", "LastName":"Smith"}
{"Customer_ID":"1", "FirstName":"Jane", "LastName":"Smith"}

otherwise it returns:

SyntaxError: "JSON.parse: unexpected non-whitespace character after ..."

I thought this might be because the record is not being returned in a single JSON response, but I can't see that being the issue as the concurrent responses are JSON. Any ideas? Any suggests? Feel free to point out the semantic issues.

HTML:

getRecord("*", "customer", "");

JavaScript:

function getRecord(field, table, condition) {
    var request = $.ajax({
        url: "./handler.php",
        method: "GET",
        dataType: "JSON",
        cache: "false",
        data: {
            action: "SELECT",
            field: `${field}`,
            table: `${table}`,
            condition: `${condition}`,
        },
    });

    request.done(function(data, status, xhr) {
        console.log(data, status, xhr);
    });

    request.fail(function(xhr, status, error) {
        console.log(xhr, status, error);
    });

};

PHP:

<?php

    # IMPORT SETTINGS.
    include "settings.php";

    # FUNCTION DISPATCHER.
    switch($_REQUEST["action"]) {

        case "SELECT":
            getRecord($conn);
            break;

        default:
            printf('Connection Error: Access Denied.');
            mysqli_close($conn);
    }

    # LIST OF COLUMNS THAT WE NEED.

    function getRecord($conn) {
        $table = $_REQUEST["table"];
        $field = $_REQUEST["field"];
        $condition = $_REQUEST["condition"];

        if (!empty($condition)) {
            $query = "SELECT $field FROM $table WHERE $condition";
        } else {
            $query = "SELECT $field FROM $table";
        }

        if ($result = mysqli_query($conn, $query)) {
            while ($record = mysqli_fetch_assoc($result)) {
                echo json_encode($record);
            }
        }

        # CLOSE THE CONNECTION.
        mysqli_close($conn);

    }

?>

Note that a statement like `$query = "SELECT $field FROM $table WHERE $condition";` is not safe at all. Hackers will be able to execute any SQL command with such. You need to verify every single parameter (i.e. is `$table` really the name of a table?) See [SQL Injection](https://en.wikipedia.org/wiki/SQL_injection). — Alexis Wilke, Feb 17 '19 at 23:17
@AlexisWilke that was something I started to realize, I just wanted to get past this error to figure out what I was doing wrong. My hope is to keep things as dynamic as possible, but I'm not sure how exactly. One idea was to white list tables, but I'm not sure if that is possible. Any suggestions to keep the code dynamic? — artomason, Feb 17 '19 at 23:23
Yes, a list of your tables and checking whether `$table` is one of those names will work. The problem for `$field` is that you're going to have to enter all the names of all the fields from all your tables (you can also do a `SELECT` against MySQL to get the info, but then it's time spend each time you want to access the database!) On my end, I often use `*` for the field because loading the entire once is going to be much faster then load one field at a time. Now you get another problem: you probably don't want to return a user's password... (even an encrypted one.) — Alexis Wilke, Feb 17 '19 at 23:26

Nick · Accepted Answer · 2019-02-17T23:22:03.833

5

Your JSON is not valid because it consists of multiple objects. What you need to do is put all your results into an array and then echo the json_encode of that. Try something like this:

    $records = array();
    if ($result = mysqli_query($conn, $query)) {
        while ($records[] = mysqli_fetch_assoc($result)) {
        }
    }
    echo json_encode($records);

This will give you an output that looks something like this:

[
    {"Customer_ID":"0", "FirstName":"John", "LastName":"Smith"},
    {"Customer_ID":"1", "FirstName":"Jane", "LastName":"Smith"}
]

and you can access each of the elements in your Javascript by something like

let customer = data[0].FirstName + ' ' + data[0].LastName;

edited Feb 17 '19 at 23:22

answered Feb 17 '19 at 23:14

Nick

118,076
20
42
73

I planned on using `$.each()` for the return data, but your answer fixed the issue; thank you. Now I need to figure out how to keep things dynamic and still prevent vulnerabilities. – artomason Feb 17 '19 at 23:25
@artomason yeah, `$.each()` will work fine over that data. [This question](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) has a good discussion on how to prevent SQL injection – Nick Feb 17 '19 at 23:33

SyntaxError: "JSON.parse: unexpected non-whitespace ..." when returning JSON from PHP

1 Answers1

Linked

Related