database write error

[Ken Corey]

On 31/01/2012 12:32, Len Morgan wrote:
> Apostrophe is a reserved character in SQL so you'll have to escape it.
> If you take the part of your INSERT statement that has the field with
> the apostrophe in it and "expand" the variable (i.e., write it in with
> the *contents *of the variable instead (e.g., INSERT
> .....,'[[globaldata] ]' becomes INSERT ..., 'your's') you can see that
> the parser has no way to tell which apostrophe is real and which is part
> of the field data.
>
> What I usually do is is replace all the apostrophes in my DATA with
> backticks (`) when I write the data and then do the reverse when I read
> it back. This fools SQL into doing what you want but still looks right
> when you manually look at the data in the database.

Ack!  You don't want munged data in your database, you want the data you 
intended to put in.  Why?  Your selects will be wonky and your sorting 
questionable.

Worse, are you sure that apostrophes are the worst character you'll be 
facing?  What if someone gave their name as
"bill');drop table "master";"

Your apostrophe's might be protected, but that semicolon could bite you 
in the butt.   (http://xkcd.com/327/)

Mike got it right when he said to use the substitution form of 
revExecuteSQL.  This is by far the cleanest solution and less of a 
security risk.  If someone is trying to goof you up, you just see goofy 
entries in your database.

However, if you *must* build your own string and execute that, then 
escape the apostrophes (in the way appropriate to your database).  For 
SQLite3, MySQL and PostgreSQL it'd be something like:

replaceText(fieldText,"'","''")

So, your sql becomes something like:
INSERT INTO master (delivery_date, mothers_name, mothers_phin,
mothers_mhsc, mothers_dob, care_provider, note) VALUES ('1/1/1970', 
'J''ames', 'M''cintosh','','1/1/1970', 2345, '')

This example suffers because I couldn't think of appropriate names, but 
you get the idea.

-Ken