?
Solved

help writing a query

Posted on 2003-11-20
8
Medium Priority
?
287 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 19

Accepted Solution

by:
cheekycj earned 375 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
WordPress Tutorial 1: Installation & Setup

WordPress is a very popular option for running your web site and can be used to get your content online quickly for the world to see. This guide will walk you through installing the WordPress server software and the initial setup process.

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

There are two main kinds of selectors in CSS: One is base selector like h1, h2, body, table or any existing HTML tags.  For instance, the following rule sets all paragraphs (<p> elements) to red: (CODE) CSS also allows us to define our own custom …
Shoutout to Emily Plummer (http://www.experts-exchange.com/members/eplummer26.html) for giving me this article! She did most of it, I just finished it up and posted it for her :)    Introduction In a previous article (http://www.experts-exchang…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.
Suggested Courses

752 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