Solved

help writing a query

Posted on 2003-11-20
8
284 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
video playback resume on webpages 2 83
jQuery Date picker not working 16 224
Fixed div within Bootstrap carousel item 11 62
Where does legacy ASP.NET initialize language? 2 62
It's sometimes a bit tricky to use date functions in Oracle BPEL. I'll explain quickly how you can add N days to the current date. In a BPEL process this can be useful, and you can adapt it to fit your needs. First of all, let's see how to add 1 …
Browsers only know CSS so your awesome SASS code needs to be translated into normal CSS. Here I'll try to explain what you should aim for in order to take full advantage of SASS.
The viewer will learn the benefit of using external CSS files and the relationship between class and ID selectors. Create your external css file by saving it as style.css then set up your style tags: (CODE) Reference the nav tag and set your prop…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

733 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