Unable to sort data by price in PHP an AJAX

Question

Am trying to sort data from sql based on price Low to High and high to Low so i tried with below code and it doesn't sort by ASC or DESC

i get Syntax error or access violation basically i tried every solution, and couldn't sort data by price.

HTML

<li class="dropdown-item">
 <input type="radio" class="filter_all sort" name="sort" id="new" value="new">
 <label for="new">Date Published</label>                                                        
</li>
<li class="dropdown-item">
 <input type="radio" class="filter_all sort" name="sort" id="asc" value="ASC">
 <label for="asc">Price : Low to High</label>
</li>
<li class="dropdown-item">
  <input type="radio" class="filter_all sort" name="sort" id="desc" value="DESC">
  <label for="desc">Price : High to Low</label>
</li>

SCRIPT

       $(document).ready(function () {
            filter_data();
            function filter_data() {
                $.post(
                        "fetch.php",
                        {
                            action: 'fetch_data',
                            cate: get_filter('cate'),
                            brand: get_filter('brand'),
                            model: get_filter('model'),
                            sort: get_filter('sort')
                        }
                )
                        .done(function (data) {
                            $('.filter_data').html(data);
                        });
            }
            function get_filter(class_name) {
                var filter = [];
                $('.' + class_name + ':checked').each(function () {
                    filter.push($(this).val());
                });
                return filter;
            }
            $('.filter_all').click(function () {
                filter_data();
            });
        });

PHP

if (isset($_POST["action"])) {
$query = "SELECT * FROM allpostdata WHERE sts = '1'";


if (!empty($_POST['cate'])) {
    $query .= " AND sca IN (" . str_repeat("?,", count($_POST['cate']) - 1) . "?)";
} else {
    $_POST['cate'] = []; // in case it is not set 
}

if (!empty($_POST['brand'])) {
    $query .= " AND product_brand IN (" . str_repeat("?,", count($_POST['brand']) - 1) . "?)";
} else {
    $_POST['brand'] = []; // in case it is not set 
}

if (!empty($_POST['model'])) {
    $query .= " AND mdl IN (" . str_repeat("?,", count($_POST['model']) - 1) . "?)";
} else {
    $_POST['model'] = []; // in case it is not set 
}

if (!empty($_POST['sort'])) {
    $query .= " GROUP BY prs ORDER BY prs (" . str_repeat("?,", count($_POST['sort']) - 1) . "?)";
} else {
    $_POST['sort'] = []; // in case it is not set 
}

$stmt = $conn->prepare($query);
$params = array_merge($_POST['cate'], $_POST['brand'], $_POST['model'], $_POST['sort']);
$stmt->execute($params);
$result = $stmt->fetchAll();
$total_row = $stmt->rowCount();
$output = '';

Can some one help me how do i sort data based on price.

Answer 1

You can't parameterise items in the the ORDER BY clause.

To resolve this safely, just have a simple whitelist of the allowed values (for validation) and then include the requested value using regular string concatenation:

So, if the $_POST["sort"] variable is an array with always exactly one item in it, and that item will contain either ASC or DESC then it would be something like this:

if (!empty($_POST['sort'])) { 
  if ($_POST["sort"][0] == "ASC" || $_POST["sort"][0] == "DESC") { //simplistic whitelist
    $query .= " GROUP BY prs ORDER BY prs " . $_POST['sort'][0]; 
  } 
}

(N.B. you also had some incorrect brackets there in the SQL).

And then change the parameter list to exclude the "sort" value:

$params = array_merge($_POST['cate'], $_POST['brand'], $_POST['model']);