Apostrophe in TEXT

General
1

We store data into a sql lite file. One column is just Description as type TEXT.
User can enter free form text and have it saved.

We’ve noticed if the text has an apostrophe… somehow that is turned into like 20 apostrophe’s.

So let’s say they enter: John’s IRA
then at some point it shows up as John’’’’’’’’’’’’’’’’’’’’’’’’’’’’'s IRA

We aren’t doing any looping or anything over that text string so this is baffling.
Anyone seen anything like this?

2

Hmm. Single quotes cannot be inserted in SQL as clear text as they are a part of the syntax.
How are you inserting the data? No Prepared Statements?

3

[quote=131878:@Tim Turner]We store data into a sql lite file. One column is just Description as type TEXT.
User can enter free form text and have it saved.

We’ve noticed if the text has an apostrophe… somehow that is turned into like 20 apostrophe’s.

So let’s say they enter: John’s IRA
then at some point it shows up as John’’’’’’’’’’’’’’’’’’’’’’’’’’’’'s IRA

We aren’t doing any looping or anything over that text string so this is baffling.
Anyone seen anything like this?[/quote]

You could replace the regular single quote by the “curly quote” as Word does, which is &u2019 : ’. Then save to your database.

See http://documentation.xojo.com/index.php/ReplaceAll

4

I always thought (not an expert) in sqlite you just replaced a single quote with two. It works for me.

Also applies to &?

5

I think this may be an encoding problem, as I also once had a problem with inserting values into an SQLite database which contained apostrophes.

I defined the encoding as UTF-8, and also used a prepared statement, and the issue was resolved.

I think this was my post - take a look, it may help to solve your problem?
link text

6

I am using Storm from Paul Lefebvre so don’t really do any prepared sql statements. Storm saves the objects into the file automagically.

yes I suspected it must be some kind of encoding problem… makes me wonder i there are other characters that also need to be escaped/encoded/tweaked…

7

I believe this works too

s="\‘quoted string\’"

8

Try defining it as UTF -8 and then make a few entries containing random characters.

9

If it’s user entered text ALWAYS use a prepared statement. Nothing worse then having Mr. Delete From trying to sign into software.

10

Using a DatabaseRecord is safe too. If Storm isn’t using either, you should modify the code so it does. Remember these rules about sanitizing inputs.

  1. Never sanitize on your own inputs. Use a Prepared Statement or DatabaseRecord in all cases where you do not control the inputs.
  2. If you must sanitize your own inputs, see Rule 1.
11

There was a bug in Storm that Paul found… I think we have it fixed… and it was definitely related to escaping the single quote/apostrophe char.