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 duplicate\n";
} else {
db_addCandiate($db,$item,$dangerBatchId);
echo 'Applicant '.$dangerItem['EmployeeId']." added\n";
}
}
// 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.
