php - 从 $_get 绑定(bind) PDO mysql 参数不起作用

Question

// 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();

我正在尝试从获取参数中获取排序选项，有人可以帮我吗？

更新正如@Uchiha Madara 所建议的，我创建了一些预定义的排序字符串

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));
}

但仍然是相同的结果，排序选择被忽略

最后更新!

$sort = 'val1';

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

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

感谢@Madara Uchiha 和@Ray!

Answer 1

参见马德拉的 answer .

您不能绑定(bind)列。使用有效列的白名单数组进行排序(比较 $_GET 值)并将其代入查询:

$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");