Solved

CFSELECT in Coldfusion

Posted on 2014-02-10
7
582 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
  • 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 our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 

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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

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…
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…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

860 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