Re: [ACFUG Discuss] a very strange sql error that only happens when using CF
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.comwrote:
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.netwrote:
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
Re: [ACFUG Discuss] a very strange sql error that only happens when using CF
Yea that's a good point Dean. Will cfqueryparam tags solve that? Also if
using a connection string approach as opposed to a cfquery tag, what's the
best way to deal with SQLInjection?
I know what SQLinjection is and I'm sure I should probably be more concerned
with it that I have been in the past. Thanks for pointing that out and I'm
going to make it a point to do better in that area.
Dusty
On Wed, Jan 27, 2010 at 6:29 PM, Dean H. Saxe
d...@fullfrontalnerdity.comwrote:
D'oh! Anyone else see the classic SQLinjection vuln here...
--
Dean H. Saxe
A true conservationist is a person who knows that the world is not
given by his fathers, but borrowed from his children. -- John James
Audubon
On Wed, Jan 27, 2010 at 11:47 AM, Dusty Hale 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
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
On Wed, Jan 27, 2010 at 2:24 PM, Dusty Hale 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
-
To unsubscribe from this list, manage your profile @
http://www.acfug.org?falogin.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
-
Re: [ACFUG Discuss] a very strange sql error that only happens when using CF
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 On Wed, Jan 27, 2010 at 2:24 PM, Dusty Hale 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
Re: [ACFUG Discuss] a very strange sql error that only happens when using CF
Looking out on the internet, I came across an interesting article that might point to the issue - take a look at the surrounding threads on here for further info - not sure this will be the solution - just an ideasounds the very same as what you're seeing. Thanks, Craig Nassal Federal Reserve Bank of Atlanta 1000 Peachtree St. N.E. Atlanta, Ga 30309-4470 (404)498-8437 craig.nas...@atl.frb.org Dusty Hale du...@climbonline.com Sent by: ad...@acfug.org 01/27/10 02:25 PM Please respond to discussion@acfug.org To discussion@acfug.org cc Subject [ACFUG Discuss] a very strange sql error that only happens when using CF 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 - 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 -
Re: [ACFUG Discuss] a very strange sql error that only happens when using CF
No I just basically output the query before it gets to the cfquery tag. That outputs it on the page instead of running the query in CF. Then I copied the query into SQL Studio and it runs fine. However when I run the same query in a cfquery tag I get the message. No division is being used. The single quotes are just straight ASCII text. No evil MS Word. On Wed, Jan 27, 2010 at 2:28 PM, Teddy R. Payne teddyrpa...@gmail.comwrote: 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 On Wed, Jan 27, 2010 at 2:24 PM, Dusty Hale 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
Re: [ACFUG Discuss] a very strange sql error that only happens when using CF
Craig do you have a link to that thread? Many thanks, Dusty On Wed, Jan 27, 2010 at 2:38 PM, craig.nas...@atl.frb.org wrote: Looking out on the internet, I came across an interesting article that might point to the issue - take a look at the surrounding threads on here for further info - not sure this will be the solution - just an ideasounds the very same as what you're seeing. Thanks, Craig Nassal Federal Reserve Bank of Atlanta 1000 Peachtree St. N.E. Atlanta, Ga 30309-4470 (404)498-8437 craig.nas...@atl.frb.org Dusty Hale du...@climbonline.com Sent by: ad...@acfug.org 01/27/10 02:25 PM Please respond to discussion@acfug.org To discussion@acfug.org cc Subject [ACFUG Discuss] a very strange sql error that only happens when using CF 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 - 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
Re: [ACFUG Discuss] a very strange sql error that only happens when using CF
Sorry - thought I put it in my e-mail: http://www.mail-archive.com/cf-t...@houseoffusion.com/msg214188.html Craig Nassal Federal Reserve Bank of Atlanta 1000 Peachtree St. N.E. Atlanta, Ga 30309-4470 (404)498-8437 craig.nas...@atl.frb.org Dusty Hale du...@climbonline.com Sent by: ad...@acfug.org 01/27/10 02:44 PM Please respond to discussion@acfug.org To discussion@acfug.org cc Subject Re: [ACFUG Discuss] a very strange sql error that only happens when using CF Craig do you have a link to that thread? Many thanks, Dusty On Wed, Jan 27, 2010 at 2:38 PM, craig.nas...@atl.frb.org wrote: Looking out on the internet, I came across an interesting article that might point to the issue - take a look at the surrounding threads on here for further info - not sure this will be the solution - just an ideasounds the very same as what you're seeing. Thanks, Craig Nassal Federal Reserve Bank of Atlanta 1000 Peachtree St. N.E. Atlanta, Ga 30309-4470 (404)498-8437 craig.nas...@atl.frb.org Dusty Hale du...@climbonline.com Sent by: ad...@acfug.org 01/27/10 02:25 PM Please respond to discussion@acfug.org To discussion@acfug.org cc Subject [ACFUG Discuss] a very strange sql error that only happens when using CF 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 - 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 http://www.fusionlink.com -
Re: [ACFUG Discuss] a very strange sql error that only happens when using CF
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.comwrote:
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
On Wed, Jan 27, 2010 at 2:24 PM, Dusty Hale 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
Re: [ACFUG Discuss] a very strange sql error that only happens when using CF
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
On Wed, Jan 27, 2010 at 2:47 PM, Dusty Hale 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.comwrote:
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
On Wed, Jan 27, 2010 at 2:24 PM, Dusty Hale du...@climbonline.comwrote:
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
Re: [ACFUG Discuss] a very strange sql error that only happens when using CF
I agree with you because in the last part of the CF error message notice the SQL state the quotes are doubled up. However, when I output the string there not doubled up. I'm testing it out now. Never seen anything like this in all my years LOL ... The web site you are accessing has experienced an unexpected error. Please contact the website administrator. The following information is meant for the website developer for debugging purposes.Error Occurred While Processing Request Error Executing Database Query.[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 151 : -- SQLSTATE 22012 SQLSelect 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 VENDORERRORCODE 8134 DATASOURCE xytexcom_sql2005Resources: - Check the ColdFusion documentationhttp://www.macromedia.com/go/proddoc_getdocto verify that you are using the correct syntax. - Search the Knowledge Basehttp://www.macromedia.com/support/coldfusion/to find a solution to your problem. Browser Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.1.7) Gecko/20091221 Firefox/3.5.7 (.NET CLR 3.5.30729) Remote Address 65.13.120.212 Referrer http://stage2.xytex.com/search_adv.cfm Date/Time 27-Jan-10 02:01 PM Stack Trace at cfdonorsearch2ecfc665956255$funcDONORSEARCH._factor8(D:\websites\xytexcom_stage2\htdocs\cfc\donorsearch.cfc:149) at cfdonorsearch2ecfc665956255$funcDONORSEARCH.runFunction(D:\websites\xytexcom_stage2\htdocs\cfc\donorsearch.cfc:139) at cfsearch2ecfm984473465.runPage(D:\websites\xytexcom_stage2\htdocs\search.cfm:48) at cfdonorsearch2ecfc665956255$funcDONORSEARCH._factor8(D:\websites\xytexcom_stage2\htdocs\cfc\donorsearch.cfc:149) at cfdonorsearch2ecfc665956255$funcDONORSEARCH.runFunction(D:\websites\xytexcom_stage2\htdocs\cfc\donorsearch.cfc:139) at cfsearch2ecfm984473465.runPage(D:\websites\xytexcom_stage2\htdocs\search.cfm:48) java.sql.SQLException: [Macromedia][SQLServer JDBC Driver][SQLServer]Divide by zero error encountered. at macromedia.jdbc.base.BaseExceptions.createException(Unknown Source) at macromedia.jdbc.base.BaseExceptions.getException(Unknown Source) at macromedia.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown Source) at macromedia.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown Source) at macromedia.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Source) at macromedia.jdbc.sqlserver.SQLServerImplStatement.getNextResultType(Unknown Source) at macromedia.jdbc.base.BaseStatement.commonTransitionToState(Unknown Source) at macromedia.jdbc.base.BaseStatement.postImplExecute(Unknown Source) at macromedia.jdbc.base.BaseStatement.commonExecute(Unknown Source) at macromedia.jdbc.base.BaseStatement.executeInternal(Unknown Source) at macromedia.jdbc.base.BaseStatement.execute(Unknown Source) at coldfusion.server.j2ee.sql.JRunStatement.execute(JRunStatement.java:348) at coldfusion.sql.Executive.executeQuery(Executive.java:1210) at coldfusion.sql.Executive.executeQuery(Executive.java:1008) at coldfusion.sql.Executive.executeQuery(Executive.java:939) at coldfusion.sql.SqlImpl.execute(SqlImpl.java:325) at coldfusion.tagext.sql.QueryTag.executeQuery(QueryTag.java:831) at coldfusion.tagext.sql.QueryTag.doEndTag(QueryTag.java:521) at cfdonorsearch2ecfc665956255$funcDONORSEARCH._factor8(D:\websites\xytexcom_stage2\htdocs\cfc\donorsearch.cfc:149) at cfdonorsearch2ecfc665956255$funcDONORSEARCH.runFunction(D:\websites\xytexcom_stage2\htdocs\cfc\donorsearch.cfc:139) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:418) at coldfusion.runtime.UDFMethod$ReturnTypeFilter.invoke(UDFMethod.java:360) at coldfusion.runtime.UDFMethod$ArgumentCollectionFilter.invoke(UDFMethod.java:324) at coldfusion.filter.FunctionAccessFilter.invoke(FunctionAccessFilter.java:56) at
Re: [ACFUG Discuss] a very strange sql error that only happens when using CF
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.comwrote:
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
On Wed, Jan 27, 2010 at 2:47 PM, Dusty Hale 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.comwrote:
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
On Wed, Jan 27, 2010 at 2:24 PM, Dusty Hale du...@climbonline.comwrote:
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
--
Dusty Hale
Email: du...@dustyhale.com
Phone (Atlanta): 404.474.3754
Phone (Toll Free USA): 877.841.3370
Website: www.DustyHale.com
Re: [ACFUG Discuss] a very strange sql error that only happens when using CF
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
Re: [ACFUG Discuss] a very strange sql error that only happens when using CF
Very familiar with that function but in this case. That's not it. If I
remember correctly that function is to preserve single quotes that might be
in the string like:
'It's a problem'
in this case the string never has a quote:
select * from mydonors where donorid like '%ABC123%'
On Wed, Jan 27, 2010 at 3:36 PM, Forrest C. Gilmore fcg0...@wctel.netwrote:
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.commailto:
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,
Re: [ACFUG Discuss] a very strange sql error that only happens when using CF
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.netwrote:
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.commailto:
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
Re: [ACFUG Discuss] a very strange sql error that only happens when using CF
So just to let you all know. I did find a solution.
I basically rolled my code back to the way it was yesterday because it
always worked before. Then I made the same little update I made today AGAIN
(just adding a reference to one other column in the query). Now it works.
I still don't for the life of me get how I can output the string of SQL and
run it fine in both a query analyzer and and in a cfquery tag yet when I
pass it in as a variable, CF adds additional single quotes on its own. That
is a new and very bizarre thing to see for me.
Well I can't figure out but I did at least get past it so I can move on but
still the curiosity is killing me.
Thanks to all that replied and have a great day and a great rest of the
week.
On Wed, Jan 27, 2010 at 3:53 PM, Dusty Hale du...@climbonline.com 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.netwrote:
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.commailto:
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#
Re: [ACFUG Discuss] a very strange sql error that only happens when using CF
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.netwrote:
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.netwrote:
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.commailto:
teddyrpa...@gmail.com wrote:
Dusty,
As a source of troubleshooting, have you put the SQL string into
the cfquery statement in
Re: [ACFUG Discuss] a very strange sql error that only happens when using CF
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.netwrote:
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
Re: [ACFUG Discuss] a very strange sql error that only happens when using CF
I may have to do that because now I have another issue LOL. While using the
connection string approach, it works great on our production server. However
I can't for the life of me get connections strings like this to work on my
development server (Mac Book with VMWare Win XP CF 8) so I'm likely going to
do something like
cfif production server
!--- use connection string approach
cfelseif my Mac Book
!--- use cfquery tag ---
cfelse
!--- give up and go to the nearest bar and begin drinking vodka shots
---
/cfif
Muchos Gracias Teddy. I'm knockin off but will try this in the morning.
On Wed, Jan 27, 2010 at 5:30 PM, Teddy R. Payne teddyrpa...@gmail.comwrote:
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
Re: [ACFUG Discuss] a very strange sql error that only happens when using CF
Yep. But I knew that we could count on you to point it out...
You could mitigate the possibility to a degree with a filter like my
cf_xssblock tag, plus configuring the db to only allow select and exec.
I actually used to include something very similar to that in my servertools app
so you could run adhoc SQL from a form against any CF dsn, but then I thought
about how bad that could be in the wrong hands and binned it.
From: Dean H. Saxe d...@fullfrontalnerdity.com
To: discussion@acfug.org
Sent: Wed, January 27, 2010 6:29:03 PM
Subject: Re: [ACFUG Discuss] a very strange sql error that only happens when
using CF
D'oh! Anyone else see the classic SQLinjection vuln here...
--
Dean H. Saxe
A true conservationist is a person who knows that the world is not
given by his fathers, but borrowed from his children. -- John James
Audubon
On Wed, Jan 27, 2010 at 11:47 AM, Dusty Hale 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
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
On Wed, Jan 27, 2010 at 2:24 PM, Dusty Hale 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
-
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
-
-
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
-
