Solved

help writing a query

Posted on 2003-11-20
8
283 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
Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Article by: Matthew
I am a very big proponent of technology compliance standards and strive to meet such criteria in all of my work. That includes my site, which is 100% XHTML 1.0 compliant as determined by the World Wide Web Consortium. https://www.matthewstevenkel…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

825 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