Solved

CFSELECT in Coldfusion

Posted on 2014-02-10
7
592 Views
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.

Thanks.

<CFQUERY NAME="FindItem" DATASOURCE="DSNName">
      Select      *
      FROM      Inventory
    WHERE   ID = #cfgridkey#
</CFQUERY>

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

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

...

<CFSELECT NAME="CategoryID" query="GetCategory" VALUE ="ID" DISPLAY="Category">
</CFSELECT>  
<CFSELECT NAME="DesignerID" query="GetDesigner" VALUE="ID" DISPLAY="DesignerName">
</CFSELECT>      
<CFINPUT TYPE="text" NAME="Status" SIZE=2 MAXLENGTH= 2 VALUE=#FindItem.Status#>
           <SELECT SIZE="1" NAME="Status">
              <OPTION>Select Status</OPTION>
              <OPTION VALUE="A">ACTIVE</OPTION>
              <OPTION VALUE="I">INACTIVE</OPTION>
              <OPTION VALUE="D">DON'T LIST YET</OPTION>
           </SELECT>
0
Comment
Question by:sglee
[X]
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
7 Comments
 
LVL 52

Accepted Solution

by:
_agx_ earned 325 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
</CFQUERY>

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

<CFSELECT NAME="CategoryID"
           query="GetCategory"
           selected="#FindItem.CategoryID#"
           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.
0
 

Author Comment

by:sglee
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"
        selectMode="row"
        sort = "yes"
        format="flash"
        rowHeaders="no"
            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 ...
     </cfgrid>
</cfform>
0
 
LVL 52

Expert Comment

by:_agx_
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:
http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7f6f.html
0
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 

Author Comment

by:sglee
ID: 39849148
did I change it right?
<CFQUERY NAME="FindItem" DATASOURCE="DSNName">
      Select      *
      FROM      Inventory
    WHERE   ID = <cfqueryparam value="#cfgridkey#" cfsqltype="cf_sql_integer">
</CFQUERY>

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

http://www.DomainName.com/..../AdminItemDetails.cfm?cfgridkey=10
0
 
LVL 52

Expert Comment

by:_agx_
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.
0
 

Author Comment

by:sglee
ID: 39849196
Thank you for your help.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 39849214
You're welcome :)
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

PROBLEM: How to add your own buttons to the bottom toolbar with paging info ( result count ). While creating a cfgrid, I ran into an issue where I wanted to embed my own custom buttons where the default ones ( insert / delete / etc… ) are for aes…
Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

710 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