Inspiring

Question

Login Problem

Forum|Forum|17 years ago
January 6, 2009
4 replies
838 views

I have a login page where users enter a password which is a combination of the last for digits of their ssn and their 4 digit birth year. for example (43211971).

I am using this query to make the check:

<cfquery name="validatemanager" datasource="SelfNom">
SELECT * FROM tblEmployeeList3
WHERE EmployeeNo = #Form.HREmpNo#
AND right(SSNLast4, 4) = left(#form.HRSSNo#, 4)
AND BirthYear = Right(#form.HRSSNo#, 4)
</cfquery>

My problem is when someone has a last 4 digit ssn that begins with a 0 or two, the query fails the the user gets flagged with an invalid password.

How do I account for missing 0's when they arise?

This topic has been closed for replies.

D

Dan_Bracuk

Inspiring

I'm missing something. Probably because I don't work with Access. If ssnLast4 is a numeric field, how can you perform a right() on it? Isn't right() a string function?

Also, if the last 4 digits of someone's ssn is 0123, what would be the value of the ssnlast4 field for that record?

-

-__cfSearching__-

Inspiring

> I'm missing something. Probably because I don't work with Access. If ssnLast4 is a numeric field,
> how can you perform a right() on it? Isn't right() a string function?

No, you are probably not missing something. I suspect Access does an automatic conversion from number to string when they call the right() function...

J

JatrixAuthor

Inspiring

This problem is a mess, it seems that the real issue is that the DB value is a number and should be a text.

Am I correct in this assumption? If the SSN number was a text field, this would not be an issue?

-

-__cfSearching__-

Inspiring

> I don not have control over setting up the field types

I missed that comment. Are you are saying you absolutely cannot change the column type? If you really cannot, you might be able to check the length of the value. If it is 7 (not 8) then assume it has a leading zero. It is very "hack-ish" but may work if you cannot control the field types.

J

JatrixAuthor

Inspiring

I am trying that now, here is the issue I am running into. If the length is 7, what do I do then in the query? I am not sure what that code looks like. How do I manually add a 0 a that point.

-

-__cfSearching__-

Inspiring

> AND right(SSNLast4, 4) = left(#form.HRSSNo#, 4)
> AND BirthYear = Right(#form.HRSSNo#, 4)

If the SSNLast4 column only stores the last four digits, as the name implies, just append a '0' to that value. Since you are using right(column, 4) the leading zero will ignored if it is not needed.

As an aside, you should also use cfqueryparam for all of the values.

-

-__cfSearching__-

Inspiring

> Should I assume there is no solution for this?

No. You just need to change the column data. Like I said, numeric columns do not allow leading zeroes. So when you insert a value with a leading zero it gets dropped. To preserve the zeroes you have to use a character type like varchar (I believe it is called "text" in Access.)

D

Dan_Bracuk

Inspiring

What is the datatype of the ssnlast4 field?

J

JatrixAuthor

Inspiring

Number

the database is Access

-

-__cfSearching__-

Inspiring

> Number
> the database is Access

Numeric fields do not not maintain leading zeroes. The different data types also vary in terms of the maximum number they can store.

> I have a login page where users enter a password which is a combination of the last for digits
> of their ssn and their 4 digit birth year. for example (43211971).

I would not stored that kind of information in Access, let alone use it for passwords ... for obvious reasons.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded