SQLSTATE [42000]：语法错误或访问冲突：1064登​​录表单

I am getting into PHP/MySQL code and I've searched all over for a solution to this problem but no answers match my issue.

我正在进入PHP / MySQL代码,我已经搜索了所有问题的解决方案,但没有答案符合我的问题。

My code is very simple but I can't find whats causing this error

我的代码很简单,但我找不到导致此错误的内容

  <?php

  $servername = "localhost";
  $username = "langalungalangalunga";
  $password = "langalungalangalunga";
  $dbname = "user_main";

  $client_username = $client_password = $client_email = "";
  $usernameErr = $passwordErr = $emailErr = "";

  if ($_SERVER['REQUEST_METHOD'] == 'POST') {

    if (!empty($_POST['username'])) {
      $client_username = test_input($_POST['username']);
    } else {
      $usernameErr = "No input on UserName";
    }

    if (!empty($_POST['password'])) {
      $client_password = test_input($_POST['password']);
    } else {
      $passwordErr = "No input on Password";
    }

    if (!empty($_POST['email'])) {
      $client_email = test_input($_POST['email']);
    } else {
      $emailErr = "No input on Email";
    }
  }

    function test_input($data) {
     $data = trim($data);
     $data = stripslashes($data);
     $data = htmlspecialchars($data);
     return $data;
   }

  if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    try {
      $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
      // set the PDO error mode to exception
      $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
      $sql = "INSERT INTO user_main (UserName, Password, Email)
      VALUES ($client_username, $client_password, $client_email)";
      // use exec() because no results are returned
      $conn->exec($sql);
      echo "<script> alert('Success!');</script>";
      }
        catch(PDOException $e)
      {
        echo $sql . "<br>" . $e->getMessage();
      }
        $conn = null;
     }
   ?>

The error points at line 2 of the email part

错误指向电子邮件部分的第2行

check the manual that corresponds to your MySQL server version for the right syntax to use near ' , [e-mail])' at line 2

查看与MySQL服务器版本对应的手册,以便在第2行的',email @email.com)附近使用正确的语法

<form class="" action='<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>' method="post">
  <label for="username">Username</label>
  <input type="text" name="username" value="<?php echo htmlspecialchars($_SERVER['username']) ?>">
  <label for="password">Password</label>
  <input type="password" name="password" value="">
  <label for="email">Email</label>
  <input type="email" name="email" value="">
  <button type="submit" name="button">Register</button>
</form>

I am sorry if it's my mistake somewhere but I am new to PHP all together so I dont really have a feel for the syntax

我很抱歉,如果这是我的错误,但我是PHP新手,所以我真的不觉得语法

2 个解决方案

#1

$sql = "INSERT INTO user_main (`UserName`, `Password`, `Email`)
      VALUES ('$client_username', '$client_password', '$client_email')";

      $conn->query($sql);

The above code is vulnerable to SQL injection. Use prepared statements as

上面的代码容易受到SQL注入的攻击。使用预备语句作为

$stmt = $conn->prepare("INSERT INTO user_main (`UserName`, `Password`, `Email`)
VALUES (:UserName, :Password, :Email)");//placeholders
$stmt->bindParam(':UserName', $client_username);//you do not need to escape inputs
$stmt->bindParam(':Password', $client_password);
$stmt->bindParam(':Email', $client_email);

if($stmt->execute() == true){
//all good
echo'Data successfully saved Securely!';
} else {
 print_r($stmt->errorInfo());
 exit;
}

An extra tip. Do not store your passwords in plain text. Use password_hash

一个额外的小费。不要以纯文本格式存储密码。使用password_hash

$hashedPassword = password_hash($client_password, PASSWORD_DEFAULT);

On checking if the password matches the hash, use password_verify

在检查密码是否与哈希匹配时,请使用password_verify

 if(password_verify($client_password, $hashedPassword)){// do this when logging in or during some other authentication
    //all good
    echo 'Password is Correct';

    } else {
    echo 'Password is InCorrect.Sorry';
}

#2

change you insert sql code like below:

改变你插入如下的SQL代码:

Try this:

$sql = "INSERT INTO user_main (UserName, Password, Email)
      VALUES ('".$client_username."', '".$client_password."', '".$client_email."')";