CODEWITHSUNDEEP
phpmysql
0xgareth
0xgareth

Reputation: 621

Sorting not working for PHP SQL ORDER BY

Hi I've managed to sort a table by column when each is clicked. This is my table:

<table class="table table-hover">
            <thead>
                <tr>
                    <th><a href="?sort=id">Game ID </a></th>
                    <th><a href="?sort=title">Game title </a></th>
                    <th><a href="?sort=developer">Developers </a></th>
                    <th><a href="?sort=release">Year of release </a></th>
                    <th><a href="?sort=stock">No. of items in stock </a></th>
                    <th><a href="?sort=price">Cost price </a></th>
                    <th>Options</th>
                </tr>
            </thead>

I then use this to order the table according to the column selected.

if (isset($_GET['sort'])){
                  $sort = $_GET['sort'];
                  $query = "SELECT * FROM Games ORDER BY " . $sort . ";";
                  $result = mysqli_query($connection, $query);

(Underneath this code I use a loop to insert data into the table)

All works perfectly apart from my 'Year of release' column, when I click this the table empties.

I have no idea why this is happening.

Upvotes: 1

Views: 600

Answers (1)

chris85
chris85

Reputation: 23892

Release is a reserved term. You need to encapsulate it in backticks; or change your column name. This approach you are taking also opens you to SQL injections.

I'd add make a white list of terms $_GET['sort'] could be and compare that against $_GET['sort']. Rough untested code example:

$valid_columns = array('id', 'title', 'release', 'developer', 'stock', 'price');
if (in_array($_GET['sort'], $valid_columns)) {
     $sort = $_GET['sort'];
     $query = "SELECT * FROM Games ORDER BY `" . $sort . "`;";
//execute query and valid stuff
} else {
     echo 'invalid column supplied
}

This way you know what is going to end up in your SQL; otherwise a user could add anything which may cause errors on hang up your application.

Upvotes: 3

Related Questions