Sorting not working for PHP SQL ORDER BY

Question

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.

will try it now bare with me, thanks in advance

0xgareth
– 0xgareth

2015-11-28 19:02:00 +00:00
Commented Nov 28, 2015 at 19:02 — 0xgareth
– 0xgareth, Commented Nov 28, 2015 at 19:02

chris85 · Accepted Answer · 2015-11-28 18:45:24Z

3

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.

edited Nov 28, 2015 at 18:45

answered Nov 28, 2015 at 18:39

chris85

23.9k7 gold badges36 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

chris85 Over a year ago

When it goes to the DB it needs to be in backticks. The code above should handle that, ORDER BY `" . $sort . "`. So it would be when it gets to the DB SELECT * FROM Games ORDER BY `release`;.

Collectives™ on Stack Overflow

Sorting not working for PHP SQL ORDER BY

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related