Highlighted

Selecting and outputting certain columns and rows from a database table

Community Beginner ,
Jan 08, 2017

Copy link to clipboard

Copied

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.

Views

176

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Selecting and outputting certain columns and rows from a database table

Community Beginner ,
Jan 08, 2017

Copy link to clipboard

Copied

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.

Views

177

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Jan 08, 2017 0
LEGEND ,
Jan 09, 2017

Copy link to clipboard

Copied

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,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 09, 2017 0
Community Beginner ,
Jan 09, 2017

Copy link to clipboard

Copied

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...

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 09, 2017 0
LEGEND ,
Jan 09, 2017

Copy link to clipboard

Copied

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,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 09, 2017 0
Community Beginner ,
Jan 09, 2017

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 09, 2017 0