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