/t5/coldfusion-discussions/login-problem/td-p/89586Jan 06, 2009
Jan 06, 2009
Copy link to clipboard
Copied
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.
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.
although I agree, it is unfortunately not my call. Access is
the DB that has been dropped in my lap, and I don not have control
over setting up the field types, I am just expected to manipulate
the data.
/t5/coldfusion-discussions/login-problem/m-p/89591#M8917Jan 06, 2009
Jan 06, 2009
Copy link to clipboard
Copied
> 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.)
/t5/coldfusion-discussions/login-problem/m-p/89592#M8918Jan 06, 2009
Jan 06, 2009
Copy link to clipboard
Copied
> 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.
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.
> 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.
/t5/coldfusion-discussions/login-problem/m-p/89595#M8921Jan 06, 2009
Jan 06, 2009
Copy link to clipboard
Copied
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?
> 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...
Jatrix wrote:
>
> Am I correct in this assumption? If the SSN number was a
text field, this
> would not be an issue?
>
You are correct. As a general rule of thumb a database field
should
only be a number if you plan or need to do math with it. Just
because a
string happens to contain only digits does not mean that a
numeric type
field is the best way to store it. For id's such as SSN
numbers, phone
numbers, part unit numbers etc, where one is never going to
add|divide|multiply|subtract them then there is no need for
them to be
numeric.
13
Replies
AdChoices