post variable in mysql query

Question

I'd like to use the post variable value in my sql query to sort data in order chosen by a user. The table gets displayed correctly with appropriate fields but the values are not sorted.

I'm aware this is subject to sql injection, however, I'm doing this for training purposes on my local server.

<?php
$sort_in = $_POST['SortIn'];

$sql = 'select * from db.Runner order by "'.$_POST['SortIn'].'"';
    $result = mysql_query($sql, $con);

    if($result)
    {
        echo "<table border = '1'>
        <tr>
        <th>RunnerID</th>
        <th>EventID</th>
        </tr>";

        while($row = mysql_fetch_array($result))
        {
            echo "<tr><td>";
            echo $row['RunnerID'];
            echo "</td><td>";
            echo $row['EventID'];
            echo "</td><td>";
            </tr>";
        }
        echo "</table>";
?>

If you're doing it for training purposes, you should ditch the obsolete/deprecated mysql_*() functions and learn with at least mysqli, or PDO. — Marc B
– Marc B, Commented Dec 9, 2013 at 14:45

CompuChip · Accepted Answer · 2013-12-09 16:43:03Z

5

You are currently producing and running a query like

select * from db.Runner order by "fieldname";

which should of course be either of

select * from db.Runner order by fieldname;
select * from db.Runner order by `fieldname`;  -- for MySql
select * from db.Runner order by [fieldname];  -- for MSSQL

(I suggest one of the last two, depending on your database, in case your field name happens to be "order", for example).

Remove the double quotes

$sql = 'select * from db.Runner order by '.$_POST['SortIn'];

and possibly replace them by the appropriate delimiter, e.g.

$sql = 'select * from db.Runner order by `'. $_POST['SortIn'] . '`';

You already mentioned SQL injection and mysql_ vs mysqli_ so I'll keep my mouth shut today ;) Although I don't really see a reason - even for a training project on localhost - not to do it right, to be honest.

[edit]After posting this answer, some useful comments were made to your OP by MarcB and to the other answer by zan. Despite this being training, please heed them, as they are good advice!

edited Dec 9, 2013 at 16:43

answered Dec 9, 2013 at 14:43

CompuChip

9,2524 gold badges28 silver badges49 bronze badges

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

2 Comments

zan Over a year ago

I'd like to add another variable $_POST['SortBy'] to sort in descending order if a user wishes to do that. How do I do that? Thanks

CompuChip Over a year ago

You could pass in SortOrder as +1 or -1 and add something like ($_POST['SortOrder'] == -1 ? "DESC" : "ASC") to your query.

Ronald Swets · Accepted Answer · 2013-12-09 14:43:24Z

1

You actually sorting on a string instead of a field, remove the quotes in the query:

$sql = 'select * from db.Runner order by '.$_POST['SortIn'];

P.S. I won't start a rant about the injection ;)

answered Dec 9, 2013 at 14:43

Ronald Swets

1,65710 silver badges16 bronze badges

2 Comments

zan Over a year ago

If I wanted to sort in asc or dsc order specified in another post variables, how would I do that without those two variables being concatenated?

Marcel Korpel Over a year ago

@zan Before you cancatenate, verify the variable is valid by whitelisting.

Collectives™ on Stack Overflow

post variable in mysql query

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related