Error in Sql syntax when connecting to a database using mysql

Question

I am creating a form in which user will enter its details when he wants to donate blood and his or her all data will store in our database I have named my database "donor" and my table name is also "donor" but I don't know why I am getting this error when I try to input values.

Error:

ERROR: INSERT INTO `donate`.`donate` (`name`, `bloodgroup`, `address`,`number`,`gmail`) VALUES (`Mahad Ali`, `A+`, `Mirpur sarban colony,opposite of INOR hospital ,Sabeena khattak road`,`03115673591`,`gx85560@gmail.com`);
Unknown column 'Mahad Ali' in 'field list'

Code:

 <?php
    if(isset($_POST["name"]))
    {
      $name = $_POST["name"];
    }
    if(isset($_POST["bloodgroup"]))
    {
        $bloodgroup = $_POST["bloodgroup"];
    }
    if(isset($_POST["address"]))
    {
        $address = $_POST["address"];
    }
    if(isset($_POST["number"]))
    {
        $number = $_POST["number"];
    }
    if(isset($_POST["gmail"]))
    {
        $gmail = $_POST["gmail"];
    }

    // Set connection variables
    $server = "localhost";
    $username = "root";
    $password = "";

    // Create a database connection
    $con = mysqli_connect($server, $username, $password);

    // Check for connection success
    if(!$con){
        die("connection to this database failed due to" . mysqli_connect_error());
    }
    // echo "Success connecting to the db";

    // Collect post variables
    $sql = "INSERT INTO `donate`.`donate` (`name`, `bloodgroup`, `address`,`number`,`gmail`) VALUES (`$name`, `$bloodgroup`, `$address`,`$number`,`$gmail`);";
    // echo $sql;
    if($con->query($sql) == true){
         echo "Successfully inserted";
        $insert = true;
    }
    else{
        echo "ERROR: $sql <br> $con->error";
    }

    // Close the database connection
    $con->close();

 ?>
<!DOCTYPE html>
<html lang="en" dir="ltr">
  <head>
    <meta charset="utf-8">
    <link rel="stylesheet" href="index.css?v=<?php echo time(); ?>">
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.0-beta1/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-giJF6kkoqNQ00vy+HMDP7azOuL0xtbfIcaT9wjKHr8RbDVddVHyTfAAsrekwKmP1" crossorigin="anonymous">
    <title>Donate</title>
  </head>
  <body>
    <nav class="navbar navbar-expand-lg navbar-light" style="background-color:#654062;">
    <div class="container-fluid">
      <a class="navbar-brand" href="#">Appolo Blood Bank</a>
      <button class="navbar-toggler" type="button" data-bs-toggle="collapse" data-bs-target="#navbarSupportedContent" aria-controls="navbarSupportedContent" aria-expanded="false" aria-label="Toggle navigation">
        <span class="navbar-toggler-icon"></span>
      </button>
      <div class="collapse navbar-collapse" id="navbarSupportedContent">
        <ul class="navbar-nav me-auto mb-2 mb-lg-0">
          <li class="nav-item1">
            <a class="nav-link active" aria-current="page" href="#">Home</a>
          </li>
          <li class="nav-item2">
            <a class="nav-link active" aria-current="page" href="#">Explore</a>
          </li>
          <li class="nav-item3">
            <a class="nav-link active" aria-current="page" href="#">About Us</a>
          </li>
          <li class="nav-item4 ">
            <a class="nav-link active ml-5" aria-current="page" href="#">Team Members</a>
          </li>
        </ul>
        <form class="d-flex">
          <input class="form-control me-2" type="search" placeholder="Search" aria-label="Search">
          <button class="btn btn-outline-success" type="submit">Search</button>
        </form>
      </div>
    </div>
  </nav>
  <div class="">
    <h1 class="text-donation">Blood Donation Form</h1>
    <div class="inner-donation">
      <form class="" action="donate.php" method="post">
        <label class="name-donation" for="">Name:</label>
        <input class="input-donation" type="text" name="name" value="" placeholder="Enter your name">
        <br>
        <br>
        <label class="group-donation" for="">Blood Group:</label>
        <input class="group-input-donation" type="text" name="bloodgroup" value="" placeholder="Enter your Blood Group">
        <br>
        <br>
        <label class="address-donation" for="">Address:</label>
        <input class="address-input-donation" type="text" name="address" value="" placeholder="Enter your Address">
        <br>
        <br>
        <label class="number-donation" for="">P.Number:</label>
        <input class="number-input-donation" type="text" name="number" value="" placeholder="Enter your Number">
        <br>
        <br>
        <label class="gmail-donation" for="">Gmail:</label>
        <input class="gmail-input-donation" type="text" name="gmail" value="" placeholder="Enter your Gmail">
        <button type="submit" name="button">Submit</button>
        </form>
    </div>
  </div>
  </body>
</html>

You have used apostrophes around your column names when you should use back ticks. — Tangentially Perpendicular, Dec 29 '20 at 00:56
It won't be long before you see the warnings about SQL Injection. You should refactor your code to use prepared statements. — Tangentially Perpendicular, Dec 29 '20 at 00:57
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Dec 29 '20 at 01:20

score -1 · Answer 1 · answered Dec 29 '20 at 01:12

First, as Tagentially Perpendicular said, your error is caused because you are using ' instead of ` around your identifier.

You should use:

$sql = "INSERT INTO `donate`.`donate` (`name`, `bloodgroup`, `address`,`number`,`gmail`) VALUES ('$name', '$bloodgroup', '$address','$number','$gmail');";

Second, people could just send anything into your POST variables as you don't check them, and third, as Tagentially Perpendicular said as well, you are in risk of SQL injection: someone could execute arbitrary SQL on your database and see it's whole contents. You must use prepared statements. Here is a code using prepared statements and checking errors that should work.

// Collect post variables
$name = $_POST['name'];
$bloodgroup = $_POST['bloodgroup'];
$address = $_POST['address'];
$number=$_POST['number'];
$gmail=$_POST['gmail'];
// Check post variables content...
// ....

// Query with prepared statement
$query = "INSERT INTO `donate`.`donate` (`name`, `bloodgroup`, `address`,`number`,`gmail`) VALUES (?, ?, ?, ?, ?);";

if (!($stmt = $con->prepare($query))) {
            mysqli_rollback($con);
            throw new Exception("Prepare failed for query: (" . $con->errno . ") " . $con->error);
}
if (!($stmt->bind_param("sssss", $name, $bloodgroup, $address, $number, $gmail))) {
            mysqli_rollback($con);
            throw new Exception("Bind failed for query: (" . $stmt->errno . ") " . $stmt->error);
}
if (!($stmt->execute())) {
            mysqli_rollback($con);
            throw new Exception("Execute failed for query: (" . $stmt->errno . ") " . $stmt->error);
}

i am getting this error now after using your method: Notice: Undefined variable: con in C:\xampp\htdocs\Blood Bank\donate.php on line 14 Fatal error: Uncaught Error: Call to a member function prepare() on null in C:\xampp\htdocs\Blood Bank\donate.php:14 Stack trace: #0 {main} thrown in C:\xampp\htdocs\Blood Bank\donate.php on line 14 — Azid Ali, Dec 29 '20 at 01:16
@AzidAli It's because you must add everything that was above ```// Collect post variables``` from your first post in your code, I guess you took only my code and thus deleted the connection you made to the database (the variable ```$con```). — Waelmio, Dec 29 '20 at 01:20

Error in Sql syntax when connecting to a database using mysql

1 Answers1