如何更新一个或多个字段，而忽略mysql数据库中的空字段? [英] how to update one or more fields ignoring the empty fields into mysql database?

问题描述

我正在寻求有关忽略用于更新mysql数据库的空值的帮助:-

$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' ");

我如何合并一个选项，使用户只能选择一个或所有字段进行更新.

我尝试根据收到的回复使用以下代码，但它具有相同的作用.用空白数据覆盖现有数据.

$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());

解决方案

首先，请记住不要通过POST，GET或REQUEST来转义出现的所有字符串(如果不确定原因，请阅读SQL注入攻击).

类似的事情可能会起作用:

$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 :永远不要简单地遍历$ _POST数组来创建SQL语句.对手可以添加额外的POST字段，并可能导致恶作剧.遍历用户输入数组还可能导致注入向量:字段名称需要添加到语句中，这意味着它们是潜在的向量.标准的注入预防技术(准备的语句参数，驱动程序提供的引用功能)不适用于标识符.而是使用字段白名单进行设置，并在白名单上循环或将输入数组传递通过白名单.

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());

解决方案

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.

这篇关于如何更新一个或多个字段，而忽略mysql数据库中的空字段?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！