PHP mysqli ->bindValue SQL error

Question

I have a SQL query:

if($stmt = $connection->prepare("INSERT INTO users(login, passwd, logged, register, last_login) VALUES(:login, :passwd, FALSE, NOW(), NULL")) {
    $stmt->bindValue(':login', $login, PDO::PARAM_STR);
    $stmt->bindValue(':passwd', md5($passwd), PDO::PARAM_STR);
    $stmt->execute();
    $stmt->close();
} else {
    echo "query error <b>".$connection->error."</b><br>";
}

and it returns this error:

Connection error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ':login, :passwd, FALSE, NOW(), NULL' at line 1

How can I fix it?

I am using MariaDB on Linux Fedora.

You sure it's connected with PDO, and not MySQLi? – Qirel Feb 07 '17 at 02:02 — Qirel, Feb 07 '17 at 02:02
Yes, sorry You are right, I changed title. – Paweł Nowak Feb 07 '17 at 02:06 — Paweł Nowak, Feb 07 '17 at 02:06
`bindValue` is PDO, mysqli only has unnamed placeholders. – chris85 Feb 07 '17 at 02:08 — chris85, Feb 07 '17 at 02:08

score 2 · Answer 1 · edited May 23 '17 at 12:33

That error indicates that you're using a MySQLi connection, and not a PDO one. If this is true, you have a few errors here - as PDO and MySQLi doesn't mix.

MySQLi doesn't offer named placeholders (like :placeholder, as PDO does), so you'll have to use ? instead. Furthermore, the MySQLi uses bind_param() and not bindValue() (which is a PDO function).

The code for MySQLi would look like this

if ($stmt = $connection->prepare("INSERT INTO users(login, passwd, logged, register, last_login) VALUES(?, ?, FALSE, NOW(), NULL)")) {
    $password = md5($passwd);
    $stmt->bind_param('ss', $login, $password);
    $stmt->execute();
    $stmt->close();
} else {
    echo "query error <b>".$connection->error."</b><br>";
}

There was also a ) missing at the end of your query, which I have added in the snippet above.

It's also worth noting that md5() isn't a good function to store passwords with. PHP offers password_hash() with password_verify() which you should use instead. The manual holds examples for those functions.

It's also worth noting that errors should only be displayed like that in development. For live versions, you shouldn't be displaying such errors, as they can be exploited.

References

Thanks man! It helped. It looks like I need to read about pdo and mysli differences ;) — Paweł Nowak, Feb 07 '17 at 02:14
Yes, the mysql libraries doesn't mix ;-) Even though some functions are very similar to one another. I recommend you learn both MySQLi and PDO, and use the one you find the easiest to work with, but there's certainly nothing wrong with using MySQLi as long as you bind your variables like that. *Cheers* — Qirel, Feb 07 '17 at 02:16

score 0 · Answer 2 · answered Feb 07 '17 at 02:06

0

Missing end of brackets at VALUES()

VALUES(:login, :passwd, FALSE, NOW(), NULL
VALUES(:login, :passwd, FALSE, NOW(), NULL)

answered Feb 07 '17 at 02:06

Calos

1,783
19
28

PHP mysqli ->bindValue SQL error

2 Answers2

References