form data into database: preventing SQL injection

Question

It has recently been mentioned to be that our method of inserting data into our SQL database via form submission is subject to SQL injection attacks, and want some advice to harden our security.

Here's the code that inserts form data into the DB:

        <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#,
                '#FORM.first_name#',
                '#FORM.last_name#',
                '#CleanPhoneNumber#',
                '#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>

CleanPhoneNumber is set this way:

<cfset CleanPhoneNumber = REReplace(form.phone_number, "[^0-9]", "", "ALL") />

I've been told to use, for instance,

<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.phone_number#" />

but I'm not sure what to replace and where. When I replace the values with such I get an error.

Any direction would be helpful..

Answer 1

You should wrap all form and url variables in cfqueryparam

Your query would look like this:

<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(
         <cfqueryparam cfsqltype="cf_sql_integer" value="#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#">,
         <cfqueryparam cfsqltype="cf_sql_integer" value="#attendee.guest#">,
         <!--- Values for list types 
            0 = NEVER USE Will cause many many problems
            1 = Main List
            2 = Waiting List --->                    
         <cfqueryparam cfsqltype="cf_sql_integer" value="#attendee.list_type#">,
         #createodbcdatetime(now())#
     )                
</cfquery>

I'm not sure I got all the data types correct, see the full documentation of cfqueryparam for all the data types.

Answer 2

There are several good practices you can do.

For the insert code you have provided one of the things you can do is explicitly check the input of the Form Fields before inserting the data.Check for things like spaces and "'". You also want to ensure that the user does not see your error messages from bad data entered. This is useful to somebody wanting to know your table structure.

Otherwise place the insert in a stored procedure and validate the input parameters before calling the stored procedure for the insert or update.

Here is a good list of things you can do to prevent SQL injection attacks. It is related to asp.net but the concepts still apply no matter what language you are using.

How To: Protect From Injection Attacks in ASP.NET