Check form submission against data in database?

0
votes

We have a registration system that we're using where people fill out a form, and their info is put into our SQL database. However, we don't have a system in place to check for duplicate registrations, and I'm hoping to get some guidance on how to check maybe name or email address and set an error.

Here's our error setting code (one example, we have several checks):

      <cfif not len(trim(form.last_name)) or form.last_name eq "Last Name">
          <cfset errors = errors & "<li>You must include your complete last name.</li>">
      </cfif>

If no errors are found, here is the query code to insert the data into the database:

    <cfquery name="InsRegistrant" datasource="#application.Datasource#" dbtype="odbc">
        INSERT INTO Schedule_Registrations(
            schedule_id,
            first_name,
            last_name,
            phone_number,
            email,
            guest,
            list_type,
            datetime_registered
        )
        VALUES(
            #url.schedule_id#,
            <cfqueryparam cfsqltype="cf_sql_varchar" value="#FORM.first_name#">,
            <cfqueryparam cfsqltype="cf_sql_varchar" value="#FORM.last_name#">,
            <cfqueryparam cfsqltype="cf_sql_varchar" value="#CleanPhoneNumber#">,
            <cfqueryparam cfsqltype="cf_sql_varchar" value="#FORM.email#">,
            #attendee.guest#,
            <!--- Values for list types 
                0 = NEVER USE Will cause many many problems
                1 = Main List
                2 = Waiting List --->                    
            #attendee.list_type#,
            #createodbcdatetime(now())#
        )                
    </cfquery>

There's no "unique" identifier tied to a specific individual, so I'm thinking the best way is to prevent registrations that match someone with an identical first/last name or even just the email address. Any ideas on how to implement such a function using the above system would be helpful.

4
coldfusioncoldfusion-9
You didn't ask about this, but I hope you're aware that your SQL code is exposed to nasty SQL injection attacks. You should but all of your dynamic query values in <cfqueryparam> tags. – Jake Feasel
Thanks, I appreciate the heads up on that. We'll fix that ASAP. This is a new language for me so any tips like this are certainly helpful! – Nietzsche

4 Answers

0
votes

This is simplified but.. you get the idea.

<cfquery name="RegistrantEmailExists" datasource="#application.Datasource#" dbtype="odbc">
   SELECT COUNT(email) as EmailExists FROM Schedule_Registrations WHERE email = <cfqueryparam value="#form.email#" cfsqltype="cf_sql_varchar" >
</cfquery>

<cfif RegistrantEmailExists.EmailExists gt 0>
   <cfset errors = errors & "<li>That Email Address already exists.</li>">
</cfif>
0
votes

You could also set the email column in the database as a primary key, which would definitely prevent duplicate registrations. You would of course have to gracefully handle the duplicate primary key which would be thrown from the DB!

0
votes

Placing a unique key on the mail-column would be the easier way than changing the primary key. But you've also to handle the error thrown by the db

0
votes
<cfquery name="checkdup" datasource="whatever"> 
Select 1 from Schedule_Registrations where email=#form.email#
</cfquery>
<cfif #checkdup.recordcount# is 0>
<cfquery name="doyourquery" datasource="whatever">
insert into Schedule_Registrations
(email,etc1,etc2) values ('#form.email#','#etc1#','#etc2#')
</cfquery>
Thanks for registering
<cfelse>
You have already registered an account
</cfif>

And yes, all the cfqueryparam stuff you'd need would go in there. This is just the basics of it.

Good Luck :)

WHeis