In situations like this I strongly recommend CFQUERYPARAM. You already know the data type of the field and I've found that cfqueryparam will take care of your single quote problems. And it saves you from nasty SQL injection hacks.
...talking about which, I hope you're not simply using form.fieldnames as the source of the list you're looping over (as it seems to me). If this is the case, you probably want to check each value of the #field# variable as some not-so-nice people could try SQL injection attacks against your query using extra form fields with some malicious values.
Oh, and I'd definitely put <cfqueryparam> or Val() around form.autonum_id. It looks disturbingly easy to set form.autonumber_id to be '1 OR 1 = 1' and overwrite your entire table with the same value :(
PS: ...I just can't help thinking about bad things like this, I'm a very twisted and evil man...
Andy Welsh wrote:
Hiya! I hope you're enjoying your mondays- just a quick shout to Geoff and Andrew as a thanks for the MXDU evening, much appreciated!
Anyway- any help with the following problem would be greatly appreciated!
Basically, I've written a CFC which dynamically creates a database update query, generating fieldnames from identically named form fields. The code is below.
<cfif listgetat(fieldnames,1) is "autonum_id"> <cfset fieldslist= lcase(listdeleteat(fieldnames,1))> <cfset fieldslist= lcase(listdeleteat(fieldslist,listlen(fieldslist)))>
<cfset valueslist=""> <cfset loopvar=1>
UPDATE #table# SET <cfloop list="#fieldslist#" index="field"> <cfset evaluatevar= "form." & field> #field# = <cfset fieldvalue=evaluate(evaluatevar)> <cfif findnocase("char",field) is 1 or findnocase("vchar",field)> <cfset aposvar= chr(39)> <cfelse> <cfset aposvar=""> </cfif> #aposvar##rtrim(fieldvalue)##aposvar#<cfif loopvar neq listlen(fieldslist)>,</cfif> <cfset loopvar = loopvar+1> </cfloop> WHERE autonum_id = #form.autonum_id#
<cfelse> There's an error- make sure that autonum_id is the first field in your form. </cfif>
NOTE: This version outputs to screen, so the obvious cfquery tags have been ommitted (sp!).
Anyway, when run, it'll output to screen a string with single " ' " preceding the text fields, which is fine- cut and paste this string into a <cfquery> tag, and it works 110%. Now, if we run this with <cfquery> tags wrapped around the query generator it returns an 'Incorrect syntax near xxx' (xxx being the 1st part of the form data. *BUT* the error information gives everything in double quotes- " " ". And when we cut and paste *this* into a <cfquery> tag, it doesn't work, and gives the same error. So to my mind, this means that I need to replace " " " with " ' ", but chr(34) *IS* " ' ". So I'm at my wits end!
Sorry, I hope this makes sense- any more explanation needed, just say!
--- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED]
MX Downunder AsiaPac DevCon - http://mxdu.com/
