Skip to main content
W
WhoAmIEditor
Participant
October 13, 2010
Question

Inputing Data into MYSQL - Am I doing this right?

  • October 13, 2010
  • 2 replies
  • 1850 views

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>
    This topic has been closed for replies.

    2 replies

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    October 13, 2010

    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>

    D
    Dan_Bracuk
    Inspiring
    October 13, 2010

    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?

    A
    Anonymous
    October 13, 2010

    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

    D
    Community Expert
    Dave WattsCommunity Expert
    Community Expert
    October 13, 2010

    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.

    Dave Watts, Eidolon LLC
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices