Dusty Hale
Thu, 04 Feb 2010 10:13:57 -0800
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 <teddyrpa...@gmail.com>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 - teddyrpa...@gmail.com
>
>
>
> On Wed, Jan 27, 2010 at 4:25 PM, Dusty Hale <du...@climbonline.com> 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 <fcg0...@wctel.net>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
>>> <fcg0...@wctel.net>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 <
>>>>> teddyrpa...@gmail.com <mailto:teddyrpa...@gmail.com>> 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 - teddyrpa...@gmail.com <mailto:teddyrpa...@gmail.com>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> On Wed, Jan 27, 2010 at 2:47 PM, Dusty Hale <du...@climbonline.com
>>>>> <mailto:du...@climbonline.com>> 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
>>>>> <teddyrpa...@gmail.com <mailto:teddyrpa...@gmail.com>> 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 - teddyrpa...@gmail.com
>>>>> <mailto:teddyrpa...@gmail.com>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> On Wed, Jan 27, 2010 at 2:24 PM, Dusty Hale
>>>>> <du...@climbonline.com <mailto:du...@climbonline.com>>
>>>>> 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: du...@dustyhale.com
>>>>> Phone (Atlanta): 404.474.3754
>>>>> Phone (Toll Free USA): 877.841.3370
>>>>> Website: www.DustyHale.com <http://www.DustyHale.com>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Dusty Hale
>>>>> Email: du...@dustyhale.com
>>>>> 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: du...@dustyhale.com
>>> 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: du...@dustyhale.com
>> Phone (Atlanta): 404.474.3754
>> Phone (Toll Free USA): 877.841.3370
>> Website: www.DustyHale.com
>>
>
>
--
Dusty Hale
Email: du...@dustyhale.com
Phone (Atlanta): 404.474.3754
Phone (Toll Free USA): 877.841.3370
Website: www.DustyHale.com