PHP：PDO MySQL错误

Question

I don't really know MySQL but I try.

I have this script in PHP

$sql = $DB->prepare("INSERT INTO `users`(`id`, `firstname`, `lastname`, `email`, `password`) VALUES ($this->firstname, $this->lastname, $this->email, $this->password))");

and when I use

print_r($sql->errorInfo());

It is giving me this error

Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: no parameters were bound in C:\Bitnami\wampstack-5.5.27-0\apache2\htdocs\OOPLoginegister.php on line 115 Array ( [0] => HY093 [1] => [2] => )

If anyone could help me, I would appreciate it very much.

Thank you.

EDIT: I changed it to

$sql = $DB->prepare("INSERT INTO `users`(`firstname`, `lastname`, `email`, `password`) VALUES ($this->firstname, $this->lastname, $this->email, $this->password))");

And now it's giving me

Array ( [0] => 42000 [1] => 1064 [2] => 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 '@gmail.com, ff0b80f26259f9c0178aeed5198bac48))' at line 1 )

Answer 1

You are misusing the prepare() function. When using prepared statements, you are supposed to use either ? or :name as placeholders for your values. This prevents you from constructing a malicious SQL query from user input.

Also, you are listing 5 fields, but only give 4 values. If id is an AUTO_INCREMENT field then it can just be omitted from the query.

Finally, you had too many ) in your query,

$sql = $DB->prepare("INSERT INTO `users`(`firstname`, `lastname`, `email`, `password`)
    VALUES (:firstname, :lastname, :email, :password)");

Now you just pass an array of values to execute() to bind to the placeholders.

$sql->execute(array(
    'firstname' => $this->firstname,
    'lastname' => $this->lastname,
    'email' => $this->email,
    'password' => $this->password
));

P.S. Your original code didn't work because you forgot to put quotes around your strings.

INSERT INTO `users` (`email`) VALUE ('[e-mail]');