PHP PDO prepare statement, using variable for column SET

Question

how can insert variable to a prepared PDO statement? I don't know which column will be modified.

the code is (throws error):

  private function deleteData($column, $id)
  {
    $stmt = $this->db->prepare('UPDATE table SET $column = "" WHERE id = ?'); // $column variable has 1 out of 4 values, and it changes dynamically 
    $stmt->bind_param('i', $id);
    $stmt->execute();
  }

Error: Fatal error: Uncaught mysqli_sql_exception: Unknown column '$column' in 'field list' in

as I understood from the topic linked the only option is to make a switch?

switch ($column)
{
    case 'column1':
       $stmt = $this->db->prepare('UPDATE table SET column1 = "" WHERE id = ?');
    break;
    case 'column2':
       $stmt = $this->db->prepare('UPDATE table SET column2 = "" WHERE id = ?');
    break;
    and so on...

"Throws error" is very broad - can you share the exact error message? — Nico Haase, Oct 29 '20 at 08:41
PHP _basics_ … variables only get substituted for their values in double-quoted strings, not in single-quoted ones. — CBroe, Oct 29 '20 at 08:45
Do you understand that your code is vulnerable to SQL injection despite using a prepared statement? — Your Common Sense, Oct 29 '20 at 08:46
@YourCommonSense I don't understand that my code is vulnerable to SQL injection. Where is vulnerability exactly? — A. Volg, Oct 29 '20 at 08:54
You should go read https://www.php.net/manual/en/language.types.string.php#language.types.string.parsing — CBroe, Oct 29 '20 at 08:55
Well it's rather obvious. You are adding $column directly to to SQL which is the very definition of SQL injection. It's when your SQL consists of **constant** parts only, it is safe from injections — Your Common Sense, Oct 29 '20 at 09:03
You don't need that kind of `switch`, but you need to ensure `$column` is a validated value. E.g. `in_array($column, ['column1', 'column2', ...], true)` would do too as a check. — deceze, Oct 29 '20 at 09:05
Regarding the case statement, it is not necessary to duplicate the whole query. You can still add a variable in a string in PHP, you just have to learn [PHP strings](https://stackoverflow.com/q/3446216/285587). But it should contain a static constant value nevertheless — Your Common Sense, Oct 29 '20 at 09:06
@deceze well thank you for a check example, will use it. But that doesn't answer my question: How can I use variable for column name ? — A. Volg, Oct 29 '20 at 09:25
@deceze i moved it into the comment, because double duplicates make rather a negative sense, answering a completely different questions and spoiling the SEO, preventing the automatic redirect which takes place when there is only a single dupe target and no answers — Your Common Sense, Oct 29 '20 at 09:28
@deceze https://stackoverflow.com/a/182353/7463900 this should be the answer ? — A. Volg, Oct 29 '20 at 09:37
@A.Volg there are **two** questions you asked. One is how to use a variable as a column name. And for this question this should be answer. But in reality you meant a completely different question "why do I get literal `$column` instead of the value of $column variable?" and for this question the answer is https://stackoverflow.com/q/3446216/285587 — Your Common Sense, Oct 29 '20 at 10:01
I think the problem is that you are using single quote for string literal. You have to use double quote if you want `$column` be evaluated and replace with its value in the string (CAUTION: Beware of possibility of vulnerability of SQL Injection attacks when using variables in query) — Ahmad, Oct 29 '20 at 16:28

PHP PDO prepare statement, using variable for column SET

0 Answers0