I have users that register email addresses with of without capital letters in. When these users login from their mobile and they by accident do or do not add this capital and the validation set on field is email, they cannot login. Is there way on login with security server connect to add the function to convert both input field and the server field to lowercase before comparing for login validation?
There are data formatters for to lowercase that you would probably need to use in both the registration process as well as the login process, to make sure they always are lowercase and always work correctly.
In the Server Connect > Security Login > Login Properties make sure you have the Username and Password both set with that data formatter I think it is
{{$_POST.username.lowercase()}}
{{$_POST.password.lowercase()}}
Although if you are using encryption I am not sure if that will mess anything up, maybe put your lowercase before it though.
{{$_POST.password.lowercase().sha256(“blablabla”)}}
2 Likes
If you are using mysql, you could also create a trigger that would update automatically when people register:
CREATE TRIGGER lowercase_insert BEFORE INSERT ON users FOR EACH ROW
SET NEW.username= LOWER(NEW.username);
CREATE TRIGGER lowercase_update BEFORE UPDATE ON users FOR EACH ROW
SET NEW.username= LOWER(NEW.username);
Then just update everything in the table:
UPDATE users SET username = LOWER(username);
Assuming table is users and username field is username.
2 Likes
Paul I created the login form from your video(thank you for taking the time to do that), I’m having the same issue with username, I’m using an email address in my case as the username. A usernname should not be case sensitive like a password, I’m my case I receive lists with email addresses all in lowercase or upper lower, most do not remember if they used upper or lower case characters when they registered so if they receive a login not correct, most would not know that it’s because of a casing issues, they just think they are positive that’s the email address they used.
I’m using dreamweaver in the past to create login forms I never had to worry about usernames/email being case sensitive, is there a way with wappler handling this without having to reformat the actual data?
It is a problem unfortunately in my opinion.
Lets say you have a situation where your username field in your database has eMaiL@SiTe.cOm or something like that, then that is what the security provider has to exactly match.
What I would do at this stage is go to the form where the users register to become a user and on the input the use to type their username, make it convert to lowercase when inserting to your database.
{{$_POST.username.lowercase()}}
Can be added to the value of the server connect inset step.
Then on your login page use the data formatters to convert whatever is typed in the username field to lowercase when it reads from the security provider.
{{$_POST.username.lowercase()}}
Can be added to the Security Login steps Username field
These two procedures will ensure all usernames stored and queried will always be inserted and queried in lowercase from this point forward.
To fix your existing data, go to your existing database and run a sql command on the username column to convert all entries to only lowercase only.
UPDATE usertable SET usernamecol = LOWER(usernamecol);
Can use something like phpMyAdmin to run this.
After all these steps are done you should never really face these issues again.
Hope that helps.
1 Like
Yes, what you’re saying would solve the issue. However it seems like a bandaid to the actual issue which is the login username with wappler, hoping they put in a fix. The great thing about wappler is that it’s supposed to be a time saving tool, hate to keep having to add extra steps to make things work they way they should that’s all.
To be very honest I do not really see this as a bug, as far as I am aware SQL has been case reliant for many years now if your table is utf8. Although I do not know all that much about it.
In my opinion, if anything, if the Dreamweaver DMX tools do not impose this limitation on login forms, I would personally see that as more of a bug, than the Wappler version that is case reliant.
@Teodor or maybe @Hyperbytes, @TomD, @ben would probably have better insights though in this case.
Basic rule i always consider usernames should NOT be case sensitive, passwords should.
As email addresses are by definition not case sensitive then if they are used as a login they should not be treat as case sensitive,
Other username such as “brian” or “Paul”, that is a design decision for the developer, personally i would not make them case sensitive but I accept that it adds a degree of security
Personally i think the security provider should cater for that but that is only my opinion.
2 Likes
I’ve hit this scenario where people have used mobiles to register and it’s auto-capitalised their email address so that is what’s stored in the database. Or it could be that they actually give their email address with some capital letters (FirstLast@domain.com) so I’d rather the database be left alone and store it exactly as they registered it.
So, I would like to achieve this:
Compare lowercase($_POST['username']) to lowercase($username_field_in_database)
This makes it completely case-insensitive without messing with the actual data.
But… how do I achieve this with Security Provider?
Is anyone able to help me with this?
Unfortunately that’s not possible with the current implementation of the security provider. The best way to avoid such issues is to just convert user emails to lowercase on insert.
Thanks @Teodor. I thought that might be the case. I’ll add it as a feature request.
I’ve done as you suggested and now the login form converts the post to lower case and then compares that to the data and I’ve UPDATED all the records so all emails are stored in lower case and done the same as part of the registration process so all is good.
1 Like
Came across this by accident looking for something else and thought this tip may be useful even thought this post is old
Perhaps an easier solution to this is to use triggers at table level to manage the conversion
Assuming the table is called users and the field is called email
Something like
CREATE TRIGGER email_ins
BEFORE INSERT ON users
FOR EACH ROW
SET NEW.email = LOWER(NEW.email)
CREATE TRIGGER email_update
BEFORE UPDATE ON users
FOR EACH ROW
SET NEW.email = LOWER(NEW.email)
3 Likes
Ooh, I like that Brian. Thanks for posting. 
I know this is a old post but I may have found a work around just wondering how safe it is to do this,
when signing up nothing is different so the database gets a username like Jeff_Bob for example
Then when logging in I take the post value form the form what contains my username as typed in by the user
Next I send a query to my users database where I compare the username column to the post value from the form but with the lower case option enabled in the query
this pulls the username as it is in the database I then send that to the security login to be compiled with the pass and remember etc
This seems to be working just wondering if this makes any security flaws?
Looks good to me 