How to use Prepared Statements in php to choose (with SELECT) a column of the database?

Question

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.

Possible duplicate of [Variable column names using prepared statements](https://stackoverflow.com/questions/3135973/variable-column-names-using-prepared-statements) — Dekel, Sep 16 '17 at 23:00
@Dekel I think its not the same because there its about Java my question is about php. anyway the answers there are already a good idea: to create a white list. — and-bri, Sep 16 '17 at 23:04
SQL is not related to the programming language you use, and you are asking about using a column name as a variable in a prepared statement (which is exactly the other question). Read it carefully. — Dekel, Sep 16 '17 at 23:06
@Dekel I thought the `prepare()` function is php and not SQL? — and-bri, Sep 16 '17 at 23:08
the `prepare()` function is php's implementation to sql-prepared statements. — Dekel, Sep 16 '17 at 23:09
An earlier answer of mine could give you the complete view about [How to use mysqli prepared statements and exception handling](https://stackoverflow.com/questions/45092344/how-do-i-echo-rows-that-have-a-specific-variable-in-it-from-database/45097253#45097253). Good luck! — , Sep 16 '17 at 23:11

score 3 · Accepted Answer · 2017-09-17T00:32:14.163

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;

score 0 · Answer 2 · answered Sep 17 '17 at 00:03

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

Ajmal PraveeN · Answer 3 · 2017-09-16T23:25:19.673

-1

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

edited Sep 16 '17 at 23:25

answered Sep 16 '17 at 23:04

Ajmal PraveeN

414
8
16

thanks for the effort but I ask for a SELECT a column by the user with `Where user = ...` you choose the row. not what I want. – and-bri Sep 16 '17 at 23:10
Okay can you give the right query field? i can modify as you want.. i have added placeholder thats way that ? are replaced by :user or etc.. got it? – Ajmal PraveeN Sep 16 '17 at 23:11
the field is flexible...it have to be chosen by the user. – and-bri Sep 16 '17 at 23:19
So you mean the sql query **SELECT `DISTINCT` FROM `refPlant` WHERE `flexible` = :flexible** right? – Ajmal PraveeN Sep 16 '17 at 23:20
Re written, if you want to fetch one row? and 1 column? if yes i can give some more optimal way. reply me asap. and Dont forgot to VOTE ME UP! – Ajmal PraveeN Sep 16 '17 at 23:26
SELECT DISTINCT **flexible1** FROM refPlant WHERE **flexible2** = **flexible3** – and-bri Sep 16 '17 at 23:29
flexible1, flexible2 and flexible3 are three different user defined variables...you only define one variable. – and-bri Sep 16 '17 at 23:30
@and-bri You are making me confuse. what are the columns you want to fetch and for which columns should match? can you make screenshot of your db structure and mark in the image.] – Ajmal PraveeN Sep 16 '17 at 23:35

How to use Prepared Statements in php to choose (with SELECT) a column of the database?

3 Answers3

EDIT:

Linked

Related