?
Solved

Coldfusion Query Problem

Posted on 2013-05-30
11
Medium Priority
?
431 Views
Last Modified: 2013-06-18
I have a website that I recently moved to a new host. This site has worked perfectly for about five years but is now getting error messages on the new host. Specifically, it is the line of code in the query that stores orders after a customer submits the order. It is the last line that submits the security code that has the problem. The query is below. It gets the error:

Error [jrpp-95452] - Error Executing Database Query.[Macromedia][SQLServer JDBC Driver][SQLServer]String or binary data would be truncated. The specific sequence of files included or processed is: D:\home\mysite.com\wwwroot\checkout2.cfm, line: 106

It gets another error also but my host only allows me to see the last 24 hours of the application logs so I do not have it to include. I am not sure why it is getting this message. It is a Microsoft SQL database and the field is a string.


<cfquery name="save_order"
            datasource="#application.dsn#"
            dbtype="OLEDB">
            insert into orders
                  (order_sessionid,
                   order_billing_name,
                   order_billing_address1,
                   order_billing_address2,
                   order_billing_city,
                   order_billing_state,
                   order_billing_zip,
                   order_billing_country,
                   order_billing_phone,
                   order_billing_email,
                   order_shipping_name,
                   order_shipping_address1,
                   order_shipping_address2,
                   order_shipping_city,
                   order_shipping_state,
                   order_shipping_zip,
                   order_shipping_country,
                   order_shipping_phone,
                   order_ship_via,
                   order_card_type,
                   order_card_name,
                   order_card_number,
                   order_exp_month,
                   order_exp_year,
                   order_date,
                   sec_code)
                  values
                  (<cfqueryparam value="#session.sessionid#" cfsqltype="CF_SQL_VARCHAR">,
                   <cfqueryparam value="#form.billing_name#" cfsqltype="CF_SQL_VARCHAR">,
                   <cfqueryparam value="#form.billing_address1#" cfsqltype="CF_SQL_VARCHAR">,
                   <cfqueryparam value="#form.billing_address2#" cfsqltype="CF_SQL_VARCHAR">,
                   <cfqueryparam value="#form.billing_city#" cfsqltype="CF_SQL_VARCHAR">,
                   <cfqueryparam value="#form.billing_state#" cfsqltype="CF_SQL_VARCHAR">,
                   <cfqueryparam value="#form.billing_zip#" cfsqltype="CF_SQL_VARCHAR">,
                   <cfqueryparam value="#form.billing_country#" cfsqltype="CF_SQL_VARCHAR">,
                   <cfqueryparam value="#form.billing_phone#" cfsqltype="CF_SQL_VARCHAR">,
                   <cfqueryparam value="#form.billing_email#" cfsqltype="CF_SQL_VARCHAR">,
                   <cfqueryparam value="#form.shipping_name#" cfsqltype="CF_SQL_VARCHAR">,
                   <cfqueryparam value="#form.shipping_address1#" cfsqltype="CF_SQL_VARCHAR">,
                   <cfqueryparam value="#form.shipping_address2#" cfsqltype="CF_SQL_VARCHAR">,
                   <cfqueryparam value="#form.shipping_city#" cfsqltype="CF_SQL_VARCHAR">,
                   <cfqueryparam value="#form.shipping_state#" cfsqltype="CF_SQL_VARCHAR">,
                   <cfqueryparam value="#form.shipping_zip#" cfsqltype="CF_SQL_VARCHAR">,
                   <cfqueryparam value="#form.shipping_country#" cfsqltype="CF_SQL_VARCHAR">,
                   <cfqueryparam value="#form.shipping_phone#" cfsqltype="CF_SQL_VARCHAR">,
                   <cfqueryparam value="#form.ship_via#" cfsqltype="CF_SQL_VARCHAR">,
                   <cfqueryparam value="#form.card_type#" cfsqltype="CF_SQL_VARCHAR">,
                   <cfqueryparam value="#form.card_name#" cfsqltype="CF_SQL_VARCHAR">,
                   <cfqueryparam value="#form.card_number#" cfsqltype="CF_SQL_VARCHAR">,
                   <cfqueryparam value="#form.exp_month#" cfsqltype="CF_SQL_VARCHAR">,
                   <cfqueryparam value="#form.exp_year#" cfsqltype="CF_SQL_VARCHAR">,
                   <cfqueryparam value="#ddate#" cfsqltype="CF_SQL_VARCHAR">,
             <cfqueryparam value="#form.sec_code#" cfsqltype="CF_SQL_VARCHAR">
             )

      </cfquery>
