Storing and retrieving data from a SQL database

[Mike Kerner]

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."