Check duplication record from one table and duplication record insert to another table using php script – Apache

While the accepted answer may do the job, it has several SQL injection vulnerabilities, including $_GET['id'] (which should not be trusted – a malicious actor could put anything in here including a SQL command to clear your database), and the JSON harvested from php://input (likewise – even if it’s valid JSON you could hide commands to delete data in here). Parameter escaping (as your original example) is the way to go.

<?php

/* Your headers etc here*/

///Build a database connection
function db_conn() {
    /* Your Mysql setup with user/pass/db etc in a PDO object here*/
    $db=new PDO('mysql:host=x;dbname=x', $user, $pass);
    //You might want to set this here (for all connections:)
    //$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    return $db;
}

///Search the candidate table for an entry for the given ID
function db_employeeCandidateExists($db,$dangerEmployeeId) {
    $stmt=$db->prepare('SELECT EmployeeId FROM CandidateDetails WHERE EmployeeId=?');
    $stmt->execute([$dangerEmployeeId]);
    $result=$stmt->fetchAll();//We're expecting 0 rows (not found) or 1 row with the ID if it's a dup
    return count($result)>0;
}

///Add a row to the DuplicateCandidate table
function db_addDuplicate($db,$dangerRow,$dangerBatchId) {
    //All columns you want to fill out - layed out to be visually obvious how many there are
    $cols=['EmployeeId',
        'FirstName',
        'LastName',
        'Mobile',
        'Email',
        'BatchId'];
    //Values for the above columns - layed out to be visually equal to above
    // don't forget
    $vals=[$dangerRow['EmployeeId'],
        $dangerRow['FirstName'],
        $dangerRow['LastName'],
        $dangerRow['Mobile'],
        $dangerRow['Email'],
        $dangerBatchId];
    //The parameters can use a count of the cols (above)
    $params=substr(str_repeat('?,',count($cols)),0,-1);

    $stmt=$db->prepare('INSERT INTO DuplicateCandidate ('.
        implode(',',$cols).
        ') VALUES ('.
        $params.
        ')');

    $stmt->execute($vals);
    //todo: You might want to check there are no SQL errors reported here
}

///Add a row to the CandidateDetails table
function db_addCandiate($db,$dangerRow,$dangerBatchId) {
    //All columns you want to fill out - layed out to be visually obvious how many there are
    $cols=['EmployeeId',
        'FirstName',
        'LastName',
        'Mobile',
        'Email',
        'BatchId'];
    //Values for the above columns - layed out to be visually equal to above
    // don't forget
    $vals=[$dangerRow['EmployeeId'],
        $dangerRow['FirstName'],
        $dangerRow['LastName'],
        $dangerRow['Mobile'],
        $dangerRow['Email'],
        $dangerBatchId];
    //The parameters can use a count of the cols (above)
    $params=substr(str_repeat('?,',count($cols)),0,-1);

    $stmt=$db->prepare('INSERT INTO CandidateDetails ('.
        implode(',',$cols).
        ') VALUES ('.
        $params.
        ')');

    $stmt->execute($vals);
    //todo: You might want to check there are no SQL errors reported here
}

///Get JSON from input and decode it into an associative array
function getJson() {
    $json = file_get_contents('php://input');
    return json_decode($json, true);
}

$db = db_conn();
//You might want to set this inside the `conn()` method for all usage?
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// *** *** Dangerous CONTENT *** ***
// Both $id (presuambly an integer) and $json (an associative array of data)
// are user-provided and therefore the cannot be trusted - you NEED to escape these
// values before using them in SQL
$dangerBatchId = $_GET['id'];
$dangerJson = getJson();

foreach($dangerJson as $dangerItem)
{
    if (db_employeeCandidateExists($db,$dangerItem['EmployeeId'])) {
        //Duplicate
        db_addDuplicate($db,$dangerItem,$dangerBatchId);
        echo 'Applicant '.$dangerItem['EmployeeId']." was a duplicaten";
    } else {
        db_addCandiate($db,$item,$dangerBatchId);
        echo 'Applicant '.$dangerItem['EmployeeId']." addedn";
    }
}
// Further processing

I’ve used position parameter escaping (?) which should also work with MySQL. Named position escaping (:id) is probably better, but doesn’t allow me to quickly generate all parameters (build the $params string), and the database I tested against doesn’t support them.

After separating the components into functions (as I’ve done), you’ll notice we have a bit of a grouping of functions beginning with db_… you might, instead, configure bundling these into a class. At that point you could avoid passing $db as the first parameter because you could internally share that in the class. But that’s a completely different topic and out of scope of your original question.