Convert PHP Array to JSON Array and get Response with SLIM3

Question

I have problems with converting the result of a database query to a json array. My Output looks like this:

{
  "user_id": "1",
  "username": "testuser1",
  "user_permissions": [
    {
      "list_id": "1"
    },
    {
      "list_id": "2"
    },
    {
      "list_id": "3"
    }
  ],
  "android_app_id": null,
  "iat": 1537694955,
  "exp": 1537702155
}

The brackets ({}) around the JSON array lead to problems parsing the array in the client's response. I just need to have a simple array like (1, 2, 3).

The Array is produced by database query result (PHP) and then using SLIM3 Operation $this->response->withJson:

$query = "SELECT list_id FROM permissions WHERE user_id='$user_id'";
$sth = $this->dbconnection->prepare($query);
$sth->execute();
$result_permissions = $sth->fetchAll();
return $result_permissions;

I really have problems to convert database results to normal JSON arrays, because PHP only knows associative arrays (numeric or with keys) which leads to bad formatted json arrays.

The json Output is returned to the server. Using SLIM3 Framework I access the JSON Data and the permissions array like this: $user_permissions = $decoded_response['user_permissions']; Now i try to get list-ids with $user_permissions[list'id][0] which gives 1 using print_r command.

What I want to do next is using a database query with IN Operator to check for the permission_ids. Therefore I need to produce an array like (1, 2, 3).. I'm stuck right now, because I don't know how to produce such array from the JSON..

For me the easiest approach would be to directly produce such an array after the database query and add it to the JSON at the beginning but I don't know how to achieve that.

Any hints?

the JSON you posted is perfectly valid. Could you clarify your question ? — YvesLeBorg, Sep 23 '18 at 11:39

Salvatore Q Zeroastro · Answer 1 · 2018-09-23T11:12:33.600

1

If I understood what you need to achieve, you can use array_column php function to get an array of list_ids http://php.net/manual/en/function.array-column.php

$json = '{
    "user_id": "1",
    "username": "testuser1",
    "user_permissions": [
        {
            "list_id": "1"
        },
        {
            "list_id": "2"
        },
        {
            "list_id": "3"
        }
    ],
    "android_app_id": null,
    "iat": 1537694955,
    "exp": 1537702155
    }
';

$arrayFromJson = json_decode($json, true);

$ids = array_column($arrayFromJson['user_permissions'], 'list_id');

print_r($ids);

The output of the print_r will be

Array
(
    [0] => 1
    [1] => 2
    [2] => 3
)

To get a string like (1,2,3) you can use the php implode function https://secure.php.net/manual/en/function.implode.php

$inString= "(" . implode(",", $ids) . ")";

You will get a string like this: (1,2,3).

Keep in mind that using directly variables into a SQL query leads to SQL injection vulnerabilities

edited Sep 23 '18 at 11:12

answered Sep 23 '18 at 10:37

Salvatore Q Zeroastro

758
1
8
17

This could help but I still need to get an array (or String) like this (1, 2, 3) to use with the IN Operator for my next Database query to check the permissions on serverside.. – NKnuelle Sep 23 '18 at 10:57
Thank you I'll give it a try! Regarding the SQL injection: If use bindParam and bind the strong it is secure right? Ich also get the values from a token that I issued from the server itself. So it should not be that insecure. – NKnuelle Sep 23 '18 at 11:24
Yeah, but in this case you can't bind the whole `$inString` because will add quotes to the whole string ( `'(1,2,3)'` ) which is an invalid SQL IN syntax. you need to bind each value in the parenthesis separately https://stackoverflow.com/questions/920353/can-i-bind-an-array-to-an-in-condition – Salvatore Q Zeroastro Sep 23 '18 at 11:30

score 0 · Answer 2 · answered Sep 23 '18 at 11:53

For anyone with the same problem, here is what I did to produce the array:

      $query = "SELECT list_id FROM permissions WHERE user_id='$user_id'";
      $sth = $this->dbconnection->prepare($query);
      $sth->execute();
      $result_permissions;
      for ($i = 0; $i < $sth->rowCount(); $i++) {
        if ($i == $sth->rowCount() - 1) {
          $result = $sth->fetchColumn();
          $result_permissions .= $result;
        } else {
          $result = $sth->fetchColumn();
          $result_permissions .= $result . ",";
        }
      }
      return explode(',', $result_permissions);

If you json_encode this String it will lead to this: {user_permissions":["1","2","3"]} which is what I needed.

@DanielO Thanks. I already changed this with bindParam. Additionally this user id is issued by the server itself inside a jwt token so the risk of SQL injection is minimized. — NKnuelle, Sep 25 '18 at 07:15

Convert PHP Array to JSON Array and get Response with SLIM3

2 Answers2