Solved

Coldfusion Query Problem

Posted on 2013-05-30
11
428 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 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
Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

This article  is about submitting  form through  ColdFusion.Ajax.submitForm to the action page and send a response back in JSON format which later can be decoded using ColdFusion.JSON.decode. By this way you can avoid the usual page refresh for subm…
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…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

734 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