?
Solved

Passing ValueList through CFSET, then pass to second query

Posted on 2008-06-20
9
Medium Priority
?
497 Views
Last Modified: 2010-08-05
-- -- -- -- -- --
Question:
-- -- -- -- -- --

I am doing a query for a list of IDs below in students_list1, passing the valuelist through a CFSET tag, and then doing a second query based on that data.

I had this app working in a local environment (Windows XP, Apache, Access DB) but am now in a shared hosting environment and still using Access DB and I get error below.

I tried putting single quotes around ('#NotRequired#') in both the CFSET and the CFQUERY tags, but I did not get the expected result (that is, no records were shown when there should have been).

All of this code was written years ago for ColdFusion 4.5. Should I be wrapping single quotes or anything similar around much of my values?

Please assist.

-- -- -- -- -- --
ERROR:
-- -- -- -- -- --

Error Executing Database Query.
Syntax error (missing operator) in query expression 'Student_ID NOT IN () and ActiveTraining = 1'.
 
The error occurred in G:\index.cfm: line 88

86 : <CFQUERY NAME="student_list2" DATASOURCE="mydatasource">
87 : SELECT Student_ID, NameFirst, NameLast, FKClassType_ID FROM Students
88 : WHERE Student_ID NOT IN (#NotRequired#) and ActiveTraining = 1
89 : ORDER BY NameLast
90 : </CFQUERY>

-- -- -- -- -- --
CODE:
-- -- -- -- -- --

<h3>No Payments Due</h3>

<CFQUERY NAME="student_list1" DATASOURCE="mydatasource">
SELECT DISTINCT Student_ID, NameFirst, NameLast, PayH_Date, PayH_Amount, FKPayPeriod_ID, FKClassType_ID
FROM Students
LEFT JOIN PayHistory on PayHistory.FKStudent_ID = Students.Student_ID
WHERE PayUp = 0 and ActiveTraining = 1
ORDER BY PayH_Date, NameLast
</CFQUERY>

<CFSET NotRequired = #ValueList(student_list1.Student_ID)#>

<CFQUERY NAME="student_list2" DATASOURCE="mydatasource">
SELECT Student_ID, NameFirst, NameLast, FKClassType_ID FROM Students
WHERE Student_ID NOT IN (#NotRequired#) and ActiveTraining = 1
ORDER BY NameLast
</CFQUERY>

-- -- -- -- -- --
0
Comment
Question by:webdude77
[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
  • 5
  • 3
9 Comments
 
LVL 2

Expert Comment

by:adobe116
ID: 21835601
Hey, I'm pretty sure your code should work.  I'd actually check your select statement.  The error seems to indicate that len(NotRequired) == 0

Can you out put NotRequired before your second cfquery and make sure there is something in it?

The proper thing to do now-a-days is to use a CFQueryParam.  But that's to nicely format your code and make it more tamper-proof.  And once again, you SHOULD see something in your error between the parenthesis. But if you wanted to use it (you should) you would use:

<cfqueryparam value = "#NotRequired#"  CFSQLType = "CF_SQL_INTEGER" list="yes">
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 21838112
adobe found the problem, your NotRequried variable is empty so the resulting syntax is invalid...

where student_id not in () and....

The solution is to test for the existance of a value and change the where clause if it does not exist..

<CFQUERY NAME="student_list2" DATASOURCE="mydatasource">
SELECT Student_ID, NameFirst, NameLast, FKClassType_ID FROM Students
WHERE ActiveTraining = 1
<cfif len(NotRequired)>
  and Student_ID NOT IN (#NotRequired#)
<cfelse>
  and 1=2 -- return nothing
</cfif>
ORDER BY NameLast
</CFQUERY>

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 21838114
if using the cfqueryparam approach, you need to add the NULL parameter...

<cfqueryparam value="#NotRequired#"  null="#NOT len(NotRequired)#" CFSQLType = "CF_SQL_INTEGER" list="yes">

0
Cloud Training Guides

FREE GUIDES: In-depth and hand-crafted Linux, AWS, OpenStack, DevOps, Azure, and Cloud training guides created by Linux Academy instructors and the community.

 

Author Comment

by:webdude77
ID: 21839453
Thanks for the responses.

gdemaria: I must apologize, I've been away from programming and CFML for quite some time. When you say "and 1=2 -- return nothing", could you provide the actual code that I should use? I don't understand what I need to do to this to make it work.

adobe & gdemaria: I have inserted the <cfqueryparam> into the <cfquery> tag as a little research showed that this is where the location of it should be:

<CFQUERY NAME="student_list2" DATASOURCE="mydatasource">
SELECT Student_ID, NameFirst, NameLast, FKClassType_ID FROM Students
WHERE Student_ID NOT IN (<cfqueryparam value="#NotRequired#" null="#NOT len(NotRequired)#" CFSQLType="CF_SQL_INTEGER" list="yes">) and ActiveTraining = 1
ORDER BY NameLast
</CFQUERY>

So, now the above doesn't show any errors. Good, but not great because it should show 150 records.

I have this app running on my local machine with ColdFusion 8, Apache 2.2.9 and Microsoft Access DB (.mdb) and it shows the 150 records without the <cfqueryparam> or any changes to my original code. Now, with this app up on a shared web hosting provider running ColdFusion MX 7, IIS 6.0 (or 7.0, not sure) and Microsoft Access DB (.mdb), I receive the error, and by adding the <cfqueryparam>, no error but the 150 records do not show up.

Any idea what might be causing the discrepancy? Both iterations are using a duplicate .mbd file (so the data is the same).
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 21839557
> could you provide the actual code that I should use?

ah, that was the actual code.

I will add some comments...
<cfif len(NotRequired)>  --  if NotRequired has a value
  and Student_ID NOT IN (#NotRequired#)  --- then use it in the query
<cfelse> ---- if NotRequired is empty, then the query should return nothing
  and 1=2 -- so include a condition that can never be true 1=2, the query will return nothing
</cfif>


Regarding the code running in production and returning nothing - I would look at the data.  It seems the data itself is different and there is nothing populating the variable NotRequired.    If its empty, the query returns nothing, which is what it seems you want?

0
 

Author Comment

by:webdude77
ID: 21839643
The queries were failing because the field ActiveTraining had values of 0 or 1 in my query, but Yes or No in my database. Apparently, on my local setup it did not have a problem with it, but in my shared web hosting server environment, there was a problem.

In my WHERE clause, should I be using parentheses, single quotes, double quotes or anything similar? It looks like the word yes is floating out there and is not contained:

WHERE PayUp = 0 and ActiveTraining = Yes

Also, do you have any recommendations to clean up the query below? I still need the CFSET, right? Is all intact even with addition of the <cfqueryparam> tag? If you could extrapolate as to what the <cfqueryparam> does, it would be helpful. So, here is the latest query:

<CFQUERY NAME="student_list1" DATASOURCE="mydatasource">
SELECT DISTINCT Student_ID, NameFirst, NameLast, PayH_Date, PayH_Amount, FKPayPeriod_ID, FKClassType_ID
FROM Students
LEFT JOIN PayHistory on PayHistory.FKStudent_ID = Students.Student_ID
WHERE PayUp = 0 and ActiveTraining = Yes
ORDER BY PayH_Date, NameLast
</CFQUERY>

<CFSET NotRequired = #ValueList(student_list1.Student_ID)#>

<CFQUERY NAME="student_list2" DATASOURCE="mydatasource">
SELECT Student_ID, NameFirst, NameLast, FKClassType_ID FROM Students
WHERE ActiveTraining = Yes
<cfif len(NotRequired)>
  and Student_ID NOT IN (<cfqueryparam value="#NotRequired#" null="#NOT len(NotRequired)#" CFSQLType="CF_SQL_INTEGER" list="yes">)
<cfelse>
  and 1=2
</cfif>
ORDER BY NameLast
</CFQUERY>
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 21839677
strings in your query need single quotes, so it should be written like this...
   WHERE PayUp = 0 and ActiveTraining = 'Yes'

The cfqueryparam tag creates a bind variable for the database, that makes it more efficient to run each time you process this query as the database doesn't have to rebuild the execution plan each time.  So, a tad faster in the database.   Also, the tag ensures that the value is the type specified by CFSQLType (integer).  This helps prevent the hack called sql injection where someone could push  Student_ID=123;drop table students; on your URL or form submit to try and effect your database.

 I think your query looks fine.

Regards,

0
 

Author Comment

by:webdude77
ID: 21839780
gdemaria: Thanks for your help. Does the sql injection come into play only when the CFSET tag is used? If it is a concern elsewhere, would <cfqueryparam> protect me in other instances? If so, where else can I place them? Most of my queries are similar to the one we have been discussing. Are there other tags that would help to keep my app secure?

On a related note, and I won't ask to much on this as I am about to close this question and award points, but I wanted to put a username and password facade in front of my app and have list of users that have access to various sub-directories on the site. Could you point me in the right direction for this? Is there a prefabricated opensource CFML script available?
0
 
LVL 39

Accepted Solution

by:
gdemaria earned 500 total points
ID: 21841918
> Does the sql injection come into play only when the CFSET tag is used?

the cfset tag is completely unrelated to sql injection or cfqueryparam.  cfqueryparam is used in sql statements rather than saying this...

where user_id = #form.user_id#

because it's possible for the user to edit the browser source and alter the value of the user_id.  they could change it to   123;delete from users;  which would execute this...

where user_id = 123; delete from users;  

That would do the select first, then delete your users table.

It would also prevent it if you did this...
where user_id = #val(form.user_id)#

val() basically converts a string to a number and strips off alpha characters (letters)
cfqueryparam has the added benefit of the binding that I mentioned early for performance.


Username and password doesn't require any particular big module.  It's just a few lines of code here and there.   When the user logs in, set a session variable or a session cookie.   If that cookie or session variable is not set, redirect them to the login page.
The code would look something like this and go in your application.cfm page.  You also create a simple form called login.cfm which asks for username and password..

<cfif isDefined("form.username") and isDefined("form.password")>
   --- somehow validate username and password and get the user's userid
  <cfif xxxxx>  --- if they are valid, set the cookie
      <cfcookie name="userID" value="#userID#">
  </cfif>
</cfif.
<cfif NOT isDefined("cookie.userID")>    ---- the ID of the logged in user
   <cfinclude template="login.cfm">
   <cfabort>
</cfif>


0

Featured Post

Containers & Docker to Create a Powerful Team

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

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…
I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

770 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