CODEWITHSUNDEEP
phpmysql
Paweł Nowak
Paweł Nowak

Reputation: 75

PHP mysqli ->bindValue SQL error

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.

Upvotes: 0

Views: 3815

Answers (2)

Qirel
Qirel

Reputation: 26470

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

Upvotes: 3

Calos
Calos

Reputation: 1942

Missing end of brackets at VALUES()

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

Upvotes: 0

Related Questions