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.
3 posts Page 1 of 1
Post Reply

rickc

Posts: 5
Joined: Wed Sep 10, 2014 11:33 am
Post by rickc » Thu Jul 07, 2016 6:52 am
Quote
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?
Top

Support_Rick

Support Specialist
Posts: 592
Joined: Tue Jul 17, 2012 2:12 pm
Location: Phoenix, AZ
Post by Support_Rick » Mon Aug 01, 2016 8:52 am
Quote
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
Top

rickc

Posts: 5
Joined: Wed Sep 10, 2014 11:33 am
Post by rickc » Sat May 06, 2017 10:09 am
Quote
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>
Top
Post Reply
3 posts Page 1 of 1
Return to “Community Forum”