What's wrong with my simple insert?

Question

I'm using coldfusion to insert the contents of a struct (key-value pairs) into a database table. This is my code:

<cfloop collection="#results#" item="ID" >
    <cfquery name="insertStuff" datasource="myDataSource">
        INSERT INTO web..Stuff (ID, Name)
        VALUES (#ID#, #results[ID]#)
    </cfquery>
</cfloop>

This seems simple enough... but I'm getting the following error:

Incorrect syntax near 'VA'.

Any ideas?

score 10 · Accepted Answer · answered Dec 20 '10 at 09:38

10

You really ought to think about parameterising your data too.

<cfloop collection="#results#" item="ID" >
    <cfquery name="insertStuff" datasource="myDataSource">
        INSERT INTO web..Stuff (ID, Name)
        VALUES (
            <cfqueryparam cfsqltype="cf_sql_varchar" value="#ID#">, 
            <cfqueryparam cfsqltype="cf_sql_varchar" value="#results[ID]#">)
    </cfquery>
</cfloop>

answered Dec 20 '10 at 09:38

Steve Martin

1,632
10
19

To Steve's point, using the cfqueryparam will do a lot for you. It would have quoted the string 'VA' for you while protecting you from a whole bunch of bad stuff. Even in an Intranet app, you want to do this. – Aaron Greenlee Dec 20 '10 at 11:45
good point... thanks. I'll definitely try this. I'm getting back into cf after a long break from it... so there're a lot of things I've forgotten about :) – froadie Dec 20 '10 at 12:09

score 2 · Answer 2 · answered Dec 20 '10 at 08:54

2

I think I may have solved it... forgot the quotes, and they're both varchar fields :-/

<cfloop collection="#results#" item="ID" >
    <cfquery name="insertStuff" datasource="myDataSource">
        INSERT INTO web..Stuff (ID, Name)
        VALUES ('#ID#', '#results[ID]#')
    </cfquery>
</cfloop>

answered Dec 20 '10 at 08:54

froadie

79,995
75
166
235

I would still do the CFQueryParam as well. – Nathan Stanford Jan 05 '11 at 17:41
Totally agreed. CFQueryParam is definitely a best practice. I use CFEclipse's Snippets to make inserting them a breeze (they can be a pain to type). – Kyle Humfeld Jan 05 '11 at 20:25

What's wrong with my simple insert?

2 Answers2