Error on searching for a record in bootgrid

Question

I converted a bootgrid plugin (code snippet) from mysqli to pdo. The records are retrieved but I can not perform a search. Surprisingly, the mysqli version of the code works just fine (both retrieval and search). Running the query in mysql works just fine so not sure what I am doing wrong.

The function [block] that is generating the errors:

function getRecords($params) {
        $sql = $sqlRec = $sqlTot = $where = '';
        $rp = isset($params['rowCount']) ? $params['rowCount'] : 10;

        if (isset($params['current'])) {
            $page = $params['current'];
        } else {
            $page = 1;
        };
        $start_from = ($page - 1) * $rp;
        if (!empty($params['searchPhrase'])) {
            $where .= "WHERE";
            $where .= "name LIKE '" . $params['searchPhrase'] . "%' ";
        
        }
        if (!empty($params['sort'])) {
            $where .= " ORDER By " . key($params['sort']) . ' ' . current($params['sort']) . " ";
        }
        // getting all records without any search    

        $sql = "select * from students";
        $sqlTot .= $sql;
        $sqlRec .= $sql;

        //concatenate search sql if value exist
        if (isset($where) && $where != '') {

            $sqlTot .= $where;
            $sqlRec .= $where;
        }
        if ($rp != -1) {
            $sqlRec .= " LIMIT " . $start_from . "," . $rp;
        }

        $qtot = $this->conn->prepare($sqlTot);
        $qtot->execute();


        $queryRecords = $this->conn->prepare($sqlRec);
        $queryRecords->execute();

        while ($row = $queryRecords->fetch(PDO::FETCH_ASSOC)) {
            $this->data[] = $row;
        }
        $json_data = array(
            "current" => intval($params['current']),
            "rowCount" => 10,
            "total" => intval($qtot->rowCount()),
            "rows" => $this->data   // total data array
        );
        return $json_data;
    }

Error log from the console:

Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 in ..\inc\process.php:95
Stack trace:
#0 ..\process.php(95): PDOStatement-&gt;execute()
#1 ..\process.php(27): Candidate-&gt;getRecords(Array)
#2 {main}
  thrown in <b>..\process.php</b> on line <b>95</b><br />```

this would not produce the error,. But you are using prepared statemenst wrong so this is still vulnerable to sql injection. also it will help you see https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — nbk, Oct 31 '20 at 00:44
@nbk, the actual query is a bit complex with 3 different joint tables. That works fine too (in mysql). Except it does not work within the function aforementioned. I only sent a simplified version (query) since either of which, I get the same errors from the console. — K.Chams, Oct 31 '20 at 00:50
try the normal prepared statemnnts with parameters, becaue i would gues the the text is making the problem, but as you didn't show the complete query this will only be a guess, so try it and see — nbk, Oct 31 '20 at 00:53
Okay @nbk. Once the search works fine, I shall use prepared statement as advised. Thank you. — K.Chams, Oct 31 '20 at 00:54
you didn't understand you should try it **now** it will resolve your problem. — nbk, Oct 31 '20 at 00:59

Error on searching for a record in bootgrid

0 Answers0