如果没有$_POST值，则无法将SQLI设置为INSERT null - Cannot get SQLI to INSERT null if no $_POST value

Cannot get SQLI to INSERT null if no $_POST value

我已经尝试了多种方法来尝试并使其发挥作用。已经尝试了所有关于这个主题的问题的答案，但无法获得。

如果$_POST变量没有值，我将尝试在数据库中插入NULL，而不是字符串NULL。它只是不断插入字符串"NULL"或只是一个空白列。以下是我尝试查询的所有方法。

我的数据库类有一个方法sql_prep:

public function sql_prep($postVariable){
  $output;
  if(trim($postVariable) == ''){
    $output = 'NULL';
  }else{
  $output = strval(mysqli_real_escape_string($this->connection, $postVariable));
  };
  return $output;
}

以下是查询：

 if(isset($_POST["createUserSubmit"])) : 
  $temp_connection = mysqli_connect(DB_SERVER, DB_USER, DB_PASS, DB_NAME);
  $firstName = $db->sql_prep($_POST["firstName"]);
  $lastName = $db->sql_prep($_POST["lastName"]);
  $companyName = $db->sql_prep($_POST["companyName"]);
  $streetAddress = $db->sql_prep($_POST["streetAddress"]);
  $streetAddress2 = $db->sql_prep($_POST["streetAddress2"]);
  $streetAddress3 = $db->sql_prep($_POST["streetAddress3"]);
  $city = $db->sql_prep($_POST["city"]);
  $state = $db->sql_prep($_POST["state"]);
  $zip = $db->sql_prep($_POST["zipCode"]);
  $country = $db->sql_prep($_POST["country"]);
  $phone = $db->sql_prep($_POST["phone"]);
  $fax = $db->sql_prep($_POST["fax"]);
  $email = $db->sql_prep($_POST["email"]);
  mysqli_query($temp_connection, "INSERT INTO Address(firstName, lastName, companyName, address1, address2, address3, city, state, zip, country, phone, fax, email, dateCreated, dateModified) VALUES (" . $firstName . ", " . $lastName . ", " . $companyName . ", " . $streetAddress . ", " . $streetAddress2 . ", " . $streetAddress3 . ", " . $city . ", " . $state . ", " . $zip . ", " . $country . ", " . $phone . ", " . $fax . ", " . $email . ", NOW(), NOW())");
  mysqli_close($temp_connection);
  redirect_to('./create-user.php');
endif;

即使填写了字段，该查询也不会将ANY数据推送到数据库。我尝试查询的另一种方式：

mysqli_query($temp_connection, "INSERT INTO Address(firstName, lastName, companyName, address1, address2, address3, city, state, zip, country, phone, fax, email, dateCreated, dateModified) VALUES ('{$firstName}', '{$lastName}', '{$companyName}', '{$streetAddress}', '{$streetAddress2}', '{$streetAddress3}', '{$city}', '{$state}', '{$zip}', '{$country}', '{$phone}', '{$fax}', '{$email}', NOW(), NOW())");

如果$_POST变量为空，则会将字符串"NULL"返回到数据库中。我还尝试将我的sql_prep函数更改为：

public function sql_prep($postVariable){
  $output;
  if(trim($postVariable) == ''){
    $output = NULL; //returned PhP Null instead of string 'NULL'
  }else{
  $output = strval(mysqli_real_escape_string($this->connection, $postVariable));
  };
  return $output;
}

将其更改为返回PhP NULL而不是"NULL"会导致查询将一个空白列推入数据库。

搞不清楚这个，真想推SQL NULL，如果没有值的话。

尽量使用准备好的语句，这也有助于保持安全。

$var = NULL;
$stmt = $mysqli->prepare("INSERT INTO test(id) VALUES (?)");
$stmt->bind_param("s", $var);
$stmt->execute();

应该为您插入一个NULL值。