0

The Objective

I am creating a dynamic statement by using AJAX to pass on a string to PHP. I am using Ajax to pass on this string by first creating an array in Javascript of a group of selected checkbox elements, then using array.join to create the statement. There are two separate groups of checkbox elements, one is based on staff location, the other is based on staff department. The final search can either involve one or both categories of checkboxes which will ultimately filter and narrow down the results based on this search criteria.

Here is the Javascript code below - executed once the user cliks on the search button. As you will see, its a loop which goes through the checkboxes to see which ones have been checked, then pushes them into an array, before creating the statement via join method.

document.getElementById("submitFilterSearch").addEventListener("click",()=>{
    alert("working");
    const filteredDepartments = document.querySelectorAll(".deptCB");
    const filteredLocations = document.querySelectorAll(".locCB");

    let departmentsArr = [];
    let locationArr = [];

    //push item values into the arrays to create dynamic SQL query search term, concat array once done and sent through ajax
    filteredDepartments.forEach((checkbox, index)=>{
        if (checkbox.checked) {
            departmentsArr.push(checkbox.value);
        }
    })

    filteredLocations.forEach((checkbox, index)=>{
        if (checkbox.checked) {
            locationArr.push(checkbox.value);
        }
    })

    let depQuery = departmentsArr.map((value, index)=>{
        if (index == 0){
            return "d.id = "+value;
        } else {
            return "OR d.id = "+value;
        }
    })

    let locQuery = locationArr.map((value, index)=>{
        if (index == 0){
            return "l.id = "+value;
        } else {
            return "OR l.id = "+value;
        }
    })      

    let depQueryStr = depQuery.join(" ");
    let locQueryStr = locQuery.join(" ");       

    $.ajax({
        url: "./libs/php/filteredSearch.php",
        type: "POST",
        dataType: "JSON",
        data: {
            depFilter: depQueryStr,
            locFilter: locQueryStr
        },
        success: function(success){
            console.log(success);
        },
        error: function(xhr, txts, err){
            console.log(err)
        }
    })


})

Here is what the final joined arrays hold before sending off to PHP

d.id = 2 OR d.id = 3 OR d.id = 9
l.id = 1 OR l.id = 2 OR l.id = 3

Here is the PHP code for creating the dynamic statement

$queryString = 'SELECT p.id, p.lastName, p.firstName, p.jobTitle, p.email, 
                        d.name as department, 
                        l.name as location, l.id as locationID, d.id as departmentID 
                FROM personnel p 
                    LEFT JOIN department d ON (d.id = p.departmentID) 
                    LEFT JOIN location l ON (l.id = d.locationID) 
                WHERE ';


$depQuery = $_REQUEST["depFilter"];
$locQuery = $_REQUEST["locFilter"];

if ( isset($_REQUEST["depFilter"]) && isset($_REQUEST["locFilter"]) ){
    global $queryString;
    $queryString.=$depQuery." AND ".$locQuery;
} else if ( isset($_REQUEST["depFilter"]) && !isset($_REQUEST["locFilter"]) ){
    global $queryString;
    $queryString.=$depQuery;        
} else {
    global $queryString;
    $queryString.=$locQuery;        
}

$result = $conn->query($queryString);

if (!$result) {

    $output['status']['code'] = "400";
    $output['status']['name'] = "executed";
    $output['status']['description'] = "query failed";  
    $output['data'] = [];

    mysqli_close($conn);

    echo json_encode($output); 
    exit;
}
   
$data = [];

while ($row = mysqli_fetch_assoc($result)) {
    array_push($data, $row);
}

$output['status']['code'] = "200";
$output['status']['name'] = "ok";
$output['status']['description'] = "success";
$output['status']['returnedIn'] = (microtime(true) - $executionStartTime) / 1000 . " ms";
$output['data'] = $data;

mysqli_close($conn);

echo json_encode($data); 

The Problem: When I select the checkboxes from BOTH categories, the statement works as expecting and brings back the results. However, when I choose only ONE of the categories from the checkboxes, and therefore on only one of the joined arrays is sent through as a value, the response I get through the status is "code: "400", name: "executed", description: "query failed""

Where am I going wrong? I presume the if statement within the PHP code.

Also, perhaps you can already tell from my code and approach, that I am new to using SQL with PHP. I know that SQL injections is also something I need to look into a bit more, so if anyone can give me advice on if this is a safe enough approach, that is also appreciated!

jimmy118
  • 313
  • 2
  • 10
  • 1
    Why on earth are you using `global $queryString;` all the code is in the same scope – RiggsFolly Aug 29 '21 at 14:04
  • 3
    Don't build the query in JS! Only send the values to PHP and let PHP build the statement. The way you're doing it now is extremely insecure and are _wide open_ for SQL injection attacks! _Never_ trust user data! Read Read [how to prevent SQL injection in PHP](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) to learn how to protect yourself. – M. Eriksson Aug 29 '21 at 14:05
  • 1
    Remember, I can stop JS Running, I can intercept JS and send exactly what I want to in place of what your JS does :) – RiggsFolly Aug 29 '21 at 14:06
  • Okay, I have fixed the issue regarding the global call, and I will take your advice @MagnusEriksson and create within PHP, but just from an educational stand-point, what did I do wrong in the above code in regards to getting the results I wanted? Is something wrong within the statement itself? Please, if you know, your feedback will be greatly appreciated. – jimmy118 Aug 29 '21 at 14:07
  • Thanks for the comment @RiggsFolly, what would you say is a better alternative? Perhaps using the POST method directly with form to send the results to PHP? – jimmy118 Aug 29 '21 at 14:08
  • 3
    I would recommend that you spend your time reading up about and rewriting your queries using prepared statements and bound parameters (read the link I posted and the manual to learn more) instead of keep debugging this. It's a waste of time debugging code that you just need to rewrite anyway. – M. Eriksson Aug 29 '21 at 14:09
  • 1
    **Find out for yourself!** To get errors out of PHP even in a LIVE environment add these 4 lines to the top of any `MYSQLI_` based script you want to debug `ini_set('display_errors', 1); ini_set('log_errors',1); error_reporting(E_ALL); mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);`. This will force any `MYSQLI_` errors to generate an Exception that you can see on the browser as well as normal PHP errors. – RiggsFolly Aug 29 '21 at 14:11
  • 2
    Okay will do, I'll make you proud ! – jimmy118 Aug 29 '21 at 14:18

0 Answers0