Solved

Coldfusion Query Problem

Posted on 2013-05-30
11
426 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
The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

The technique is by far very Simple! How we can export the ColdFusion query results to DOC file?  Well before writing this I researched a lot in Internet but did not found a good Answer anyways!  So i thought now i should share my small snippet w…
PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

820 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