Securely Handling JSON Data with PHP and MySQL

In this blog post, we’ll walk through a PHP script designed to securely receive JSON data via POST requests, validate the data using a security token, and dynamically insert it into a MySQL database. This script is particularly useful for building APIs or web services where data needs to be stored and managed dynamically.

The Code

Below is the PHP code we’ll be discussing:

<?php
// Replace the placeholders with your actual database credentials
$servername = "localhost";
$username = "username";
$password = "password";
$database = "database";

// Security token to verify requests
$security_token = "your_security_token"; // Replace with your own security token

// Create connection
$conn = new mysqli($servername, $username, $password, $database);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Receive the JSON post data
$json_post_data = file_get_contents("php://input");

// Check if security token is present in the URL parameters
if (isset($_GET['token']) && $_GET['token'] === $security_token) {
    // Decode the JSON data
    $json_data = json_decode($json_post_data, true);

    // Check if JSON data is not empty
    if (!empty($json_data)) {
        // Check if table name is provided in the JSON data
        if (isset($json_data['table'])) {
            // Sanitize and escape the table name
            $table_name = $conn->real_escape_string($json_data['table']);

            // Remove 'table' key from JSON data to avoid inserting it into the database
            unset($json_data['table']);

            // Sanitize and escape the remaining JSON data to prevent SQL injection
            $sanitized_data = array_map(function($value) use ($conn) {
                return $conn->real_escape_string($value);
            }, $json_data);

            // Check if the table exists, if not create it
            $sql_create_table = "CREATE TABLE IF NOT EXISTS $table_name (
                row_id INT(9) AUTO_INCREMENT PRIMARY KEY,
                ";
            foreach ($sanitized_data as $column => $value) {
                $sql_create_table .= "$column VARCHAR(255), "; // Adjust data type and size as needed
            }
            $sql_create_table = rtrim($sql_create_table, ", ") . ")";

            if ($conn->query($sql_create_table) === TRUE) {
                echo "Table created successfully.\n";
            } else {
                echo "Error creating table: " . $conn->error . "\n";
            }

            // Prepare placeholders for the values
            $columns = implode(", ", array_keys($sanitized_data));
            $placeholders = str_repeat("?, ", count($sanitized_data) - 1) . "?";

            // Prepare the SQL INSERT statement
            $sql = "INSERT INTO $table_name ($columns) VALUES ($placeholders)";

            // Prepare and bind parameters dynamically
            $stmt = $conn->prepare($sql);
            $types = str_repeat("s", count($sanitized_data)); // Assuming all values are strings, change as needed
            $stmt->bind_param($types, ...array_values($sanitized_data));

            // Execute the SQL statement
            if ($stmt->execute()) {
                echo "Data inserted successfully.";
            } else {
                echo "Error: " . $sql . "<br>" . $conn->error;
            }

            // Close statement
            $stmt->close();
        } else {
            echo "Table name not provided in JSON data.";
        }
    } else {
        echo "No JSON data received.";
    }
} else {
    echo "Unauthorized access.";
}

// Close connection
$conn->close();
?>

Explanation of the Code

Database Connection

The script begins by establishing a connection to the MySQL database using mysqli. The database credentials (server name, username, password, and database name) need to be replaced with your actual database details.

$servername = "localhost";
$username = "username";
$password = "password";
$database = "database";

Security Token Verification

A security token is used to ensure that only authorised requests are processed. The token is passed as a URL parameter and is checked against a pre-defined value.

$security_token = "your_security_token"; // Replace with your own security token

Handling JSON Data

The script reads the incoming JSON data from the request body using file_get_contents(“php://input”). This data is then decoded into a PHP associative array.

$json_post_data = file_get_contents("php://input");
$json_data = json_decode($json_post_data, true);

Dynamic Table Handling

The script checks if a table name is provided in the JSON data. If provided, it sanitises the table name to prevent SQL injection and then dynamically creates the table if it doesn’t already exist. The table’s columns are created based on the keys of the JSON data.

$table_name = $conn->real_escape_string($json_data['table']);

Inserting Data

After creating the table, the script prepares an SQL INSERT statement using placeholders to insert the sanitised data into the table. It binds the parameters dynamically and executes the SQL statement.

$sql = "INSERT INTO $table_name ($columns) VALUES ($placeholders)";

Error Handling and Security

The script includes basic error handling for database connection issues, SQL errors, and unauthorized access attempts.

Bash Payload Example

#!/bin/bash

API_URL="http://your-api-url/post.php?token=your_security_token"
JSON_DATA='{
  "table": "your_table_name",
  "name": "John Doe",
  "age": 30,
  "email": "[email protected]",
  "city": "Sydney"
}'

curl -X POST \
  -H 'Content-Type: application/json' \
  -d "$JSON_DATA" \
  "$API_URL"

Notes

This script provides a simple yet flexible solution for receiving JSON data and inserting it into a MySQL database. By using a security token and dynamically handling table creation, it adds a layer of security and adaptability to your PHP applications. This approach is useful for applications that need to handle dynamic data structures, making your system more resilient and easier to maintain.