MySQL,PHP 将 ORDER BY 参数传递

标签: sql MySQL PHP
发布时间: 2017/4/9 23:21:11
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

我使用jTable ,并试图对记录进行排序。

目前 'jtSorting' jTable 变量是被正确传递给我的 PHP 脚本通过 GET 变量。 我打电话 MySQL 存储过程如下所示︰

$res = $mysqli->query("CALL getReadReportsPaging($startIndex, $pageSize, '$sorting');");

查询返回的记录和不给一个错误,但不是返回记录按指定字段排序。 由主 ID ASC 始终返回记录。

下面是存储过程︰

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `getReadReportsPaging`(startIndex INTEGER, pageSize INTEGER, sorting VARCHAR(255))
BEGIN
    SELECT * FROM reports 
    WHERE `new`=0
    ORDER BY sorting
    LIMIT startIndex, pageSize;
END

我试着调用该过程,并通过在列名称与 ASC 或 DESC 像这样︰

call getReadReportsPaging(
    0,
    10,
    'ReportingName DESC'
);

它还返回主 ID ASC 的记录。

任何援助表示赞赏。

解决方法 1:

我认为这︰

function buildQuery($startIndex, $pageSize, $sorting) {
    return "SELECT * FROM reports WHERE `new`=0 ORDER BY $sorting LIMIT $startIndex, $pageSize";
}

$res = $mysqli->query(buildQuery($startIndex, $pageSize, $sorting));

是很容易定义︰

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `getReadReportsPaging`(startIndex INTEGER, pageSize INTEGER, sorting VARCHAR(255))
BEGIN
    SET @Query = "SELECT * FROM reports WHERE `new`=0 ORDER BY ";
    @Query = CONCAT(@Query, sorting);
    @Query = CONCAT(@Query, " LIMIT ");
    @Query = CONCAT(@Query, startindex);
    @Query = CONCAT(@Query, ", ");
    @Query = CONCAT(@Query, pageSize);
    PREPARE stmt FROM @Query;
    EXECUTE stmt;
END

然后再调用︰

$mysqli->query("CALL getReadReportsPaging($startIndex, $pageSize, '$sorting');");

我会去与第一次。更短更具可读性。

编辑︰
如果用户可以设置 $pageSize、 $sorting 或 $startIndex,我概述的方法是非常不安全。我不知道如何对他们在 mySQL,但做到在 PHP 中。在 PHP 中,我会做这与预准备语句,但您想要使用一个变量进行排序。这是列名称和列名称不能使用预准备的语句参数。实际上,你只能使用它们的值。所以我建议你做获取要排序的列名称和 ASC|DESC 分别,(或只是拆分上的第一个空格),然后做这样的事情︰

$columns = array(/* the column names of the table `reports` as strings */);

if (!in_array($sortingColumn, $columns) {
    $sortingColumn = /* insert default sorting column name here */;
}

if ($sortingDirection != "ASC" && $sortingDirection != "DESC") { // I may be forgetting a sorting direction here.
    $sortingDirection = /* insert default sorting direction here */;
}

$stmt = $mysqli->prepare("SELECT * FROM reports WHERE `new`=0  ORDER BY $sortingColumn $sortingDirection LIMIT ?, ?");
$stmt->bindParam("ii", intval($startIndex), intval($pageSize)); // If the input to intval cannot be parsed as int it will return 0. This prevents injection in $startIndex and $pageSize.

$stmt-> execute ();

上面的 PHP 代码片段可以包装成一个函数以及。(这甚至可以返回 $stmt 没有电话 $stmt->execute() .)

为了保持 mySQL 方式安全,需要以某种方式在 mySQL (这我认为是有点不切实际) 中执行相同的检查。但在这里︰

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `getReadReportsPaging`(startIndex INTEGER, pageSize INTEGER, sortingDirection VARCHAR(255), sortingColumn VARCHAR(255))
    BEGIN
        SET @Query = "SELECT * FROM reports WHERE `new`=0 ORDER BY ";
        IF NOT sortingColumn IN (/* list of column names here */) THEN
            sortingColumn = /* default sorting column */;
        @Query = CONCAT(@Query, sortingColumn);
        @Query = CONCAT(@Query, " ");
        IF NOT sortingDirection IN ('ASC', 'DESC' /* I may be forgetting some directions) THEN
            sortingDirection = /* default sorting direction */;
        @Query = CONCAT(@Query, sortingDirection);
        @Query = CONCAT(@Query, " LIMIT ");
        @Query = CONCAT(@Query, CAST(startindex AS SIGNED)); // I assume this will return an int even if the input is not parseable as int.
        @Query = CONCAT(@Query, ", ");
        @Query = CONCAT(@Query, CAST(pageSize AS SIGNED));
        PREPARE stmt FROM @Query;
        EXECUTE stmt;
    END

免责声明︰ 1) 我不是一位安全专家,2) 我没有测试所有上面的代码。 我希望它将帮助你无论如何。

官方微信
官方QQ群
31647020