Is there a way to use a variable in a MySQL update query

Question

Let's say I have a form like this

<?php include 'file2.php' ; ?>
<form action="file1.php" method="POST">
   <input type="hidden" name="LHS" value="column1">
   <input type="hidden" name="RHS" value="row">
   <button type="submit">Submit</button>
</form>

and I have an update query in file2.php like this

$lhs = $_POST['LHS']
$rhs = $_POST ['RHS']

$update = "UPDATE table1 SET
           column1 = '".$rhs."'
           WHERE id = '".$_SESSION['id']."'";

mysqli_query($conn, $update);

So my questions is how can I use a variable($lhs) in place of "column1" to update mysql data

I have tried $table1['column1'] method too, but it only seems to work on Rhs side but not the LHS

Even hidden data on forms can be changed if the user wants, this will leave your code open to users being able to hack your web site. — Nigel Ren, Oct 03 '18 at 06:41
Then what's a better way to do it @NigelRen ? I have only asked about it because I didn't want to have to create same file2.php over and over for different inputs with the same submit form pattern.. — i0N77, Oct 03 '18 at 06:44
Adding dynamic column name from a form is not safe. One can easily edit that hidden name to say `id` and set whatever value they type. Try to use `PDO` for database. For whatever reason this is not safe. — Krishnadas PC, Oct 03 '18 at 06:46
Without knowing what you are trying to achieve it's impossible to tell. Do you really need to have dynamic statements like this? — Nigel Ren, Oct 03 '18 at 06:46

Madhur Bhaiya · Accepted Answer · 2018-10-03T07:13:26.673

1

Most Importantly: Your query is open to SQL Injection related attacks. Please learn how to use Prepared Statements

Also, it seems that the value of your $lhs variable is a Reserved keyword in MySQL. It is incidentally your column name as well. So, you need to use backticks (`), asking MySQL to consider it as a column/table/database name or some aliased expression.

Now, just do the following, to use $lhs as a column in the Update query:

$update = "UPDATE table1 `" .
          $lhs . "` = '" . $rhs . "' 
          WHERE id = '".$_SESSION['id']."' ";

mysqli_query($conn, $update);

edited Oct 03 '18 at 07:13

answered Oct 03 '18 at 06:39

Madhur Bhaiya

28,155
10
49
57

to add to this, PREPARE YOUR STATEMENTS. – Joshua Oct 03 '18 at 06:40
That doesn't work either, I've already tried it. It doesn't work when I use the $lhs variable, but $rhs works fine. – i0N77 Oct 03 '18 at 06:45
1

@i0N77 what is the value of `$lhs` variable. I have just added backticks in the answer. Use them. Also ensure that a column name by value in the `$lhs` actually exists in your database. – Madhur Bhaiya Oct 03 '18 at 06:46
1

Ah, it's working now. Just didn't know that we would have to use those backticks and all, will learn how to use prepared statements first and then include this in it. Upvoted. – i0N77 Oct 03 '18 at 07:02
1

@i0N77 Most likely in `$lhs`, there must be a reserved keyword (in MySQL), which is incidentally your column name as well. So MySQL throws errors, if you dont use backticks, asking MySQL to consider it as a column/table/database name or some aliased name. – Madhur Bhaiya Oct 03 '18 at 07:08

Is there a way to use a variable in a MySQL update query

1 Answers1