Insert text in database with an apostrophe

Post Reply
mjacobson
Posts: 204
Joined: Fri Feb 08, 2002 3:35 pm

Insert text in database with an apostrophe

Post by mjacobson »

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.
User avatar
mark
Site Admin
Posts: 5513
Joined: Tue Apr 25, 2000 6:56 pm

Insert text in database with an apostrophe

Post by mark »

Use double apostrophe.
'O''Neil'
User avatar
John
Site Admin
Posts: 2597
Joined: Mon Apr 24, 2000 3:18 pm
Location: Cleveland, OH
Contact:

Insert text in database with an apostrophe

Post by John »

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.
John Turnbull
Thunderstone Software
User avatar
mark
Site Admin
Posts: 5513
Joined: Tue Apr 25, 2000 6:56 pm

Insert text in database with an apostrophe

Post by mark »

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.
User avatar
Kai
Site Admin
Posts: 1271
Joined: Tue Apr 25, 2000 1:27 pm

Insert text in database with an apostrophe

Post by Kai »

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.
mjacobson
Posts: 204
Joined: Fri Feb 08, 2002 3:35 pm

Insert text in database with an apostrophe

Post by mjacobson »

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

<sql novars "update mytable set name=$NAME where id=$ID"></sql>
User avatar
Kai
Site Admin
Posts: 1271
Joined: Tue Apr 25, 2000 1:27 pm

Insert text in database with an apostrophe

Post by Kai »

Correct.
Post Reply