Thunderstone Support Forums

How do you insert a string into the sql database that has an apostrophe in it? Do you need to convert it to something else before the insert/update?

I am getting SQLPrepare() failed errors when I try to insert something like "O'Neil" in my table.

Use double apostrophe.
'O''Neil'

In Vortex the best way is to have the string in a variable, and just insert that, e.g.

<sql "insert into Table value($name)"></sql>

which will take care of any escapement and SQL injection issues.

But it's better to use parameters rather than literal values.
Instead of
insert into xx values('o''neil');
it's better and safer to
insert into xx values($LastName);
No escapement necessary and no chance of SQL injection vulnerabilities.

In Vortex you should assign the value to a variable first, then use it as a parameter so there are no escapement issues in the SQL (and no SQL injection problems):

<$insertVal = "I don't know">
<sql novars "insert into myTable values($insertVal)">
</sql>

In tsql (command line), where you cannot use parameters, you can escape the apostrophe with another:

tsql "insert into myTable values('I don''t know')"

Using parameters in Vortex is safer because it protects not only against apostrophes, but any other character that is SQL syntax.

So if it was an update it would be something like:

<sql novars "update mytable set name=$NAME where id=$ID"></sql>

Correct.