PHP - queries on mySQL database and failure upon showing result in JSON format

Question

So we recently set up a mySQL database for a project in our university and are currently working on the data access files written in php. In order to use the data on our front end we need it formatted as JSON. While putting an array of objects in the result of a certain object is working it won't work with 2 arrays of objects. Working code:

<?php
include_once 'db.php';

$email = "email@online.com";

$conn = connect();
$conn->set_charset('utf8');

$resultArr = array();
$sql = "SELECT * FROM `customer` WHERE email = '$email'";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
    $resultArr['customer'][$row['email']] = array('email' => $row['email'], 'address' => $row['address']);

    $sql2 = "SELECT id, name, address FROM shop INNER JOIN rel_shop_customer WHERE customer_email='".$row['email']."' AND rel_shop_customer.shop_id = shop.id";
    $result2 = $conn->query($sql2);
    while($row2 = $result2->fetch_assoc()) {
        $resultArr['customer'][$row['email']]['shops'][] = $row2;
    }

}
$resultArr['customer'] = array_values($resultArr['customer']);
} else {
echo "failure";
}
echo json_encode($resultArr, JSON_UNESCAPED_UNICODE);

?>

And here is the not working code:

<?php
include_once 'db.php';

$email = "email@online.com";

$conn = connect();
$conn->set_charset('utf8');

$resultArr = array();
$sql = "SELECT * FROM `customer` WHERE email = '$email'";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
    $resultArr['customer'][$row['email']] = array('email' => $row['email'], 'address' => $row['address']);

    $sql2 = "SELECT id, name, address FROM shop INNER JOIN rel_shop_customer WHERE customer_email='".$row['email']."' AND rel_shop_customer.shop_id = shop.id";
    $result2 = $conn->query($sql2);
    while($row2 = $result2->fetch_assoc()) {
        $resultArr['customer'][$row['email']]['shops'][] = $row2;
    }

    $sql3 = "SELECT img, name FROM ad INNER JOIN rel_customer_ad WHERE customer_email='".$row['email']."' AND rel_customer_ad.ad_name = ad.name";
    $result3 = $conn->query($sql3);
    while($row3 = $result3->fetch_assoc()) {
        $resultArr['customer'][$row['email']]['ads'][] = $row3;
    }
}
$resultArr['customer'] = array_values($resultArr['customer']);
} else {
   echo "failure";
}
echo json_encode($resultArr, JSON_UNESCAPED_UNICODE);

?>

Both SQL queries provide the expected result when run directly on the database and I have no idea why the second one won't work in the PHP script.

The first code snipped gives the result: {"customer":[{"email":"bla@bla.de","address":"00001 Hometown\r\nHomestreet 123","shops":[{"id":"1","name":"WM_FLO_123","address":"pimewqöofmwe\r\nwpokmfwlkmefk"}]}]} as expected. The second one however does not echo anything without giving an error message. — Roy, Jul 14 '17 at 14:14
As I said "The second one however does not echo anything **without** giving an error message." — Roy, Jul 14 '17 at 14:18
@YashParekh That would be dangerous as OP is not using the `mysql_` database extension, for obvious reasons like that old extension s dead and buried as it was dangerous — RiggsFolly, Jul 14 '17 at 14:21
Add `ini_set('display_errors', 1); ini_set('log_errors',1); error_reporting(E_ALL); mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);` to the top of your script. This will force any `mysqli_` errors to generate an Exception that you can see on the browser and other errors will also be visible on your browser. — RiggsFolly, Jul 14 '17 at 14:22
Added it at the begin of the script and still no errors shown in browser... — Roy, Jul 14 '17 at 14:29
Then you will have to revert to old fashioned debugging. Add `print_r($resultArr);` at various places in the code so you can see where something is doing something funny to your array — RiggsFolly, Jul 14 '17 at 14:34
`$sql = "SELECT * FROM `customer` WHERE email = '$email'";` should not work in either of the queries. `$email` wrapped in quotes will be treated as string literal $email instead of the value contained in the variable $email. If you are not getting any error that means that the database is returning nothing for the query. — Donkarnash, Jul 14 '17 at 14:38
this code is vulnerable to SQL injection attacks. Learn how to use parameterised queries to protect your data. It will also eliminate any potential errors due to problems with string concatenation, as suggested above. — ADyson, Jul 14 '17 at 15:01
The `$email` inside of your double quoted queries is fine, and as long as the data in that variable comes from you or is escaped using the correct escaping mechanism (e.g. `mysql_real_escape_string`), you are NOT vulnerable to SQL injection attacks. — Jo., Jul 14 '17 at 17:35
@RiggsFolly I will try that now. @Donkarnash the querys are working like Josh said, just the `sql3` won't return anything even though it works on the database itself — Roy, Jul 15 '17 at 12:00
@JoshH Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) — RiggsFolly, Jul 15 '17 at 14:18
@Donkarnash Complete and utter **balderdash** Please read up on variable expansion in a DOUBLE QUOTED LITERAL in PHP — RiggsFolly, Jul 15 '17 at 14:19
@RiggsFolly yes, I have read that question and all the answers on it. And, to sum up, if you use single quotes inside your mysql query, use a utf8 character set (actually latin1 and ascii would work as well), and use php's mysqli escape function, you are not vulnerable to sql injection. — Jo., Jul 17 '17 at 15:38
@RiggsFolly then, please, enlighten me on what I have misunderstood. Also, please point out where in the given code there is an SQL injection vulnerability. — Jo., Jul 17 '17 at 16:21
The only safe way is using parameterised bound queries http://php.net/manual/en/security.database.sql-injection.php — RiggsFolly, Jul 17 '17 at 16:33
@RiggsFolly No where in the manual's page that you linked does it say that the only safe way is to use prepared statements. It may be recommended, but it is not required. On using concatenated strings, it states `quote each non numeric user supplied value that is passed to the database with the database-specific string escape function (e.g. mysql_real_escape_string(), sqlite_escape_string(), etc.).` — Jo., Jul 17 '17 at 17:17
@JoshH https://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string/12118602#12118602 — RiggsFolly, Jul 17 '17 at 23:19

PHP - queries on mySQL database and failure upon showing result in JSON format

0 Answers0