Escape single quotes in sql variables

[rickc]

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]

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 '

[rickc]

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>