PHP json_encode() adding extra double-quotes around Array Elements

Question

I have an Array named $items that looks like the following:

array   (
            0 => '{"id":"1", "qty":"1", "price":"12.51"}',
            1 => '{"id":"2", "qty":"2", "price":"25.02"}',
        )

I'm trying to build a mySQL INSERT statement which includes the data in $items as follows:

$sql = 'INSERT INTO vals (id, items, timestamp) 
        VALUES (' . $id . ', "' . json_encode($items) . '", "' . date('Y-m-d H:i:s')  . '")';

However, the INSERT into mySQL is failing due to json_encode() adding double-quotes around the Array Elements:

INSERT INTO
            vals
                    (
                        id,
                        items,
                        timestamp
                    )
        VALUES
                    (
                        1,
                        "[
                            "{\"id\":\"1\", \"qty\":\"1\", \"price\":\"12.51\"}",
                            "{\"id\":\"2\", \"qty\":\"2\", \"price\":\"25.02\"}"
                        ]",
                        "2015-11-26 20:31:02"
                    )  

It's the double-quotes before/after the curly-braces "{ ...data... }" that are the problem.

Is there a way to convert the Array to a String that will elimnate these extra quotes?

Thanks much for any guidance!

EDIT:

From the examples below, I'm trying to use mysqli prepared statements.

I'm executing the following:

$stmt->bind_param("i", (int) $id)

and am getting this error:

ERROR: exception 'Symfony\Component\Debug\Exception\FatalErrorException' 
with message 'Call to a member function bind_param() on a non-object'

I didn't get an error executing the following:

$stmt = $mysqli->prepare($sql)

so I'm thinking $stmt should be okay to call bind_param() on.

I looked at the PHP docs and don't believe I need to do anything else with $stmt. Does anyone see something I'm missing?

Answer 1

You either need to escape your json_encode'd string for use in the query, or use a prepared statement and bind the value to a parameter.

Your quotes around the individual array items are actually required as your individual array items are in fact strings... so all you need is to escape the entire value...

So either:

$sql = 'INSERT INTO vals (id, items, timestamp)
        VALUES (' . $id . ', "' . mysql_real_escape_string(json_encode($items)) . '", "' . date('Y-m-d H:i:s')  . '")';

or a better way of doing this:

$json = json_encode($items);
$sql = 'INSERT INTO vals (id, items, timestamp) VALUES (?, ?, ?)';

/* Prepared statement, stage 1: prepare */
if (!($stmt = $mysqli->prepare($sql))) {
    echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

/* Prepared statement, stage 2: bind and execute */
if (!$stmt->bind_param("i", $id)) {
    echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
}
if (!$stmt->bind_param("s", $json)) {
    echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
}
if (!$stmt->bind_param("s", date('Y-m-d H:i:s'))) {
    echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
}

if (!$stmt->execute()) {
    echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
}

EDIT

Also, @JoshJ is correct about your values being JSON strings... re-encoding a Json string will double escape everything. If this is not your intended use (i.e. retrieving the values as strings), then you should in fact try decoding each string to an associative array using:

foreach ($items as $key => $item) {
    $items[$key] = json_decode($item, true);
}

This will give you an $items value of:

[
    0 => [
        "id" => "1",
        "qty" => "1",
        "price" => :"12.51"
    ],
    1 => [
        "id" => "2",
        "qty" => "2",
        "price" => "25.02"
    ]
]

There are other options of course for treating numeric string s as numbers in the output which you may also want to investigate...

See: http://php.net/manual/en/function.json-decode.php

EDIT 2 In Symfony, you may need to use the PDO bindParam() function syntax.

$sql = 'INSERT INTO vals (id, items, timestamp) VALUES (:id, :items, :datetime)';
$stmt = $pdoconnection->prepare($sql);
$stmt->bindParam(':id', $id);
etc...

See: http://php.net/manual/en/pdostatement.bindparam.php and http://php.net/manual/en/class.pdostatement.php

Answer 2

The simplest and safest answer for this is to switch to prepared statements. Documentation for that can be found here

If that doesnt work right with you, you can use simple built in methods for escaping characters.

If you are using MYSQL, you can make use of the mysql_real_escape_string function — It escapes special characters in a string for use in an SQL statement.

Be carefull, as this extension (MYSQL) was deprecated in PHP 5.5.0, and it was removed in PHP 7.0.0.

for MYSQLi, use mysqli::real_escape_string

A use case for your scenarial, i assume you are using the mysql extension:

$sql = 'INSERT INTO vals (id, items, timestamp) VALUES (' . $id . ', "' .mysql_real_escape_string(json_encode($items)). '", "' . date('Y-m-d H:i:s')  . '")';

You can even use php's addslaches() function documented here. What it does is that it: returns a string with backslashes before characters that need to be escaped. These characters are single quote ('), double quote ("), backslash () and NUL (the NULL byte).

Answer 3

Note that from PHP 5.3.3 on, there's a flag for auto-converting numbers, while options parameter was added since PHP 5.3.0:

$arr = array( 'row_id' => '1', 'name' => 'George' );
echo json_encode( $arr, JSON_NUMERIC_CHECK ); // {"row_id":1,"name":"George"}