Page 1 of 1
Escape single quotes in sql variables
Posted: Thu Jul 07, 2016 6:52 am
by 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?
Re: Escape single quotes in sql variables
Posted: Mon Aug 01, 2016 8:52 am
by 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 '
Re: Escape single quotes in sql variables
Posted: Sat May 06, 2017 10:09 am
by 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="'" version="2.0" />
<setVariable label="Constant: DOUBLEQUOTE" name="C_DOUBLEQUOTE" value="''" version="2.0" />
<setVariable label="Set vLastName" name="vLastName" value="De'Vonte" version="2.0" />
<sql resourceId="openid-dev" version="1.0">
<query label="SQL Insert">
<statement>Insert into tempchudebug values('${replace(vLastName,C_SINGLEQUOTE,C_DOUBLEQUOTE)}')
</statement>
</query>
</sql>
</module>
</project>