I did end up re doing the entire query in a cfquery tag instead of the connection string. I also used the queryparam tags like below. Seems to have resolved the error and hopefully prevents SQL injection too.
Thanks On Wed, Jan 27, 2010 at 5:30 PM, Teddy R. Payne <[email protected]>wrote: > Dusty, > How about something like this? > > > <cfquery name="q" > datasource="#application.dsn_name#" > username="#application.db_user#" > password="#application.db_pword#"> > > select > [donorid] , > [occupation] , > [race] , > [haircolor] , > [hairtexture] , > [eyecolor] , > [religion] , > [bloodtype] , > [height] , > [weight] , > [heightmetric] , > [weightmetric] , > [reportedpregnancy] , > [opendonorid] , > [infomp3avail] , > [ethnicity] , > [cmvstatus] , > [DateEntered] , > [ARTavail] , > [ARTonly] , > [SelectDonors] > from > [v_websearch] > where > [available] = 1 > <cfif len(drpHairColor)> > and [haircolor] like <cfqueryparam value="#drpHairColor#%" > cfsqltype="cf_sql_varchar" /> > </cfif> > > </cfquery> > > Qualify the columns and the view and query param the search string. This > may also add some legibility as well. > > > > Teddy R. Payne, ACCFD > Google Talk - [email protected] > > > > On Wed, Jan 27, 2010 at 4:25 PM, Dusty Hale <[email protected]> wrote: > >> I also stopped using the <cfquery> tag and now it works: >> >> <cfscript> >> classLoader = createObject("java", "java.lang.Class"); >> classLoader.forName("sun.jdbc.odbc.JdbcOdbcDriver"); >> dm = createObject("java","java.sql.DriverManager"); >> >> con = dm.getConnection("jdbc:odbc:DRIVER={SQL Server};Database=" & >> application.db_name & ";Server=" & application.dbserver_name & ";", >> application.db_user, application.db_pword); >> >> qText = "Select donorid, occupation, race, haircolor, hairtexture, >> eyecolor, religion, bloodtype, height, weight, heightmetric, weightmetric, >> reportedpregnancy, opendonorid, infomp3avail, ethnicity, cmvstatus, >> DateEntered, ARTavail, ARTonly, SelectDonors FROM v_websearch where >> available = 1 "; >> if(len(drpHairColor)){ >> qText = qText & "AND haircolor like '" & drpHairColor & "%' "; >> } >> if(len(drpEyeColor)){ >> qText = qText & "AND eyecolor like '" & drpEyeColor & "%' "; >> } >> if(len(drpEthnic)){ >> qText = qText & "AND ethnicity like '" & drpEthnic & "%' "; >> >> } >> if(len(txtDonorId)){ >> qText = qText & "AND donorid like '%" & txtDonorId & "%' "; >> } >> //if(chkAudioFile){ >> //qText = qText & "AND InfoMP3Avail = 1 "; >> //} >> if(chkOpenId){ >> qText = qText & "AND OpenDonorID = 1 "; >> } >> if(chkCanadian){ >> qText = qText & "AND CanadianCompliantAvail = 1 "; >> } >> if(len(drpRace)){ >> qText = qText & "AND race like '" & drpRace & "%' "; >> } >> if(len(drpReligion)){ >> qText = qText & "AND religion like '" & drpReligion & "%' "; >> } >> if(len(drpBloodType)){ >> qText = qText & "AND bloodtype like '" & drpBloodType & "%' "; >> } >> if(len(drpHeight)){ >> if(drpHeight eq "1-66"){ >> qText = qText & "AND heightregular < 507 "; >> } >> if(drpHeight eq "67-71"){ >> qText = qText & "AND heightregular < 512 AND heightregular > >> 507 "; >> } >> if(drpHeight eq "72-76"){ >> qText = qText & "AND heightregular < 605 AND heightregular > >> 512 "; >> } >> if(drpHeight eq "77-100"){ >> qText = qText & "AND heightregular > 604 "; >> } >> } >> if(IsNumeric(txtWeight1) AND IsNumeric(txtWeight2)){ >> if(chkWeight is "kg"){ >> qText = qText & " AND weightmetric <= " & txtWeight2 & " AND >> weightmetric >= " & txtWeight1 & " "; >> } >> else{ >> qText = qText & " AND weight <= " & txtWeight2 & " AND weight >> >= " & txtWeight1 & " "; >> } >> } >> if(len(drpCMVstatus)){ >> qText = qText & "AND cmvstatus like '" & drpCMVstatus & "%' "; >> } >> if(chkART){ >> qText = qText & "AND ARTavail IS NOT NULL "; >> } >> if(chkSelect){ >> qText = qText & "AND SelectDonors IS NOT NULL "; >> } >> qText = qText & "ORDER BY donorid"; >> st = con.createStatement(); >> rs = st.ExecuteQuery(qText); >> q = createObject("java", "coldfusion.sql.QueryTable").init(rs); >> >> stRecordCount = con.createStatement(); >> rs2 = stRecordCount.ExecuteQuery("Select count(*) AS MyCount FROM >> tbldonors where available = 1"); >> qRecordCount = createObject("java", >> "coldfusion.sql.QueryTable").init(rs2); >> </cfscript> >> >> >> On Wed, Jan 27, 2010 at 4:18 PM, Forrest C. Gilmore <[email protected]>wrote: >> >>> Can you show me the exact code you used when you tried >>> PreserveSingleQuotes? >>> According to the documentation, "This function is useful in SQL >>> statements to defer evaluation of a variable reference until runtime. " >>> Seems to be just what you need, but it must be used properly. >>> >>> *Example B*: Consider this code: >>> >>> <cfset list0 = " '1','2''3' "> >>> >>> <cfquery sql = "select * from foo where bar in (#list0#)"> >>> >>> ColdFusion escapes the single-quote characters in the list as follows: >>> >>> ""1"", ""2"", ""3"" >>> >>> The cfquery tag throws an error. >>> >>> You code this function correctly as follows: >>> >>> <cfquery sql = "select * from foo where bar in >>> (#preserveSingleQuotes(list0)#)"> >>> >>> This function ensures that ColdFusion evaluates the code as follows: >>> >>> '1', '2', '3' >>> -------------------------------------- >>> >>> Forrest C. Gilmore >>> =========================== >>> Dusty Hale wrote: >>> >>> Just to rule it out though I tried it. Didn't help though. >>> >>> >>> On Wed, Jan 27, 2010 at 3:36 PM, Forrest C. Gilmore >>> <[email protected]>wrote: >>> >>>> Go into CF Help and look up the Preservesinglequotes function. >>>> >>>> Forrest C. Gilmore >>>> ======================== >>>> Dusty Hale wrote: >>>> >>>>> Yep that runs fine. >>>>> >>>>> This is really strange. The value of the qText variable is the same as >>>>> the query below yet when I stuff #qText# in there, it somehow doubles the >>>>> quotes by itself. Damn I just don't get how it could or would do that. >>>>> >>>>> If I output the value of the qText variable on a page, quotes are >>>>> correct. Have you ever seen anything like this? >>>>> >>>>> Thanks, >>>>> Dusty >>>>> >>>>> On Wed, Jan 27, 2010 at 3:00 PM, Teddy R. Payne < >>>>> [email protected] <mailto:[email protected]>> wrote: >>>>> >>>>> Dusty, >>>>> As a source of troubleshooting, have you put the SQL string into >>>>> the cfquery statement in its final form? I am wondering if the >>>>> SQL runs correctly in the cfquery before the dynamic evaluation. >>>>> >>>>> <cfquery name="q" datasource="#application.dsn_name#" >>>>> username="#application.db_user#" password="#application.db_ >>>>> >>>>> pword#"> >>>>> Select donorid, occupation, race, haircolor, hairtexture, >>>>> eyecolor, religion, bloodtype, height, weight, heightmetric, >>>>> weightmetric, reportedpregnancy, opendonorid, infomp3avail, >>>>> ethnicity, cmvstatus, DateEntered, ARTavail, ARTonly, SelectDonors >>>>> FROM v_websearch where available = 1 AND donorid like '%9986%' >>>>> ORDER BY donorid >>>>> </cfquery> >>>>> >>>>> Does this run correctly? >>>>> >>>>> >>>>> >>>>> Teddy R. Payne, ACCFD >>>>> Google Talk - [email protected] <mailto:[email protected]> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> On Wed, Jan 27, 2010 at 2:47 PM, Dusty Hale <[email protected] >>>>> <mailto:[email protected]>> wrote: >>>>> >>>>> Teddy here's how I build the qText string part where the >>>>> quotes are: >>>>> >>>>> if(len(txtDonorId)){ >>>>> qText = qText & "AND donorid like '%" & txtDonorId & >>>>> "%' "; >>>>> } >>>>> >>>>> >>>>> >>>>> On Wed, Jan 27, 2010 at 2:28 PM, Teddy R. Payne >>>>> <[email protected] <mailto:[email protected]>> wrote: >>>>> >>>>> Dusty, >>>>> What type of single quotes are those? What is the source >>>>> of the text? Was the query copied and pasted from a >>>>> Microsoft document? >>>>> >>>>> >>>>> Teddy R. Payne, ACCFD >>>>> Google Talk - [email protected] >>>>> <mailto:[email protected]> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> On Wed, Jan 27, 2010 at 2:24 PM, Dusty Hale >>>>> <[email protected] <mailto:[email protected]>> >>>>> wrote: >>>>> >>>>> Hi: >>>>> >>>>> I've run into a very strange issue. I have a cfc which >>>>> has a <cfquery> tag in it. I recently added one field >>>>> the SQL in the query and am getting an error I've >>>>> never seen before. I can't seem to dig out any info to >>>>> solve this. If anyone is familiar, please share. >>>>> >>>>> Of course when I output the SQL and run in a SQL >>>>> Studio Query window, the query runs fine with no errors. >>>>> >>>>> Here the error I see in CF: >>>>> >>>>> >>>>> >>>>> >>>>> ----------------------------------------------------------------------------------------------------------------------------- >>>>> [Macromedia][SQLServer JDBC Driver][SQLServer]Divide >>>>> by zero error encountered. >>>>> >>>>> The error occurred in >>>>> *D:\websites\xytexcom_stage2\htdocs\cfc\donorsearch.cfc: >>>>> line 149* >>>>> *Called from* >>>>> D:\websites\xytexcom_stage2\htdocs\cfc\donorsearch.cfc: >>>>> line 139 >>>>> *Called from* >>>>> D:\websites\xytexcom_stage2\htdocs\search.cfm: line 48 >>>>> *Called from* >>>>> D:\websites\xytexcom_stage2\htdocs\cfc\donorsearch.cfc: >>>>> line 149 >>>>> *Called from* >>>>> D:\websites\xytexcom_stage2\htdocs\cfc\donorsearch.cfc: >>>>> line 139 >>>>> *Called from* >>>>> D:\websites\xytexcom_stage2\htdocs\search.cfm: line 48 >>>>> >>>>> 147 : </cfscript> >>>>> 148 : <cfquery name="q" >>>>> datasource="#application.dsn_name#" username="#application.db_user#" >>>>> password="#application.db_pword#"> >>>>> *149 : #qText#* >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> 150 : </cfquery> >>>>> ----------------------------------------- >>>>> >>>>> >>>>> Here's the SQL code in the qText variable. Please note >>>>> that it runs fun in Query Analyzer. Also note that no >>>>> division is being used. >>>>> >>>>> >>>>> ---------------------------------------------------------------- >>>>> Select donorid, occupation, race, haircolor, >>>>> hairtexture, eyecolor, religion, bloodtype, height, >>>>> weight, heightmetric, weightmetric, reportedpregnancy, >>>>> opendonorid, infomp3avail, ethnicity, cmvstatus, >>>>> DateEntered, ARTavail, ARTonly, SelectDonors FROM >>>>> v_websearch where available = 1 AND donorid like >>>>> '%9986%' ORDER BY donorid >>>>> >>>>> >>>>> ---------------------------------------------------------------------------------- >>>>> >>>>> Any advise or thought on this of course is greatly >>>>> appreciated. >>>>> >>>>> Dusty >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> -- Dusty Hale >>>>> Email: [email protected] >>>>> Phone (Atlanta): 404.474.3754 >>>>> Phone (Toll Free USA): 877.841.3370 >>>>> Website: www.DustyHale.com <http://www.DustyHale.com> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> -- >>>>> Dusty Hale >>>>> Email: [email protected] >>>>> Phone (Atlanta): 404.474.3754 >>>>> Phone (Toll Free USA): 877.841.3370 >>>>> Website: www.DustyHale.com <http://www.DustyHale.com> >>>>> >>>> >>>> >>>> >>>> >>>> ------------------------------------------------------------- >>>> To unsubscribe from this list, manage your profile @ >>>> http://www.acfug.org?fa=login.edituserform >>>> >>>> For more info, see http://www.acfug.org/mailinglists >>>> Archive @ http://www.mail-archive.com/discussion%40acfug.org/ >>>> List hosted by http://www.fusionlink.com >>>> ------------------------------------------------------------- >>>> >>>> >>>> >>>> >>> >>> >>> -- >>> Dusty Hale >>> Email: [email protected] >>> Phone (Atlanta): 404.474.3754 >>> Phone (Toll Free USA): 877.841.3370 >>> Website: www.DustyHale.com >>> >>> >>> >>> ------------------------------------------------------------- >>> To unsubscribe from this list, manage your profile @ >>> http://www.acfug.org?fa=login.edituserform >>> >>> For more info, see http://www.acfug.org/mailinglists >>> Archive @ http://www.mail-archive.com/discussion%40acfug.org/ >>> List hosted by FusionLink <http://www.fusionlink.com> >>> ------------------------------------------------------------- >> >> >> >> >> -- >> Dusty Hale >> Email: [email protected] >> Phone (Atlanta): 404.474.3754 >> Phone (Toll Free USA): 877.841.3370 >> Website: www.DustyHale.com >> > > -- Dusty Hale Email: [email protected] Phone (Atlanta): 404.474.3754 Phone (Toll Free USA): 877.841.3370 Website: www.DustyHale.com
