Escape single quotes in sql variables

Post any question you may have in regards to GoAnywhere MFT and let our talented support staff and other users assist you.
If you need a quicker response, please create a support ticket via the customer portal my.goanywhere.com or contact our support team by email at [email protected].
3 posts Page 1 of 1

rickc

Posts: 5
Joined: Wed Sep 10, 2014 11:33 am

Post by rickc » Thu Jul 07, 2016 6:52 am
I have a project with a SQL task.

The SQL task updates a record in the database with a statement like:
update foo
set ProcessedDate= current_timestamp
where FirstName='${myFirstName[2]}'

The variable ${myFirstName[2]} contains a value like De'Vonte.

The single quote in the value is causing the package to fail. How do I escape the single quote?

Support_Rick

Support Specialist
Posts: 590
Joined: Tue Jul 17, 2012 2:12 pm
Location: Phoenix, AZ

Post by Support_Rick » Mon Aug 01, 2016 8:52 am
You will need to double-up on quotes before you insert, or replace them as you insert.

You could replace with something like '-X-', then go back after the insert to replace the '-X-' with a '
Rick Elliott
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696

rickc

Posts: 5
Joined: Wed Sep 10, 2014 11:33 am

Post by rickc » Sat May 06, 2017 10:09 am
I mocked up a project that illustrates how I worked through this problem.
Basically I used the replace function in MFT to escape the single quote.

Rick
Code: Select all
<project name="Forum post to handle quotes" mainModule="Main" version="2.0" logLevel="verbose">
	<description>If the data has quotes, then insert will have a problem. Must escape quote.</description>

	<module name="Main">

		<setVariable label="Constant: SINGLEQUOTE" name="C_SINGLEQUOTE" value="&apos;" version="2.0" />


		<setVariable label="Constant: DOUBLEQUOTE" name="C_DOUBLEQUOTE" value="&apos;&apos;" version="2.0" />


		<setVariable label="Set vLastName" name="vLastName" value="De&apos;Vonte" version="2.0" />


		<sql resourceId="openid-dev" version="1.0">
			<query label="SQL Insert">
				<statement>Insert into tempchudebug values(&apos;${replace(vLastName,C_SINGLEQUOTE,C_DOUBLEQUOTE)}&apos;)


</statement>
			</query>
		</sql>

	</module>

</project>
3 posts Page 1 of 1