0

I have been trying for days to sort some "products" from a database depending on a variable, I cannot figure this out. I tried all different methods and they don't work. This is what I have so far, I am new on this topic, so I would appreciate some explanation.

$resultsall = $conn->prepare("SELECT productName, productVendor, SUBSTRING(productDescription, 1, 150), quantityInStock, buyPrice, productcat, MSRP FROM products LEFT JOIN productlines ON products.productLine = productlines.productLine ORDER BY :orderby");
    $orderby = "buyPrice";
    $resultsall->bindParam(':orderby', $orderby, PDO::PARAM_STR);
    $resultsall->execute();
Carlino Gonzalez
  • 189
  • 1
  • 11
  • 1
    You can't use placeholders for table or column names, only for expressions. What you're doing is equivalent to `ORDER BY 'buyPrice'`, where you're giving it a literal string, not a column name. – Barmar Dec 28 '16 at 21:55
  • For something like this, you'll have to construct your SQL dynamically. You can use a [whitelist](https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet#Defense_Option_3:_White_List_Input_Validation) approach to make sure only valid column names make it into your query. – Don't Panic Dec 28 '16 at 22:11
  • @barmar I read if you display it as PDO::PARAM_INT you can. $resultsall->bindParam(':orderny', $order, PDO::PARAM_INT); I have not tested it. – user3606329 Dec 28 '16 at 22:22
  • No, that won't work either. An integer is only treated as a column number if it's a literal, not an expression that returns a number. – Barmar Dec 28 '16 at 22:23
  • @Barmar Actually, there is *sort of* a way to do this. Define it `DEFINE("id", ":orderby");` then `GROUP BY id ORDER BY id` then `$records->bindParam(':orderby', $orderby, PDO::PARAM_INT);` which would work. Ok... I know what you're *probably* going to tell me: *"what use is there to define it and then doing that, and not just simply an `ORDER BY id` without a prepared statement?"*. This in all theory, works. – Funk Forty Niner Dec 28 '16 at 22:32
  • @Fred-ii- Since the point of this is to order by a dynamically-determined column, even if that's possible it's useless. – Barmar Dec 28 '16 at 22:33
  • @Barmar Yes I know and that's why you didn't get my *"I know what you're probably going to tell me"* - I was way ahead of you on this one. I just wanted to prove somewhat of a point, that it wasn't impossible. Let's call it a *"pseudo bind"* ;-) – Funk Forty Niner Dec 28 '16 at 22:34
  • @Barmar could you explain me how to sort the products by column then? I cannot find any information about this anywhere else – Carlino Gonzalez Dec 28 '16 at 22:39
  • @CarlinoGonzalez The duplicate question shows how to do it: `ORDER BY $orderby` – Barmar Dec 28 '16 at 22:41
  • @Barmar thank you I got it :) – Carlino Gonzalez Dec 28 '16 at 22:57

0 Answers0