# Syntax error write back to table

**URL:** <https://forum.inductiveautomation.com/t/syntax-error-write-back-to-table/7422>\
**Category:** Ignition\
**Created:** [October 16, 2013, 3:27pm UTC](https://forum.inductiveautomation.com/t/syntax-error-write-back-to-table/7422 "2013-10-16T15:27:57Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![lcs226](https://avatars.discourse-cdn.com/v4/letter/l/848f3c/32.png) [@lcs226](https://forum.inductiveautomation.com/u/lcs226)\
**Post date:** [October 16, 2013, 3:27pm UTC](https://forum.inductiveautomation.com/t/syntax-error-write-back-to-table/7422/1 "2013-10-16T15:27:57Z")

</div>

Error writing to database table

I have the following string in my customer table taht I can retreive without a problem, but when I try to write it back I get a syntax error

Joe Feta’s - It would appear the apostrophe is causing the error in the donator field in the following command -

donator = event.source.parent.getComponent(“dd\_Donator”).selectedLabel

system.db.runUpdateQuery(“UPDATE Items SET itemName = ‘%s’, Donator = ‘%s’, Value = ‘%s’, StreetNumber = ‘%s’, Street = ‘%s’, City = ‘%s’, Province = ‘%s’, PostalCode = ‘%s’, PhoneNumber= ‘%s’, CellPhone = ‘%s’, Fax = ‘%s’, Email = ‘%s’, Description = ‘%s’ WHERE EventName = ‘%s’ AND Year = ‘%s’ AND idItems = %d” % (itemName, donator, value, streetNum, street, city, prov, pCode, phone, cell, fax, email, description, eventName, year, itemNum), ‘Auction\_System’)

What do I need to change in order to allow the apostrophe

---

<div class="post-metadata">

**Author:** ![RRRancher](https://avatars.discourse-cdn.com/v4/letter/r/c67d28/32.png) [@RRRancher](https://forum.inductiveautomation.com/u/RRRancher)\
**Post date:** [October 16, 2013, 4:25pm UTC](https://forum.inductiveautomation.com/t/syntax-error-write-back-to-table/7422/2 "2013-10-16T16:25:06Z")

</div>

I had a similar problem in a table I built to hold log messages. The apostrophe was being interpreted as syntax of the command instead of what it really was, just part of the text field. It wasn’t pretty, but what I did to workaround it was to change the apostrophe in the text field to another character before writing to the SQL table, then changing it back to an apostrophe when I read and displayed that text field later on. Ugly, but it worked. I was in a hurry and told myself I would look into a better solution later on. Yeah, right.

The other character I used was the one under the ~ on the keyboard. I think it is called the accent. I don’t use MySQL, but I think it uses the accent for things in its syntax too, so that may not work for it. But the accent means nothing to MSSQL, as far as I’ve seen.

---

<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:** [October 16, 2013, 7:51pm UTC](https://forum.inductiveautomation.com/t/syntax-error-write-back-to-table/7422/3 "2013-10-16T19:51:40Z")

</div>

You can escape out apostrophes when storing them in the database by replacing them with two apostrophes:

```auto
donator = donator.replace("'","''")
```

This works both in MS SQL and MySQL.

You can also avoid this potential for [SQL Injection](http://en.wikipedia.org/wiki/SQL_injection) by using the [runPrepUpdate](http://www.inductiveautomation.com/support/usermanuals/ignition/system_db_runprepupdate.htm) function:

```auto
system.db.runPrepUpdate("UPDATE Items SET itemName = ?, Donator = ?, Value = ?, StreetNumber = ?, Street = ?, City = ?, Province = ?, PostalCode = ?, PhoneNumber= ?, CellPhone = ?, Fax = ?, Email = ?, Description = ? WHERE EventName = ? AND Year = ? AND idItems = ?", [itemName, donator, value, streetNum, street, city, prov, pCode, phone, cell, fax, email, description, eventName, year, itemNum], 'Auction_System')
```

The prepared query is smart enough to figure out when to put the apostrophes and escape them and when to use an integer value such as in your WHERE clause.

---

<div class="post-metadata">

**Author:** ![lcs226](https://avatars.discourse-cdn.com/v4/letter/l/848f3c/32.png) [@lcs226](https://forum.inductiveautomation.com/u/lcs226)\
**Post date:** [October 16, 2013, 10:00pm UTC](https://forum.inductiveautomation.com/t/syntax-error-write-back-to-table/7422/4 "2013-10-16T22:00:54Z")

</div>

Thanks, works like a charm

---

<div class="post-metadata">

**Author:** ![RRRancher](https://avatars.discourse-cdn.com/v4/letter/r/c67d28/32.png) [@RRRancher](https://forum.inductiveautomation.com/u/RRRancher)\
**Post date:** [October 17, 2013, 2:10pm UTC](https://forum.inductiveautomation.com/t/syntax-error-write-back-to-table/7422/5 "2013-10-17T14:10:40Z")

</div>

Thanks for that, Adam. Very good information. My Google search skills need an upgrade. 😉