Storing and retrieving data from a SQL database
paul at researchware.com
Thu May 19 18:20:04 CEST 2016
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
>>> 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)",
>> revExecuteSQL myID,"insert into mytable values(:1,:2)","myArray"
>> where myArray has the content for the first column and myArray for
>> teh second column in the table
>> That the revExecuteSQL command will escape the data in Variable1 or
>> myArray 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:
More information about the use-livecode