[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Coldfusion Query Problem

Posted on 2013-05-30
11
Medium Priority
?
433 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 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
Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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…
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…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses
Course of the Month19 days, 3 hours left to enroll

834 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