I am getting an sql error in php

Question

Error: INSERT INTO register VALUES (DEFAULT,r, r@d.com, uuu, uuu, uuu, Engineering, 25-34, Male, 111111, Who is your favourite historical person?, uuuu) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@d.com, uuu, uuu, uuu, Engineering, 25-34, Male, 111111, Who is your favourite h' at line 1

I am getting this error and wasn't able to solve it. Thanks.

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "test";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
// define variables and set to empty values
$nameErr = $emailErr = $userErr = $passErr = $conErr = $compErr = $jobErr = $ageErr = $genderErr = $zipErr = $securityErr = $ansErr = "";
$name = $email = $username = $password = $confirm = $company = $job_function = $age_group = $gender = $zip_code = $security_question = $security_answer = "";

  if ($_SERVER["REQUEST_METHOD"] == "POST") {
      if (empty($_POST["name"])) {
          $nameErr = "Name is required";
      } else {      
          // check if name only contains letters and whitespace
          if (!preg_match("/^[a-zA-Z ]*$/",$name)) {
              $nameErr = "Only letters and white space allowed"; 
          }
          $name = $_POST["name"];
      }
      if (empty($_POST["email"])) {
          $emailErr = "Email is required";
      } else {      
          $email = $_POST["email"];     
      }
      if (empty($_POST["username"])) {
          $userErr = "username is required";
      } else {
          $username = $_POST["username"]; 
      }

      if (empty($_POST["password"])) {
          $passErr = "password is required";
      } else {
          $password = $_POST["password"];      
      }
      if (empty($_POST["confirm"])) {
          $conErr = "confirm password is required";
      } 
      if($_POST["password"] != $_POST["confirm"]) {
          $conErr = "password mismatch";
      }
      if (empty($_POST["company"])) {
          $compErr = "company is required";
      } else {
          $company = $_POST["company"];      
      }
      if ($_POST["job_function"] == "") {
          $jobErr = "job_function is required";
      } else {
          $job_function = $_POST["job_function"];      
      }
      if ($_POST["age_group"] == "") {
          $ageErr = "age_group is required";
      } else {
          $age_group = $_POST["age_group"];      
      }
      if (empty($_POST["gender"])) {
          $genderErr = "gender is required";
      } else {
          $gender = $_POST["gender"];      
      }
      if (empty($_POST["zip_code"])) {
          $zipErr = "zip_code is required";
      } else {
          $zip_code = $_POST["zip_code"];      
      }
      if ($_POST["security_question"] == "") {
          $securityErr = "security_question is required";
      } else {
          $security_question = $_POST["security_question"];      
      }
      if (empty($_POST["security_answer"])) {
          $ansErr = "security_answer is required";
      } else {
          $security_answer = $_POST["security_answer"];      
      }
      $sql = "INSERT INTO register VALUES (DEFAULT,$name, $email, $username, $password, $company, $job_function, $age_group, $gender, $zip_code, $security_question, $security_answer)";
      if ($conn->query($sql) === TRUE) {
          header('Location: '.'login.php');
      } else {
          echo "Error: " . $sql . "<br>" . $conn->error;
      }
      $conn->close();
  } 
?>

