Storing and retrieving data from a SQL database

[Paul Dupuis]

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.