OK, I'm making something with PHP and hit a bit of a wall. I've tried what I think should work, but it doesn't, and results on Google weren't really much help.
I want to order the results of a select query based on what the user inputs into a form, so if they want it to order by column1 descending, or column3 ascending, they choose those options from the form, then the stuff they put in is assigned to a variable... this is my query now:
$select="SELECT * FROM `table_name` ORDER BY `{$orderby}` {$orderdir}";
And I get this:
Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in (path to file and line number)
Now, I know that it
can be sorted how I want it, if I manually put in the table name and the order into the query, instead of the variable, it sorts it perfectly well, but it doesn't work if it uses the variables.
Help??
Shouldn't it be:
$select = "SELECT * FROM `table_name` ORDER BY '{$orderby}' {$orderdir}";
The accents you've used are for tables/fields... at least, that's the only thing I can see wrong...
In fact, you can simply miss out accents altogether and use {$orderby}...
Tried that, I've used ' ` and none at all, still the same thing... that's what I don't get, can't really see what would cause it to not work...
Edit: When I echo the query it just says:
SELECT * FROM `table_name` ORDER BY
So it isn't including the variables. However, I'm also echoing the variables themselves and they are what they need to be...
2nd edit: I can now get it to show the correct query but it
still doesn't work.
You'll need to create a "link" to your database. Here's how that works:
// Creates the link to the Database
$link = mysqli_connect($host, $username, $password);
mysqli_select_db($link, $name);
$select = "SELECT * FROM `table_name` ORDER BY '{$orderby}' {$orderdir}";
$query = mysqli_query($link, $select);
// Loads array of the data
$data = mysqli_fetch_assoc($query);
echo $data['what ever field you need'];
I had all that, just figured it out though, made a small typo in the form which I'd copied and pasted to all of them so it wasn't getting the info properly
Thanks all that chimed in
How are those variables initialized? If you take their values 1:1 from a form, you're probably vulnerable to SQL injections there.
Even if they're an option in a dropdown box??
Haven't really looked into making sure it's secure just yet though, just getting it working for now, but it's on my list.
Yes, even if it's in a drop down box. Quite simple to SQL inject through them actually. Using a program such as firebug (easiest), or even just saving the page onto our desktop, change the value, and submit the form which processes through your website.
(2009-04-25, 04:59 PM)MattRogowski Wrote: [ -> ]Even if they're an option in a dropdown box??
You don't need a drop down box (or a form for that matter) to submit a form with arbitrary values in it.
When it comes to SQL statements and user input:
- escape all user input ($somestring = $db->escape_string($somestring))
- only allow user input in strings e.g. "INSERT INTO ... VALUES ('$somestring')"
if other parts of the query, such as order, depend on user input, don't use the user input directly, but instead do something like:
if($form['asc'] == '1')
{
$order = "ORDER BY field ASC";
}
else
{
$order = "ORDER BY field DESC";
}
so you can be sure your own static order string is being used and not some user input that could contain something other than a simple order statement or field name.
Make sure to escape integers with intval ( ) when inserting into the database, as well.