Form

 <form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>" method="post">
     <h1>Let's Create your account</h1>
         <div class="form-group">
             <label>Name</label>
             <input type="text" class="form-control" name="name" required=""><span class="error"><?php echo $nameErr;?></span>
         </div>
         <div class="form-group">
             <label>Email</label>
             <input type="email" class="form-control" name="email" required=""><span class="error"><?php echo $emailErr;?></span>
         </div>            
         <div class="form-group">
             <label>Enter Username</label>
             <input type="text" class="form-control" name="username" required=""><span class="error"><?php echo $userErr;?></span>
         </div> 
         <div class="form-group">
             <label>Create a Password</label>
             <input type="text" class="form-control" name="password" required=""><span class="error"><?php echo $passErr;?></span>
         </div> 
         <div class="form-group">
             <label>Confirm Password</label>
             <input type="text" class="form-control" name="confirm" required=""><span class="error"><?php echo $conErr;?></span>
         </div> 
         <div class="form-group">
             <label>Company</label>
             <input type="text" class="form-control" name="company" required=""><span class="error"><?php echo $compErr;?></span>
         </div> 
         <div class="form-group">
             <label>Job Function</label>
             <select class="form-control" name="job_function"><span class="error"><?php echo $jobErr;?></span>
                 <option selected="" value="">Select Job Function</option>
                 <option value="Engineering">Engineering</option>
                 <option value="Business Development / Sales">Business Development / Sales</option>
                 <option value="Supply Chain / Procurement">Supply Chain / Procurement</option>
                 <option value="Consultant">Consultant</option>
                 <option value="Other">Other</option>
             </select>
         </div> 
         <div class="form-group">
             <label>Age Group</label>
             <select class="form-control" name="age_group"><span class="error"><?php echo $ageErr;?></span>
                 <option selected="" value="">Select Age Group</option>
                 <option value="18-24">18-24</option>
                 <option value="25-34">25-34</option>
                 <option value="35-44">35-44</option>
                 <option value="45-54">45-54</option>
                 <option value="55-64">55-64</option>
                 <option value="65+">65+</option>
             </select>             
         </div> 
         <div class="form-group">
             <label>Gender</label>
             <select class="form-control" name="gender"><span class="error" required=""><?php echo $genderErr;?></span>
                 <option selected="" value="">Select gender</option>
                 <option value="Male">Male</option>
                 <option value="Female">Female</option>
             </select>
         </div> 
         <div class="form-group">
             <label>Zip Code</label>
             <input type="text" class="form-control" name="zip_code" required=""><span class="error"><?php echo $zipErr;?></span>
         </div> 
         <div class="form-group">
             <label>Set Security Question</label>
             <select class="form-control" name="security_question" required=""><span class="error"><?php echo $securityErr;?></span>
                 <option selected="">Select a security question</option>
                 <option value="Who was your first boss?">Who was your first boss?</option>
                 <option value="What is the name of your first pet?">What is the name of your first pet?</option>
                 <option value="Who was your favourite teacher?">Who was your favourite teacher?</option>
                 <option value="Who is your favourite historical person?">Who is your favourite historical person?</option>
                 <option value="What was the name of your primary school?">What was the name of your primary school?</option>
                 <option value="What is the first foreign country you have travelled to?">What is the first foreign country you have travelled to?</option>
                 <option value="What was the color and make of your first car?">What was the color and make of your first car?</option>
             </select>
             <br>
             <input type="text" class="form-control" placeholder="Type in your answer" name="security_answer" required=""><span class="error"><?php echo $ansErr;?></span>
         </div> 
         <button type="submit" class="btn btn-default" name="submit">Register</button>
     </form>

Surround your variables (strings) with single quotes in your insert query — Fabio, Dec 31 '16 at 07:27
Your code is vulnerable to mysql_injections, read here http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1 — Fabio, Dec 31 '16 at 07:28
Your code is so dangerous that I can't even look at it :-) But strings should be quoted. — Flash Thunder, Dec 31 '16 at 07:29

score 3 · Answer 1 · answered Dec 31 '16 at 07:34

Change this:

$sql = "INSERT INTO register VALUES (DEFAULT,$name, $email, $username, $password, $company, $job_function, $age_group, $gender, $zip_code, $security_question, $security_answer)";

to this:

$sql = "INSERT INTO register VALUES (DEFAULT,'".mysqli_real_escape_string($conn,$name)."','".mysqli_real_escape_string($conn,$email)."','".mysqli_real_escape_string($conn,$username)."','".mysqli_real_escape_string($conn,$password)."','".mysqli_real_escape_string($conn,$company)."','".mysqli_real_escape_string($conn,$job_function)."','".mysqli_real_escape_string($conn,$age_group)."','".mysqli_real_escape_string($conn,$gender)."','".mysqli_real_escape_string($conn,$zip_code)."','".mysqli_real_escape_string($conn,$security_question)."', '".mysqli_real_escape_string($conn,$security_answer)."')";

I've added quotas and escaped strings so the query wouldn't be vulnerable to mysql injection attacks.

I am getting an sql error in php

1 Answers1