How to Check Username and Change If It Exists?

[Gimple]

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.

[benanamen]

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

[dalecosp]

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

Hmm. It certainly should be thought about. A public system that uses real names as usernames isn't too common. Although anonymity on the Web is less common than it used to be....

 

There's a wealth of law and standard industry practices. Of course, Gimple doesn't say exactly what (s)he's building, either ...

 

Let's assume all those questions are solved ... how DO we check if a name exists? Without knowing details of your DB name, DB table names, etc. can't say exactly. But food for thought would be something similar to:

 

// assuming MySQLi, "user_table" is the user table, username is the name field and the primary key is "id"

function isUsername($name) {
    $s = "select id from user_table where username = '$name';";
    $r = $db->query($s);
    if ($r && $r->num_rows > 0) return true;
    return false;
}

$proposed_name = "Bob_Jones"; // this comes from your form, actually
$c = 0; // counter

while (isUsername($proposed_name)) {
    $c++;
    $proposed_name .= $c;
}

// et voilà!
$username = $proposed_name;

Edited April 27, 2017 by dalecosp

[Gimple]

dalecosp, wow, thanks so much! I've basically got it working now with your help.

 

However, the results I'm getting are these:

 

name

name1

name12

name123

etc.

 

Instead of:

 

name

name1

name2

name3

etc.

 

I'm an amateur coder, and I coded it a bit different than you did. Here's my code:

