Syntax error write back to table

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

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.

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

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

This works both in MS SQL and MySQL.

You can also avoid this potential for SQL Injection by using the runPrepUpdate function:

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.

Thanks, works like a charm

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