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.

enter image description here

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!

If you can, you should [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) [statements](http://php.net/manual/en/pdo.prepared-statements.php) instead, and consider using PDO, [it's really not hard](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Jul 07 '15 at 15:03
`$sql = "INSERT INTO users (ID, name, email, password, status) VALUES ('' , '" . $name . "', '" . $email . "', '" . $password . "', '" . $status . "');";` — Alive to die - Anant, Jul 07 '15 at 15:03
Please don't post screenshots, especially illegible ones, unless the presentation is relevant to the question. Also that tutorial is teaching you extremely bad habits, that style of PHP is straight out of the 1990s, so please don't use it. A guide like [PHP the Right Way](http://www.phptherightway.com/) can help explain modern best practices. Learning and using a [development framework](http://codegeekz.com/best-php-frameworks-for-developers/) like [Laravel](http://laravel.com/) will significantly help your productivity. — tadman, Jul 07 '15 at 15:52
**WARNING**: When using `mysqli` you should be using parameterized queries and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add user data to your query. **DO NOT** use string interpolation or concatenation to accomplish this because you will create severe [SQL injection bugs](http://bobby-tables.com/). **NEVER** put `$_POST` data directly into a query and avoid using manual escaping because a single mistake can create total chaos. — tadman, Jul 07 '15 at 15:52

score 3 · Answer 1 · answered Mar 30 '20 at 16:29

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":"ash@yahoo.coom",
      "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);
?>

Pratik Soni · Answer 2 · 2015-07-07T17:12:49.563

1

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

edited Jul 07 '15 at 17:12

answered Jul 07 '15 at 15:08

Pratik Soni

2,498
16
26

Try to catch all errors and you may get the error message. by error_reporting(E_ALL); – Pratik Soni Jul 07 '15 at 15:09
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. – Walorn Jul 07 '15 at 15:23
**NO**. Do not use `addslashes` for **anything** related to SQL. – tadman Jul 07 '15 at 15:51

score 0 · Answer 3 · answered Jul 07 '15 at 16:03

Thanks for all the help guys! I have it working though I do not understand it. The problem was indeed in my Advanced Rest Client application. I was sending the payload as application/json and it wasn't getting the POST data but when I changed the Content-Type to application/x-www-form-urlencoded it worked. If anyone wants to explain why that fixed it I will appreciate it and I'm sure others will as well.

PHP insert into MySQL database with REST

3 Answers3