function checkname($url){
	$users = mysql_query("SELECT * FROM users
	WHERE url='$url'")
	or die(mysql_error());
	
	if(mysql_num_rows($users) > 0){
		return true;
	}
	else{
		return false;
	}
}

while(checkname($url)){
	$num++;
	$url = $url. $num;	
}

Any idea where I'm going wrong?

 

 

benanamen, your advice is useless. Most unsolicited advice is.

 

Your sig says you're never wrong. Well, guess what. In this case, you are!

Edited April 27, 2017 by Gimple

[benanamen]

Yeah, you know better don't you. I guess that's why you're using obsolete and dangerous Mysql code that has been completely removed from Php and was previously deprecated for over 10 years with a big red warning in the Php manual. Good thing an expert like you is outputting the internal system errors to the user who can do absolutely nothing with it unless he is a hacker. Lets not forget your complete brilliance in putting variables in your query instead of using prepared statements. And lets not forget your expert use of * to select every single column of the database when you are only checking one column.

 

Yeah, I don't know what I'm talking about.

Edited April 27, 2017 by benanamen

[Jacques1]

None of this works. What you guys just cannot seem to understand is that the world doesn't stand still while you run your PHP scripts. There can be many, many requests all coming in at the same time and possibly asking for the same name. That's when your pretty numbering scheme falls apart. So benanamen is right, whether you like it or not.

 

Besides that, the database code is horrible.

 

There are valid solutions to your problem. But if you brush off any criticism and rather go with a broken script, I won't waste any time on this. Your decision.

Edited April 27, 2017 by Jacques1

[Gimple]

benanamen, you're probably right about all the things you mentioned in your last post, but you're still wrong about using a person's first and last name for their username. And that's what I was addressing. I wasn't questioning your hacking skills, just your judgment and rashness.

 

I know I'm a crappy programmer. But I do what works to get the result I want. That's all I care about at this point.

 

Anyway, I figured out a solution to my last problem. So this thread is solved.

[Jacques1]

Your “solution” is demonstrably wrong, and it's now a matter of luck whether you will end up with duplicate usernames. But like you said, you don't give a shit, so obviously your application wasn't relevant in the first place.

[Gimple]

My only focus is to built a site that serves 10s of thousands of people and generates a million dollars in revenue per year, and if being an expert programmer isn't required to accomplish that, I could care less if my code doesn't appease any old random hacking perfectionist.

[Jacques1]

You're delusional. The defects in your code don't even require any “hacking”. It's the other way round: Your users have to actively work around the problems you've created.

 

In what kind of job is this acceptable? You wouldn't tolerate that attitude from a friggin' fast food waiter.

[benanamen]

My only focus is to built a site that serves 10s of thousands of people and generates a million dollars in revenue per year, and if being an expert programmer isn't required to accomplish that, I could care less if my code doesn't appease any old random hacking perfectionist.

 

Alright, you got me. I fell for this hoax thread. I almost snorted coffee through my nose when I read the punchline.

Edited April 27, 2017 by benanamen

[Gimple]

I'm glad you liked it.

[dalecosp]

None of this works. What you guys just cannot seem to understand is that the world doesn't stand still while you run your PHP scripts. There can be many, many requests all coming in at the same time and possibly asking for the same name. That's when your pretty numbering scheme falls apart. So benanamen is right, whether you like it or not.

Please don't aim your paintbrush this direction!

 

I provided an example algorithm; I didn't write his code for him. He didn't say at the time what he was building; a locking system would be a Good Idea if high concurrency is expected.  An in-house system, on the other hand ... it would be very unlikely you'd have two "Bob Jones" registering at the same time unless your company's the size of IBM or Google, and even then ...

 

Security?  My example used MySQLi code and a hard-coded variable; no particular dangers from that. Of course for a public web application you would sanitize inputs.    Dear Gimple, please read about SQL injection attacks and read the PHP Manual's page on filter_input(), for starters.

 

To you Gurus, is it really necessary to make every question/answer thread here a complete treatise on software engineering?  I can do better, but it seems like a ton of wasted breath/overkill in many situations.

[Gimple]

dalecosp, SQL injection is when people insert code into a form on the site that can mess with the database, right?

[Jacques1]

So we're finally past the la-la-la-I-can't-hear-you stage? Great.

 

Yes, this is one type of SQL injections. In practice, you'll often encounter more subtle injection vulnerabilities. For example, you can also be attacked with your own data which is already stored on the server and which you may assume to be trusthworthy (second-order injection).

 

filter_var() doesn't help against injections and isn't a very useful function in general, so I'm not sure why dalecosp brought it up. The only reliable counter-measure is to completely separate the dynamic input from the query itself by using prepared statements. A prepared statement is a “query template” with parameters. Once you've created it on the server, you can then safely pass data to the parameters and execute the statement.

// step 1: create prepared statement
$userStmt = $databaseConnection->prepare('
    INSERT INTO
      users (username, password_hash, age)
    VALUES
      (:username, :password_hash, :age)    -- three parameters
');

// step 2: bind data to the prepared statement and execute it; the data doesn't have to be "sanitized", because it cannot affect the query itself
$userStmt->execute([
    'username' => $username,
    'password_hash' => $passwordHash,
    'age' => $age,
]);

And while you're at it, you can solve the entire username problem by declaring the name column as UNIQUE, adding a name_counter column of type INT with a default value of 0 and adding one extra query:

$userData = [
    'username' => $username,
    'password_hash' => $passwordHash,
    'age' => $age,
];

// try to insert new user; if the name is already present, increment the counter instead and store the new value in a session variable
$initialUserStmt = $databaseConnection->prepare('
    INSERT INTO
      users (username, password_hash, age)
    VALUES
      (:username, :password_hash, :age)
    ON DUPLICATE KEY UPDATE
      name_counter = (@name_counter := name_counter + 1)
');
$initialUserStmt->execute($userData);

// if the previous insertion failed, do it again but append the counter value to the name
if ($initialUserStmt->rowCount() != 1)
{
    $retriedUserStmt = $databaseConnection->prepare('
        INSERT INTO
          users (username, password_hash, age)
        VALUES
          (CONCAT(:username, @name_counter), :password_hash, :age)
    ');
    $retriedUserStmt->execute($userData);
}

This works under all circumstances, and it requires at most two queries – whereas the brute-force approach only works if you're lucky and has to repeat the insertion over and over again until it finally succeeds.

 

So solving the problem is a matter of, say, 5 minutes. Denying it, coming up with excuses and arguing with us took you several hours. What do you think is smarter?

[dalecosp]

filter_var() doesn't help against injections and isn't a very useful function in general, so I'm not sure why dalecosp brought it up.

I didn't mention filter_var() ... I mentioned filter_input(). Granted, they are related and do the same sort of thing. To suggest they're less than useful, however, either implies that you have a depth of security knowledge that's way beyond most (including perhaps the engineers who wrote the Filter extension), or that you've not used it much, or deeply(?)

Edited May 1, 2017 by dalecosp

[benanamen]

 implies that you have a depth of security knowledge that's way beyond most 

 

Actually, @Jaques1 does in fact.

[dalecosp]

dalecosp, SQL injection is when people insert code into a form on the site that can mess with the database, right?

SQL injection is when an attacker accesses the database in a way you don't intend; it can be from a form, from the URL/GET, from any number of holes in security (as an example, a well-known RDBMS runs on port 3306 and must be secured by various means against outside attacks, etc.) Please read up on it, as I'm not an expert on security.

 

Yes, this is one type of SQL injections. In practice, you'll often encounter more subtle injection vulnerabilities.

 

And these vulnerabilities are hard to predict. Hence the voices calling for you to use PDO.

 

So, by all means, if PDO is available on your server, and your project/project leader will give you time to learn it, check it out.

 

I find in the real world that PDO isn't always installed, however. In addition, I've seen attacks that attempt to inject information into a document itself. I'm going to continue sanitizing inputs whether I use PDO or not.

 

Actually, @Jaques1 does in fact.

Well, I wouldn't doubt it. I've not read much of his stuff, but I don't think one gets a GURU tag here without showing some mental muscle. And to some extent I understand the desire, even the demand, to shout out loud when we encounter bad code; we're trying to absolve ourselves of any responsibility for the state of the PHP language's popular image in the minds of many IT personnel.

 

By the same token, I'm not sure that we help ourselves if the community can't try and explain this a tad more humanely at times.

[Jacques1]

In IT, there are objective technical facts which aren't up for debate. Sure, I could try to sugarcoat the errors and dance around the issue to avoid conflicts, but if I actually want to help, I have to address them at some point. And playing defects down can be harmful, because inexperienced programmers are usually unable to assess the gravity of the situation. For example, a common misconception about SQL injection attacks is that they only affect the data of the application, and that “hackers” aren't interested in this data anyway. In reality, attackers use bots to automatically scan a large number of websites for vulnerabilities. If they find one, it can often be used to gain access to the system itself and abuse it for all kinds of criminal or other malicious purposes.

 

This isn't some fantasy of software engineering nerds. If you do a quick search, you'll see people who are in deep shit, because their server suddenly started spreading malware or attacking other machines. I'd rather discuss this problem before it happens than go through the painful and frustrating procedure of recovering from a breach.

 

Concurrency defects may be less obvious, but then again, programmers tend to underestimate them. I remember working on a medium-sized web application where my predecessors had implemented a custom session mechanism with no concurrency control. There weren't too many users, so surely this would never become a problem – until it did. Errors happened for no appearent reason, users sometimes got the session of other users (not very funny), and it took quite a while to figure out what the hell is happening. If people had spent just a few minutes on a proper implementation instead of making wild assumptions, they would have saved everybody a lot of time and money.

 

It's also wrong to set up this imaginary conflict between simple, pragmatic solutions and complex, overly correct ones. As you see above, the correct approach can in fact be simpler. After a while, you don't even have to think about making the right choice. It becomes part of your programming style.

 

As to the question of “sanitizing”: I'm sure the filter extension is well-meant, but it's naive. There is no “filter” which magically turns “evil data” into “good data”, because whether a particular value can safely be used depends on the specific context. Every language has its own syntax rules, and even within a language there are usually many different subcontexts. SQL doesn't work like HTML. An SQL string doesn't work like an SQL identifier – and so on. The filter extension would have to be truly all-knowing to deal with this, and as you've probably guessed, that's not the case.

 

Not a single filter deals with SQL contexts. A lot of them are misleading. And some are downright harmful (like the HTML-related ones), because they mangle the input based on primitive algorithms. Except for a few useful validators like FILTER_VALIDATE_EMAIL, the entire extension should be avoided.

 

Validation is hardly a security feature at all. A lot of data (like this very text) simply cannot be validated, and even if data is “valid” in some sense, you again run into the problem of different contexts. For example, a perfectly valid e-mail address can still be used for SQL injection attacks, because e-mails were never meant to be SQL-safe.

 

The only real solution to (most) SQL injection problems is the prepared statement. And it's now available in all sane PHP installations, be it through PDO or mysqli.

[Gimple]

Jacques1, thanks for that example of a prepared statement. Very helpful.

 

But would you please also give me examples of prepared statements for SELECT and UPDATE queries too? All the examples I've found searching the net are of INSERT queries. But I'd like to know how those two others work as well.

[Jacques1]

The procedure is always the same: prepare() and then execute(). The underlying query is irrelevant. It can be anything, SELECT, INSERT, UPDATE, DELETE, CREATE, ...

[Gimple]

I seem to be having a problem with the correct syntax. Been trying to change my queries to prepared statements, but the script does nothing.

 

Here's an example of my code:

$users = $conn->prepare("SELECT * FROM users
WHERE email=?");
$stmt->bind_param("s", $email);
$email = $_POST['email'];
$users->execute();

if(!mysql_num_rows($users)){
	//code
}
else{
	//code
}

What am I doing wrong?

Edited May 2, 2017 by Gimple

[Jacques1]

You aren't passing any parameters to execute().

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

Also make sure that your PDO connection has emulated prepared statements off and exceptions on. Then you'll get proper error messages instead of "nothing".

$conn = new PDO($dsn, $username, $password, [
    PDO::ATTR_EMULATE_PREPARES   => false,                     // disable fake prepared statements
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,    // throw an exception in case of an error
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,          // fetch associative arrays by default
]);

[Gimple]

The script still isn't running.

 

Here's all of my code, just in case there's a problem elsewhere.

$dbHost = 'localhost';
$dbUser = 'gimple';
$dbPass = 'XCw3@#';
$dbName = 'gimple';

$conn = new mysqli($dbHost, $dbUser, $dbpass, $dbName);

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

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

if(!mysql_num_rows($users)){
	//code
}
else{
	//code
}

Any idea?

[Jacques1]

You're now mixing three different database interfaces: mysqli, PDO and the old MySQL extension. This isn't possible. All extensions are incompatible with each other.

 

My example code is written for PDO, because this is by far the most intuitive and flexible interface. If you insist on mysqli, things will be more complicated. In any case, you cannot use mysql_num_rows().