0
Comment
Question by:Robcarter10
[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
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 2

Accepted Solution

by:
eli411 earned 668 total points
ID: 39208598
"][SQLServer]String or binary data would be truncated" meant that particular column length is either too short or the input string is too long so all the input values might not save into the database.  Check your table columns - you might want to change varchar(25) to varchar(50) or something like that in your database!
0
 
LVL 52

Expert Comment

by:_agx_
ID: 39208637
(no points. eli411 already gave the answer)

   > It is the last line that submits the security code that has the problem.

Maybe .. maybe not.  Line numbers aren't always accurate.  Might also be a different column that's *near* that line number.
0
 

Author Comment

by:Robcarter10
ID: 39208642
Eli411: That is what is confusing me. I have the character length set to 255 and the form will not even accect more than 10 characters
0
AWS Certified Solutions Architect - Associate

This course has been developed to provide you with the requisite knowledge to not only pass the AWS CSA certification exam but also gain the hands-on experience required to become a qualified AWS Solutions architect working in a real-world environment.

 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 1332 total points
ID: 39208687
@Robcarter10

It is not necessarily the "sec_code" column, or even a string column. Like I mentioned, line numbers are often approximate.  The problem could be a completely different column

Also, are you sure the error occurred with a legitimate submission? Client side validation can be bypassed easily OR it could be a malicious site doing a direct submit to your action page ie like with cfhttp.
0
 
LVL 29

Expert Comment

by:Pravin Asar
ID: 39208861
Is any of the string being passed to query has single or double quote ???
0
 
LVL 16

Expert Comment

by:Gurpreet Singh Randhawa
ID: 39210187
why do't you provide maxlength attribute to cfqueryparam
0
 
LVL 16

Expert Comment

by:Gurpreet Singh Randhawa
ID: 39210205
if you know the length of each posted form field, try to create a checkand see wheere the problem lies:

Do like this

<cfif len(form.fielname) EQ 255>
<cfqueryparam value="#form.shipping_phone#" cfsqltype="CF_SQL_VARCHAR">,
<cfelse>
<cfqueryparam value="#Left(form.shipping_phone,255)#" cfsqltype="CF_SQL_VARCHAR">,
</cfif>

This way you can check which fields is causing trouble,

although not a good way to check, but give it a try,
0
 

Author Comment

by:Robcarter10
ID: 39211011
@pravinasar: There should not be any single or double quotes. It is just credit card information.

@myselfrandhawa: I will give the maxlength a try. I will try the other suggestion also. I am not sure how much it will help me though. I have never been able to duplicate the problem myself.
0
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 1332 total points
ID: 39211255
> I have never been able to duplicate the problem myself.

Well since it's not the type of error that occurs every time - if you can't reproduce it, it's  probably because your test values aren't long enough OR aren't the right type of values.

I can give you a script that can generate test values from the db metadata.  Are all the columns in the table really type VARCHAR?  If not can you post the table DDL so I can adjust it?
0
 

Author Closing Comment

by:Robcarter10
ID: 39253872
I do think that the problem was in another column. I increased the lengths of all fields in the database and added the maxlength values to the cfqueryparam. After all this time, the problem has not occured again. So, I am confident that this worked.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 39257203
> form.card_number
> form.sec_code
> It is just credit card information.

Just noticed that... I sincerely hope the app isn't storing full cc numbers in plain text or storing security codes (CVV, ...) at all - because afaik both are PCI violations.
0

Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Today, I was working on some optimization and spam-stopping techniques when I encountered Ben Nadel's post to reduce spam feature using Math (http://www.bennadel.com/blog/197-How-I-Stop-Spammers-On-My-ColdFusion-Blog.htm). While this method is not o…
I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Suggested Courses

752 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