Issues with single quotes and commas in data being loaded

Archived Topics
1

Greetings.

I’m after some guidance about how folks deal with text that contains commas and/or single quotes when they are submitted as part of an insert or update to a SQL Server Database.

Is there any inbuilt functionality within ignition or do I need to cleanse or escape the data prior to inserting/updating.

Cheers and thanks

Peter

2

You’ll have to to do some cleanup. The method changes depending on whether you’re scripting or using an expression.

Through scripting:

value=value.strip("',")

Through an expression:

replace(replace(value,"'",""),",","")
3

Hi destino,

The answer is yes, Ignition does have a built-in way to handle commas and quotes in data that is being inserted/updated in the database.

Use Ignition’s prepared statement database functions such as system.db.runPrepQuery, and system.db.runPrepUpdate.

With these database functions commas and quotes in data do not affect inserting/updating the data in the database.

Best,

4

Thanks for your reply Jordan.

STRIP() is not going to cut the mustard as these characters may appear at any point in a string, but REPLACE() is certainly an option.

I come from a VB background and am very familiar with modifying strings and building up SQL statements. It’s generally the folks that are attempting to add grammatically correct comments, complete with punctuation that cause the greatest grief. I don’t want to discourage them but neither do I want these quotes and commas to cause me grief when I run the query or later when someone wants to download a CSV file.

Another member has suggested system.db.runPrepQuery, and system.db.runPrepUpdate, so I’ll check out the manual and see what is the best for me.

The system I am working with was built with no validation for this issue and we are getting a number of issues on the factory floor when strange errors pop up and the operators don’t know what’s happening.

I’m investigating whether its better to validate at the data entry point or cleanse prior to building the insert/update.

Once again thanks

Peter

5

Nick

Thanks for your response.

I’m off to the manuals for a bit of homework.

It will be a day or so before I have a chance to test it but I’ll get back and let you know how I go.

Peter

6

If at all possible, Nick has the right approach. It’s just so much cleaner and you don’t have to think about all the possible edge cases. It’s actually the recommended approach to prevent SQL injection attacks on web sites.

Also, DB’s generally preform better if the query can be precompiled and reused and Nick’s approach allows that to happen.