Case sensitivity and srttolower prepared statements..

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
hybris
Forum Contributor
Posts: 172
Joined: Wed Sep 25, 2013 4:09 am

Case sensitivity and srttolower prepared statements..

Post by hybris »

Hi all,

I made a function that shall check if a username has a certain length, should not begin with a number and shall check the current user db so that the username or email doesnt exist.

The problem is I allow (and want to allow) case sensitive usernames when I store them into the db but I dont want to allow another user having the same username where they use for example capital letters..

Like if a user is called Pelle i dont want another user to be able to register pelle or PELLE.

Code: Select all

<?php
function verifyusername($username, $email, $mysqli) {

    if (strlen($username) <3 || strlen($username) > 16){
        //Kolla sa att anvandarnamnet ar mellan 3 och 16 bokstaver
        echo 'Username should be 3 to 16 characters please';
            exit();
        } else {
            //Kolla sa att anvandarnamnet inte borjar pa en siffra
            if (is_numeric($username[0])) {
               echo 'Usernames must begin with a letter';
               exit();
     
           } else {
          //kontrollera username mot databasen
               if($stmt=$mysqli->prepare("SELECT * FROM users WHERE username = ? LIMIT 1")) {
                $stmt->bind_param('s', $username); // Bind "$username" to parameter (string).
                $stmt->execute(); 
                $stmt->store_result();
                $stmt->bind_result($user_id); // get variables from result.
                $stmt->fetch();
 
                    if($stmt->num_rows == 1) { // If the user exists
                     
                       echo 'User already exists';
                       exit();
                 //    return false;
                       } else {
                     //kontrollera emailadressen mot databasen
                          if($stmt=$mysqli->prepare("SELECT * FROM users WHERE email = ? LIMIT 1")) {
                            $stmt->bind_param('s', $email); // Bind "$email" to parameter.
                            $stmt->execute(); 
                            $stmt->store_result();
                            $stmt->bind_result($user_id); // get variables from result.
                            $stmt->fetch();
 
                              if($stmt->num_rows == 1) { // If the user exists
                              
                                  echo 'Email already exists';
                                  exit();
                            //    return false;
                               }
                        return true;
                           }  
                     return true;
                        }
                return true;
                }
                return true;
            }
            return true;
        }
        return true;
}

?>
One solution would be to add a new column (userlow) in my db where i store the username in lowercase using the function strtolower and use that function in the code above to check against for example userlow but I dont want to add another coulmn.

So is it possible to in the code above convert $username to lowletters and then somehow check it against the stored value in the db converted to lowletters without to bind the result from the stored username to for example $db_username and then compare the two?

Thanks
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Case sensitivity and srttolower prepared statements..

Post by Celauran »

Unless you have specifically set it otherwise, MySQL tables are case insensitive, so a query for Pelle will find PELLE as a result.
hybris
Forum Contributor
Posts: 172
Joined: Wed Sep 25, 2013 4:09 am

Re: Case sensitivity and srttolower prepared statements..

Post by hybris »

You sir are my hero!

Thank You for all Your help :) I really appreciate it.

So finally I managed to create a password protected system using prepared statements (thx for Your previous advice) with a registration page and email verification :)

I even managed to create a simple bulletin board (separate from the login system so now I will try to connect the two so 1. you must be logged in to post and 2. the post will register on the logged in user.

Might sound simple but I'm totally new to this (PHP/mysql) so I'm pretty proud of myself so far :D

One question though:

for the email activation I create a random key that i send to the registred email together with the username (I use email addr + passw to log in and then display username within the site).

the activation.php simply take the username and the random key and compare it to the user db using username as a key and then check the supplied key with the key that is registred at the user when he create the account. If the keys match I uppdate the user table and remove the activation key (null).

if the keys mismatch the account will be flagged or locked (havent done that part yet). I assume its hard to fail if you just click the link so it should be enough with 1 mismatch to lock the account and log the ip..

Are there any obvious security issues related with this type of construction?

The emailactivationlink looks like this:
http://www.mysite.xxx/activation.php?u= ... 61836bf3d0

Thanks
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Case sensitivity and srttolower prepared statements..

Post by Celauran »

You're simply confirm the email address at this stage, right, and not logging the users in? If that's the case, I think that's probably fine. One thing to consider, though, is the length of the key. I've seen links break in some mail clients when they need to wrap several times.
hybris
Forum Contributor
Posts: 172
Joined: Wed Sep 25, 2013 4:09 am

Re: Case sensitivity and srttolower prepared statements..

Post by hybris »

Yeah after they verify the account they will be redirected to the login page.

Ok yeah maybe I should shorten the key somewhat, thanks for the advice :)
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Case sensitivity and srttolower prepared statements..

Post by Weirdan »

Unless you force users to use ASCII for usernames, this part:

Code: Select all

if (strlen($username) <3 || strlen($username) > 16){
could give you unexpected results for multibyte encodings.
Post Reply