January 9, 2005 at 3:22 pm
Hello, I'd like to hear your opinion: What is the best way to handle single quotes in strings, stored in a DB?
I have been asked to help create English version of texts, that appear on a webpage; all these texts are stored in a table in SQLS database, and are always displayed to user via web browser (ASP). Now, what to do with texts like "You can't do this right now!"? Problem is, that the program uses dynamic SQL sometimes, and in that moment a single quote would cause error.
I came up with 3 possibilities:
- store the single quote as a code (&#...)
- use another character (problem is, which one? people with many different language versions of Windows may be accessing the page and I don't know which would display correctly in all of them)
- store it as a single quote, and modify the code (dynamic SQL) so that it replaces any single quote with two single quotes before doing anything with the string
Did I overlook some obvious solution? Or is one of these methods recommended? Luckily the Czech language does not use the single quote character, or only exceptionally, so that it is easy to avoid it... but I can hardly avoid it in English. I've tried to search the forums, but didn't find anything of value to this problem - if that was just my fault (badly set search conditions), please simply point me to the right thread!
Thanks, Vladan
January 9, 2005 at 3:31 pm
It depends on what language I am working with and whether I can use ADO/ADO.NET.
If I am passing a web input or textarea box then providing I am assigning the value of the control to a command.parameter then ADO looks after the text for me.
Server Side JavaScript has a connection.varcharFromString function that automatically converts a string to a correct form for use with SQL.
You could do a simple replace on any single quotes to make sure that they appear in pairs.
January 9, 2005 at 4:41 pm
I always replace them with double single quotes going into the db - the place I used to work at had a function that pre-processed the text going into the db and removed and non-ascii chars and took care of quote chars etc whihch i thought was good practice.
cheers
dbgeezer
January 12, 2005 at 4:42 am
Thanks for your replies, I'll try to explain that to the author of the code and get him to modify it so that it uses REPLACE at all vital points (problem is not with data input from users, but with various manipulation and concatenation of several messages into one).
Vladan
January 12, 2005 at 7:32 am
Or with ADO use a Command object and pass each input as a parameter. When you do so it will auto handle these situations better than trying to remeber to catch them yourself and enforces typing of the data better.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply