如何在php中使用Prepared Statements选择(使用SELECT)数据库的一列?

I like to use prepare() for protection against SQL-injection. But when I use the code below, i only get the name of the column.

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

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

EDIT

To make it more clear, here is the table I'm working with:

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

The user have the choice of the column between, genera, family, ord and class. Further more he can also have free choice for the WHERE clause.

From the 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...

Also, from the same source, referring to the sql statement:

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


So, if you want to provide the wanted column name, you must do it using PHP variables. I wrote a solution, involving all the steps you should use when running db operations. I know, it's a lot but it's easy to follow. The extended and documented version is in the link I provided you in my comment, earlier.

Good luck.

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

EDIT:

Since you posted your "white list", I thought you might want to see it in action in my code structure as well. Just for fun :-)

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

I have re written your code.. have a look into that. but always sanitize the user input...

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

I finally create a white list for the possibilities:

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