I have a form that contains textboxes and textareas with user entered data. I am using a simple ColdFusion actionpage with a cfquery tag to submit the data to my database.
<cfquery name="add_ticket" datasource="#datasource#">
INSERT INTO service_ticket(end_user, customer_id, ticket_id, company_name, service_description, service_date, status, customer_signature, technician_signature, materials, ticket_type, equipment, discount, percent_discount, material_subtotal, material_tax, material_shipping, material_total, billable_service_total, tax_rate, grand_total, equipment_id, equipment_name)
VALUES("", '#get_customer.customer_id#', "#ticketnum#", "#url.customer_name#", "#url.description# - #get_equipment.equipment_name#", #CreateODBCDateTime(Now())#, "In-progress", "0", "0", "0", "#url.TT#", "0", '0.00', '0', '0.00', '6.625', '0.00', '0.00', '0.00', '#subscriber.tax_rate#', '0.00', "#get_equipment.id#", "#get_equipment.equipment_name#")
</cfquery>
I sporadically run into errors submitting this query because the values for certain columns contain special characters.
For instance, the value for company_name might be: Smith & Johnson, LLC.
The ColdFusion code breaks once it hits the ampersand. I've tried enclosing the value in single quotes and double quotes as well as
<cfqueryparam value="#url.company_name#" cfsqltype="cf_sql_longvarchar" />
but nothing seems to help.
Is there a better way to handle this situation?
I'm currently running a Lucee server.
After reading your comment I now understand what the issue is. The solution to your problem is to use the ColdFusion function encodeForURL() when calling your action page using javascript.
Change your code from
window.open(href='actionpages/add_ticket_maintenance.cfm?TT=commercial&equipment_id=#id#&customer_name=#customer_name#&description=Equipment Maintenance&end_user=Scheduled maintenance', "_self");
to
window.open(href='actionpages/add_ticket_maintenance.cfm?TT=commercial&equipment_id=#id#&customer_name=#encodeForURL(customer_name)#&description=Equipment Maintenance&end_user=Scheduled maintenance', "_self");