CFSELECT in Coldfusion

Posted on 2014-02-10
Medium Priority
Last Modified: 2014-02-27
Edit FormDesigner TableCategory TableHi,

  I have CFM page where I populate the data from currently selected record (from prev. cfm page using CFGRID), allow users to edit the contents of data and let them save the changes.
  The question is how I can display what is already in the record for category and designer fields, but at the same time, let the user change the them from the drop down if they wish to choose different category or designer. Same question applies to Status field, but the only difference is that Status does not have a table. It is just a one char field with three possible values - A, I or D.
  If I have not explained myself clear, please let me know.


      Select      *
      FROM      Inventory
    WHERE   ID = #cfgridkey#

 <CFQUERY NAME="GetCategory" datasource="DSNName">
               SELECT   *
               FROM        Category
               WHERE      ID=#FindItem.CategoryID#

<CFQUERY NAME="GetDesigner" datasource="DSNName">
               SELECT   *
               FROM        Designer
               WHERE      ID=#FindItem.DesignerID#


<CFSELECT NAME="CategoryID" query="GetCategory" VALUE ="ID" DISPLAY="Category">
<CFSELECT NAME="DesignerID" query="GetDesigner" VALUE="ID" DISPLAY="DesignerName">
<CFINPUT TYPE="text" NAME="Status" SIZE=2 MAXLENGTH= 2 VALUE=#FindItem.Status#>
           <SELECT SIZE="1" NAME="Status">
              <OPTION>Select Status</OPTION>
Question by:sglee
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
LVL 52

Accepted Solution

_agx_ earned 1300 total points
ID: 39849105
Since you're populating the cfselect from a query, you can use the "selected" attribute.

selected - One or more option values to preselect in the selection list. To specify multiple values, use a comma-delimited list. This attribute applies only if selection list items are generated from a query.

For example, say #FindItem.CategoryID# contains the value you want to select: Run a query to get ALL of the categories:

<CFQUERY NAME="GetCategory" datasource="DSNName">
       SELECT   *
       FROM        Category
       ORDER BY Category

Then set selected=#FindItem.CategoryID# (Edit Fix Typo)

           VALUE ="ID" DISPLAY="Category" >

Same with the other select fields.

WHERE   ID = #cfgridkey#
BTW: Never use any raw client values (even cfgrid values) in a query without wrapping them in cfqueryparam to protect the db from sql injection.

Author Comment

ID: 39849127
"BTW: Never use any raw client values (even cfgrid values) in a query without wrapping them in cfqueryparam to protect the db from sql injection.  "

Obeviously you are referring to the "security issue". I am fairly new to this. Would you educate me as to what kind of damage it can cause if I don't protect the db?
What is SQL injection?
How do you wrap them in cfqueryparam?

Here is my code that displays records in flash grid format:
<cfform name="frmtest" skin="HaloBlue" timeout="600">
    <cfgrid name="gridtest"
        height="400" width="700"
        sort = "yes"
            query = "GetInventory"
        onChange = "getURL('ItemDetails.cfm?cfgridkey='+gridtest.selectedItem.ID)">

        <cfgridcolumn name="ID" header="Rec##" width="5">
        <cfgridcolumn name="ItemNo" header="Item No">
               <CFGRIDCOLUMN NAME="Title" DATAALIGN="LEFT"  HEADER="Title" width="60">
..... more column names ...
LVL 52

Expert Comment

ID: 39849140
(EDIT) SQL injection is just when someone tries to hack your system by inserting malicious sql into form fields, url parameters, etc... in the hopes that your queries are unprotected - so the bad SQL will be executed in your db without your knowledge.   Unfortunately it's extremely common.

what kind of damage it can cause if I don't protect the db?

Given the right command and db permissions it could do anything from updating your tables w/bad data to dropping (ie deleting) your entire database.  

Using bind variables (which is what cfqueryparam does) protects against the common multi-statement form of sql injection. In it's simplest form, you only need to provide the "value" and "cfsqltype" (ie data type of the db column you're matching). So say the Inventory.ID column is type "INTEGER". Your cfqueryparam statement would be:

     WHERE ID = <cfqueryparam value="#cfgridkey#" cfsqltype="cf_sql_integer">

You can find a complete list of cfsqltypes in the docs:
Certified OpenStack Administrator Course

We just refreshed our COA course based on the Newton exam.  With 14 labs, this course goes over the different OpenStack services that are part of the certification: Dashboard, Identity Service, Image Service, Networking, Compute, Object Storage, Block Storage, and Orchestration.


Author Comment

ID: 39849148
did I change it right?
      Select      *
      FROM      Inventory
    WHERE   ID = <cfqueryparam value="#cfgridkey#" cfsqltype="cf_sql_integer">

URL in the address bar still shows ID for cfgridkey. Is that ok?

LVL 52

Expert Comment

ID: 39849179
IF the data type of your "ID" column is INTEGER, then yes, it's correct. Otherwise, lookup the correct data type for your db in cfqueryparam matrix I posted above.

Though it'll work without either of these tips... 1) for best practices, you should also scope the cfgridkey variable ie  URL.cfgridKey or FORM.cfgridkey.  2) I'd also throw a val() around it. Val() converts non-numeric values to zero. That way the query won't throw an error if passed an empty or invalid value for some reason

WHERE ID = <cfqueryparam value="#val(URL.cfgridkey)#" cfsqltype="cf_sql_integer">

URL in the address bar still shows ID for cfgridkey. Is that ok?

If you're using method=get, that's fine. You just don't want to use it in the query without cfqueryparam.

Author Comment

ID: 39849196
Thank you for your help.
LVL 52

Expert Comment

ID: 39849214
You're welcome :)

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article  is about submitting  form through  ColdFusion.Ajax.submitForm to the action page and send a response back in JSON format which later can be decoded using ColdFusion.JSON.decode. By this way you can avoid the usual page refresh for subm…
CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question