Inputing Data into MYSQL - Am I doing this right?

[WhoAmIEditor]

So I built a website a few years ago when I was first learning coldfusion, I allowed users to register, it has worked fine for my needs but I am getting ready to build another site and am wondering if my Data Insert into MYSQL is correct for the times...

Here is my code for my current solution, I guess my main question is, after a user clicks "Submit" is this the best way to insert data into my database?

<cfquery name="peopleregister" datasource="sitedata">
     INSERT INTO users (registrationdate, namefirst, namelast, fullname, UserPassword, email, streetaddress, addressline2, city, state, zipcode, country, phonenumber)

     VALUES (#CreateODBCDateTime(Now())#, '#REReplace(titleCase(Trim(Form.namefirst)), "[^0-9a-zA-Z_]", "", "ALL")#', '#REReplace(titleCase(Trim(Form.namelast)), "[^0-9a-zA-Z_]", "", "ALL")#', '#REReplace(titleCase(Trim(Form.namefirst)), "[^0-9a-zA-Z_]", "", "ALL")# #REReplace(titleCase(Trim(Form.namelast)), "[^0-9a-zA-Z_]", "", "ALL")#','#Form.UserPassword#', '#LCase(Trim(Form.email))#', '#Form.streetaddress#', '#Form.addressline2#', '#Form.city#', '#REReplace(UCase(Form.state), "[^0-9a-zA-Z_]", "", "ALL")#', '#Form.zipcode#', '#Form.country#', '#Form.phonenumber#')
</cfquery>

[Dave Watts]

You need to wrap all input values (Form, URL, Cookie, CGI) with CFQUERYPARAM within your query. This is very important.

Also, there's no need to wrap Now() with CreateODBCDateTime(), since they both return the same format.

Dave Watts, CTO, Fig Leaf Software

http://www.figleaf.com/

http://training.figleaf.com/

Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on

GSA Schedule, and provides the highest caliber vendor-authorized

instruction at our training centers, online, or onsite.

[BKBK]

I have included Dave's cfqueryparam suggestion, and have assumed the paramaters are all varchars. Modify accordingly.

<!--- store the variables locally, so your SQL script wont be cluttered. If in a function, then 'var' these variables. --->
<cfset variables.namefirst = REReplace(titleCase(Trim(Form.namefirst)), "[^0-9a-zA-Z_]", "", "ALL")>
<cfset variables.namelast  = REReplace(titleCase(Trim(Form.namelast)), "[^0-9a-zA-Z_]", "", "ALL")>
<cfset variables.fullname  = REReplace(titleCase(Trim(Form.namefirst)), "[^0-9a-zA-Z_]", "", "ALL") & " " & REReplace(titleCase(Trim(Form.namelast)), "[^0-9a-zA-Z_]", "", "ALL")>
<cfset variables.email     = LCase(Trim(Form.email))>
<cfset variables.state     = REReplace(UCase(Form.state), "[^0-9a-zA-Z_]", "", "ALL")>

<!--- indent properly to save you time and hair-pulling during debugging --->
<cfquery name="peopleregister" datasource="sitedata">    
INSERT INTO users (
   registrationdate,
   namefirst,
   namelast,
   fullname,
   UserPassword,
   email,
   streetaddress,
   addressline2,
   city, state,
   zipcode,
   country,
   phonenumber
    )    
   str_to_date('#dateNow#','%m-%d-%Y %H:%i:%s'),
   <cfqueryparam value="#variables.namefirst#" cfsqltype = "cf_sql_varchar">,
   <cfqueryparam value="#variables.namelast#" cfsqltype = "cf_sql_varchar">,
   <cfqueryparam value="#variables.fullname#" cfsqltype = "cf_sql_varchar">,
   <cfqueryparam value="#form.UserPassword#" cfsqltype = "cf_sql_varchar">,
   <cfqueryparam value="#variables.email#" cfsqltype = "cf_sql_varchar">,
   <cfqueryparam value="#form.streetaddress#" cfsqltype = "cf_sql_varchar">,
   <cfqueryparam value="#form.addressline2#" cfsqltype = "cf_sql_varchar">,
   <cfqueryparam value="#form.city#" cfsqltype = "cf_sql_varchar">,
   <cfqueryparam value="#variables.state#" cfsqltype = "cf_sql_varchar">,
   <cfqueryparam value="#form.zipcode#" cfsqltype = "cf_sql_varchar">,
   <cfqueryparam value="#form.country#" cfsqltype = "cf_sql_varchar">,
   <cfqueryparam value="#form.phonenumber#" cfsqltype = "cf_sql_varchar">
</cfquery>

[Dan_Bracuk]

Regarding str_to_date('#dateNow#','%m-%d-%Y %H:%i:%s'),

Seems a tad overengineered to me.  Doesn't mysql have a function that returns the current date and time?

[Guest]

I believe it's CURRENT_TIMESTAMP

So in MySQL you can run this:

     SELECT CURRENT_TIMESTAMP

Which would return the current timestamp.

Dave @ Oyova Software

http://www.oyova.com - Web Design and Development

[BKBK]

Dan Bracuk wrote:

Regarding str_to_date('#dateNow#','%m-%d-%Y %H:%i:%s'),

Seems a tad overengineered to me.  Doesn't mysql have a function that returns the current date and time?

MySQL does indeed have functions that return the current date/time. However, I would advise choosing a format in which to store the datetime, and sticking with it throughout. There can be confusion between formats when you move back and forth between Coldfusion and MySQL.

[WhoAmIEditor]

Got it.

So there isn't a 'more simple' way of creating a form submit data?

Currently it looks like this. None of the code is correct below but just the overall idea.

<cfif button is clicked>
        <cfquery send data to server
</cfif>
<cfform>
     All input boxes here
</cfform>

I guess I'm just wondering if doing the whole IF/THEN/ELSE way of inputing data is the correct way of doing most INSERT interactions with the database.

[Dan_Bracuk]

The way you describe is neither correct nor incorrect.  It's one of two methods that work equally well.

The other method is to submit the form to a different page with the appropriate code.

Going back to your original question, there is another way that incorporates the prevention of duplicate records.  The query would resemble

insert into atable

(field1, field2, etc)

select distinct

value1, value2, etc

from some_small_table

where you don't already have the record.

[BKBK]

addendum:

The code should have started as follows:

<!--- store the variables locally, so your SQL script wont be  cluttered. If in a function, then 'var' these variables. --->

<cfset variables.dateNow = dateformat(now(), "mm-dd-yyyy") & " " & timeformat(now(),"HH:MM:SS")>

and the line  str_to_date('#dateNow#','%m-%d-%Y %H:%i:%s')

should have been

str_to_date('#variables.dateNow#','%m-%d-%Y %H:%i:%s'),