bindParam & bindValue don't work?

Question

I'm trying to make a register/login system. To check if usernames and email addresses aren't used, I use this :

$username = $_POST['uLogin'];
    $usernameLC = strtolower($username);
    $query1 = $db0->query("SELECT userLogin FROM tbuser WHERE userLogin=':login';");
    $query1->bindValue(":login", $usernameLC, PDO::PARAM_STR);

But it doesn't work. I can create as much users with the same username as I want. By extension, it also won't let me connect onto the website as it doesn't bind values or anything, so it can't compare my username to the one in the DB.

Verifying if a username is not taken worked when I used it like this

$username = $_POST['uLogin'];
    $usernameLC = strtolower($username);
    $query1 = $db0->query("SELECT userLogin FROM tbuser WHERE userLogin='$usernameLC';");

But it isn't the proper way to go :/

Can anybody help ? :)

Answer 1

They're not working because your binded values contain quotes; remove them.

userLogin=':login'

as

userLogin=:login

"Verifying if a username is not taken worked when I used it like this"

WHERE userLogin='$usernameLC'

• You need to remove the quotes in the bind as already stated on top, and make sure you're using PDO to connect with, as stated below; if that is the case.

Using setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION) would have signaled the syntax errors.

Read up on how to use prepared statements in PDO, to prepare and execute:

• http://php.net/pdo.prepared-statements

---

An insight:

Make sure you are indeed using a PDO connection rather than a mysqli-based (it's unknown). I see these types of questions often, where OP's use mysqli_ to connect with and querying with PDO.

Those different MySQL APIs do not intermix with each other.

• Connecting through PDO on PHP.net

If you're using mysqli_ to connect with:

• See mysqli prepared statements and how to use them.

---

Add error reporting to the top of your file(s) which will help find errors, if any in regards to your POST arrays, or other possible errors.

<?php 
error_reporting(E_ALL);
ini_set('display_errors', 1);

// rest of your code

Sidenote: Error reporting should only be done in staging, and never production.

---

Edit:

"Thanks, it works great. When logging in though, comparing submitted password to the password in DB returns false. I try stocking the received password in $_SESSION['test'] to see what it gets and print_r($_SESSION); returns me this : Array ( [test] => Array ( [userPwd] => test12 [0] => test12 ) ) (test12 is my password, userPwd is the password Field in the db) Any idea ? ^^"

In regards to a comment you left about using passwords.

It seems you are storing passwords in plain text, rather than a hash. This is highly discouraged, as well as being stored in sessions; a very bad idea.

• Read up on sessions hijacking.

See this Q&A on Stack on hashed passwords:

• Q: Php 5.5 And Pdo Login
• A: https://stackoverflow.com/a/27023211/

Using PHP's password_hash() function and password_verify() function.

For PHP < 5.5 use the password_hash() compatibility pack.

---

A note about the column type and length when storing a hashed password.

• The password column should be VARCHAR.
• It should also be long enough to store the hash.
• Using VARCHAR(255) is best.

Answer 2

First off, if you're going to prepare, use ->prepare(), and remove quotes in your named placeholders, they don't need to have that:

$query1 = $db0->prepare("SELECT userLogin FROM tbuser WHERE userLogin= :login");

Then $query1->execute(), the prepared statement after the binding, so all in all:

$username = $_POST['uLogin'];
$usernameLC = strtolower($username);
$query1 = $db0->prepare('SELECT userLogin FROM tbuser WHERE userLogin = :login'); // prepare
$query1->bindValue(':login', $usernameLC, PDO::PARAM_STR); // bind
$query1->execute(); // execute