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

Question

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

$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::prepare :

Note:

The markers are legal only in certain places in SQL statements. For example, they are allowed in the VALUES() list of an INSERT statement (to specify column values for a row), or in a comparison with a column in a WHERE clause to specify a comparison value.

However, they are not allowed for identifiers (such as table or column names), in the select list that names the columns to be returned by a SELECT statement, or to specify both operands of a binary operator such as the = equal sign...

同样，来自同一来源，引用sql语句:

You should not add a terminating semicolon or \g to the statement.

<小时/>

因此，如果您想提供所需的列名称，则必须使用 PHP 变量来完成。我编写了一个解决方案，涉及运行数据库操作时应使用的所有步骤。我知道，虽然很多，但很容易理解。扩展和记录的版本位于我之前在评论中提供给您的链接中。

祝你好运。

<?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;