?
Solved

Just a syntax question: building a query statement based on user's selections

Posted on 2003-03-11
23
Medium Priority
?
264 Views
Last Modified: 2013-12-24
Hello all,

I am trying to dynamically build an SQL query based on what my user selects on a web form. I'm using the following  approach:

<CFSET strSQL = "Select attribute_a, attribute_b, from table_a where">
<CFIF user wants student_id info>
     <CFSET strSQL = strSQL & "student_id = '#student_ID#'">
</CFIF>
<CFIF user wants something else -- (here's where my problem is)>
     <CFSET strSQL = strSQL & "student_major = '" & #user's_selection_from_form# & "'">
     
I've seen this done plenty of times in CF and ASP, but for some reason I'm getting an SQL syntax error where my #user's_selection_from_form# is referenced. The CF error page that is returned displays the output of my query string as sent to SQL (MS-SQL 2000, btw) with the student major correctly displayed, but it appears to be inside two sets of single quotes. I know SQL wants those items to be enclosed in single quotes, so as you can see I entered them and closed the string before referencing the form variable, but it's not working. Am I missing a required trick for escaping the quotes?

Thank you...
0
Comment
Question by:natG
[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
  • 11
  • 5
  • 2
  • +4
23 Comments
 
LVL 9

Expert Comment

by:HamdyHassan
ID: 8113404
why you have ' at #user's_selection_from_form#  ?

change this variable to #users_selection_from_form#


0
 
LVL 10

Expert Comment

by:substand
ID: 8113493
just make it single quotes, don't worry about escaping and all that. if there are possibly going to be single quotes in the form variable, then use #preservesinglequotes(form.variable)#

0
 
LVL 8

Expert Comment

by:TallerMike
ID: 8113596
Beyond all that, creating a SQL string is rather awkward. You can do this instead:

<cfquery>
  SELECT attribute_a, attribute_b
  FROM table_a
  WHERE
<CFIF user wants student_id info>student_id = '#student_ID#'</CFIF>
<CFIF user wants something else -- (here's where my problem is)>
    student_major = '#preservesinglequotes(form.variable)#'
</cfquery>
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 11

Expert Comment

by:hart
ID: 8117096
Tallermike is correct, but just a suggestion.

say if the user selects more than one options then you will have to form your query more apropriately.

that is say user wants both student_id info student_major then you will have to combine cfif's

<CFSET EXTRACONDITION = "">
<CFSET S_SQL== "">

<CFIF user wants student_id info>
   <CFSET S_SQL = "student_id = '" & #student_ID# & "'">
   <CFSET EXTRACONDITION = "AND">
</CFIF>

<CFIF user wants something else -- (here's where my problem is)>
     <CFSET S_SQL = #S_SQL# & #EXTRACONDITION#  & "student_major = '" & #PRESERVESINGLEQUOTES(form.variable)# & "'">
</CFIF>

<cfquery>
 SELECT attribute_a, attribute_b
 FROM table_a
 WHERE #PRESERVESINGLEQUOTES(S_SQL)#
</cfquery>

Regards
Hart(Harish)
0
 

Expert Comment

by:plegg
ID: 8119193
What if the user doesn't select anything?  It should be like this:

<cfquery>
 SELECT attribute_a, attribute_b
 FROM table_a
 WHERE 0=0
<CFIF user wants student_id info>AND student_id = '#student_ID#'</CFIF>
<CFIF user wants something else -- (here's where my problem is)>AND student_major = '#preservesinglequotes(form.variable)#'
</cfquery>

Why use single quotes in the student_major form field at all?  If it's not a multiple select, there's really no reason to do this.  So if that's the case, get rid of the preservesinglequotes above.
0
 

Expert Comment

by:plegg
ID: 8119208
Forgot something...  Isn't student_id an integer field?  If not, it should be and the single quotes should be removed.  Also, is there any form validation that prevents the user from NOT selecting anything?  If so, the query I submitted above will work but wouldn't be necessary.
0
 
LVL 1

Expert Comment

by:ericm123
ID: 8122315
Simple :

1/ the way you build your query is OK. (don't forget a space at the end of each concatenation, to avoir sticking words together)

2/ to send the query, just do :

  <cfquery name="..." datasource="...">
    #PreserveSingleQuotes(strSQL)#
  </cfquery>

This is due to the fact that CF will double the ' when expressing a variable :

IF variable "a" contains "it's cool"

THEN "#a#" will produce "it''s cool" (only inside the cfquery tag, of course).

This is very usefull : this is why you don't have to worry about double-quoting usualy...

E.
0
 
LVL 8

Expert Comment

by:TallerMike
ID: 8160864
Any luck on this question natG?
0
 

Author Comment

by:natG
ID: 8217875
TallerMike et al,

This question has become an issue again. I was doing it by embedding CFIFs inside my query tag like you suggested on 3/11, and it was working fine. However, now I realize I need to save this query string as text in a db field so that users can choose default querys (filtering options on a list of students) for their individual needs. And when they sort the list by clicking on the column heads the page reloads, so having the query string as a var that I can pass around seems like the way to go.

Anyway, I am still having some syntax problems-- There is a CFLOOP insided the select statement that dynamically adds columns to the query, and the output(column names) has the index of the loop appended to them to give them unique names. So, I have coldfusion tags and variables inside the string that shouldn't be evaluated until the query is actually run; I think I need to use the DE() and / or evaluate() functions, but I'm still getting various errors.

I don't have the code with me at the moment. I could post it tonight. In the meantime, if anyone has any examples they could post I'd appreciate it.
0
 

Author Comment

by:natG
ID: 8221890
OK, here is the latest version of the code. I've tried multiple variations and I get a different error every time. I have part of it commented out just to try to tackle one problem at a time. The commented-out part, inside the cfloop, was throwing a different error- CF was trying to evaluate the i at the time I set the var, and not later. DE() did not seem to work, so I commented it out and found myself with another problem. Here it is:


<cfset sql = DE('Select     Student.Student_ID,
                    <cfloop index = i from = 1 to = #getprereqs.RecordCount#>
                    (Select Prereq_Status from PetitionPrereqState
                              where PetitionPrereqState.Student_ID = Student.Student_ID
                              and PetitionPrereqState.Prereq_ID = 1) as Prereq_Status1,
          <!---               and PetitionPrereqState.Prereq_ID = '&chr(35)&'i'&chr(35)&') as Prereq_Status'&#chr(35)#&'i'&#chr(35)#&',     --->
          <!---               and PetitionPrereqState.Prereq_ID = '&chr(35)&'i'&chr(35)&') as Prereq_Status'&#chr(35)#&'i'&#chr(35)#&',     --->
                    </cfloop>

                    Student_Lname, Student_Fname, Student_MidInit, Petition.Petition_ID,
                    Student_Major, Student_GPA, Pref_Class_Section,
                    (Select TOP 1 Status_name from StatusHistory
                                                            where Petition.Petition_ID = StatusHistory.Petition_ID
                                                            order by     Status_date desc) as Pet_Status

                    from Student, Petition
                    where Student.Student_ID = Petition.Student_ID
                    and Petition.Course_ID = #SESSION.DEFAULTCOURSE#')>
     <!---
<cfif state is "2">
     <cfif #form.firstchoice# is "1">
               and DispCode = '#form.secondChoice#'   change next line to else if!
          <cfif #form.firstchoice# is "2">
               <cfset sql = sql & ' and Student.Student_GPA = '#form.secondChoice#''>
          <cfelseif #form.firstchoice# is "3">
               <cfset sql = sql & " and Student.Student_Major = '#form.secondChoice#'">
          </cfif>
</cfif> --->
                   
<cfset sql = sql & '     order by #sort#  #sortorder#'>

<cfquery      name="getstudents"
                    DATASOURCE="#ODBC_SOURCE#"  
                    username="#ODBC_USERID#"
                    password= "#ODBC_PASSWD#">

                    #PreserveSingleQuotes(sql)#

                                   
</CFQUERY>




The error (this time) is:
"[Macromedia][SQLServer JDBC Driver][SQLServer]The identifier that starts with 'Select Student.Student_ID, (Select Prereq_Status from PetitionPrereqState where' is too long. Maximum length is 128."


Anyone who could throw me a bone here would be much appreciated.
Thanks in advance.

NatG


Thanks in advance.
0
 
LVL 10

Expert Comment

by:substand
ID: 8221937
you need to increase the size of your column in your db structure, or change the datatype to something bigger.
0
 

Author Comment

by:natG
ID: 8222040
I don't understand-- the size of what column? This is a query, I am not adding or updating to the db. Can you elaborate?
0
 

Author Comment

by:natG
ID: 8222102
I don't understand-- the size of what column? This is a query, I am not adding or updating to the db. Can you elaborate?
0
 
LVL 8

Accepted Solution

by:
TallerMike earned 300 total points
ID: 8222174
Do this:

<cfoutput>#PreserveSingleQuotes(sql)#</cfoutput>

And paste the results here. I want to see the query that's producing the error. It sounds like there is a missing single quote somewhere...
0
 

Author Comment

by:natG
ID: 8222269
OK, the code has evolved a little as I am trying different things. Here it is as it looks now:

<cfset sql ='Select     Student.Student_ID,
                    #chr(60)#cfloop index = i from = 1 to = #getprereqs.RecordCount##chr(62)#
                    (Select Prereq_Status from PetitionPrereqState
                              where PetitionPrereqState.Student_ID = Student.Student_ID
                              and PetitionPrereqState.Prereq_ID = 1) as Prereq_Status1,
                    #chr(60)##chr(47)#cfloop#chr(62)#

                    Student_Lname, Student_Fname, Student_MidInit, Petition.Petition_ID,
                    Student_Major, Student_GPA, Pref_Class_Section,
                    (Select TOP 1 Status_name from StatusHistory
                                                            where Petition.Petition_ID = StatusHistory.Petition_ID
                                                            order by     Status_date desc) as Pet_Status

                    from Student, Petition
                    where Student.Student_ID = Petition.Student_ID
                    and Petition.Course_ID = #SESSION.DEFAULTCOURSE#'>
     <!---
<cfif state is "2">
     <cfif #form.firstchoice# is "1">
               and DispCode = '#form.secondChoice#'   change next line to else if!
          <cfif #form.firstchoice# is "2">
               <cfset sql = sql & ' and Student.Student_GPA = '#form.secondChoice#''>
          <cfelseif #form.firstchoice# is "3">
               <cfset sql = sql & " and Student.Student_Major = '#form.secondChoice#'">
          </cfif>
</cfif> --->
                   
<cfset sql = sql & '     order by #sort#  #sortorder#'>


<CFOUTPUT> #PreserveSingleQuotes(sql)# </cfoutput>

<cfquery      name="getstudents"
                    DATASOURCE="#ODBC_SOURCE#"  
                    username="#ODBC_USERID#"
                    password= "#ODBC_PASSWD#">

                    #sql#



                                   
</CFQUERY>



Here is the cfoutput result:

Select Student.Student_ID, (Select Prereq_Status from PetitionPrereqState where PetitionPrereqState.Student_ID = Student.Student_ID and PetitionPrereqState.Prereq_ID = 1) as Prereq_Status1, Student_Lname, Student_Fname, Student_MidInit, Petition.Petition_ID, Student_Major, Student_GPA, Pref_Class_Section, (Select TOP 1 Status_name from StatusHistory where Petition.Petition_ID = StatusHistory.Petition_ID order by Status_date desc) as Pet_Status from Student, Petition where Student.Student_ID = Petition.Student_ID and Petition.Course_ID = 1 order by Student_Lname


And, on the same page right below the output is the error that says:

Error Executing Database Query.  
[Macromedia][SQLServer JDBC Driver][SQLServer]Line 2: Incorrect syntax near '<'.  
 
Then it shows the SQL string as it was sent to the DB, which reads:

Select Student.Student_ID, (Select Prereq_Status from PetitionPrereqState where PetitionPrereqState.Student_ID = Student.Student_ID and PetitionPrereqState.Prereq_ID = 1) as Prereq_Status1, Student_Lname, Student_Fname, Student_MidInit, Petition.Petition_ID, Student_Major, Student_GPA, Pref_Class_Section, (Select TOP 1 Status_name from StatusHistory where Petition.Petition_ID = StatusHistory.Petition_ID order by Status_date desc) as Pet_Status from Student, Petition where Student.Student_ID = Petition.Student_ID and Petition.Course_ID = 1 order by Student_Lname  


Now, how weird is it that there is no open anglebracket (<) anywhere in the output of the SQL (indeed I escaped them for the cfloop) but the error refers to one....



0
 

Author Comment

by:natG
ID: 8222307
Here's another bit of info:  In the code I pasted above, I replaced the index i in the cfloop with just a 1. If I actually include the i like this:

<cfset sql ='Select     Student.Student_ID,
                    #chr(60)#cfloop index = i from = 1 to = #getprereqs.RecordCount##chr(62)#
                    (Select Prereq_Status from PetitionPrereqState
                              where PetitionPrereqState.Student_ID = Student.Student_ID
                              and PetitionPrereqState.Prereq_ID = #chr(35)#i#chr(35)#) as Prereq_Status#chr(35)#i#chr(35)#,
                    #chr(60)##chr(47)#cfloop#chr(62)#

                    Student_Lname, Student_Fname, Student_MidInit, Petition.Petition_ID,
                    Student_Major, Student_GPA, Pref_Class_Section,
                    (Select TOP 1 Status_name from StatusHistory
                                                            where Petition.Petition_ID = StatusHistory.Petition_ID
                                                            order by     Status_date desc) as Pet_Status

                    from Student, Petition
                    where Student.Student_ID = Petition.Student_ID
                    and Petition.Course_ID = #SESSION.DEFAULTCOURSE#'>


...then the output and error message on the screen includes the pound signs, even though the <cfloop> is NOT included (nor are the <'s).


....
0
 

Author Comment

by:natG
ID: 8222311
... and here is the output:

Select Student.Student_ID, (Select Prereq_Status from PetitionPrereqState where PetitionPrereqState.Student_ID = Student.Student_ID and PetitionPrereqState.Prereq_ID = #i#) as Prereq_Status#i#, Student_Lname, Student_Fname, Student_MidInit, Petition.Petition_ID, Student_Major, Student_GPA, Pref_Class_Section, (Select TOP 1 Status_name from StatusHistory where Petition.Petition_ID = StatusHistory.Petition_ID order by Status_date desc) as Pet_Status from Student, Petition where Student.Student_ID = Petition.Student_ID and Petition.Course_ID = 1 order by Student_Lname



0
 
LVL 8

Expert Comment

by:TallerMike
ID: 8222341
I don't think you can set a variable with a string that has line breaks in it. I don't think the following is valid:

<cfset thisVariable = "This is the first line.
   And this is the second">

You'll either need to do this:

<cfscript>
  thisVariable = "This is the first line.";
  thisVariable = thisVariable & "And this is the second";
</cfscript>

Or you'll need to create a custom tag to do something like this:

<cf_SuperVariable variableName="thisVariable">
  <cfoutput>
    This is the first line.
    And this is the second
  </cfoutput>
</cf_SuperVariable>

Where cf_SuperVariable is going to take thiTag.generatedContent and put it into the variableName attribute which you can then access later like so:

<cfoutput>#thisVariable#</cfoutput>

************************************************************************************************************************
************************************************************************************************************************

The reason you're getting errors about a '>' is that you're missing a single quote somewhere, so it is interpreting some of your code as a string. You'll need to find this extra single quote.
0
 

Author Comment

by:natG
ID: 8222364
OK, thanks-- I'll look for it and get back to you....

0
 

Author Comment

by:natG
ID: 8222472
... and here is the output:

Select Student.Student_ID, (Select Prereq_Status from PetitionPrereqState where PetitionPrereqState.Student_ID = Student.Student_ID and PetitionPrereqState.Prereq_ID = #i#) as Prereq_Status#i#, Student_Lname, Student_Fname, Student_MidInit, Petition.Petition_ID, Student_Major, Student_GPA, Pref_Class_Section, (Select TOP 1 Status_name from StatusHistory where Petition.Petition_ID = StatusHistory.Petition_ID order by Status_date desc) as Pet_Status from Student, Petition where Student.Student_ID = Petition.Student_ID and Petition.Course_ID = 1 order by Student_Lname



0
 

Author Comment

by:natG
ID: 8222490
I think I have solved it. The problem was in the cfloop being inside a string and then evaluated along with the variables inside it. I changed the code to loop OUTSIDE the cfset, and put the cfset inside the loop, and it works like a charm:

<!--- Begin building Query string --->
<cfset sql ='Select     Student.Student_ID, '>
<cfloop index = i from = 1 to = #getprereqs.RecordCount#>
     <cfset sql = sql & 
          '(Select Prereq_Status from PetitionPrereqState
                    where PetitionPrereqState.Student_ID = Student.Student_ID
                    and PetitionPrereqState.Prereq_ID = #i#) as Prereq_Status#i#, '>
</cfloop>

<cfset sql = sql & ' Student_Lname, Student_Fname, Student_MidInit, Petition.Petition_ID,
                    Student_Major, Student_GPA, Pref_Class_Section,
                    (Select TOP 1 Status_name from StatusHistory
                                                            where Petition.Petition_ID = StatusHistory.Petition_ID
                                                            order by     Status_date desc) as Pet_Status

                    from Student, Petition
                    where Student.Student_ID = Petition.Student_ID
                    and Petition.Course_ID = #SESSION.DEFAULTCOURSE#'>

<cfif state is "2">
               <cfif #form.firstchoice# is "1">
                    <cfset sql = sql & " and DispCode = '#form.secondChoice#' ">
               <cfelseif #form.firstchoice# is "2">
                    <cfset sql = sql & " and Student.Student_GPA = '#form.secondChoice#' ">
               <cfelseif #form.firstchoice# is "3">
                    <cfset sql = sql & " and Student.Student_Major = '#form.secondChoice#' ">
               </cfif>
</cfif>

                   
<cfset sql = sql & '     order by #sort#  #sortorder#'>
<!--- end building query string--->

<cfquery      name="getstudents"
                    DATASOURCE="#ODBC_SOURCE#"  
                    username="#ODBC_USERID#"
                    password= "#ODBC_PASSWD#">

                    #PreserveSingleQuotes(sql)#
</CFQUERY>




I hope somebody else comes along and benefits from my hours of frustration, because this seems like a common challenge. I imagine I will run into this kind of thing often.

Mike, thanks for your help and quick responses.

NatG
0
 

Author Comment

by:natG
ID: 8222506
SOLUTION:

The problem was in the cfloop being inside a cfset command, and then evaluated along with the variables inside it. I changed the code to loop OUTSIDE the cfset, and put the cfset inside the loop, and it works like a charm:

<!--- Begin building Query string --->
<cfset sql ='Select     Student.Student_ID, '>
<cfloop index = i from = 1 to = #getprereqs.RecordCount#>
    <cfset sql = sql & 
         '(Select Prereq_Status from PetitionPrereqState
                   where PetitionPrereqState.Student_ID = Student.Student_ID
                   and PetitionPrereqState.Prereq_ID = #i#) as Prereq_Status#i#, '>
</cfloop>

<cfset sql = sql & ' Student_Lname, Student_Fname, Student_MidInit, Petition.Petition_ID,
                   Student_Major, Student_GPA, Pref_Class_Section,
                   (Select TOP 1 Status_name from StatusHistory
                                                           where Petition.Petition_ID = StatusHistory.Petition_ID
                                                           order by     Status_date desc) as Pet_Status

                   from Student, Petition
                   where Student.Student_ID = Petition.Student_ID
                   and Petition.Course_ID = #SESSION.DEFAULTCOURSE#'>

<cfif state is "2">
              <cfif #form.firstchoice# is "1">
                   <cfset sql = sql & " and DispCode = '#form.secondChoice#' ">
              <cfelseif #form.firstchoice# is "2">
                   <cfset sql = sql & " and Student.Student_GPA = '#form.secondChoice#' ">
              <cfelseif #form.firstchoice# is "3">
                   <cfset sql = sql & " and Student.Student_Major = '#form.secondChoice#' ">
              </cfif>
</cfif>

                   
<cfset sql = sql & '     order by #sort#  #sortorder#'>
<!--- end building query string--->

<cfquery      name="getstudents"
                   DATASOURCE="#ODBC_SOURCE#"  
                   username="#ODBC_USERID#"
                   password= "#ODBC_PASSWD#">

                   #PreserveSingleQuotes(sql)#
</CFQUERY>
0
 
LVL 8

Expert Comment

by:TallerMike
ID: 8224765
Well, glad you solved it at least. =) Wish we could have helped more!
0

Featured Post

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
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…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

764 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