# escapeSQL inserting double single quotes

**URL:** <https://forum.inductiveautomation.com/t/escapesql-inserting-double-single-quotes/8240>\
**Category:** Ignition\
**Created:** [March 31, 2014, 3:08pm UTC](https://forum.inductiveautomation.com/t/escapesql-inserting-double-single-quotes/8240 "2014-03-31T15:08:12Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![stuartd](https://avatars.discourse-cdn.com/v4/letter/s/ecae2f/32.png) [@stuartd](https://forum.inductiveautomation.com/u/stuartd)\
**Post date:** [March 31, 2014, 3:08pm UTC](https://forum.inductiveautomation.com/t/escapesql-inserting-double-single-quotes/8240/1 "2014-03-31T15:08:12Z")

</div>

Hey there,  
So I know this didn’t use to do this. But the escapeSQL is causing an insertion of double single quotes into the database. I could have set it up wrong but I did not do anything different then what is normally done. I have two text fields both of them have their text set to escapeSQL with a custom property. One tables data is queried from the database, then the ability to edit and save it is set up. The second text field has a static template set up to it. Editing to that is done then saved, I go through and check to see if there is already a field saved in the database and if there is I display the SQL field if not I display the template field. This worked properly before I set up the template part of it. Any suggestions on how to not have this double single quote problem?

---

<div class="post-metadata">

**Author:** ![drewdin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/drewdin/32/3363_2.png) [@drewdin](https://forum.inductiveautomation.com/u/drewdin)\
**Post date:** [April 8, 2014, 5:24pm UTC](https://forum.inductiveautomation.com/t/escapesql-inserting-double-single-quotes/8240/2 "2014-04-08T17:24:55Z")

</div>

can you post your code so we can check it out?

---

<div class="post-metadata">

**Author:** ![stuartd](https://avatars.discourse-cdn.com/v4/letter/s/ecae2f/32.png) [@stuartd](https://forum.inductiveautomation.com/u/stuartd)\
**Post date:** [April 10, 2014, 2:41pm UTC](https://forum.inductiveautomation.com/t/escapesql-inserting-double-single-quotes/8240/3 "2014-04-10T14:41:39Z")

</div>

This is what will trigger the save event.

[code]"""  
Saves Run Summary  
“”"

lineID = event.source.parent.getComponent(‘Top Bar’).getComponent(‘Production Line’).selectedLineID  
wo = event.source.parent.getComponent(‘Top Bar’).selectedWO  
batch = event.source.parent.getComponent(‘Top Bar’).selectedBatch  
summary = event.source.parent.summaryToSave

# Insert run summary

query = “”“EXEC updateBatchSummary ?, ?, ?, ?;”""  
values = [lineID,wo,batch,summary]  
system.db.runPrepUpdate(query,values,“MES\_Runtime”)[/code]

This is the custom property that detects which text field to grab it from, and if i escape sql here, it will double input the apostrophes.

```auto
if({Root Container.hasSummary} = 1,
{Root Container.Summary.text},
{Root Container.Summary Template.text})
```

Here is the SQL code that gets ran.

[quote]USE [MES\_Runtime]  
GO  
/\*\*\*\*\*\* Object: StoredProcedure [dbo].[updateBatchSummary] Script Date: 04/09/2014 15:47:11 \*\*\*\*\*\*/  
SET ANSI\_NULLS ON  
GO  
SET QUOTED\_IDENTIFIER ON  
GO

ALTER PROCEDURE [dbo].[updateBatchSummary](  
@LineID INT,  
@WO INT,  
@Batch INT,  
@Summary nvarchar(MAX))   
AS

BEGIN  
– SET NOCOUNT ON added to prevent extra result sets from  
– interfering with SELECT statements.  
SET NOCOUNT ON;

MERGE INTO BatchSummary AS Target  
USING (VALUES(@LineID,@WO,@Batch)) AS Source (LineID,WO,Batch)  
ON (Target.[LineID] = Source.[LineID] AND Target.[WO] = Source.[WO] AND Target.[Batch] = Source.[Batch])  
WHEN MATCHED THEN  
UPDATE SET Target.[Summary]=@Summary, Target.[Timestamp]=GETDATE()  
WHEN NOT MATCHED THEN  
INSERT (LineID,WO,Batch,Summary,Timestamp) VALUES (@LineID,@WO,@Batch,@Summary,GETDATE());

END  
[/quote]

---

<div class="post-metadata">

**Author:** ![adamaustin](https://avatars.discourse-cdn.com/v4/letter/a/5f8ce5/32.png) [@adamaustin](https://forum.inductiveautomation.com/u/adamaustin)\
**Post date:** [April 10, 2014, 2:52pm UTC](https://forum.inductiveautomation.com/t/escapesql-inserting-double-single-quotes/8240/4 "2014-04-10T14:52:45Z")

</div>

According to the user manual:

[escapeSQL](http://www.inductiveautomation.com/support/usermanuals/ignition/gen_strings_escapesql.htm)

> [@](#):
>
> This is a fairly simplistic function - it just replaces single quotes with two single quotes, and backslashes with two backslashes.

So I guess you uncovered a bug that was fixed or something.

---

<div class="post-metadata">

**Author:** ![stuartd](https://avatars.discourse-cdn.com/v4/letter/s/ecae2f/32.png) [@stuartd](https://forum.inductiveautomation.com/u/stuartd)\
**Post date:** [April 10, 2014, 3:01pm UTC](https://forum.inductiveautomation.com/t/escapesql-inserting-double-single-quotes/8240/5 "2014-04-10T15:01:40Z")

</div>

If I put in the escape sql, it wont work properly. if I leave it out, like I did in the code posted it all works fine. So I am not sure if it is getting done in another place also.