database write error

[Pete]

Two different topics here, I think, - escaping single (and double) quotes
and sql injection. Both appear to be solved with the revExecuteSQL
substitution.

If you don't use revExecuteSQL solution then there's two choices for
escaping.  SQL accepts either single or double quotes to enclose literals.
 If you use single quotes, double quotes don't need to be escaped in the
literal, and vice versa.  So, if you're sure that your data won't contain
double quotes, then you can use double quotes to enclose the literals and
single quotes won't cause a problem.  On the other hand, who can be sure of
anything when it comes to user data!

The other issue is protecting against SQL injection, which I think is why
the reference to DROP TABLE and semicolon came in.  There was a thread
about this a few weeks back which resulted in enhancement request 9932.   A
RunRev comment on it was

 "The function mysql_real_escape_string (and its equivalents for the
other database
types) is called internally by revDB when passing data using variable lists
(for an example, see the dictionary entry for revQueryDatabase). This is the
preferred method for escaping data."

Pete

On Tue, Jan 31, 2012 at 4:52 AM, Ken Corey <ken at kencorey.com> wrote:

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


-- 
Pete
Molly's Revenge <http://www.mollysrevenge.com>