PHP - Can you use prepared statements with a * and ORDER BY?

Question

Can you use prepared statements with a * and ORDER BY?

Because I can't figure out how to do it. I've been trying for awhile now. I've looked at a few other threads but haven't found out how. If not, how could I go about getting a mysqli query like this and prevent sql-injection?

Would appreciate any ideas or critiques.

Thanks, Matt

<?php
    require ($_SERVER['DOCUMENT_ROOT'].'/db-connect.php');
    $conn = new mysqli($servername, $username, $password, $dbname);
    if ($conn->connect_error) {
                    die("Connection failed: " . $conn->connect_error);
    }

    if ($stmt = $conn->prepare("SELECT * FROM websites ORDER BY ? DESC LIMIT 1")){
    $id = 'id';

    $stmt->bind_param('s',$id);

    $stmt->execute();

    $stmt->store_result();

    $result = $stmt->get_result();

    $row = $result->fetch_assoc();

    echo $row['title'];


    $stmt->free_result();

    $stmt->close();

    }

$conn->close();

?>

The first thing this tells you is that you need to enable error reporting and / or verify the variables you're operating over to make sure your connector didn't report an error back to you. — Marco Aurélio Deleu, Apr 15 '16 at 01:10
well I was trying to set it to my id column with a placeholder which apparently doesn't work — Matthew Ediger, Apr 15 '16 at 01:11

score 1 · Answer 1 · answered Apr 15 '16 at 01:13

1

You can't use placeholders for column names. You will need to either hard code it in:

$stmt = $conn->prepare("SELECT * FROM websites ORDER BY `id` DESC LIMIT 1");

or do something like this to allow user input:

switch ($_POST['sortby']) {
    // Add all possible column names here
    case 'id':
    case 'col2':
    case 'col3':
    case 'col4':
    case 'col5':
        $sortby = $_POST['sortby'];
        break;
    default:
        throw new Exception("Invalid sort by");
}
$stmt = $conn->prepare("SELECT * FROM websites ORDER BY `$sortby` DESC LIMIT 1");

answered Apr 15 '16 at 01:13

Mike

23,542
14
76
87

1

or another alternative is a whitelist array of columns, the just use `in_array` – Kevin Apr 15 '16 at 01:55
@Ghost Yes, that would work as well, and less lines of code. – Mike Apr 15 '16 at 04:09

PHP - Can you use prepared statements with a * and ORDER BY?

1 Answers1