asked on

help writing a query

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

ASKER CERTIFIED SOLUTION

cheekycj

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

YZlat

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

Rimvis

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.

David Williams

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?

billythehamster

ASKER

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.

billythehamster

ASKER

the database is MSSQL 2000 does that make a difference?

David Williams

That won't make any difference; the SQL query above is standard SQL and works with any SQL-compliant database.

cheekycj

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