Solved

How to create a form where the user enters there own query?

Posted on 2004-08-02
13
315 Views
Last Modified: 2013-12-24
I would like to create a web form that will send a complete query to the action page.
In other words, I want the user to be able to design their query in this form, then submit it to the action page and it gets plugged into the CFQUERY tag of the action page.

This would be just a select query, not a insert, edit or delete.
Also, the field names and number of fields would need to be dynamic.  Which would be determined by the user's query from the form.

Is this possible?  If so, is there a code sample I can go by?
Thanks
0
Comment
Question by:g118481
  • 3
  • 2
  • 2
  • +4
13 Comments
 
LVL 35

Accepted Solution

by:
mrichmon earned 20 total points
ID: 11694931
wow VERY VERY VERY VERY DANGEROUS!!!!!!

I would not let the user actually create the query.

I would do something like :

Indicate the fields that you would like to view:

<checkbox>  Field1
<checkbox>  Field2
<checkbox>  Field3
<checkbox>  Field4
<checkbox>  Field5
<checkbox>  Field6


Then on the actionpage

<cfset fieldlist = "">
<cfif IsDefined("Form.field1")><cfset fieldlist = ListAppend(fieldlist, "Field1")></cfif>
<cfif IsDefined("Form.field2")><cfset fieldlist = ListAppend(fieldlist, "Field2")></cfif>
<cfif IsDefined("Form.field3")><cfset fieldlist = ListAppend(fieldlist, "Field3")></cfif>
<cfif IsDefined("Form.field4")><cfset fieldlist = ListAppend(fieldlist, "Field4")></cfif>
<cfif IsDefined("Form.field5")><cfset fieldlist = ListAppend(fieldlist, "Field5")></cfif>
<cfif IsDefined("Form.field6")><cfset fieldlist = ListAppend(fieldlist, "Field6")></cfif>

<cfif fieldlist NEQ "">
<cfquery name="UserQuery" datasource="mydsn">
   SELECT #fieldlist# FROM yourtable
</cfquery>

     
0
 
LVL 1

Author Comment

by:g118481
ID: 11695882
Ok, that sounds logical.
What about how to output the field list.  It may not always be the same?
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 11695962
You need to have some way of knowing what their choices will be.  This could be coded, it could be pulled from a query, it is up to you....
0
 
LVL 15

Expert Comment

by:tim_cs
ID: 11696324
What about if you just used mrichmon's way to let them choose the fields they want then use cfdump for the output of the query?
0
 
LVL 1

Assisted Solution

by:seangw
seangw earned 20 total points
ID: 11696432
As stated above, a straight SQL statement is dangerous, but easily done (passing the SQL statement as a string, then executing the variable in the CFQUERY tag).

The method mentioned is great, and to return any results you get you can cfdump, or generate a formatted table with the column names.

To get the column names in a database you can always:

CFQUERY NAME=sqlQuery
SELECT * FROM tbl

then you can loop through the query

CFLOOP Query="sqlQuery"
    columnName = sqlQuery.Fieldname
    <cfoutput>Column Header: #columnName#</CFOUTPUT>

Then you can go ahead and display the contents of the query from this information
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 5

Assisted Solution

by:Seth_Bienek
Seth_Bienek earned 20 total points
ID: 11696973

<cfquery> also returns a variable called "columnlist" that you can access to determine what columns were returned in the query, in a list format:

<cfquery name="myquery"...>
  SELECT * FROM mytable
</cfquery>

<table>
  <tr>
    <cfloop list="#myquery.columnlist#" index="i">
    <th>#i#</th>
    </cfloop>
  </tr>
<cfoutput query="myquery">
  <tr>
    <cfloop list="#myquery.columnlist#" index="i">
    <td>#evaluate("myquery.#i#")#</td>
    </cfloop>
  </tr>
</cfoutput>
</table>

Something like that.  

Regards,

Seth
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 11698116
how will you determine that it is a select vs an update/insert/delete?

Are you looking for basically search for the word insert/update/delete and don't run the query?

pay attention to words like drop/alter/create etc.

Is there a reason you would want to open your DB to anyone running a query?

I would look at the DB providing a level of security here.  Either provide a client interface to the users with a username that will just have read permission.

Otherwise use the web driven one with the same premise that the DB user account will just have read permission.

Again, one wrong query can cause big problems to a DB and even cause it to crash.

You may also want to consider putting time limits on execution of any query by the assigned user.

CJ

0
 
LVL 1

Author Comment

by:g118481
ID: 11699008
These are all good suggestions, and perhaps I should have further qualified the DB setup.
I have setup an account for this new web page to use in its query that only has read permission.
Also, this new web page will be used by my management team only.

Currently, I receive an email several times a week from different management individuals within my group, asking me to run these reports from our DB for them.  So, I thought it would be more efficient for them to run them their selves.  Not every request is identical, thus I have to manually adjust my query, run it, then export it to Excel and send it back to them.  This takes at least 4 or 5 hours of my time each week, so I thought it would be a time saver to let them run something for themselves.
0
 
LVL 9

Assisted Solution

by:Jerry_Pang
Jerry_Pang earned 20 total points
ID: 11700266
i made this a few months ago.

<CFHEADER NAME="Content-Disposition" VALUE="inline; filename=results.htm">
<CFCONTENT TYPE="text/html">
<cfset sqlstatement=form.sqlarea>
<cfquery datasource="#form.dns#" name="qry_results" username="#form.username#" password="#form.password#">
#sqlstatement#
</cfquery>
<cfdump var="#qry_results#" label="Results">

then moved this file to a directory with htapassword and access.
very dangerous indeed.

>>how will you determine that it is a select vs an update/insert/delete?
Datasource access in coldfusion, uncheck all query access except select statement
0
 
LVL 19

Assisted Solution

by:cheekycj
cheekycj earned 20 total points
ID: 11704201
>> Datasource access in coldfusion, uncheck all query access except select statement

true, good point.

I have something similar to what Jerry_Pang and Seth posted, Its from one of my old postings on this site:

form.cfm:
<form action=query.cfm>
<textarea name=query>Enter Query Here</textarea>
<input name=submit type=submit>
</form>

query.cfm:

<CFSETTING ENABLECFOUTPUTONLY="yes">
<cfif IsDefined("FORM.query") AND LEN(TRIM(FORM.query)) GT 0>
<cfquery name="getData" datasource="blah">
#FORM.query#
</cfquery>
<cfheader name="content-disposition" value="inline;filename=Query_Results.xls">
<cfcontent type="application/octet-stream"> <!--- This forces a download rather than opening it in the browser --->
<cfoutput>
<table border="0">
     <tr>
     <tr>
     <cfloop index=ColumnName list=#getData.ColumnList#>
       <td>#ColumnName#</td>
     </cfloop>
     </tr>
     <cfloop query=getData>
     <tr>
     <cfloop index=ColumnName list=#getData.ColumnList#>
       <td><cfif LEN(TRIM(Evaluate("getData.#ColumnName#"))) GT 0>#Evaluate("getData.#ColumnName#")#<cfelse>&nbsp;</cfif></td>
     </cfloop>
     </tr>
     </cfloop>
</table>
</cfoutput>

Its dynamic based on columns returned by the query.

HTH,
CJ
0
 
LVL 1

Author Comment

by:g118481
ID: 12005893
Sorry, I have been out of the country.
The allocation above is fine with me.  Thanks
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now