Storing and retrieving data from a SQL database

[Peter Haworth]

No substitution or escaping involved, although the dictionary makes
constant reference to to substitution which is misleading.

I think it's worth a short explanation of what goes on behind the scenes.

Although executing an sql statement in lc is just one call, it's multiple
calls to the sql library routines.

Prepare
The statement is analyzed and decisions such as which indexes to use are
made.  The statement isn't executed at this point.

Bind
The data in the variables/array you provide  is linked to the placeholders
in the statement as pointers, not substituted into the statement.

Execute
The statement is executed.

The bind and execute steps can be repeated without going through the
prepare step, providing some performance improvements, although lc doesn't
allow us to do that.

Using parameterized queries removes the need for any escaping and protects
you against some forms of SQL injection attacks. I'm not sure why people
don't use them more, but one reason is that the dictionary just lays out
their availability without explaining why it's important to use them.

On Thu, May 19, 2016, 9:02 AM Mike Kerner <MikeKerner at roadrunner.com> wrote:

> No, it won't escape it, and it won't just substitute it.
>
> Substitution would be
> put "INSERT INTO myTable VALUES("&variable1&comma&variable2&")"
>
> If you were to use substitution in a query, you would have to put quotes
> around the contents of variable1 and variable2 before you passed them or
> they wouldn't go at all (and if there are quotes, etc. in those containers,
> you're really hosed).
>
> If you use parameterized query, and variable1 is
> "Hello, my friend," he said.
>
> Then the value of the field in the table will be
> "Hello, my friend," he said.
>
> When you retrieve the value, you'll get it as-is.  When you perform a query
> against the table, you would send the literal string that you are looking
> for, also with parameters, including the quotes, semicolons, etc.
>
>
>
> On Thu, May 19, 2016 at 11:41 AM, Paul Dupuis <paul at researchware.com>
> wrote:
>
> > On 5/19/2016 10:41 AM, Mike Kerner wrote:
> > > Remember the conversations about handling CSV?  All I will tell you
> from
> > > escaping data for SQL is...good luck.  I accidentally discovered SQL
> > > injection (as did everyone else, I'm sure) 30 years ago when C/S was
> just
> > > getting legs.  Since then, we have yet to come up with an escaping
> scheme
> > > that doesn't break down, eventually.  In simple or special cases, you
> > will
> > > be able to make it work, but understand going in that someone is going
> to
> > > break it for you.  When they do, hopefully they don't
> > > accidentally/intentionally break the rest of your system.  When
> possible,
> > > use parameterized queries, instead.
> > >
> >
> > To be more precise, several fields in a table are TEXT and the intended
> > data is large amounts of free form text (contents of documents). There
> > is no concern in this context of SQL injection by altering the contents
> > of the target documents. The concern is that the documents - being
> > documents - contain tabs and end of line characters and single quotes
> > and double quotes and any other type-able character you may find in the
> > English language.
> >
> > Mike and Peter: So you're both saying that if I use either:
> >
> > revExecuteSQL myID, "insert into mytable values(:1,:2)",
> > "Variable1","Variable2"
> > OR
> > revExecuteSQL myID,"insert into mytable values(:1,:2)","myArray"
> > where myArray[1] has the content for the first column and myArray[2] for
> > teh second column in the table
> >
> > That the revExecuteSQL command will escape the data in Variable1 or
> > myArray[1] even if it contains returns and tabs and single and double
> > quotes and so on?
> >
> > I had though that it just did substitution. I.e. it effectively just
> > replaced the :1 in the query with the contents of the variable, so for a
> > VarChar, Char, or Text column in the database, Variable1 would have to
> > contain 'some text' in single quotes. I would be happy if my assumption
> > is completely wrong.
> >
> >
> >
> > _______________________________________________
> > 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
> >
>
>
>
> --
> On the first day, God created the heavens and the Earth
> On the second day, God created the oceans.
> On the third day, God put the animals on hold for a few hours,
>    and did a little diving.
> And God said, "This is good."
> _______________________________________________
> 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
>