I'm working on a simple project where I'm collecting the JSON data from a register page and trying to insert in mysql database using prepared statement.
This is sample query after getting the details from form
INSERT INTO `registered_users`(`USER_EMAIL`, `USER_NAME`,`USER_PWD`, `ADDED_BY`) VALUES ('aaaa@aaa.ddd ','aaaaaaa','$2y$10$NEJFPvgnR/WhDkZKWChknOzfAe6Pzk.9LOYip9y36OOoyHDQKVFPm','aaaa@aaa.ddd')
I checked manually by running this query , it got inserted which means there's no issue with insert statement , don't know what is the issue with prepared statement , please help .
Here's the code
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
error_reporting(E_ALL);
/*
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
* @author : Mahaveer
* @date : 25/03/2018
*/
require_once 'database_connection.php';
class DBWebServices extends DBConnection {
function insertUserData($register_json) {
try {
//decode json data to make array
$json_data_array = json_decode($register_json, true);
//reference : https://stackoverflow.com/questions/37367992/php-inserting-values-from-the-form-into-mysql
//https://stackoverflow.com/questions/1290975/how-to-create-a-secure-mysql-prepared-statement-in-php
//https://websitebeaver.com/prepared-statements-in-php-mysqli-to-prevent-sql-injection
$query = "INSERT INTO registered_users(`USER_EMAIL`,`USER_NAME`, `USER_PWD`, `ADDED_BY`) VALUES ((?),(?),(?),(?)); ";
//prepare the stament
$stmt = $this->connectDB()->prepare($query);
if (!($stmt)) {
echo "Prepare failed: (" . $stmt->errno . ") " . $stmt->error;
}
//Then start binding the input variables to the prepared statement:
if (!$stmt->bind_param("ssss", $email, $name, $pwd, $modified_by)) {
echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
}
$email = $json_data_array['register_user_email'];
$name = $json_data_array['register_user_name'];
$pwd = $json_data_array['user_password'];
$modified_by = $json_data_array['register_user_email'];
//Execute the query
if (!($query_result=$stmt->execute())) {
echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
}
if (!$query_result) {
echo $stmt->error;
}
$stmt->close();
//defining response in case of success or failure
$response = array();
if ($query_result) {
// successfully inserted into database
$response["success"] = 1;
$response["message"] = "User successfully added.";
// echoing JSON response
echo json_encode($response);
} else {
//insert failed
$response["success"] = 0;
$response["message"] = "User cannot be added.";
// echoing JSON response
echo json_encode($response);
}
} catch (Exception $ex) {
echo 'Exception occurred ' . $ex->getTraceAsString();
}
}
}Code For Database connection :
<?php
/*
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/
class DBConnection {
protected function connectDB() {
// import database connection variables
require_once './config.php';
// Connecting to mysql database & choosing database
$conn = new mysqli(DB_HOST, DB_USER_NAME, DB_PWD, DB_NAME);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo 'Connection to database was successful....';
return $conn;
}
}Error stack trace produces following message :
Exception occurred #0 C:\xampp\htdocs\LockerWebApp\webservices_php\locker_web_services.php(62): mysqli_stmt->execute() #1 C:\xampp\htdocs\LockerWebApp\webservices_php\validate_n_submit_user.php(94): DBWebServices->insertUserData('{"register_user...') #2 C:\xampp\htdocs\LockerWebApp\user_registraion.php(7): include('C:\\xampp\\htdocs...') #3 {main} and Error Message is : mysql server has gone away which is weird.
I'm stuck here it's my first php project and due to this error I can not move forward...
