PHP insert into MySQL database with REST

Question

I am attempting to test inserting in a database using a restful web service. I followed this tutorial https://trinitytuts.com/build-first-web-service-php/ Whenever I post the data I get back successful but the database doesn't display the information (IE it created an entry but all the fields are blank). I am 75% sure it is the Advanced Rest Client but I don't know whats wrong with it. Here's the code/Post command.

Post string is name=Apple&email=banna%40orange.com&pwd=12345&status=ok, Picture of how I send it using Advanced Rest Client.

confi.php file

 <?php
 $conn = mysqli_connect("localhost", "root", "", 'tuts_rest');
 ?>

Rest of the code

        <?php

include_once('confi.php');

if($_SERVER['REQUEST_METHOD'] == "POST"){
$name = isset($_POST['name']) ? mysqli_real_escape_string($_POST['name']) : "";
$email = isset($_POST['email']) ? mysqli_real_escape_string($_POST['email']) : "";
$password = isset($_POST['pwd']) ? mysqli_real_escape_string($_POST['pwd']) : "";
$status = isset($_POST['status']) ? mysqli_real_escape_string($_POST['status']) : "";

 // Insert data into data base
 $sql = "INSERT INTO users (ID, name, email, password, status) VALUES ('' , '" . $name . "', '" . $email . "', '" . $password . "', '" . $status . "');";
 $qur = $conn->query($sql);
 if($qur){
 $json = array("status" => 1, "msg" => "Done User added!");
 }else{
 $json = array("status" => 0, "msg" => "Error adding user!");
 }
}else{
 $json = array("status" => 0, "msg" => "Request method not accepted");
}

mysqli_close($conn);

/* Output header */
 header('Content-type: application/json');
 echo json_encode($json);
?>

Thank you!

Solution 1

Instead of this use my code(I change all of your code and add extra security whether you can remove the header as you wish). here you can easily insert the data from json format into database. in Advance rest client you see there is a button Raw where you can write json data type. For this example,

{
      "name": "Ashraf",
      "email":"[email protected]",
      "pwd": "1234",
      "status": "nice"
  }

Now click send button and see your data will be inserted in your database

<?php

// Include confi.php
include_once('confi.php');
// required headers
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json");
header("Access-Control-Allow-Methods: POST");
header("Access-Control-Max-Age: 3600");
header("Access-Control-Allow-Headers: Content-Type, Access-Control-Allow-Headers, Authorization, X-Requested-With");
	// Get data
	$content=trim(file_get_contents("php://input"));
	$data = json_decode($content, true);

if(
    !empty($data['name']) &&
    !empty($data['email']) &&
    !empty($data['pwd']) &&
    !empty($data['status'])
){
	$name=$data['name'];
	$email=$data['email'];
	$password=$data['pwd'];
	$status=$data['status'];


	// Insert data into data base
	$sql = "INSERT INTO users (name, email, password, status) VALUES (? , ?, ?, ?)";
	$qur=mysqli_query($conn,$sql);
	
//sanitize
	  $name=htmlspecialchars(strip_tags($name));
       $email=htmlspecialchars(strip_tags($email));
       $password=htmlspecialchars(strip_tags($password));
       $status=htmlspecialchars(strip_tags($status));
  	//bind values 
   $stmt = $conn->prepare($sql);
  	if($stmt)
  	{	
	  	$stmt->bind_param("ssss", $name, $email, $password,$status);
		
		if($stmt->execute())
		{
			$json = array("status" => 1, "msg" => "Done User added!");
		}
		else
		{
			$json = array("status" => 0, "msg" => "Error adding user!");
		}
	}
	else
	{
		$json = array("status" => 0, "msg" => "Request method not accepted");
	}
}


@mysqli_close($conn);

/* Output header */
	header('Content-type: application/json');
	echo json_encode($json);
?>

Solution 2

Because you are using mysql_real_escape_string. The function is deprecated. You may use mysqli_real_escape_string instead of that.

Comments

[Pratik Soni]

Try to catch all errors and you may get the error message. by error_reporting(E_ALL);

[Walorn]

Good catch, tried to update to all mysqli but seems I missed that important one. Didn't fix it though sadly, I think it has something to do with my _Post payload.

[tadman]

NO. Do not use addslashes for anything related to SQL.