Having issues interpreting json inside php and passing it to mysql

Question

I am new to php and I am not sure how to debug this.

I am trying to pass json to a php page and then send that data to mySQL.

I think it is having issues interpreting the data inside the php file or getting the information to the php page. When I open the php file it gives signs that it is properly accessing the database.

Here is my javascript code:

 var request = new XMLHttpRequest();
                  request.open('POST', 'http://website/saveF.php', true);
                  request.setRequestHeader('Content-Type', 'application/json; charset=UTF-8');
                  request.send(bInfo);

This is taking information in and passing it to a php file to then be added to a mySQL database.

Here is my php code:

This is decoding the jSon and then itterating over each entry inside the array. It then asks the question if it has a website listed or not and stores it into the appropriate table.

//as long as the connection is good then we keep it live.
include_once "head.php";

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

//gettting the information from the front end (index.html)
$inputJSON = file_get_contents('php://input');
//decode all the previously encoded information
$postThings = json_decode($inputJSON, TRUE);
$input = filter_var($postThings, FILTER_SANITIZE_STRING); 

//create a variable the is the total length of our array
$totalNum = count($input);
//arrays start at 0
$i = 0;
//you can see where this is going. We have a while loop that will continue as long as i is less than totalnum. Ask me why i didn't use a for loop.... I don't have an answer.

    while($i < $totalNum){
        $var0 = $input[$i][0]; 
        $var1 = $input[$i][1]; 
        $var2 = $input[$i][2];
        $var3 = $input[$i][3];
        $var4 = $input[$i][4];
        $var5 = $input[$i][5];
        $var6 = $input[$i][6];

        if($var1 == "Not Listed") {
            $sql = "INSERT INTO missing(cName, website, rating, phone, id, address, placeType) VALUES ('$var0', '$var1', '$var2', '$var3', '$var4', '$var5', '$var6')";
        }else{

            //here we set the information into the database.
           $sql = "INSERT INTO companies(cName, website, rating, phone, id, address, placeType) VALUES ('$var0', '$var1', '$var2', '$var3', '$var4', '$var5', '$var6')";
    }


        if ($conn->query($sql) === TRUE) {
            echo "New record created successfully";
        } else {
            echo "Error: " . $sql . "<br>" . $conn->error;
        }

        $i++;
    }

Your code is vulnerable to [**SQL injection**](https://en.wikipedia.org/wiki/SQL_injection) attacks. You should use prepared statements with bound parameters, via either the [**mysqli**](https://secure.php.net/manual/en/mysqli.prepare.php) or [**PDO**](https://secure.php.net/manual/en/pdo.prepared-statements.php) driver. [**This post**](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) has some good examples. — Alex Howansky, Dec 07 '17 at 15:34
@Luca I didn't use $_POST array because wherever I learned from didn't. I realize that I should but for now tphp://input is working. — krizpers, Dec 07 '17 at 15:40
I also realize that I need to use prepared statements but the code isn't working properly so I need to resolve that first. — krizpers, Dec 07 '17 at 15:41
What does your json look like? Is it just an array? Relying on data from an array where the values are in a specific order is like asking for trouble. — Magnus Eriksson, Dec 07 '17 at 15:41
_"but the code isn't working properly so I need to resolve that first"_ - Why spend time debugging and fixing something you need to replace? Do it correctly from the start instead. — Magnus Eriksson, Dec 07 '17 at 15:41
@MagnusEriksson It looks something like this Array[[place, website, id, rating, phone, others] ,[place, website, id, rating, phone, others]]; — krizpers, Dec 07 '17 at 15:44
@MagnusEriksson Because moving on to something I am not familiar with and still needing to solve a bug is going to cause more stress and issues than If I can understand what I am doing wrong here first. — krizpers, Dec 07 '17 at 15:45
Use proper keys instead: `[ {place: 'someplace', website: 'somewebsite', ...}, {...}]` instead. — Magnus Eriksson, Dec 07 '17 at 15:46
1. You need to debug the code properly to find _where_ it fails. We can't do that for you. 2. I would recommend you to change the code to use a proper POST and then use PHP's `$_POST` to fetch the values. 3. You should learn Prepared Statements straight away. No excuses! There's no point knowingly learning (and debugging) something insecure. Prepared statements are easy. An extra line or two. 4. Fix this one step at the time. First, post data and see if you can get that to work. Then try to add it to your DB (_correctly_). — Magnus Eriksson, Dec 07 '17 at 15:50
@RavinderReddy If I am sending data from an html page to a php page how do I view the print_r(input) statement? — krizpers, Dec 07 '17 at 16:29
I wanted you to see the array $input = filter_var($postThings, FILTER_SANITIZE_STRING); Print_r($input); — Ravinder Reddy, Dec 07 '17 at 16:33
@RavinderReddy I don't understand where it gets printed to though. Like console.line() in javascipt goes to the console in the browser but immediately gets deleted once you are off the page. So if I am not on the php page when it receives information how do I view the contents of print_r? — krizpers, Dec 07 '17 at 16:56
I am sorry. If you are not on the page in browser, you cannot print it. — Ravinder Reddy, Dec 07 '17 at 17:00
@RavinderReddy Is there a way that I could fake this information in order to view it? — krizpers, Dec 07 '17 at 18:43
you can write output of print_r in a txt file. https://stackoverflow.com/questions/13361376/write-output-of-print-r-in-a-txt-file-php — Ravinder Reddy, Dec 07 '17 at 18:51

score 0 · Accepted Answer · answered Dec 07 '17 at 17:16

First, note that this line:

$input = filter_var($postThings, FILTER_SANITIZE_STRING);

Will return FALSE if sanitization fails on any of the array elements. In your code, you should be testing if($input) immediately after the sanitization.

Furthermore, you will want to sanitize your inputs further to avoid SQL injection and XSS attacks. (e.g. remove SQL escape characters and other injectable characters).

http://php.net/manual/en/mysqli.real-escape-string.php

Last, it is recommended that you use bound parameters or fully sanitized inputs to avoid a SQL injection attack.

I typically use preg_replace and strict patterns myself. Usually, I will replace any character other than a-zA-Z0-9 with no character (empty) `''`. It depends on the format expected. The stricter the better when it comes to sanitation. — Nerds of Technology, Dec 07 '17 at 22:14

Having issues interpreting json inside php and passing it to mysql

1 Answers1