Solved

Passing ValueList through CFSET, then pass to second query

Posted on 2008-06-20
9
484 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
  • 5
  • 3
9 Comments
 
LVL 2

Expert Comment

by:adobe116
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:webdude77
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 39

Expert Comment

by:gdemaria
Comment Utility
> 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
Comment Utility
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
Comment Utility
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
Comment Utility
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 125 total points
Comment Utility
> 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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

771 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

13 Experts available now in Live!

Get 1:1 Help Now