Solved

help writing a query

Posted on 2003-11-20
8
281 Views
Last Modified: 2010-04-06
Hi I was wondering if anyone could help. I have a database which I need
to query but I am not sure how to do it.

There are 3 fields in the table called CONTACTPARAMETER

PARAMETERNAME
PARAMTERVALUE
SERIALNUMBER

I want the user to input their username (their email address) and
password in order to login. Here is a sample of the data in the
database.

PARAMETERNAME | PARAMTERVALUE | SERIALNUMBER  
user_id | me@mydomain.com | 00510
password | 123456 | 00510

The form fields are user_id and password.

Thanks


Joe

PS I am using coldfusion
0
Comment
Question by:billythehamster
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 19

Accepted Solution

by:
cheekycj earned 125 total points
ID: 9788713
This is how you can do it in cf against an Oracle DB:

<cfquery name="loginQuery" dsn="yourdsn">
select cp1.serialnumber from contractparameter cp1, contractparameter cp2
where cp1.user_id = '#FORM.user_id#'
and cp1.serialnumber = cp2.serialnumber
and cp2.password = '#FORM.password#'
</cfquery>

<cfif loginQuery.recordCount eq 1>
  Your logged in!
<cfelse>
sorry invalid username or password
</cfif>

CJ
0
 
LVL 35

Expert Comment

by:YZlat
ID: 9789348
try this, where strEmail is a user e-mail and strPWD is a password for that user:

sSQL="SELECT * FROM Table1 WHERE PARAMETERNAME = 'user_id' AND PARAMTERVALUE ='" & strEmail & "' AND PARAMETERNAME = 'password' AND PARAMTERVALUE ='" & strPWD & "'"

set rst=connObj.Execute(sSQL)

if not (rst.eof and rst.bof) then

'user exists in the database
else
'no user found
end if
0
 
LVL 19

Expert Comment

by:Rimvis
ID: 9796232
Slightly off topic:

I would STRONGLY suggest to search both strEmail and strPWD for single quotes (') and replace them with double (''). Code in VBScript would look like this
strEmail = Replace(strEmail, "'", "''")

Otherwise your code would be vulnerable to SQL injection.
0
 
LVL 4

Expert Comment

by:davidmwilliams
ID: 9801871
YZlat, your query will fail; you can't have any rows where PARAMETERNAME = both user_id and password!

CheekyCJ has the syntax right.

Note though, there is a presumption that the serial_number is the same for all instances of the same user? Is this valid? Otherwise, there is no way of relating a username and password to each other.

FWIW, your table design is somewhat curious; why not have the userid and password in one row?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 2

Author Comment

by:billythehamster
ID: 9804187
hi thanks for the input i will give it a try when I am in the office on monday. in response to your question the table design is not mine. i am building a web interface for another persons product which has a VB front end.
0
 
LVL 2

Author Comment

by:billythehamster
ID: 9804194
the database is MSSQL 2000 does that make a difference?
0
 
LVL 4

Expert Comment

by:davidmwilliams
ID: 9804577
That won't make any difference; the SQL query above is standard SQL and works with any SQL-compliant database.
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 9812027
I agree, I do question the design a bit :-)

My code should work in MSSQL 2000, let us know if you run into any problems

CJ
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

SASS allows you to treat your CSS code in a more OOP way. Let's have a look on how you can structure your code in order for it to be easily maintained and reused.
JavaScript has plenty of pieces of code people often just copy/paste from somewhere but never quite fully understand. Self-Executing functions are just one good example that I'll try to demystify here.
The viewer will learn how to dynamically set the form action using jQuery.
HTML5 has deprecated a few of the older ways of showing media as well as offering up a new way to create games and animations. Audio, video, and canvas are just a few of the adjustments made between XHTML and HTML5. As we learned in our last micr…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now