Warning: SQLite3::exec(): near ".": syntax error

Question

I am creating a login system with PHP and SQLite, in the logic to insert the user in the database, my code is as followed:

<?php

 class my_db extends SQLite3 {

    public $first_name;
    public $last_name;
    public $email;
    public $password;

    public function __construct($first_name, $last_name, $email, $password) {
        $this->first_name = $first_name;
        $this->last_name = $last_name;
        $this->email = $email;
        $this->password = $password;
        $this->open("didactic-eureka.db");
    }

    public function create_user_table() {
        $sql = <<<EOF
      CREATE TABLE IF NOT EXISTS USER(
          ID PRIMARY KEY NOT NULL ,
          FIRST_NAME TEXT NOT NULL ,
          LAST_NAME TEXT NOT NULL ,
          EMAIL TEXT NOT NULL ,
          PASSWORD TEXT NOT NULL 
      );
EOF;
        $this->exec($sql);
    }

    public function insert_user() {
        $sql = <<<EOF
     INSERT INTO USER(FIRST_NAME, LAST_NAME, EMAIL, PASSWORD)
     VALUES ($this->first_name,$this->last_name,$this->email,$this->password);
EOF;
46        $insert = $this->exec($sql);

        if (!$insert) {
            $this->lastErrorMsg();
        } else {
            echo "User Inserted into the database";
        }
    }
}


if (isset($_POST["submit"])) {
//    Get value from form
    $first_name = htmlspecialchars($_POST['first_name']);
    $last_name = htmlspecialchars($_POST['last_name']);
    $email = htmlspecialchars($_POST['user_email']);
    $password = htmlspecialchars($_POST['user_password']);

//    Check if values is empty
    if (empty($first_name) || empty($last_name)) {
        $err = "alert alert-danger";
        $err_msg = "Please enter a first or last name";
    } else {
        if (empty($email)) {
            $err = "alert alert-danger";
            $err_msg = "An email is required";
        } else {
            if (empty($password)) {
                $err = "alert alert-danger";
                $err_msg = "A password is required";
            } else {
                //    Hash password when all parameters are passed
                $hashed_password = password_hash($password, PASSWORD_DEFAULT);
                $db = new my_db($first_name, $last_name, $email, $hashed_password);

                // Create table
                $db->create_user_table();

                // Insert data into table
                $db->insert_user();
            }
        }
    }
}

?>

When I try it out in the browser, it get the following response back:

Warning: SQLite3::exec(): near "@gmail": syntax error in /var/www/html/index.php on line 46

How do I fix this?

Your code is vulnerable to [SQL injection](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). You should always bind your parameters. Also, what line is line 26 ? — Nicolas, Dec 16 '19 at 19:06
Just added the line number to the question. I know it is vulnerable, the code is never going to make it into production — Tijani, Dec 16 '19 at 19:14
there is not . anywhere. Print/display what is in $sql just before calling the exec on it — B. Go, Dec 16 '19 at 19:36

score 0 · Accepted Answer · answered Dec 16 '19 at 20:10

I finally go the solution to the problem. At the point where I am inserting the values gotten from the form into the database, each value has to be wrapped around single quotes like so:

$sql = <<<EOF
INSERT INTO USER(FIRST_NAME, LAST_NAME, EMAIL, PASSWORD)
VALUES ('$this->first_name','$this->last_name','$this->email','$this->password');
EOF;

Warning: SQLite3::exec(): near ".": syntax error

1 Answers1