CODEWITHSUNDEEP
phpbindinggetpdoparameter-passing
Adonis K. Kakoulidis
Adonis K. Kakoulidis

Reputation: 5132

binding PDO mysql parameter from $_get doesn't work

// url: http://localhost/asdf/?sort=credits


if(isset($_GET['sort'])){
    $sort = $_GET['sort'];
}

$statement = $db->prepare("SELECT * FROM myTable ORDER BY :sort");
$statement->bindParam(':sort', $sort, PDO::PARAM_STR, 8);

var_dump($statement);
//object(PDOStatement)[2] public 'queryString' => string 'SELECT * FROM table1 ORDER BY :sort' (length=36)

$statement->execute();

Im trying to an ordering option from the get parameters, can anyone help me out?

update as suggest by @Uchiha Madara, i created some predifed ordering strings

if(isset($_GET['sort'])){
    switch ($_GET['sort']){
        case 'val1':
            $sortName = 'val1';
            break;
        case 'val2':
            $sortName = 'val2';
            break;

        default:
            $sortName = false;
            break;
    }
}

if($sortname != false){
    // then prepare query
    $statement = $db->prepare("SELECT * FROM table1 ORDER BY ?");
    $statement->execute(array($sortName));
}

but still same result, ordering choice is ignored

last update!

$sort = 'val1';

$orderList = array(
    'val1',
    'val2'
);

// then prepare query
$statement = $db->prepare("SELECT * FROM myTable ORDER BY $sort");
$statement->execute();

thank @Madara Uchiha and @Ray!

Upvotes: 2

Views: 540

Answers (2)

Ray
Ray

Reputation: 41428

See Madra's answer.

You can't bind a column. Use a white list array of valid columns to sort against (to compare the $_GET value to) and just substitute it into the query:

$valid_cols = array('name', 'age');
$sort = 'default_sort_field';
if(isset($_GET['sort']) && in_array($_GET['sort'], $valid_cols)){
    $sort = $_GET['sort'];
}

$statement = $db->prepare("SELECT * FROM myTable ORDER BY $sort");

Upvotes: 3

Madara's Ghost
Madara's Ghost

Reputation: 174977

Maybe because you named you variable $sort, but you're trying to bind $order?

That's because you can't bind column names with prepared statements. They're only meant to be used with values.

Instead, what you should do, is to have a set of predefined options, and sort by those. You shouldn't give the user a choice of directly ordering by a real column name.

Upvotes: 2

Related Questions