how to update one or more fields ignoring the empty fields into mysql database?

Question

i am seeking help on ignoring null values for updating the mysql database:-

$cst = $_POST['custname'];
$a = $_POST['tel'];
$b = $_POST['fax'];
$c = $_POST['email'];
$sql = mysql_query("UPDATE contacts SET TEL = '$a', FAX = '$b', EMAIL = '$c'
                    WHERE Cust_Name = '$cst' ");

how do i incorporate an option where the user can only select one or all fields for updation.

i tried using the following code based on responses received but it does the same thing. overwrites the existing data with the blank ones.

$upd = mysql_query("UPDATE custcomm_T SET 
Telephone = ".(is_null($a)?'Telephone':"'$a'").",
Fax = ".(is_null($b)?'Fax':"'$b'").",
Mobile = ".(is_null($c)?'Mobile':"'$c'").",
EMail = ".(is_null($d)?'EMail':"'$d'").",
trlicense = ".(is_null($e)?'trlicense':"'$e'").",
trlicexp = ".(is_null($f)?'trlicexp':"'$f'")."
WHERE Cust_Name_VC = '$g' ") or die(mysql_error());

Holy [SQL injection](http://stackoverflow.com/questions/332365/xkcd-sql-injection-please-explain), Batman! — , Jun 29 '11 at 14:16
@tim i have simplified the code so it's easier to understand — mmdel, Jun 29 '11 at 14:18
@mmdel: then please specify so in your question, so new users coming here from google won't use your post as an example and expose themselves to SQL Injection. — Konerak, Jun 29 '11 at 14:44
I believe this might assist you in ignoring updates: http://dev.mysql.com/doc/refman/5.0/en/update.html Then all you have to do is not allow nulls in those fields and set your variables to null if empty. — Nightwolf, Jun 29 '11 at 14:50
@Konerak: this is a question/a problem and so i think it should not be used as an example until the time it is resolved. users should rather refer to relevant posts on the subject :-) — mmdel, Jun 29 '11 at 14:54
I understand what you mean, but let's agree to disagree. SQL Injection is one of the most frequent mistakes that get exploited on the web, imho we should not allow junior programmers to make the mistake. But I understand that if we each the full security check to each minor example, it might get tiring fast :) — Konerak, Jun 29 '11 at 15:02
@mmdel I think the problem is that variables ($a, $b, $c, etc) are not NULL, It seems that they are empty strings or something. Try to `var_dump($a, $b, $c, etc)`. — Karolis, Jun 29 '11 at 16:19

score 4 · Accepted Answer · edited Dec 26 '11 at 23:38

4

Firstly remember to escape any strings coming to you via POST, GET, or REQUEST (read up on SQL injection attacks if you're unsure why).

Something like this might work:

$semaphore = false;
$query = "UPDATE contacts SET ";
$fields = array('tel','fax','email');
foreach ($fields as $field) {
   if (isset($_POST[$field]) and !empty($_POST[$field]) {
     $var = mysql_real_escape_string($_POST[$field]);
     $query .= uppercase($field) . " = '$var'";
     $semaphore = true;
   }
}

if ($semaphore) {
   $query .= " WHERE Cust_Name = '$cst'";
   mysql_query($query);
}

NB: Do not ever simply loop through your $_POST array to create a SQL statement. An opponent can add extra POST fields and possibly cause mischief. Looping through a user input array can also lead to an injection vector: the field names need to be added to the statement, meaning they're a potential vector. Standard injection prevention techniques (prepared statement parameters, driver-provided quoting functions) won't work for identifiers. Instead, use a whitelist of fields to set, and loop over the whitelist or pass the input array through the whitelist.

edited Dec 26 '11 at 23:38

outis

75,655
22
151
221

answered Jun 29 '11 at 14:29

Andy

2,095
1
29
59

Are you putting comma's between your fields in the UPDATE section? – Konerak Jun 29 '11 at 19:09
Values shouldn't be interpolated directly into statements. Instead, build the statement with positional parameters and use a prepared statement – outis Dec 26 '11 at 23:05
@Andy , how would you insert a comma between each parameter but not have on for the last paramenter? – hellomello Dec 27 '11 at 00:24
@andrewliu The easiest way to do this is to add the strings to an array and then explode it when you need them separated with commas. – Andy Jan 14 '12 at 12:04
@Andy, I appreciate you responding me back after this old post. But do you think you can give me an example? It'll help me greatly! Thanks! – hellomello Jan 14 '12 at 20:38

score 1 · Answer 2 · answered Jun 29 '11 at 14:20

You need to build your query. Something like this:

$query = 'update contacts set ';
if ($_POST['tel'] != '') $query .= 'TEL="'.$_POST['tel'].'", ';
if ($_POST['fax'] != '') $query .= 'FAX="'.$_POST['fax'].'", ';
if ($_POST['email'] != '') $query .= 'EMAIL="'.$_POST['email'].'", ';
$query .= "Cust_Name = '$cst' where Cust_Name = '$cst'";

The last update field: Cust_Name = '$cst' basically is to 'remove' the last comma.

score 0 · Answer 3 · answered Jun 29 '11 at 14:31

Keeping in mind that $_POST values should be cleaned before use, and that all $_POST values are strings, so an empty field is '' and not null, something like this will work:

        foreach ($_POST as $var=>$value) {
            if(empty($value)) continue; //skip blank fields (may be problematic if you're trying to update a field to be empty)
            $sets[]="$var= '$value";

        }
        $set=implode(', ',$sets);
        $q_save="UPDATE mytable SET $set WHERE blah=$foo";

Abhay · Answer 4 · 2011-06-29T16:53:10.380

This should work (the MySQL way):

"UPDATE `custcomm_T`
SET `Telephone` = IF(TRIM('" . mysql_real_escape_string($a) . "') != '', '" . mysql_real_escape_string($a) . "', `Telephone`),
SET `Fax` = IF(TRIM('" . mysql_real_escape_string($b) . "') != '', '" . mysql_real_escape_string($b) . "', `Fax`),
SET `Mobile` = IF(TRIM('" . mysql_real_escape_string($c) . "') != '', '" . mysql_real_escape_string($c) . "', `Mobile`),
SET `EMail` = IF(TRIM('" . mysql_real_escape_string($d) . "') != '', '" . mysql_real_escape_string($d) . "', `EMail`),
SET `trlicense` = IF(TRIM('" . mysql_real_escape_string($e) . "') != '', '" . mysql_real_escape_string($e) . "', `trilicense`),
SET `trlicexp` = IF(TRIM('" . mysql_real_escape_string($f) . "') != '', '" . mysql_real_escape_string($f) . "', `trlicexp`)
WHERE Cust_Name_VC = '" . mysql_real_escape_string($g) . '";

I've tried to keep the columns and variables to what you have posted in your question, but feel free to correct as per your schema.

Hope it helps.

score 0 · Answer 5 · answered Dec 26 '11 at 23:32

Loop over the optional input fields, building up which fields to set. The field names and values should be kept separate so you can use a prepared statement. You can also loop over required fields as a basic validation step.

# arrays of input => db field names. If both are the same, no index is required.
$optional = array('tel' => 'telephone', 'fax', 'email');
$required = array('custname' => 'cust_name');

# $input is used rather than $_POST directly, so the code can easily be adapted to 
# work with any array.

$input =& $_POST;

/* Basic validation: check that required fields are non-empty. More than is 
 necessary for the example problem, but this will work more generally for an 
 arbitrary number of required fields. In production code, validation should be 
 handled by a separate method/class/module.
 */
foreach ($required as $key => $field) {
    # allows for input name to be different from column name, or not
    if (is_int($key)) {
        $key = $field;
    }
    if (empty($input[$key])) {
        # error: input field is required
        $errors[$key] = "empty";
    }
}
if ($errors) {
    # present errors to user.
    ...
} else {
    # Build the statement and argument array.
    $toSet = array();
    $args = array();
    foreach ($optional as $key => $field) {
        # allows for input name to be different from column name, or not
        if (is_int($key)) {
            $key = $field;
        }
        if (! empty($input[$key])) {
            $toSet[] = "$key = ?";
            $args[] = $input[$key];
        }
    }
    if ($toSet) {
        $updateContactsStmt = "UPDATE contacts SET " . join(', ', $toSet) . " WHERE cust_name = ?";
        $args[] = $input['custname'];
        try {
            $updateContacts = $db->prepare($updateContactsStmt);
            if (! $updateContacts->execute($args)) {
                # update failed
                ...
            }
        } catch (PDOException $exc) {
            # DB error. Don't reveal exact error message to non-admins.
            ...
        }
    } else {
        # error: no fields to update. Inform user.
        ...
    }
}

This should be handled in a data access layer designed to map between the database and program objects. If you're clever, you can write a single method that will work for arbitrary models (related forms, tables and classes).

Karolis · Answer 6 · 2011-06-29T14:32:19.183

-1

mysql_query("
    UPDATE contacts 
    SET 
        TEL = ".(is_null($a)?'TEL':"'$a'").", 
        FAX = ".(is_null($b)?'FAX':"'$b'").", 
        EMAIL = ".(is_null($c)?'EMAIL':"'$c'")."
    WHERE Cust_Name = '$cst'
");

edited Jun 29 '11 at 14:32

answered Jun 29 '11 at 14:19

Karolis

9,396
29
38

TEL = `1'; DROP TABLE contacts; --`. Please escape or sanitize POST data! – Konerak Jun 29 '11 at 14:41
@Konerak: This won't work in php, as you can only execute one sql query at a time, but I believe mmdel said he simplified the question and have sanitation in his program. – Nightwolf Jun 29 '11 at 14:47
Ok. TEL = `1', FAX = users.password FROM contacts,users where users.name='ADMIN'; --`, but you are right that the questioneer commented he was properly using escaping. Still, code examples without taking care of injection should be frowned upon - atleast add a comment. – Konerak Jun 29 '11 at 14:51
Calling people names is not the way to handle a downvote and discourages people from actually explaining why they downvote. I hope you handle constructive criticism better in your real life, even when you disagree with the person trying to help you. – Konerak Jun 29 '11 at 18:59
@Konerak you know, the internet is very funny thing. People can't see each other's emotions. So we are always at risk that somebody will take something too serious. Then one clever man invented emoticons. But this not always helps :) – Karolis Jun 29 '11 at 19:57

how to update one or more fields ignoring the empty fields into mysql database?

6 Answers6

Linked