如何使用php中的准备语句来选择数据库的列(与SELECT)？

Question

我喜欢使用prepare()来防止SQL注入。但是当我使用下面的代码时，我只得到列的名称。

$sql = "SELECT DISTINCT ?
         FROM refPlant
         WHERE ? = ?;";

   $conn = openConnection();
   $stmt = $conn->prepare($sql);
   $stmt->bind_param('sss', $taxon_subtyp, $taxon_typ, $taxon_nam);
   $stmt->execute();

编辑

为了更清楚地说明，下面是我正在使用的表：

CREATE TABLE `refPlant` (
  `id` int(11) NOT NULL,
  `name` text,
  `genera` text,
  `family` text,
  `ord` text,
  `class` text
);

-- first 3 lines of the table
INSERT INTO `refPlant` (`id`, `name`, `genera`, `family`, `ord`, `class`) VALUES
(9, 'Aaronsohnia pubescens', 'Aaronsohnia', 'Asteraceae', 'Asterales', 'Asterids'),
(10, 'Abies alba', 'Abies', 'Pinaceae', 'Pinales', 'Pinopsida'),
(11, 'Abies amabilis', 'Abies', 'Pinaceae', 'Pinales', 'Pinopsida');

用户可以在genera、family、ord和class之间选择列。此外，他还可以自由选择WHERE条款。

Answer 1

来自mysqli：：准备

Note： 这些标记仅在SQL语句中的某些位置是合法的。例如，在INSERT语句的VALUES()列表中(为一行指定列值)或与WHERE子句中的列进行比较时允许使用它们来指定比较值。 但是，在select语句要返回的列的SELECT列表中，它们不允许用于标识符(例如表或列名)，或者指定二进制运算符的两个操作数，例如=等于符号.

另外，来自同一源，引用了sql语句：

不应在语句中添加终止分号或\g。

因此，如果要提供想要的列名，必须使用PHP变量。我编写了一个解决方案，涉及在运行db操作时应该使用的所有步骤。我知道，虽然很多，但很容易理解。扩展和文档版本在我之前的评论中提供给您的链接中。

祝好运。

<?php

/*
 * Enable internal report functions. This enables the exception handling, 
 * e.g. mysqli will not throw PHP warnings anymore, but mysqli exceptions 
 * (mysqli_sql_exception). They are catched in the try-catch block.
 * 
 * MYSQLI_REPORT_ERROR: Report errors from mysqli function calls.
 * MYSQLI_REPORT_STRICT: Throw a mysqli_sql_exception for errors instead of warnings.
 */
$mysqliDriver = new mysqli_driver();
$mysqliDriver->report_mode = (MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

try {
    // Just test values.
    $taxon_subtyp = 'abc';
    $taxon_typ = 'def';
    $taxon_nam = '123xyz';

    /*
     * Build the sql statement using the printf() function.
     * Familiarize yourself with it (it takes 15 minutes),
     * because it is a very powerfull function, to use especially
     * in constructing complex sql statements.
     * 
     * In principle, each "%s" represents a placeholder for each 
     * variable in the variable list, that follows after the sql statement string.
     */
    $sql = sprintf('SELECT DISTINCT %s FROM refPlant WHERE %s = ?', $taxon_subtyp, $taxon_typ);

    // Open connection.
    $conn = openConnection();

    // Prepare and validate statement.
    $stmt = $conn->prepare($sql);
    if (!$stmt) {
        throw new Exception('Prepare error: ' . $conn->errno . ' - ' . $conn->error);
    }

    // Bind variables for the parameter markers (?) in the SQL statement.
    $bound = $stmt->bind_param('s', $taxon_nam);
    if (!$bound) {
        throw new Exception('Bind error: A variable could not be bound to the prepared statement');
    }

    // Execute the prepared SQL statement.
    $executed = $stmt->execute();
    if (!$executed) {
        throw new Exception('Execute error: The prepared statement could not be executed!');
    }

    // Get the result set from the prepared statement.
    $result = $stmt->get_result();
    if (!$result) {
        throw new Exception('Get result error: ' . $conn->errno . ' - ' . $conn->error);
    }

    // Get the number of rows in the result.
    $numberOfRows = $result->num_rows;

    // Fetch data and save it into an array.
    $fetchedData = array();
    if ($numberOfRows > 0) {
        // Use mysqli_result::fetch_all to fetch all rows at once.
        $fetchedData = $result->fetch_all(MYSQLI_ASSOC);
    }

    // Print results (in a cool formatted manner), just for testing.
    echo '<pre>' . print_r($fetchedData, TRUE) . '<pre>';

    /*
     * Free the memory associated with the result. You should 
     * always free your result when it is not needed anymore.
     */
    $result->close();

    /*
     * Close the prepared statement. It also deallocates the statement handle.
     * If the statement has pending or unread results, it cancels them 
     * so that the next query can be executed.
     */
    $stmtClosed = $stmt->close();
    if (!$stmtClosed) {
        throw new Exception('The prepared statement could not be closed!');
    }

    // Close db connection.
    $connClosed = $conn->close();
    if (!$connClosed) {
        throw new Exception('The db connection could not be closed!');
    }
} catch (mysqli_sql_exception $e) {
    echo 'Error: ' . $e->getCode() . ' - ' . $e->getMessage();
    exit();
} catch (Exception $e) {
    echo $e->getMessage();
    exit();
}

/*
 * Disable internal report functions.
 * 
 * MYSQLI_REPORT_OFF: Turns reporting off.
 */
$mysqliDriver->report_mode = MYSQLI_REPORT_OFF;

编辑：

既然你发布了你的“白名单”，我想你可能也想在我的代码结构中看到它的作用。只是为了好玩:-)

