Cannot add the value in the table

Question

Here I am trying to add the values of proId and userId in the table whose values I am getting through post method from my android app and I have also checked it by assigning a value 5 directly to the variable (which I have commented out in the table ) but it is still adding 0 in the table. The problem here is that it is not adding the value of userId in the table instead its adding only 0 there. I have checked in the android code , the value being passed there is fine. I think something is wrong in the php code. Kindly share the solution. Here I have attached my php file and the MySQL table.

<?php
$pro= $_POST['proId'];
$user = $_POST['userId'];
//$user = 5;
include "config.php";

$check_sql = "SELECT * FROM `savedProducts` WHERE `proId` = '{$pro}' AND `userId` = 
'{$user}'";
$result_check =mysqli_query($conn,$check_sql) or die("result failed");

if(mysqli_num_rows($result_check)>0){
echo "Added already";
}else{
$sql="INSERT INTO `$db`.`savedProducts` (`proId`,`userId`)
VALUES ('{$pro}','{$user}')";
$result=mysqli_query($conn,$sql) or die("result failed");
echo "Added successfully";
}
?>

Answer 1

As explained in my comments, you have to use more security checks in your code. Typically, you cannot take $_POST input values and concatenate them into your SQL query string. This will lead the user to be able to do nasty SQL injections and break your app or steal some user's data.

1. Check the user's session to see if he is allowed to do the operation.

2. Filter your user's input values and throw errors if they are missing or invalid.

3. Use prepared statements with PDO instead of building your SQL strings yourself.

You may also have some problems in your Android app. To help debugging, you could install a proxy such as HTTP toolkit or mitmproxy and configure your smartphone to use it. This way you'll be able to see all the requests between your app and your PHP backend.

Put your config in your config.php file, create some utility functions in another include/tools.php file and add a session handling to check that the user is allowed to do the DB query or not. Typically, the user id should probably be taken directly from the current user, that you already have in the session. There's no need to send the user id via the POST request except if an admin can save products for another user.

But assuming you transfert the user id, here would be a solution where you don't just print a string to return but where you return a JSON object to your mobile app. This way you can handle more easily what has happened. I also decided that the HTTP response code would be set to something else than the 200 (for ok) in case of error. Typically a 400 error for a bad request if your Android app doesn't send valid POST parameters but a 500 error if we are having some MySQL connection errors to say that the problem is on the server side and not the client side.

This would be an example of code (without the session handling):

<?php

// Config comming from "config.php";
define('DB_NAME', 'test');
define('DB_USER', 'sdfsadf');
define('DB_PSWD', 'test');
define('DB_DSN', 'mysql:host=localhost;dbname=' . DB_NAME . ';charset=utf8mb4');
define('DB_PDO_OPTIONS', [
    PDO::ATTR_EMULATE_PREPARES => false,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

// Error codes:
define('ERROR_MISSING_PARAM', 1);
define('ERROR_WRONG_PARAM', 2);
define('ERROR_DATABASE_CONNECTION', 3);
define('ERROR_DATABASE_QUERY', 4);

/******************************** FUNCTIONS **********************************/

/**
 * Return a response to the mobile app. You can pass an object or any type of
 * variable to the mobile app. It will be converted to JSON.
 * If you are returning an error then you have to set a non-zero code value and
 * a description for the error.
 *
 * @param mixed $response A PHP object/value to return in JSON to the mobile app.
 * @param integer $error_code The app error code to return. Defaults to 0 => no error.
 * @param string $error_description An optional error description.
 * @param int $http_error_code If $error_code is not 0 then what HTTP error code to return.
 * @return void
 */
function return_json_response($response, $error_code = 0, $error_description = '', $http_error_code = 400)
{
    if ($error_code > 0) {
        http_response_code($http_error_code);
    }
    header('Content-Type: application/json; charset=utf-8');
    $data = (object)[
        'response' => $response,
        'error_code' => $error_code,
        'error_description' => $error_description,
    ];
    echo json_encode($data, JSON_PRETTY_PRINT);
    exit;
}

/*************************** PROGRAM STARTS HERE *****************************/

// Check the product id parameter and stop if it's missing or invalid.
if (!isset($_POST['proId'])) {
    return_json_response(false, ERROR_MISSING_PARAM, 'Missing "proId" POST parameter');
} elseif (!is_numeric($_POST['proId'])) {
    return_json_response(false, ERROR_WRONG_PARAM, 'Invalid "proId" value! It should be numeric.');
}
$product_id = intval($_POST['proId']);

// Check the user id parameter and stop if it's missing or invalid.
// If the user id can be taken from the session then I would not use
// this parameter since it could be changed by the client to make a big
// mess in the products of other users. If needed, keep it for admins
// by adding more security controls.
if (!isset($_POST['userId'])) {
    return_json_response(false, ERROR_MISSING_PARAM, 'Missing "userId" POST parameter');
} elseif (!is_numeric($_POST['userId'])) {
    return_json_response(false, ERROR_WRONG_PARAM, 'Invalid "userId" value! It should be numeric.');
}
$user_id = intval($_POST['userId']);

try {
    $pdo = new PDO(DB_DSN, DB_USER, DB_PSWD, DB_PDO_OPTIONS);
} catch (PDOException $e) {
    error_log($e->getMessage());
    return_json_response(false, ERROR_DATABASE_CONNECTION, 'Could not connect to the database.', 500);
}

try {
    // See if the product is already saved for this user.
    $search_query = "SELECT * FROM `savedProducts` WHERE " .
                    "`proId` = :proId AND `userId` = :userId";
    $statement = $pdo->prepare($search_query);
    $statement->execute([
        ':proId' => $product_id,
        ':userId' => $user_id,
    ]);
    if ($statement->rowCount()) {
        return_json_response([
            'ok' => true,
            'affected_rows' => 0,
            'already_saved' => true
        ]);
    }

    // Insert the saved product for the given user.
    $insert_query = "INSERT INTO `savedProducts` (`proId`, `userId`) VALUES (:proId, :userId)";
    $statement = $pdo->prepare($insert_query);
    $statement->execute([
        ':proId' => $product_id,
        ':userId' => $user_id,
    ]);
    if ($statement->rowCount() === 1) {
        return_json_response([
            'ok' => true,
            'affected_rows' => $statement->rowCount(),
            'already_saved' => false
        ]);
    } else {
        return_json_response(false, ERROR_DATABASE_QUERY, 'Did NOT manage to save the product.', 500);
    }
    echo $statement->rowCount();
} catch (PDOException $e) {
    error_log($e->getMessage());
    return_json_response(false, ERROR_DATABASE_QUERY, 'SQL query error.', 500);
}

HTTP 200 response if the product was already saved:

{
    "response": {
        "ok": true,
        "affected_rows": 0,
        "already_saved": true
    },
    "error_code": 0,
    "error_description": ""
}

HTTP 200 response if the product wasn't saved before and was added correctly:

{
    "response": {
        "ok": true,
        "affected_rows": 1,
        "already_saved": false
    },
    "error_code": 0,
    "error_description": ""
}

HTTP 400 response if the "proId" POST param is missing:

{
    "response": false,
    "error_code": 1,
    "error_description": "Missing \"proId\" POST parameter"
}

HTTP 500 response if we didn't manage to connect to the DB:

{
    "response": false,
    "error_code": 3,
    "error_description": "Could not connect to the database."
}

Answer 2

Maybe work this:

$sql="INSERT INTO `savedProducts` (`proId`, `userId`) VALUES ('" . $pro . "', '" . $user . "')";