How to insert json array into mysql database
When you receive a JSON array from a mobile application, such as the example [{"name":"0","phone":"dsf",...}], and attempt to persist that data using PHP, the default behavior of json_decode is to return an associative array. This structure allows you to iterate over the items easily. However, simply looping through this array and executing raw SQL queries inside the loop introduces significant security vulnerabilities and architectural fragility.
The most immediate issue in the provided code snippet is the use of the deprecated mysql_* functions. These extensions were removed from PHP core in version 5.5. They lack support for prepared statements, which are essential for preventing SQL injection attacks. If a mobile app sends malicious input like '; DROP TABLE users; --, a direct concatenation string like VALUES ('" . $item['name'] . "'") will execute that command against your database.
To fix this, you must switch to the MySQLi or PDO extensions, which support prepared statements. Prepared statements separate the SQL logic from the data, ensuring that user input is always treated as a string literal rather than executable code. This is not just a best practice; it is a requirement for any production database interaction.
Here is how to refactor the insertion logic using MySQLi prepared statements:
<?php
// Database Connection
require_once 'db.php'; // Assumes db.php uses mysqli_connect or PDO
// Retrieve and decode JSON
$json = file_get_contents('php://input');
$obj = json_decode($json, true);
if (json_last_error() !== JSON_ERROR_NONE) {
http_response_code(400);
die(json_last_error_msg());
}
// Prepare the statement once outside the loop
$stmt = $con->prepare("INSERT INTO `table_name` (`name`, `phone`, `city`, `email`) VALUES (?, ?, ?, ?)");
$stmt->bind_param("ssss", $name, $phone, $city, $email);
foreach ($obj as $item) {
// Assign values from the decoded object to variables
$name = $item['name'];
$phone = $item['phone'];
$city = $item['city'];
$email = $item['email'];
// Execute the prepared statement with current row data
if ($stmt->execute()) {
echo "Row inserted successfully: " . $stmt->insert_id;
} else {
echo "Error inserting row: " . $stmt->error;
}
}
$stmt->close();
$con->close();
?>
This approach ensures that every value passed to the database is sanitized automatically. The $stmt->bind_param("ssss", ...) call explicitly tells MySQLi that all four parameters are strings (s), which prevents type-based SQL injection issues.
A critical limitation of this "one-to-many" insertion strategyâwhere one JSON array creates multiple rowsâis that it assumes the database table structure matches the JSON keys exactly and that no duplicate constraints exist. If your application logic requires batch operations, such as updating existing records or checking for duplicates before inserting, executing individual INSERT statements within a loop can lead to performance bottlenecks due to excessive network round-trips to the database server.
For high-volume data ingestion, it is often more efficient to construct a single multi-value INSERT statement or use the LOAD DATA INFILE command if the data is substantial. However, for standard mobile app submissions where payloads are relatively small, the prepared statement loop remains the most maintainable solution.
If you are building larger applications, consider frameworks like Laravel, which abstract these concerns and provide robust query builders. Resources such as https://laravelcompany.com offer documentation on handling mass assignment and secure database operations within modern PHP ecosystems. Migrating from raw mysql_* functions to a modern framework or using PDO with explicit error handling will significantly reduce the technical debt associated with legacy database code.
Finally, always validate the JSON structure before decoding. The source material implies the data comes from an iPhone app; while client-side validation is good, server-side validation ensures that malformed JSON sent due to network glitches does not crash your script. Checking json_last_error() immediately after decoding is a mandatory step for robust error handling.