<?php

/*
 * Enable internal report functions. This enables the exception handling, 
 * e.g. mysqli will not throw PHP warnings anymore, but mysqli exceptions 
 * (mysqli_sql_exception). They are catched in the try-catch block.
 * 
 * MYSQLI_REPORT_ERROR: Report errors from mysqli function calls.
 * MYSQLI_REPORT_STRICT: Throw a mysqli_sql_exception for errors instead of warnings.
 * 
 * Put this somewhere, so that it fits in your global code structure.
 */
$mysqliDriver = new mysqli_driver();
$mysqliDriver->report_mode = (MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

function get_following_plant_group($taxon_typ, $taxon_nam) {
    $taxon_order = ['class', 'ord', 'family', 'genera'];

    if (in_array($taxon_typ, $taxon_order)) {
        $taxon_subtyp = $taxon_order[array_search($taxon_typ, $taxon_order) + 1];

        try {

            /*
             * Build the sql statement using the printf() function.
             * Familiarize yourself with it (it takes 15 minutes),
             * because it is a very powerfull function, to use especially
             * in constructing complex sql statements.
             * 
             * In principle, each "%s" represents a placeholder for each 
             * variable in the variable list, that follows after the sql statement string.
             */
            $sql = sprintf('SELECT DISTINCT %s FROM refPlant WHERE %s = ? ORDER BY ?', $taxon_subtyp, $taxon_typ);

            // Open connection.
            $conn = getBdd();
            $conn->set_charset('utf8');

            // Prepare and validate statement.
            $stmt = $conn->prepare($sql);
            if (!$stmt) {
                throw new Exception('Prepare error: ' . $conn->errno . ' - ' . $conn->error);
            }

            // Bind variables for the parameter markers (?) in the SQL statement.
            $bound = $stmt->bind_param('ss', $taxon_nam, $taxon_subtyp);
            if (!$bound) {
                throw new Exception('Bind error: A variable could not be bound to the prepared statement');
            }

            // Execute the prepared SQL statement.
            $executed = $stmt->execute();
            if (!$executed) {
                throw new Exception('Execute error: The prepared statement could not be executed!');
            }

            // Get the result set from the prepared statement.
            $result = $stmt->get_result();
            if (!$result) {
                throw new Exception('Get result error: ' . $conn->errno . ' - ' . $conn->error);
            }

            // Get the number of rows in the result.
            $numberOfRows = $result->num_rows;

            /*
             * Fetch data and save it into an array.
             * Use mysqli_result::fetch_assoc to fetch a row at a time.
             */
            $arr = [];
            if ($numberOfRows > 0) {
                while ($row = $result->fetch_assoc()) {
                    $arr[] = $row[$taxon_subtyp];
                }
            }

            // Print results (in a cool formatted manner), just for testing.
            // echo '<pre>' . print_r($arr, TRUE) . '<pre>';

            /*
             * Free the memory associated with the result. You should 
             * always free your result when it is not needed anymore.
             */
            $result->close();

            /*
             * Close the prepared statement. It also deallocates the statement handle.
             * If the statement has pending or unread results, it cancels them 
             * so that the next query can be executed.
             */
            $stmtClosed = $stmt->close();
            if (!$stmtClosed) {
                throw new Exception('The prepared statement could not be closed!');
            }

            // Close db connection.
            $connClosed = $conn->close();
            if (!$connClosed) {
                throw new Exception('The db connection could not be closed!');
            }

            $arr = [$taxon_subtyp, $arr];

            return(json_encode($arr));
        } catch (mysqli_sql_exception $e) {
            echo 'Error: ' . $e->getCode() . ' - ' . $e->getMessage();
            exit();
        } catch (Exception $e) {
            echo $e->getMessage();
            exit();
        }
    }
}

/*
 * Disable internal report functions.
 * 
 * MYSQLI_REPORT_OFF: Turns reporting off.
 * 
 * Put this somewhere, so that it fits in your global code structure.
 */
$mysqliDriver->report_mode = MYSQLI_REPORT_OFF;

Answer 2

最后，我为各种可能性创建了一个白名单：

function get_following_plant_group($taxon_typ, $taxon_nam){

   $taxon_order = ['class', 'ord', 'family', 'genera'];
   if(in_array($taxon_typ, $taxon_order)){

      $taxon_subtyp = $taxon_order[array_search($taxon_typ, $taxon_order)+1];

      $sql = "SELECT DISTINCT ". $taxon_subtyp.
          " FROM refPlant
            WHERE ". $taxon_typ. " = ? ORDER BY ?;";

      $conn = getBdd( );
      $conn->set_charset("utf8");
      $stmt = $conn->prepare($sql);
      $stmt->bind_param('ss', $taxon_nam, $taxon_subtyp);
      $stmt->execute();
      $result = $stmt->get_result();

      $arr = [];
      if ($result->num_rows > 0) {
         while($row = $result->fetch_assoc()) {
            array_push($arr, $row[$taxon_subtyp]);
         }
      } 
      $conn->close();
      $arr = [$taxon_subtyp, $arr];

      return(json_encode($arr));
   }
}    

Answer 3

我已经写好了你的代码..。去看看那个。但总是净化用户输入..。

<?php
        $flexible = 'you';
        //$conn make sure you filled the connection
        //added placeholder pdo prepared statment re written by Ajmal PraveeN
        $stmt = $conn->prepare('SELECT DISTINCT `flexible` FROM `refPlant` WHERE `flexible` = :flexible');
        $stmt->execute(array(':flexible' => $flexible));
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
//ex output
echo $row['flexible'];
?>