Do I have to cfqueryparam or scope values from a query when I'm looping through it?

Question

I'm was sitting forever on a Coldfusion8 INSERT into MySQL (5.0.88)

The data came from a query named "q" like so:

[Record # 1] 
ILN_KAEUFER: 9900000002985 
ILN_VERKAEUFER: 9900000003005

Then I want to make an INSERT like so:

<cfloop query="q">
     <cfquery datasource="db">
         INSERT INTO table_a ( 
            iln_kaeufer, 
            iln_verkaeufer
         )
         VALUES(
            "#iln_kaeufer#", 
            "#iln_verkaeufer#"
        )
      </cfquery>
 </cfloop>

This is the only way I can make it work. I first tried to scope both values like so:

 "#q.iln_kaeufer#", 
 "#q.iln_verkaeufer#"

And before that I was using cfqueryparam aswell like so:

  <cfqueryparam value="#q.iln_kaeufer#" cfsqltype="cf_sql_varchar" maxlength="13">
  <cfqueryparam value="#q.iln_verkaeufer#" cfsqltype="cf_sql_varchar" maxlength="13">

Both just produced an error.

I guess this has been asked before, but I did not find a good explanation on when I can/should use CFQUERYPARAM and when not and when to SCOPE and when not.

I always try to param&scope everything and this was the last thing I was looking for as being a mistake.

Thanks for shedding some insights!

@JustinScott: {"ERRORS":[["server_error","commit error"]],"SUCCESS":false,"DATA":""}, which I'm setting in my AJAX receiving CFC. I have a form_controller which receives/sends data and passes it on to a service_cfc which does the actuall database stuff and returns results. If I enter the value using this in MySQL directly it works, so must be something in CF. — frequent, Jul 17 '12 at 15:22
I would try using a CFTRY/CFCATCH block around the code and have it trap the actual CF error message to write to a log or e-mail it to you so you can get the underlying CF error which will help pinpoint the problem. — Justin Scott, Jul 17 '12 at 19:04

score 3 · Accepted Answer · answered Jul 17 '12 at 12:45

So you should always scope where possible, as it saves some overhead on ColdFusion having to backtrack through all the scopes until it finds your variable.

And in this case, even though your data is coming directly from the database, you should still use cfqueryparam. The protection against SQL injection is just one benefit of using cfqueryparam; it also helps with query performance: http://adamcameroncoldfusion.blogspot.co.uk/2012/07/what-one-can-and-cannot-do-with.html

This should work:

<cfloop query="q">
     <cfquery datasource="db">
         INSERT INTO table_a ( 
            iln_kaeufer, 
            iln_verkaeufer
         )
         VALUES(
            <cfqueryparam value="#q.iln_kaeufer#" cfsqltype="cf_sql_varchar" maxlength="13">,  // your code didn't have a comma here when using cfqueryparam?
            <cfqueryparam value="#q.iln_verkaeufer#" cfsqltype="cf_sql_varchar" maxlength="13">
        )
      </cfquery>
 </cfloop>

And if not I'd say check the column types and sizes. If that's still not it, it might be an issue with having a query inside another query as you're looping over it. You could try the other way to loop round it:

<cfloop index="i" from="1" to="#q.recordcount#">
  use #q.column[i]#
</cfloop>

Thanks for the alternative. I'll try that. – frequent Jul 17 '12 at 13:08 — frequent, Jul 17 '12 at 13:08

score 0 · Answer 2 · answered Jul 17 '12 at 12:44

0

Of course you should always use explicit scopes, and use parameterized queries (cfqueryparam), for many reasons.

Using cfqueryparam protects against sql injection, allows prepared statements and makes the world a better place. Scoping correctly may gain you some performance, but most of all, its better practice, allowing code to be read faster, since you're not trying to figure out which variable came from where.

What error did the mysql query with cfqueryparam raise ?

answered Jul 17 '12 at 12:44

Marius

3,043
1
15
24

The error is the problem... I'm sending a form to a CFC via AJAX, so I'm only getting undefined back as an answer. I wish I had a real error... that would be easy to solve, but this way I'm blindguessing what the problem is. – frequent Jul 17 '12 at 13:10
Do you have access to the exception/ application log files ? – Marius Jul 17 '12 at 13:20
Are you using Firebug or chrome? You should be able to see the response content from the http request which would contain the full error.. – Jason Jul 17 '12 at 14:06
1

To get the error you can wrap the code in question with a CFTRY tag with a CFCATCH to trap the error. From there you can have it write a log or e-mail you the error information before aborting the request. – Justin Scott Jul 17 '12 at 15:02

Do I have to cfqueryparam or scope values from a query when I'm looping through it?

2 Answers2