I have a ' in my TextField and SQLite complains

Databases
1

Hi,

I recall earlier today that 25 years ago (or so), Excel was complaining when a text file with a single quote code[/code] was loaded. And so, I tested the addition of a ’ in a field and, yes, SQLite complains.

What can I do / must do ?

Use ReplaceAll(SourceText,"'","’") // single quote surrounded with double quotes / replace ' with the curly quote (’) ?

Another solution (like \\') ?

2

Use a prepared statement and it will be handled for you.

3

Not at my Desk. Taken from my mind. May have spelling issues. :wink:

Dim ps As PreparedStatement db.Prepare("INSERT INTO myTable (myColumn) VALUES (?)") ps.Bind(0, myTextToInsert, SQLitePreparedStatement.Text) ps.SQLExecute

4

Get into the habit of using PreparedStatements all the time when you as a developer don’t have control over the input.
I always use it even if I am controlling the input, just to be safe.

5

[quote=273459:@Albin Kiland]Get into the habit of using PreparedStatements all the time when you as a developer don’t have control over the input.
I always use it even if I am controlling the input, just to be safe.[/quote]

There are only 2 cases in which i avoid them:

  1. I work with User/3rd Party Input
  2. In a Thread (but only if Number 1 is still true)

*2: Because i’ve read once, they are slow in a Thread…

6

Tim, Sasha, Albin: thank you.

While this page was opening, I ws thinking: “I really have to start using PreparedStatements”.

I think that “Now is the time” to do that.

Use a prepared statement and it will be handled for you.
I love when it will be handled for you :slight_smile:

7

[quote=273463:@Sascha S]There are only 2 cases in which i avoid them:

  1. I work with User/3rd Party Input
    [/quote]
    This is exactly when you SHOULD use them
8

OMG Norman! I am sorry for the confusion. This is exactly when i use them.
I was writing this post while i was in a Phone Conference… :wink:

There are only 2 cases in which i avoid them:

  1. I work with NON User/3rd Party Input
  2. In a Thread (but only if Number 1 is still true)

*2: Because i’ve read once, they are slow in a Thread…

9

Have you tested this?

10

Not really (read:no) :slight_smile:

11

what I don’t like about prepared statements is that they are different between databases …
they should really be unified in the syntax…

12

Same as if you wrote raw sql
Different vendors have mostly similar things but lots of proprietary or non-standard bits too (functions etc)

13

Don’t forget that escaping characters works differently in a normal SQL command string such as UPDATE than inside a LIKE string.