Search code examples
sqlcoldfusion-9

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.

Solution

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