How to Check Username and Change If It Exists?

[Gimple]

OK, I'll update my connection using PDO. One question about that. In your example, what does $dsn stand for? Is that host name?

 

Also, what would I know use instead of mysql_num_rows() to check if the query returned any results?

[Jacques1]

The DSN (data source name) sets the parameters for the database connection, i. e. the driver (in your case mysql), the database name and more. There's a detailed description in the tutorial I've linked to.

 

Checking for an empty result set can be implemented in many different ways. You can fetch the entire result set into an array and then use the standard array functions:

$users_result = $users->fetchAll();

if ($users_result)
{
    // users present
}
else
{
    // no users
}

Or you can use a flag:

// first assume there are no users
$users_exist = false;

// iterate over result set (which may be empty)
foreach ($users as $user)
{
    $users_exist = true;    // there is at least one user, so change the flag 

    // process $user
}

// check if there are no users
if (!$users_exist)
{
    // no users
}

Or you can use rowCount(), but this isn't recommended, because it only works for specific database systems (MySQL happens to be one of them).

if ($users->rowCount() > 0)
{
    // users present
}
else
{
    // no users
}

[Gimple]

Great. I actually figured out I could use rowCount() doing some Googling.

 

Now I've encountered another problem I haven't had any luck with Google yet.

 

I was using mysql_fetch_array() to fetch an array from my query, but that doesn't seem to be working now. Is that old mysql too?

 

If that doesn't work, how would I go about fetching an array from my database?

 

Once I got that figured out, I think I'm good.

[Jacques1]

All mysql_* functions belong to the old MySQL extensions and cannot be used together with any modern interface.

 

If that doesn't work, how would I go about fetching an array from my database?

 

See the code in my previous reply. You can iterate directly over $users with a foreach loop. Or you can use one of the fetch methods (fetchAll() for all rows, fetch() for a single row).

[Gimple]

I'm trying to use fetch(), but clearly I'm getting it wrong, because nothing's happening.

 

Here's my code:

$users = $conn->prepare("SELECT * FROM users
WHERE email=:email");

$users->execute([
	'email' => $email
]);

if(!$users->rowCount()){
	header("Location: ?error=3");
}
else{
	$user = fetch($users);
	
	if($password == $user['password']){
		//code
	}
	else{
		header("Location: ?error=4");
	}
}

Where am I going wrong?

[Jacques1]

Open the PHP/Apache error log. This will tell you exactly what's wrong.

 

There is no fetch() function. What you want is the fetch method which is part of the PDOStatement class:

$user = $users->fetch();

[Gimple]

Ah ha! Bingo! It's all working now.

 

Wow, this is great. Thanks so much for your help!

[Gimple]

Hi again.

 

I seem to have encountered a problem.

 

My code was using mysql_insert_id() to get the id of the inserted row, but now that I've changed the query to prepared statements that doesn't seem to be working.

Doesn't mysql_insert_id() work with prepared statements?

 

If not, how can I get the id of the row I just inserted in my database?

[Gimple]

Never mind. I figured it out.

[Jacques1]

Again: All functions starting with “mysql_” are incompatible with PDO. All of them. No exception.

 

So whenever you encounter them, you have to check the PHP manual (or Google) for the PDO equivalent -- which usually doesn't take long.

[JackN]

That is a very bad and insecure way to handle usernames. Don't do it.

Could you please briefly explain why it is insecure?

[ginerjm]

Don't you EVER look at the official PHP Manual for help with learning ANYTHING? Reading thru the posts that you and Jacques are exchanging, I can't believe he hasn't blown you off in anger at how you keep ignoring what he tells you to do.

[JackN]

I think that my words are wrongly written (english is not my 1st language) because I agree with Jacques and had no problem with what he said. Concerning this thread, I read it in whole and could not find any clear explanation (on my point of view -> I am a beginner with PHP) about why it would be insecure. Moreover I read the doc almost every day but I'am not very smart, sorry.

Edited May 3, 2017 by JackN

[ginerjm]

So? Why look in google for answers and NOT find them when they are all readily available in the manual? Do you know of the Manual? Go to "http://php.net/manual/en/funcref.php" or one of the other versions that is in your language.

[JackN]

Ginerjm, thank you very much for trying to help me whereas it seems like I bother you because I ask questions like if I was not willing to search by my own, which is I agree an horrible behavior for a community member. I really appreciate your patience.

 

The first message of this thread is:

 

 

When a new user signs up, they're assigned a user name (their first name and last name combined in a single string).

 

Because there may be two or more people with the same name, how do I create a loop that will check my database to see if that username already exists, and if it does add a number on the end to make it different, then run another query to see if that one exists too. And keep doing this until a free one is found.

 

And the firrst answer (that I answered to) is:

 

 

That is a very bad and insecure way to handle usernames. Don't do it.

 

If I am wright, no function nor any PHP code was mentioned in these two messages but the member who answered was already thinking that this was insecure the way it was though. I was therefore very curious to understand why he said that. I have a hard time seeing what could I search in Google or in the doc to find such an answer. If there something I am missing, please forgive me.

Edited May 3, 2017 by JackN

[ginerjm]

you just don't get it.

 

Why not read the last 20 posts with Jacques and see what my post was referring to?

 

I'm done. Made my point. And my patience is already shot. Good luck Jacques!

[Jacques1]

JackN isn't the OP. I think you're beating up the wrong person.

 

There's nothing inherently insecure about using the firstname and lastname. However, it can be a privacy issue, because even if there are no public user profiles, it's usually still possible to find valid usernames – or in this case the real names (see username enumeration). And as you can see in this thread, it's quite difficult to resolve name collisions.

 

Since usernames cannot really be protected, they should generally be pseudonyms. Not even e-mail addresses, just fantasy names chosen by the user.

[JackN]

Many thanks Jacques for your clear answer

 

I think I understand now, the point is that anyone could guess by brute force testing.

 

That's a privacy issue like you said, but I imagine that then when the attacker built a list of real username (even fancy one) by BF testing, he can then simulate a lot of login and try all of them with basic passwords like "123". At least one of them should work. This could be security issue in this case, no?

 

So the conclusion would be, never tell to the people that its username is not available but rather create a script like you provided in you example of prepared statements where the system adapt the username (by increment if necessary) so that it is unique without notifying the user.

[ginerjm]

JackN - I apologize for mistaking who I was responding to. I hope you now realize I was attempting to let the OP know of my thoughts and your post got in the middle of things. Sorry.

[JackN]

No problem, you are forgiven I am glad to know that I did not make anything wrong because I was really lost :s

[Jacques1]

It would have been better to create a new thread and link to this one. Jumping into a discussion creates a lot of confusion, as you just saw.

 

So the conclusion would be, never tell to the people that its username is not available but rather create a script like you provided in you example of prepared statements where the system adapt the username (by increment if necessary) so that it is unique without notifying the user.

 

No. When you add a counter to the names, then you're obviously revealing the names as well. If I'm “JoeBlow2”, I know there's also “JoeBlow1”.

 

As I already said, you cannot realistically protect the usernames. You just can't. So the solution is to not even try and instead use public pseudonyms like we do here. We can all see each other's username, but that doesn't affect our privacy at all, because we've chosen the names ourselves.