Hi, I have a search form with php coding, I'm pretty sure it is exposed to risks and threats. Does anyone have any suggestions as to how I can make it secure? Thanks
<?php
include('mysql.inc.php');
include('config.php');
$log="";
$db = new dblink($log);
$db->connect($dbhost,$dbuser,$dbpass);
if($db->selectDb($dbname) === false){echo "could not connect to database";}
$boolPerm = false;
$boolTemp = false;
$boolPartTime = false;
$boolFullTime = false;
$boolSector = false;
$boolKeywords = false;
$boolLocation = false;
$boolRef = false;
$masterQuery = "";
$key = "";
if(isset($_POST['ref']) && $_POST['ref'] != ""){
$boolRef = true;
$ref = $_POST['ref'];
$ref = "'%".$ref."%'";
$masterQuery = "SELECT * FROM jobs WHERE `Job Ref` LIKE ".$ref;
$results = getResults($db,$masterQuery);
display($results);
exit();
}
if(isset($_POST['keywords']) && $_POST['keywords'] != ""){
$keywords = explode(' ',$_POST['keywords']);
foreach($keywords as $keyword){
if($keyword !=""){$key .= "`Keywords` LIKE '%".$keyword."%' AND ";}
}
$keywords = substr($key,0,-4);
$boolKeywords = true;
}
if(isset($_POST['location']) && $_POST['location'] != ""){
$boolLocation = true;
$location = $_POST['location'];
if($location == "all"){$boolLocation = false;}
}
if(isset($_POST['permanent']) && $_POST['permanent'] != ""){$boolPerm = true;}
if(isset($_POST['temporary']) && $_POST['temporary'] != ""){$boolTemp = true;}
if(isset($_POST['parttime']) && $_POST['parttime'] != ""){$boolPartTime = true;}
if(isset($_POST['fulltime']) && $_POST['fulltime'] != ""){$boolFullTime = true;}
if(isset($_POST['sector']) && $_POST['sector'] != ""){
$boolSector = true;
$sector = $_POST['sector'];
}
//jobtype
//full
if($boolPerm != true && $boolTemp != true && $boolPartTime != true && $boolFullTime == true){
$masterQuery .= "(SELECT * FROM jobs WHERE `Job Type` LIKE '%Full Time%') as a";
}
//part
if($boolPerm != true && $boolTemp != true && $boolPartTime == true && $boolFullTime != true){
$masterQuery .= "(SELECT * FROM jobs WHERE `Job Type` LIKE '%Part Time%') as a";
}
//full & part
if($boolPerm != true && $boolTemp != true && $boolPartTime == true && $boolFullTime == true){
$masterQuery .= "(SELECT * FROM jobs) as a";
}
//temp
if($boolPerm != true && $boolTemp == true && $boolPartTime != true && $boolFullTime != true){
$masterQuery .= "(SELECT * FROM jobs WHERE `Job Type` LIKE '%Temporary%') as a";
}
//temp & full
if($boolPerm != true && $boolTemp == true && $boolPartTime != true && $boolFullTime == true){
$masterQuery .= "(SELECT * FROM (SELECT * FROM jobs WHERE `Job Type` LIKE '%Temporary%') as a WHERE `Job Type` LIKE '%Full Time%') as b";
}
//temp & part
if($boolPerm != true && $boolTemp == true && $boolPartTime == true && $boolFullTime != true){
$masterQuery .= "(SELECT * FROM (SELECT * FROM jobs WHERE `Job Type` LIKE '%Temporary%') as a WHERE `Job Type` LIKE '%Part Time%') as b";
}
//temp & part & full
if($boolPerm != true && $boolTemp == true && $boolPartTime == true && $boolFullTime == true){
$masterQuery .= "(SELECT * FROM jobs WHERE `Job Type` LIKE '%Temporary%') as a";
}
//perm
if($boolPerm == true && $boolTemp != true && $boolPartTime != true && $boolFullTime != true){
$masterQuery .= "(SELECT * FROM jobs WHERE `Job Type` LIKE '%Permanent%') as a";
}
//perm & full
if($boolPerm == true && $boolTemp != true && $boolPartTime != true && $boolFullTime == true){
$masterQuery .= "(SELECT * FROM (SELECT * FROM jobs WHERE `Job Type` LIKE '%Permanent%') as a WHERE `Job Type` LIKE '%Full Time%') as b";
}
//perm & part
if($boolPerm == true && $boolTemp != true && $boolPartTime == true && $boolFullTime != true){
$masterQuery .= "(SELECT * FROM (SELECT * FROM jobs WHERE `Job Type` LIKE '%Permanent%') as a WHERE `Job Type` LIKE '%Part Time%') as b";
}
//perm & part & full
if($boolPerm == true && $boolTemp != true && $boolPartTime == true && $boolFullTime == true){
$masterQuery .= "(SELECT * FROM jobs WHERE `Job Type` LIKE '%Permanent%') as a";
}
//perm & temp
if($boolPerm == true && $boolTemp == true && $boolPartTime != true && $boolFullTime != true){ $masterQuery .= "(SELECT * FROM jobs) as a";}
//perm & temp & full
if($boolPerm == true && $boolTemp == true && $boolPartTime != true && $boolFullTime == true){
$masterQuery .= "(SELECT * FROM jobs WHERE `Job Type` LIKE '%Full Time%') as a";
}
//perm & temp & part
if($boolPerm == true && $boolTemp == true && $boolPartTime == true && $boolFullTime != true){
$masterQuery .= "(SELECT * FROM jobs WHERE `Job Type` LIKE '%Part Time%') as a";
}
//perm & temp & full & part
if($boolPerm == true && $boolTemp == true && $boolPartTime == true && $boolFullTime == true){ $masterQuery .= "(SELECT * FROM jobs) as a";}
//location
if($boolLocation == true){
if($boolPerm == true || $boolTemp == true || $boolPartTime == true || $boolFullTime == true){
$masterQuery = "(SELECT * FROM ".$masterQuery." WHERE `location` LIKE '%".$location."%') as c";
}else{$masterQuery = "(SELECT * FROM jobs WHERE `location` LIKE '%".$location."%') as c";}
}
//sector
if($boolSector == true){
if(($boolPerm == true || $boolTemp == true || $boolPartTime == true || $boolFullTime == true) || $boolLocation == true){
if($sector == "All Social Care"){
$a=1;
$masterQuery="(SELECT * FROM ".$masterQuery." WHERE `Sector` LIKE '%Care Assistant%' OR `Sector` LIKE '%Care Manager%' OR `Sector` LIKE '%Child Care/Nanny%' OR `Sector` LIKE '%Elderly Care%' OR `Sector` LIKE '%Family Support%' OR `Sector` LIKE '%Learning Mentor%' OR `Sector` LIKE '%Qualified Social worker%' OR `Sector` LIKE '%Management%' OR `Sector` LIKE '%Social Work Assistant%' OR `Sector` LIKE '%Support Worker%' OR `Sector` LIKE '%Youth Worker%' OR '%Other%') as d";}
if ($sector == "All Nursing"){
$a=1;
$masterQuery="(SELECT * FROM ".$masterQuery." WHERE `Sector` LIKE 'Grade A,B,C' OR `Sector` LIKE '%Grade D%' OR `Sector` LIKE '%Grade E%' OR `Sector` LIKE '%Grade F%' OR `Sector` LIKE '%Grade G%' OR `Sector` LIKE '%Grade H%' OR `Sector` LIKE '%Grade I%' OR `Sector` LIKE '%Management%' OR `Sector` LIKE '%Midwifery%' OR `Sector` LIKE '%Staff Nurse%' OR `Sector` LIKE '%Student%' OR `Sector` LIKE '%Sister/ChargeNurse%' OR `Sector` LIKE '%Other%' OR `Sector` LIKE '%Nursing%') as d";}
if($sector == "All Allied Health"){
$a=1;
$masterQuery="(SELECT * FROM ".$masterQuery." WHERE `Sector` LIKE '%Audiology%' OR `Sector` LIKE '%Dental Auxiliary%' OR `Sector` LIKE '%Medical Assistant%' OR `Sector` LIKE '%Midwifery%' OR `Sector` LIKE '%Pharmacy%' OR `Sector` LIKE '%Physiotherapy%' OR `Sector` LIKE '%Optometry%' OR `Sector` LIKE '%Radiography%' OR `Sector` LIKE '%Other%') as d";}
if($a != 1){$masterQuery="(SELECT * FROM ".$masterQuery." WHERE `Sector` LIKE '%".$sector."%') as d";}
}
if(($boolPerm != true && $boolTemp != true && $boolPartTime != true && $boolFullTime != true) && $boolLocation != true){
$masterQuery="(SELECT * FROM jobs WHERE `Sector` LIKE '%".$sector."%') as d";
if($sector == "All Social Care"){
$masterQuery="(SELECT * FROM jobs WHERE `Sector` LIKE '%Care Assistant%' OR `Sector` LIKE '%Care Manager%' OR `Sector` LIKE '%Child Care/Nanny%' OR `Sector` LIKE '%Elderly Care%' OR `Sector` LIKE '%Family Support%' OR `Sector` LIKE '%Learning Mentor%' OR `Sector` LIKE '%Qualified Social worker%' OR `Sector` LIKE '%Management%' OR `Sector` LIKE '%Social Work Assistant%' OR `Sector` LIKE '%Support Worker%' OR `Sector` LIKE '%Youth Worker%' OR '%Other%') as d";}
if ($sector == "All Nursing"){
$masterQuery="(SELECT * FROM jobs WHERE `Sector` LIKE 'Grade A,B,C' OR `Sector` LIKE '%Grade D%' OR `Sector` LIKE '%Grade E%' OR `Sector` LIKE '%Grade F%' OR `Sector` LIKE '%Grade G%' OR `Sector` LIKE '%Grade H%' OR `Sector` LIKE '%Grade I%' OR `Sector` LIKE '%Management%' OR `Sector` LIKE '%Midwifery%' OR `Sector` LIKE '%Staff Nurse%' OR `Sector` LIKE '%Student%' OR `Sector` LIKE '%Sister/ChargeNurse%' OR `Sector` LIKE '%Other%' OR `Sector` LIKE '%Nursing%') as d";}
if($sector == "All Allied Health"){
$masterQuery="(SELECT * FROM jobs WHERE `Sector` LIKE '%Audiology%' OR `Sector` LIKE '%Dental Auxiliary%' OR `Sector` LIKE '%Medical Assistant%' OR `Sector` LIKE '%Midwifery%' OR `Sector` LIKE '%Pharmacy%' OR `Sector` LIKE '%Physiotherapy%' OR `Sector` LIKE '%Optometry%' OR `Sector` LIKE '%Radiography%' OR `Sector` LIKE '%Other%') as d";}
}
}
//keywords
if($boolKeywords == true){
if(($boolPerm == true || $boolTemp == true || $boolPartTime == true || $boolFullTime == true) || $boolLocation == true || $boolSector == true){
$masterQuery = "(SELECT * FROM ".$masterQuery." WHERE ".$keywords." ) as e";
}
else {$masterQuery = "(SELECT * FROM jobs WHERE ".$keywords." ) as e";}
}
if ($masterQuery == ""){$masterQuery = "SELECT * FROM jobs";}
else{$masterQuery = "SELECT * FROM ".$masterQuery;}
$results = getResults($db,$masterQuery);
display($results);
function getResults($db,$masterQuery){
$query = $db->query($masterQuery);
if(@$db->numRows($query)>0){
//we have a result
while($row=$db->fetchArray($query)){$results[] = $row;}
@mysql_free_result($query);
return $results;
}else{return $results;}
}
function display($results){
$resultCount = count($results);
//
$resultLimitLower = "1";
$resultLimitHigher = $resultCount;
include('results.inc.php');
if($resultCount == 0){echo "Nothing Found";}
else{
foreach($results as $result){include('result.inc.php');}
}
include('footer.inc.php');
}
?>
