Home-grown web service I needed to build a one-way web service for a mobile app I developed. Its only goal is to receive incoming JSON data and insert it into the correct tables. It works, but it looks very "kludgy" and cobbled together (happens a lot with solo development). 

I'd like another pair of eyes to see where it could be improved as far as design, robustness, readability, etc. This code is not yet in production, so I can implement suggested changes as needed.

&lt;?php

$body = file_get_contents("php://input");
$json = json_decode($body, true);
//print_r($json);


function extractArray($arr)
{
    foreach ($arr as $k =&gt; $v) {
        if (is_array($v)) {
            extractArray($arr[$k]);
            foreach ($v as $kk =&gt; $vv) {
                if (is_array($vv)) {
                    unset($arr[$k][$kk]);
                    $arr[$kk] = $vv; // if you want the key to the array to be preserved.
                    // $arr[] = $vv; // would be safer, or check isset($arr[$kk]) first.
                }
            }
        }
    }
    return $arr;
}


function dbInsert(&amp;$data)
{
    $tracks = array_shift($data);

//    //remove unneeded parent array
    $tracks = extractArray($tracks);

    /*
     * Note: user_id/page_id purposely unset.  Why? On the client side, Backbone.js
     * creates a model ID if one is not created manually. So, we create one on
     * the client side, but then remove it on the server side. Saves a lot of hassle otherwise.
     * db creates autoincrement id.
     * */
    unset($tracks['user_id']);


    $trackCols = array_keys($tracks);
    $trackVals = array_values($tracks);

    //connect to db
    $dbh = new PDO('mysql:host=localhost;dbname=kpl', 'root', 'root');
    $dbh-&gt;setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    //insert tracker data
    try {
        $sth = $dbh-&gt;prepare("INSERT INTO tracker (" . implode(", ", $trackCols) . ") VALUES ('" . implode("', '", $trackVals) . "')");

        $sth-&gt;execute();

        $tracksPK = $dbh-&gt;lastInsertId();
    } catch (PDOException $e) {
        return print_r('Tracker insert failed: ' . $e-&gt;getMessage());
    }

    $pages = array_pop($data);

    foreach ($pages as &amp;$page) {
        unset($page['page_id']);
        $pageCols = array_keys($page);

        /*
         * Note: Could not use same query structure as trackers because the foreign key user_id
         * is set dynamically.
         * */
        try {
            $sth = $dbh-&gt;prepare("INSERT INTO page (" . implode(", ", $pageCols) . ") VALUES (" . $page['view_count'] . "," . $page['visit_timestamp'] . ",'" . $page['page_viewed'] . "'," . $tracksPK . ",'" . $page['model_type'] . "')");

            $sth-&gt;execute();

        } catch (PDOException $e) {
            return print_r('Page insert failed: ' . $e-&gt;getMessage());
        }

    }

    return true;
}

if (isset($json)) {

    $info = dbInsert($json);

    print_r($info);

} else {
    die('JSON data fail');
}
There are some big holes in your security 


Authenticate the data source. (Is it a legitimate source, username/password/etc)
Sanitize the user data.


Check $trackCols against a list of valid column names you are expecting, to stop me doing things like I have below.

$valid_cols = array('id', 'user', 'name');
foreach ($trackCols as $col) {
    if (!in_array($col, $valid_cols)) {
        die("Alert! Unexpected Column $col");
    }
}


PDO allows you to bind variables to the statement, and it will escape any dangerous data.
This will help stop people from creating attacks as I have below.

$sth = $dbh-&gt;prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories &lt; :calories AND colour = :colour');
$sth-&gt;bindParam(':calories', $calories, PDO::PARAM_INT);
$sth-&gt;bindParam(':colour', $colour, PDO::PARAM_STR, 12);
$sth-&gt;execute();


I have included a test script below (DO NOT RUN AGAINST YOUR LIVE DATABASE)

I have manufactured some bad json data so that it will execute a delete statement on the tracker table in your database. 

Obviously it won't work unless I get the table and column names correct, but as your code is now, if I try a dummy column name it will give me back an sql error message, which can help me to narrow it down.

Log the error message instead of returning them. This is an API, all the client end needs to know is that it failed. Don't give the attacker any more information then necessary.

&lt;?php

// WARNING MAKE SURE YOU ONLY RUN THIS AGAINST A TEST DATABASE

$bad_sql = 'id) VALUES (1); DELETE FROM tracker; INSERT INTO tracker(id';
$json = json_encode(array(array($bad_sql =&gt; '99999')));

$ch = curl_init();

curl_setopt($ch, CURLOPT_URL,            "http://yourdomain/api.php" );
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1 );
curl_setopt($ch, CURLOPT_POST,           1 );
curl_setopt($ch, CURLOPT_POSTFIELDS,     $json);
curl_setopt($ch, CURLOPT_HTTPHEADER,     array('Content-Type: application/json'));

$result=curl_exec ($ch);

var_dump($result);