Storing and retrieving data from a SQL database

[Paul Dupuis]

Thank you. I'll try switching my code over to using query parameters.


On 5/19/2016 12:01 PM, Mike Kerner 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
>>
>
>