PHP 表单无法插入 MySQL

Question

这个问题已经有答案了:

When to use single quotes, double quotes, and backticks in MySQL (13 个回答)

Reference - What does this error mean in PHP? (38 个回答)

已关闭 3 年前。

我正在尝试制作一个表单并将其上传到我的数据库中，但它不起作用。
这是我的 HTML 代码:

<form name="Form-Request" method="post" action ="icn/form.php">
    <div>
        <p><input type = "text" placeholder="Name" name = "name"></p>
        <p><input type = "email" placeholder="Email-address" name = "email"></p>
        <p><input type = "text" placeholder="Virtual Airline" name = "va"></p>
        <p><input type = "text" placeholder="Virtual Airline (IATA Code)" name = "va-iata"></p>
        <p><select name="pricing">
            <option>Free</option>
            <option>Business</option>
            <option>Pro</option>
        </select></p>
        <p><select name="vam-vms">
            <option>PHPVMS</option>
            <option>VAM</option>
        </select></p>
        <p><input type = "text" placeholder="Additional Info" name = "add-info"></p>

        <input class="button-primary" type="submit" value="Submit">

这是我的 PHP post 文件，我已经输入了数据库详细信息，但我不想共享它:):

<?php
$name = $_POST['name'];
$email = $_POST['email'];
$va = $_POST['va'];
$vaiata = $_POST['va-iata'];
$pricing = $_POST['pricing'];
$vamvms = $_POST['vam-vms'];
$additionalinfo = $_POST['add-info'];

<?php
$servername = "host";
$username = "username";
$password = "password";
$dbname = "dbname";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "INSERT INTO request_site (Name, Email Address, Virtual Airline, Virtual Airline IATA, Pricing, VAM/PHPVMS, Additional info)
VALUES ('$name', '$email', '$va', '$vaiata', '$pricing', '$vamvms', '$additionalinfo' )";

if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?>

谢谢。

Answer 1

您应该使用此代码执行一些操作。

首先，由于您尝试使用表单中的每个字段，因此您应该将 required 属性添加到每个输入字段。

其次，由于您要提交 POST 请求，我建议您检查 PHP 脚本(如果该请求是 POST)，并检查您尝试从 POST 获取的值是否存在。这将防止有人简单地导航到您的脚本并尝试不正确地执行它:

<?php
if (
    $_SERVER['REQUEST_METHOD'] === 'POST' && 
    array_key_exists('name', $_POST) &&
    array_key_exists('email', $_POST) &&
    array_key_exists('va', $_POST) &&
    array_key_exists('va-iata', $_POST) &&
    array_key_exists('pricing', $_POST) &&
    array_key_exists('vam-vms', $_POST) &&
    array_key_exists('add-info', $_POST)
  ) {
  /* ... */  
} else {
  http_response_code(401);
}

?>

第三，不要使用mysqli。 PDO class自 PHP 5.1.0 起就已推出，是连接数据库的首选方法。在这种情况下，使用 Try/Case 语句尝试建立 PDO 连接:

try {
  /* database configuration */
  $servername = "host";
  $username = "username";
  $password = "password";
  $dbname = "dbname";

  /* establish a PDO connection */
  $dsn =  "mysql:dbname=$dbname;host=$servername;charset=utf8mb4";
  $db  =  new PDO($dsn, $username, $password);
  $db  -> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $db  -> setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

  /* run SQL query */
} catch(PDOException $ex) {
  /* handle database error */
}

第四，您需要稍微调整一下 SQL 语句，我认为这就是它导致一些错误的地方。通常，最佳实践是使用反引号转义列名称，但在这种情况下，由于列名称中似乎有空格，因此实际上是必要的(加上 name 也可能是关键字)。使用参数来防止 SQL 注入(inject)也是最佳实践。这个想法是，您在 SQL 查询中传递一个带有 : 前缀的用户定义名称，并且该名称对应于传递的数组中定义的值:

/* Insert all the values in the request_site */
$stmt = $db->prepare('
INSERT INTO `request_site` (
  `Name`,
  `Email Address`,
  `Virtual Airline`,
  `Virtual Airline IATA`,
  `Pricing`,
  `VAM/PHPVMS`,
  `Additional info`
) VALUES (
  :name,
  :email,
  :va,
  :vaiata,
  :pricing,
  :vamvms,
  :additionalinfo
);
');

$stmt->execute(array(
  ':name'           => $name,
  ':email'          => $email,
  ':va'             => $va,
  ':vaiata'         => $vaiata,
  ':pricing'        => $pricing,
  ':vamvms'         => $vamvms,
  ':additionalinfo' => $additionalinfo
));

把它们放在一起，你会得到:

<?php
if (
    $_SERVER['REQUEST_METHOD'] === 'POST' && 
    array_key_exists('name', $_POST) &&
    array_key_exists('email', $_POST) &&
    array_key_exists('va', $_POST) &&
    array_key_exists('va-iata', $_POST) &&
    array_key_exists('pricing', $_POST) &&
    array_key_exists('vam-vms', $_POST) &&
    array_key_exists('add-info', $_POST)
  ) {
    /* put the $_POST values in variables */
    $name = $_POST['name'];
    $email = $_POST['email'];
    $va = $_POST['va'];
    $vaiata = $_POST['va-iata'];
    $pricing = $_POST['pricing'];
    $vamvms = $_POST['vam-vms'];
    $additionalinfo = $_POST['add-info'];

  try {
    /* database configuration */
    $servername = "host";
    $username = "username";
    $password = "password";
    $dbname = "dbname";

    /* establish a PDO connection */
    $dsn =  "mysql:dbname=$dbname;host=$servername;charset=utf8mb4";
    $db  =  new PDO($dsn, $username, $password);
    $db  -> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $db  -> setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

    /* Insert all the values in the request_site */
    $stmt = $db->prepare('
    INSERT INTO `request_site` (
      `Name`,
      `Email Address`,
      `Virtual Airline`,
      `Virtual Airline IATA`,
      `Pricing`,
      `VAM/PHPVMS`,
      `Additional info`
    ) VALUES (
      :name,
      :email,
      :va,
      :vaiata,
      :pricing,
      :vamvms,
      :additionalinfo
    );
    ');

    $stmt->execute(array(
      ':name'           => $name,
      ':email'          => $email,
      ':va'             => $va,
      ':vaiata'         => $vaiata,
      ':pricing'        => $pricing,
      ':vamvms'         => $vamvms,
      ':additionalinfo' => $additionalinfo
    ));

    echo 'New record created successfully';
  } catch(PDOException $ex) {
    /* handle database error */
    echo 'Connection failed: ', $ex->getMessage();
  }
} else {
  http_response_code(401);
}

?>