How to dynamically generate MYSQL UPDATE statement based on defined variables from HTML FORM

Question

I'm using a rather long HTML form to update lots of details relating to a product - for brevity I won't share the form in its entirety. However, for illustrative purposes here's a snippet :

HTML FORM

<form name="form1" method="post" action="update_ac.php">
    <table width="100%" cellpadding="0" cellspacing="0">
    <tr>
      <td>
        <input name="season" type="text"  class="button_select" id="season" value="<?=$rows['season']; ?>" size="10" />
        <input name="restock" type="checkbox" id="restock" value="on" <?php if($rows['restock']=='on') { echo 'checked="checked"'; } ?>/>

      // other fields

      </td>
    </tr>
  </table>
</form>

My question is when posting the form to update_ac.php - how can I dynamically generate a MYSQL update statement based on the fields that are completed?

Here's an example of my form action page:

PHP FORM Action

<?php

       foreach ($_POST as $key => $value) {
        $$key = $value;
      }

$sql= mysql_query ("
UPDATE product SET
title='".$title."',
rating='".$rating."',
season='".$season."', 
brand_id='".$brand_id."',
category='".$category."', 

... etc ");

?>

I don't want to have to declare every single field that could possibly need updating in the UPDATE statement. I would like the UPDATE statement to only address the fields concerned given the presence of defined PHP variables posted from the form.

At the moment, I'm getting lots of NOTICE: Undefined variable x where there have been empty fields when posting the form.

I hope this makes sense - little long winded.

Any advice? Thanks

UPDATE

Following on from @Styphon's answer - I amended it slightly to include the WHERE condition at the end of the query.

$query = "UPDATE product SET";
$comma = " ";
foreach($_POST as $key => $val) {
    if( ! empty($val)) {
        $query .= $comma . $key . " = '" . mysql_real_escape_string(trim($val)) . "'";
        $comma = ", ";
    }
}

$product_id = $_POST['product_id'];

$query = $query . "WHERE product_id = '".$product_id."' ";

@Fluffeh - that doesn't solve my problem as I would have to test every possible variable against ISSET. I'm trying to avoid having to declare over 50 variables. — aphextwix
– aphextwix, Commented Sep 5, 2014 at 10:55

Community · Accepted Answer · 2023-11-17 20:29:45Z

5

Assuming that all the field names in the table are the same as the names of your form inputs this is straight forward. You can use this:

$query = "UPDATE product SET";
$comma = " ";
foreach($_POST as $key => $val) {
    if( ! empty($val)) {
        $query .= $comma . $key . " = '" . mysql_real_escape_string(trim($val)) . "'";
        $comma = ", ";
    }
}
$sql = mysql_query($query);

To be more secure you should create a whitelist of accepted parameters, i.e. the columns in your table like this:

$query = "UPDATE product SET";
$comma = " ";
$whitelist = array(
    'title',
    'rating',
    'season',
    'brand_id',
    'cateogry',
    // ...etc
);
foreach($_POST as $key => $val) {
    if( ! empty($val) && in_array($key, $whitelist)) {
        $query .= $comma . $key . " = '" . mysql_real_escape_string(trim($val)) . "'";
        $comma = ", ";
    }
}
$sql = mysql_query($query);

That way your query can only contain parameters you set, and if anyone manages to inject extras (by changing the names of your form inputs for example) it wont be passed to your database.

I'd also recommend you stop using Mysql_*, it's deprecated. You should look at MySQLi or PDO as alternatives.

edited Nov 17, 2023 at 20:29

CommunityBot

11 silver badge

answered Sep 5, 2014 at 10:29

Styphon

10.5k9 gold badges54 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Styphon Over a year ago

@nalply I've added in real_escape_string, too used to using prepared statements now.

aphextwix Over a year ago

@Styphon - I've just realised that the WHERE product_id = $product_id line is missing from the end of the statement. I've updated the code above. Thanks

Styphon Over a year ago

@nalply I'm using single quotes around the query so that's irrelevant, that query is protected against that. Also did you see my updated answer with a white list before you commented?

arviman Over a year ago

isn't this vulnerable to SQL injection?

Styphon Over a year ago

@arviman It has mysql_real_escape_string which will protect against most injection attempts, but it's not as good as using prepared statements.

Collectives™ on Stack Overflow

How to dynamically generate MYSQL UPDATE statement based on defined variables from HTML FORM

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related