shrimppunk shrimppunk - 9 days ago 6
SQL Question

Password generation and validation from database

I'm trying to make a method of password validation for an assignment that wants me to only allow passwords containing 'NT' followed by the last 6 digits of an existing student ID (eg. NT123456). The user will enter their last name and password on the first page, and I will run it through a query. From there if the record count is greater than 0, they were redirect to a portal, otherwise they will be told it was incorrect, but that isn't as important right now.

The form I'm using is here:

<cfform name="form" action="PortalPage.cfm">
<cfinput type="Text" name="password" maxlength="8">
<cfinput type="submit" name="submit" value="Submit">
</cfform>


Which sends the form info to here:

<cfquery name="qry1" datasource="grantme">
SELECT 'NT' + RIGHT(StudentID, 6) AS Passwords, Lname
FROM Classmates
WHERE Passwords = form.password;
</cfquery>
<cfoutput query="qry1">
#qry1.RecordCount#
</cfoutput>


But I'm getting the following error report:

Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.

The error occurred in G:\InetPub2\wwwroot\student\A00507999\PortalPage.cfm: line 9

7 :
8 : <body>
9 : <cfquery name="qry1" datasource="grantme">
10 : SELECT 'NT' + RIGHT(StudentID, 6) AS Passwords, Lname
11 : FROM Classmates

VENDORERRORCODE -3010
SQLSTATE 07002
SQL SELECT 'NT' + RIGHT(StudentID, 6) AS Passwords, Lname FROM Classmates WHERE Passwords = form.password;
DATASOURCE grantme


What can I do too fix this? I can't seem to get the format right, but the logic seems to be there.

Answer

This should get the query working. The comparison is between a varchar column and a string. Strings should be delimited with single quotes. The pound signs are necessary too.

<cfquery name="qry1" datasource="grantme">
SELECT 'NT' + RIGHT(StudentID, 6) AS Passwords, Lname
FROM Classmates
WHERE Passwords = '#form.password#';
</cfquery>

To protect against SQL injection attacks this is the best way to write the query.

<cfquery name="qry1" datasource="grantme">
SELECT 'NT' + RIGHT(StudentID, 6) AS Passwords, Lname
FROM Classmates
WHERE Passwords = <cfqueryparam value="#form.password#" cfsqltype="CF_SQL_VARCHAR">;
</cfquery>