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
– Qirel

2017-02-07 02:02:31 +00:00
Commented Feb 7, 2017 at 2:02 — Qirel
– Qirel, Commented Feb 7, 2017 at 2:02
Yes, sorry You are right, I changed title.

Paweł Nowak
– Paweł Nowak

2017-02-07 02:06:46 +00:00
Commented Feb 7, 2017 at 2:06 — Paweł Nowak
– Paweł Nowak, Commented Feb 7, 2017 at 2:06
bindValue is PDO, mysqli only has unnamed placeholders.

chris85
– chris85

2017-02-07 02:08:26 +00:00
Commented Feb 7, 2017 at 2:08 — chris85
– chris85, Commented Feb 7, 2017 at 2:08

Community · Accepted Answer · 2017-05-23 12:33:39Z

3

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

edited May 23, 2017 at 12:33

CommunityBot

11 silver badge

answered Feb 7, 2017 at 2:08

Qirel

26.6k7 gold badges54 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Paweł Nowak Over a year ago

Thanks man! It helped. It looks like I need to read about pdo and mysli differences ;)

Qirel Over a year ago

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

Calos · Accepted Answer · 2017-02-07 02:06:58Z

0

Missing end of brackets at VALUES()

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

answered Feb 7, 2017 at 2:06

Calos

1,96223 silver badges33 bronze badges

Collectives™ on Stack Overflow

PHP mysqli ->bindValue SQL error

2 Answers 2

References

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

References

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related