Solved

Coldfusion Query Problem

Posted on 2013-05-30
11
423 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
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 2

Accepted Solution

by:
eli411 earned 167 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
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 333 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 28

Expert Comment

by:Pravin Asar
ID: 39208861
Is any of the string being passed to query has single or double quote ???
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 15

Expert Comment

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

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 333 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) . I did keep the main js functions but made sever…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

911 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now