PHP sqlsrv_num_rows Error whilst populating jQuery datatables

Question

I'm trying to populate a jQuery datatables table by pulling information from SQL Sever 2016.

I'm getting PHP Warning: sqlsrv_num_rows() expects parameter 1 to be resource, bool given in... in my server-side processing PHP file and trying to figure out what am I missing.

On my investigation so far I realized the error is occuring due to conditional search set up in if (isset($_POST['search']['value'])) {} function, which I'd appreciate your help to indentify a fix.

Function to append datatables search input looks like below.

if (isset($_POST['search']['value'])) {

    $search_value = $_POST['search']['value'];
    $sql .= " WHERE SalesOrderNumber like '%" . $search_value . "%'";
    $sql .= " OR Reference like '%" . $search_value . "%'";
    $sql .= " OR OrderCustomerId like '%" . $search_value . "%'";
    $sql .= " OR Total like '%" . $search_value . "%'";

    $query = sqlsrv_query($con, $sql, array(), array("Scrollable" => "buffered"));
    $get_count_rows = sqlsrv_num_rows($query);
}

I tried to throw in a hard coded value to search funtion to assess if the function works, and yes then the page loads fine.

if (isset($_POST['search']['value'])) {

    $search_value = $_POST['search']['value'];
    $sql .= " WHERE SalesOrderNumber like '%560%'";

    $query = sqlsrv_query($con, $sql, array(), array("Scrollable" => "buffered"));
    $get_count_rows = sqlsrv_num_rows($query);
}

Can someone please point me where the potential error is? I'm not seeing a syntax error inside the search function.

Full snippet below:

<?php include('connection.php');

$output = array();
$sql = "SELECT * FROM dr.SalesOrder";

if (isset($_POST['search']['value'])) {
    $search_value = $_POST['search']['value'];
    $sql .= " WHERE SalesOrderNumber like '%" . $search_value . "%'";
    $sql .= " OR Reference like '%" . $search_value . "%'";
    $sql .= " OR OrderCustomerId like '%" . $search_value . "%'";
    $sql .= " OR Total like '%" . $search_value . "%'";
    $query = sqlsrv_query($con, $sql, array(), array("Scrollable" => "buffered"));
    $get_count_rows = sqlsrv_num_rows($query);
}

$totalQuery = sqlsrv_query($con, $sql, array(), array("Scrollable" => "buffered")); 
$total_all_rows = sqlsrv_num_rows($totalQuery);

if (isset($_POST['order'])) {
    $column_name = $_POST['order'][0]['column'];
    $order = $_POST['order'][0]['dir'];
    $sql .= " ORDER BY " . $column_name . " " . $order . "";
} else {
    $sql .= " ORDER BY SalesOrderNumber DESC";
}

if ($_POST['length'] != -1) {
    $start = $_POST['start'];
    $length = $_POST['length'];
    $sql .= " OFFSET  " . $start . " ROWS"; 
    $sql .= " FETCH NEXT  " . $length . " ROWS ONLY";
}

$query = sqlsrv_query($con, $sql, array(), array("Scrollable" => "buffered")); 
$count_rows = sqlsrv_num_rows($query);
$data = array();
while ($row = sqlsrv_fetch_array($query, SQLSRV_FETCH_ASSOC)) {
    $sub_array = array();
    $sub_array[] = $row['SalesOrderNumber'];
    $sub_array[] = $row['Reference'];
    $sub_array[] = $row['OrderCustomerId'];
    $sub_array[] = $row['Total'];
    $sub_array[] = $row['CreatedBy1'];
    $sub_array[] = $row['CreatedBy2'];
    $sub_array[] = $row['CreatedBy3'];
    $sub_array[] = '<a href="javascript:void();" data-id="' . $row['CreatedBy'] . '"  class="btn btn-info btn-sm editbtn" >Edit</a>  <a href="javascript:void();" data-id="' . $row['CreatedBy'] . '"  class="btn btn-danger btn-sm deleteBtn" >Delete</a>';
    $data[] = $sub_array;
}

$output = array(
    'draw' => intval($_POST['draw']),
    'recordsTotal' => $count_rows,
    'recordsFiltered' => empty($_POST['search']['value']) ? $total_all_rows : $get_count_rows,
    'data' => $data,
);

echo  json_encode($output);

?>

Your problem is likely that your `sqlsrv_query()` function returns a resource when it works (which is likely the query result object), but returns `false` when there is an error (such as when you use badly formed SQL). Since you're not using any error handling code here, if `$query` (or `$totalQuery`) ends up being `false`, then the next function that tries to use it (as a resource) will choke on it. — Raxi, Dec 25 '21 at 03:53
so there problem is 2-fold. (A) you have bad SQL code in a query. (B) you're not properly handling the situation that occurs when a query fails — Raxi, Dec 25 '21 at 03:54
Problem A is caused by the way you're manually assembling the `$sql` in various places by simply concatenating raw strings into it, that is a horrible thing to do and you should read up on using prepared statements to both (1) not mess that up and (2) not end up with horribly insecure leaky code and a compromised database. -- https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php/60496#60496 — Raxi, Dec 25 '21 at 03:57
To sort Problem B, you likely want to change your `$query = sqlsrv_query(...);` lines into something like: `if (!($query = sqlsrv_query(...))) { die('Database error'); }` or have it return some error-indication-json instead of `$output` — Raxi, Dec 25 '21 at 04:00
Raxi pretty much nailed it... as per the [sqlsrv_query documentation](https://www.php.net/manual/en/function.sqlsrv-query.php), _Returns a statement resource on success and false if an error occurred._ — AlwaysLearning, Dec 25 '21 at 08:28
You have major SQL injection issues here, I suggest you read up on parameterized queries. `$column_name` and `$order` would need to be whitelisted as they cannot be parameterized — Charlieface, Dec 25 '21 at 18:44

PHP sqlsrv_num_rows Error whilst populating jQuery datatables

0 Answers0