Datatables - Table Plugin for Jquery - Error searching string

Question

Good morning I’m using Datatables - Table plugin for Jquery, is a code of the old developer where correctly loads the database data however, it has a field "Search:" at the top where type something and must search in the table, this field is not working.

If I type something returns the same result as when starting the table, that is, all the survey data without any like Where (query).

The code is based on https://datatables.net/

In the view file I have a javascript code:

// ----- tabela de clientes

var oTable = $('#tblSearch').dataTable({
    processing: true,
    serverSide: true,
    //searching: false,
    ajax: {
        url: "<?php echo $this->url('clients', array('action' => 'getFilteredClients')); ?>",
        type: "POST",
        dataSrc: function (json) {
            for(var i = 0; i < json.data.length; i++) {
                let f_active = json.data[i][1];
                let f_id = json.data[i][2];

                if (!f_active) {
                    json.data[i][0] = "<span class='client-disabled'>" + json.data[i][0] + "</span>";
                }

                json.data[i][1] = (f_active == 1) ? "Ativo" : "Inativo";

                json.data[i][2] = '<a href="/pgj/clients/' + f_id + '/load" class="btn btn-primary btn-xs" title="Editar"><i class="glyphicon glyphicon-edit"></i></a> ';
                json.data[i][2] += '<a href="/pgj/clients/' + f_id + '/delete" class="btn btn-danger btn-xs" title="Apagar"><i class="glyphicon glyphicon-remove"></i></a> ';

                if (f_active == 1) {
                    json.data[i][2] += '<a href="/pgj/clients/' + f_id + '/inactive" class="btn btn-danger btn-xs" style="opacity: .65;  filter: alpha(opacity=65)" title="Inativar"><i class="glyphicon glyphicon-eye-close"></i></a>';
                } else {
                    json.data[i][2] += '<a href="/pgj/clients/' + f_id + '/active" class="btn btn-success btn-xs" title="Ativar"><i class="glyphicon glyphicon-eye-open"></i></a>';
                }
            }

            return json.data;
        }
    },
    columnDefs: [
        { targets: "no-sort", orderable: false }
    ],
    columns: [
        { className: "" },
        { className: "text-center" },
        { className: "text-center" }
    ],
    order: [
        [ 0, "asc" ]
    ]
});

});

In the controller file I have the following code:

public function getFilteredClientsAction()
{
    try {
        self::__autoload();

        $putParams = $this->getRequest()->getPost();

        $limit = $putParams["length"];
        $offset = $putParams["start"];
        $orderByColumn = $putParams["order"][0]["column"];
        $orderByDir = $putParams["order"][0]["dir"];

        $orderBy = "";
        switch ($orderByColumn) {
            case "0": $orderBy = "f_name $orderByDir"; break;
            case "1": $orderBy = "f_active $orderByDir"; break;
        }

        $listCount = $this->clientsDAO->getFilteredClientsTotal();
        $list = $this->clientsDAO->getFilteredClients( $limit, $offset, $orderBy);

        $listData = [];
        foreach ($list as $client) {
            $listData[] = [
                $client["f_name"],
                $client["f_active"],
                $client["f_id"]
            ];
        }

        return new JsonModel([
            "recordsTotal" => $listCount,
            "recordsFiltered" => $listCount,
            "data" => $listData
        ]);
    } catch (\Exception $e) {
        var_dump($e->getMessage());
        die();
    }
}

In the Model file I have the following related methods:

    private function getFilteredClientsQuery($limit = "all", $offset = 0, $orderBy = "") 
{
    $sql = "SELECT f_id, f_name, f_active from clients";

    if (!empty($orderBy)) {
        $sql .= " ORDER BY $orderBy ";
    }

    $sql .= " LIMIT $limit OFFSET $offset ";

    return $sql;
}




public function getFilteredClients($limit = "all", $offset = 0, $orderBy = "")
{
    $sql = $this->getFilteredClientsQuery($limit, $offset, $orderBy);

    return $this->tableGateway->getAdapter()->query($sql, array())->toArray();
}   

public function getFilteredClientsTotal()
{
    $sql = $this->getFilteredClientsQuery();

    $sql = "SELECT COUNT(f_id) as total FROM (" . $sql . ") as counter";

    $result = $this->tableGateway->getAdapter()->query($sql, array())->toArray();

    return $result[0]["total"];
}

Answer 1

Your PHP code (specifically the function getFilteredClientsQuery) does not include any where in the query.

private function getFilteredClientsQuery($name = "", $limit = "all", $offset = 0, $orderBy = "") 
{
    $sql = "SELECT f_id, f_name, f_active from clients";

    // deveria incluir o where abaixo
    if ($name != "") {
       $sql .= " WHERE f_name LIKE '%$name%'";
    }

    if (!empty($orderBy)) {
        $sql .= " ORDER BY $orderBy ";
    }

    $sql .= " LIMIT $limit OFFSET $offset ";

    return $sql;
}

But an observation: is not the best example because it is inserting the value of the direct variable in the query (it could be vulnerable to SQL Injection). If you are using PDO, the ideal is to prepare the query and send it as a parameter. From your other function it looks like it accepts parameters, so maybe you could do this:

// retira o name como argumento aqui
private function getFilteredClientsQuery($limit = "all", $offset = 0, $orderBy = "") 
{
    $sql = "SELECT f_id, f_name, f_active from clients";

    // deveria incluir o where abaixo
    if ($name != "") {
       $sql .= " WHERE f_name LIKE ?";
    }

    if (!empty($orderBy)) {
        $sql .= " ORDER BY $orderBy ";
    }

    $sql .= " LIMIT $limit OFFSET $offset ";

    return $sql;
}

// coloca como parametro aqui
public function getFilteredClients($name = "", $limit = "all", $offset = 0, $orderBy = "")
{
    $sql = $this->getFilteredClientsQuery($limit, $offset, $orderBy);
    // pela assinatura do metodo query abaixo, parece que aceita input como parâmetro
    // você precisa verificar se é seguro
    return $this->tableGateway->getAdapter()->query($sql, array("%$name%"))->toArray();
}