4 Replies Latest reply on Jan 9, 2017 2:46 PM by LenderDesign

    Selecting and outputting certain columns and rows from a database table

    LenderDesign Level 1

      I would like to display a monthly loan payment chart/table that shows payment amounts for various interest rates and loan amounts. The output table will have 9 columns (the first column will be the user-defined loan amounts) and 32 rows (the top row being the user-defined interest rates). I have created a master database table that is 30 columns by 992 rows and includes all of the payment amounts:

      PmtTableMaster.jpg

      I want to allow the user to select a starting interest rate and the increments in which it increases. For example, starting rate of 4.000% and increase increments of .125% (so the top row/header will be 4.000% | 4.125% | 4.250% | 4.375% | 4.500% | 4.625% | 4.750% | 4.875%). Additionally, the user would define the starting loan amount and the increments in which it increases (so the left column might be $200,000, $210,000, $220,000 and so on). I'm trying to accomplish an output like this:
      TableOutput.jpg

      I have been using CF for a number of years, but my knowledge is limited to more simple functions. I started putting together a query, etc. and realized that this extends beyond my knowledge. I am collecting the variable data from the user in a form. I can define the 8 interest rate columns and 31 loan amount rows, but I'm not sure what to do after that. Here is what I started with but wasn't getting the results I'm looking for:

       

      <cfset IntRate1 = #FORM.StartRate#>

      <cfset IntRate2 = #IntRate1# + #FORM.RateIncr#>

      <cfset IntRate3 = #IntRate2# + #FORM.RateIncr#>

      <cfset IntRate4 = #IntRate3# + #FORM.RateIncr#>

      <cfset IntRate5 = #IntRate4# + #FORM.RateIncr#>

      <cfset IntRate6 = #IntRate5# + #FORM.RateIncr#>

      <cfset IntRate7 = #IntRate6# + #FORM.RateIncr#>

      <cfset IntRate8 = #IntRate7# + #FORM.RateIncr#>

       

       

      <cfset LoanAmt1 = #FORM.StartLoanAmt#>

      <cfset LoanAmt2 = #LoanAmt1# + #FORM.LoanAmtIncr#>

      <cfset LoanAmt3 = #LoanAmt2# + #FORM.LoanAmtIncr#>

      <cfset LoanAmt4 = #LoanAmt3# + #FORM.LoanAmtIncr#>

      ...

      <cfset LoanAmt31 = #LoanAmt30# + #FORM.LoanAmtIncr#>


      <cfset List = "'#LoanAmt1#','#LoanAmt2#','#LoanAmt3#','#LoanAmt4#',...'#LoanAmt31#'">

       

      <CFQUERY NAME="tabledata" DATASOURCE="dbds" username="username" password="passw">

        SELECT LoanAmt, #IntRate1#, #IntRate2#, #IntRate3#, #IntRate4#, #IntRate5#, #IntRate6#, #IntRate7#, #IntRate8#

        FROM table

        WHERE LoanAmt IN (#PreserveSingleQuotes(List)#)

      </CFQUERY>

       

      I'm having trouble getting the loan amount to output since I am using the variables for selecting the column names. I'm hoping there is a simple way to accomplish what I'm trying to do. Any assistance will be appreciated.

        • 1. Re: Selecting and outputting certain columns and rows from a database table
          WolfShade Level 4

          I hate to sound trite, especially to someone who isn't as up-to-speed on the more advanced features of ColdFusion.  But I would be remiss if I did not mention that it is a very bad idea to use variables for column names in queries.  At the most, a variable should be used as a comparator in a query (as in a WHERE clause), or similar, and always, always, always use CFQUERYPARAM to guard against SQL injection.

           

          I wish I had better news, or some sort of suggestion on how to achieve what you seek.  But I had to jump in and say that what you are proposing (especially if personally identifiable information is involved) could have disasterous implications if any script-kiddie or serious hacker were to learn about the way your proposed system is set up.

           

          V/r,

           

          ^_^

          • 2. Re: Selecting and outputting certain columns and rows from a database table
            LenderDesign Level 1

            Thank you for the info. I appreciated your response. The table includes calculated loan payments based on a particular interest rate and loan amounts. There is no personal information included in the table and this would be the only table using variable column names. Is it less concerning if there is no sensitive data in the table?

             

            To explain further, there are 29 columns of data in the table, but the output can only accommodate 8 columns, so somehow I need to allow the end user to choose which columns they want to be displayed. The user would choose the first/starting column and then the next 7 columns will be selected in order following the starting column. It seems it might be better to rethink my solution...

            • 3. Re: Selecting and outputting certain columns and rows from a database table
              WolfShade Level 4

              Hello, LenderDesign,

               

              It is always more critical to guard against SQL-injection when PII is involved; but don't think that just because there isn't PII that you should ignore prudent security standards.  With the type of setup you describe, it would be nothing for a malicious actor to delete every table of data, or other such event, especially if you don't have a generic error template with a vague message indicating that something is wrong (allowing error output to be viewed by the user can tell a great deal about your environment and architecture, opening more attack vectors.)

               

              There may be a more secure way of doing what you want, but I'm not sure what that would be.

               

              V/r,

               

              ^_^

              • 4. Re: Selecting and outputting certain columns and rows from a database table
                LenderDesign Level 1

                Thank you again for the info. I was somewhat aware of the possible dangers, but you have helped me realize that I need to take more caution. I have found another way to do what I was wanting to accomplish that does not access a database table. Btw, I do have custom error pages enabled, so important info is more concealed.