Cannot add the value in the table-CodePudding

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";
}
?>

CodePudding user response：

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.

Check the user's session to see if he is allowed to do the operation.
Filter your user's input values and throw errors if they are missing or invalid.
Use prepared statements with PDO instead of building your SQL strings yourself.

This would be my quick solution to explain what I mean:

<?php

// Config comming from "config.php";
define('DB_NAME', 'test');
define('DB_USER', 'test');
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,
]);

/**
 * Return an HTTP error and description
 *
 * @param string $error_description The text to display to the user.
 * @param integer $http_response_code The HTTP error code to return.
 * @return void
 */
function exit_with_error($error_description, $http_response_code = 400)
{
    http_response_code($http_response_code);
    die($error_description);
}


// Check the product id parameter and stop if it's missing or invalid.
if (!isset($_POST['proId'])) {
    exit_with_error('Missing "proId" = product id.');
} elseif (!is_numeric($_POST['proId'])) {
    exit_with_error('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'])) {
    exit_with_error('Missing "userId" = user id.');
} elseif (!is_numeric($_POST['userId'])) {
    exit_with_error('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());
    exit_with_error('Database connection error :-(', 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()) {
        echo "Product already saved.";
        exit;
    }

    // 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) {
        echo "Successfully saved the product.";
    } else {
        exit_with_error('Did NOT manage to save the product.', 500);
    }
    echo $statement->rowCount();
} catch (PDOException $e) {
    error_log($e->getMessage());
    exit_with_error('Database error! ' . $e->getMessage(), 500);
}

The error handling can be improved. You should decide what your PHP should return to your Android App. I would personnally not print text like here but return a JSON response with more details, such as an error code, error description or any other specific values to return to the app.

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 the current user, that you already have in the session. So there's no need to send it via the POST request. If an admin can save products for a specific user, then ok, it's a special case but I don't think it's very often that we do that.

CodePudding user response：

Maybe work this:

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