Get Username from email address

[Adam Parker-5]

Hello -

I have inherited a web application that is secured with a login.  The user logs in with a username, but the only way the user is uniquely identified in the is by an email address.

So if user George logs in with the username george123, how can I query the database for the an email address that contains "george123?"

Currently, I have

<cfset user = #session.username#>

<cfquery name="getUser" datasource="myDSN">
  SELECT firstname, lastname
        FROM client
        WHERE email like '%#user#%'
</cfquery>

How may I compare the user variable to the email record in the database table?

Thank you,
Adam

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f

Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3202
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[James Holmes-3]

The query you wrote does it.

However, id the user is logging in with a username, isn't that
username going to be in the DB somewhere? How is the authentication
checked? What happens if two people have email addresses like
fred@... and fred@...?

mxAjax / CFAjax docs and other useful articles:
http://www.bifrost.com.au/blog/



2009/3/20 Adam Parker <adgparke@...>:

>
> Hello -
>
> I have inherited a web application that is secured with a login.  The user logs in with a username, but the only way the user is uniquely identified in the is by an email address.
>
> So if user George logs in with the username george123, how can I query the database for the an email address that contains "george123?"
>
> Currently, I have
>
> <cfset user = #session.username#>
>
> <cfquery name="getUser" datasource="myDSN">
>        SELECT firstname, lastname
>        FROM client
>        WHERE email like '%#user#%'
> </cfquery>
>
> How may I compare the user variable to the email record in the database table?
>
> Thank you,
> Adam
>
>

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f

Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3203
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Robert Rawlins - Think Blue]

Yeah I would agree with James, this certainly isn’t a way to secure an
application, I mean, what's to stop me putting in my username as 'Hotmail'
or  'Gmail' and that instantly heightens my chance of just being able to
guess a password, I'll bet I could use 'Hotmail' and 'Password' some user
will have chosen that combination because they couldn’t be bothered to
remember a proper password.

I would say just use the Email address in full, no need for shortened
usernames at all, just look at the way lots of major applications work, they
generally just use your email as a username.

The way you're currently going about it is a heavy security risk, I'd stop
and go another route.

Rob

-----Original Message-----
From: James Holmes [mailto:james.holmes@...]
Sent: 20 March 2009 02:27
To: sql
Subject: Re: Get Username from email address


The query you wrote does it.

However, id the user is logging in with a username, isn't that
username going to be in the DB somewhere? How is the authentication
checked? What happens if two people have email addresses like
fred@... and fred@...?

mxAjax / CFAjax docs and other useful articles:
http://www.bifrost.com.au/blog/



2009/3/20 Adam Parker <adgparke@...>:
>
> Hello -
>
> I have inherited a web application that is secured with a login.  The user
logs in with a username, but the only way the user is uniquely identified in
the is by an email address.
>
> So if user George logs in with the username george123, how can I query the
database for the an email address that contains "george123?"

>
> Currently, I have
>
> <cfset user = #session.username#>
>
> <cfquery name="getUser" datasource="myDSN">
>        SELECT firstname, lastname
>        FROM client
>        WHERE email like '%#user#%'
> </cfquery>
>
> How may I compare the user variable to the email record in the database

table?
>
> Thank you,
> Adam
>
>



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f

Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3204
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Adam Parker-5]

>The query you wrote does it.
>
>However, id the user is logging in with a username, isn't that
>username going to be in the DB somewhere? How is the authentication
>checked? What happens if two people have email addresses like
>fred@... and fred@...?
>
>mxAjax / CFAjax docs and other useful articles:
>http://www.bifrost.com.au/blog/
>>

The users are verified and checked by a CAS server.  Once the username has been verified, the user is redirected to the secured application.  The session variable contains only the username.  The database contains email addresses.  I am not certain how to approach this using CF.  I thought that something could be done to compare the username to the characters to the left of the "@" in the email address.  I'm just not clear on the "how" part.

Thank you.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f

Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3205
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Raymond Thompson]

<cfset UserID=Session.UserID & "@">
<cfquery>
  select *
  from table
  where email like '#UserID#%'
</cfquery>

Ray Thompson
Tau Beta Pi (www.tbp.org)
The Engineering Honor Society
865-546-4578

-----Original Message-----
From: Adam Parker [mailto:adgparke@...]
Sent: March 20, 2009 11:39 AM
To: sql
Subject: Re: Get Username from email address

>The query you wrote does it.
>
>However, id the user is logging in with a username, isn't that
>username going to be in the DB somewhere? How is the authentication
>checked? What happens if two people have email addresses like
>fred@... and fred@...?
>
>mxAjax / CFAjax docs and other useful articles:
>http://www.bifrost.com.au/blog/
>>

The users are verified and checked by a CAS server.  Once the username has
been verified, the user is redirected to the secured application.  The
session variable contains only the username.  The database contains email
addresses.  I am not certain how to approach this using CF.  I thought that
something could be done to compare the username to the characters to the
left of the "@" in the email address.  I'm just not clear on the "how" part.

Thank you.



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f

Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3206
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Raymond Thompson]

Yes, the spaces will make a significant difference.

<cfset user = Trim(Session.username) & "@">
<cfquery name="getUser" datasource="myDSN">
select email
from client
where email like <cfqueryparam value="#user#%" cfsqltype="CF_SQL_VARCHAR">
</cfquery>

That should eliminate the spaces and also provide a little more security on
the DB access. I don't think there is a difference between Access and SQL
Server in that regard. It does appear to me as if your session variable has
a space in front and in back of the actual value. The Trim() will eliminate
those spaces.

Ray Thompson
Tau Beta Pi (www.tbp.org)
The Engineering Honor Society
865-546-4578


-----Original Message-----
From: Adam Parker [mailto:adgparke@...]
Sent: March 20, 2009 02:28 PM
To: sql
Subject: Re: Get Username from email address


Hi Ray -

I've tried this and modified the query to get just the email field and
compare the username to the email field.  I don't see why it's not working:

<cfset user = Session.username & "@">


<cfquery name="getUser" datasource="myDSN">
select email from client where email like '#user#%'
</cfquery>

<cfdump var="#getUser#">

CFDump produces:

select email from client where email like ' adgparke @*'

Does the space between the username and the "@" make a difference?  Does it
make a difference that this is an Access database?  I don't get why it's not
working.





~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f

Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3208
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Adam Parker-5]

Hi Ray -

I've tried this and modified the query to get just the email field and compare the username to the email field.  I don't see why it's not working:

<cfset user = Session.username & "@">


<cfquery name="getUser" datasource="myDSN">
select email from client where email like '#user#%'
</cfquery>

<cfdump var="#getUser#">

CFDump produces:

select email from client where email like ' adgparke @*'

Does the space between the username and the "@" make a difference?  Does it make a difference that this is an Access database?  I don't get why it's not working.



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f

Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3207
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Adam Parker-5]

That solved it and it works!  Thank you for your assistance in coding and helping me understand this issue.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f

Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3209
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6