How to use prepare() with dynamic column names?
You could use a list of "approved" values instead, that way you're not really using user data inside a query. Something like this:
$Approved = array ('firstname', 'lastname', 'birthdate') ;
$Location = array_search($ColumnName, $Approved) // Returns approved column location as int
if($Location !== FALSE) {
// Use the value from Approved using $Location as a key
$Query = $wpdb->Prepare('SELECT ' . $Approved[$Location] . ' FROM myTable WHERE user_id=:userid');
$Query->Execute(array(
:userid => $current_user->user_login
));
return $Query;
} else {
return false;
}
Maybe it might be easier to just get all (SELECT * or SELECT a,b,c,d) of the user data and save it to session to use later?
How to prepare SQL query dynamically (column names too) avoiding SQL injection
You can use bound parameters only for an element that would be a constant value — a quoted string, a quoted datetime, or a numeric literal.
You can't use a parameter placeholder for anything else in SQL, like column names, table names, lists of values, SQL keywords or expressions, or other syntax.
If you need to make column names dynamic, the only option is to validate them against a list of known columns.
$columns_in_user_table = [
'userid'=>null,
'username'=>'',
'firstname'=>'',
'lastname'=>''
];
// Extract values from POST, but only those that match known columns
$parameters = array_intersect_key($_POST, $columns_in_user_table);
// Make sure no columns are missing; assign default values as needed
$parameters = array_merge($columns_in_user_table, $parameters);
If you use PDO instead of mysqli, you can skip the binding. Just use named parameters, and pass your associative array of column-value pairs directly to execute()
:
$columns = [];
$placeholders = [];
foreach ($parameters as $col => $value) {
$columns[] = "`$col`";
$placeholders[] = ":$col";
}
$column_list = implode($columns, ',');
$placeholder_list = implode($placeholders, ',');
// Write into the database
$sql = "INSERT INTO `user` ($column_list) VALUES ($placeholder_list)";
$stmt = $pdo->prepare($sql);
$stmt->execute($parameters);
Variable column names using prepared statements
This indicates a bad DB design. The user shouldn't need to know about the column names. Create a real DB column which holds those "column names" and store the data along it instead.
And any way, no, you cannot set column names as PreparedStatement
values. You can only set column values as PreparedStatement
values
If you'd like to continue in this direction, you need to sanitize the column names (to avoid SQL Injection) and concatenate/build the SQL string yourself. Quote the separate column names and use String#replace()
to escape the same quote inside the column name.
Dynamic column name using prepared statement + sql query with variable containing 's
Right. We can't supply identifiers as bind parameters. The name of the column has to be part of the SQL text.
We can dynamically incorporate the name of the column into the SQL text with something like this:
sql = "UPDATE diseaseinfo"
+ " SET `" + colname + "` = ?"
+ " WHERE companyname = 'mycom' AND diseaseName = ?";
And supply values for the two remaining bind parameters
preparedStmt.setString(1, attrData);
preparedStmt.setString(2, medname);
And you are absolutely correct about being concerned about SQL Injection.
Supplied as bind values, single quotes in the values of attrData
and medname
won't be an issue, in terms of SQL Injection.
But the example I've provided is vulnerable through incorporating the colname
variable into the SQL text, if we don't have some guaranteed that colname
is "safe" to include in the statement.
So we need to make the assignment of a value to colname
"safe".
Several approaches we can use do that. The most secure would be a "whitelist" approach. The code can ensure that only specific allowed "safe" values get assigned to colname
, before colname
gets included into the SQL text.
As a simple example:
String colname;
if (attributes.equals("someexpectedvalue") {
colname = "columnname_to_be_used";
} else if (attributes.equals("someothervalid") {
colname = "valid_columname";
} else {
// unexpected/unsupported attributes value so
// handle condition or throw an exception
}
A more flexible approach is to ensure that a backtick character doesn't appear in colname
. In the example, the value of colname
is being escaped by enclosing it in backticks. So, as long as a backtick character doesn't appear in colname
, we will prevent a supplied value from being interpreted as anything other than as an identifier.
For a more generic (and complicated) approach to using hardcoded backtick characters, we could consider making use the supportsQuotedIdentifiers
and getIdentifierQuoteString
methods of java.sql.DatabaseMetaData
class.
(In the OP code, we don't see the datatype of contents of attributes
. We see a call to a method named replace
, and the arguments that are supplied to that. Assuming that attributes
is a String, and that's supposed to be a column name, it's not at all clear why we would have "space single quote space" in the string, or why we need to remove that. Other than this mention, this answer doesn't address that.)
Dynamically change column name in PDO statement
You would need to do something like this:
$column = 'someColumn';
$stmt = $db->prepare("UPDATE tableName SET {$column} = :columnValue WHERE ID = :recordId");
Parameterized placeholders are only for values.
I would suggest you read the comment @YourCommonSense posted on your question.
MySQL Query with dynamic column name
Pseudo-code:
SELECT DISTINCT ChannelNumber
FROM source
while ($row = fetch_assoc()) {
$channel_col = 'ch'.$row['ChannelNumber']
$sql = "
UPDATE destination
JOIN source ON StartTime = destination.minutes
AND source.ChannelNumber = $row[ChannelNumber]
SET destination.$channel_col = 1
";
query($sql);
}
Normally you have to be concerned about dynamically creating a string which becomes an SQL query to avoid SQL injection, but since all the data is coming from within your database it shouldn't be a problem.
Mysqli prepared statement column with variable
It's impossible to use a parameter for a column or a table name. Instead, they must be explicitly filtered out before use, using a white list approach.
// define a "white list"
$allowed = ['Node5', 'Node4'];
// Check the input variable against it
if (!in_array($server, $allowed)) {
throw new Exception("Invalid column name");
}
// now $server could be used in the SQL string
$sqlString = "UPDATE staff_members SET $server=? WHERE Username=?";
$stmt = $connection->prepare($sqlString);
$stmt->bind_param("ss", $rank, $username);
$stmt->execute();
Related Topics
PHP Function That Creates a Nested Ul Li
PHP Array Find Duplicates, Sum Them Up & Delete Duplicates
How to Create Codeigniter Language Files from Database
How to Bind SQL Variables in PHP
Variable Type Hinting in Netbeans (Php)
Mysqli_Real_Connect(): (Hy000/2002): No Such File or Directory
Issue in Installing PHP7.2-Mcrypt
Search and Replace Multiple Values with Multiple/Different Values in PHP5
How to Use Composer Packages in Codeigniter
Foursquare API for Venue User Image Error
Detect If Android App Has Been Installed on the Device Using a Mobile Web Page - PHP and Js
Converting an Array from One to Multi-Dimensional Based on Parent Id Values
How to Avoid Code Repetition with PHP SQL Prepared Statements
Check to See If a String Is Serialized
Composer Update Failed -- Out of Memory
S3 Direct Upload Restricting File Size and Type
In MVC, Where Do You Draw the Line Between a Controller and Model