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
_______________________________________________
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
