× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Joe,

Thanks. I think that answered the question in my previous reply. Good
explanations from all.

Jerry C. Adams
IBM i Programmer/Analyst
Some days you win, some days you lose. Today I'll settle for a tie.
--
A&K Wholesale
Murfreesboro, TN
615-867-5070


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Joe Pluta
Sent: Monday, August 01, 2011 9:20 AM
To: RPG programming on the IBM i / System i
Subject: Re: Reduce large amount of logicals in SUBFL pgm, take in another
direction

On 8/1/2011 8:21 AM, Jerry C. Adams wrote:
I hate to sound like an idiot (which I am as regards SQL), but I've read
these statements before about SQL injection and even googled it.

What I don't understand is why building the SELECT string with '+
UserEnteredValue' is more liable to injection that using parameters. That
is, couldn't the parameters be compromised as well?

Just asking.

Nothing idiotic about it. As people have been trying to explain (but
it's difficult!), the issue has to do with how the statement is parsed.
Most specifically, it has to do with the semicolon, which in an
interpreted SQL denotes the end of one statement and the beginning of
another. So look at this:

update mytable set description = "?" where key = "value"

In an interpreted statement, the user can replace the ? with a carefully
crafted set of character that looks like this:

"; drop table myTable;

The final SQL statement to be executed will look like this:

update mytable set description = ""; drop table myTable;" where key =
"value"

The first double-quote in the substituted value closes the quoted
string, and thus the semicolon is treated as a statement break. This
will execute the statement { update mytable set description = "" }
(which will incidentally update every row because there's no where
clause), then execute the statement { drop table myTable } which will
delete myTable, and then attempt to execute { " where key = "value" }
which will fail, but the damage has already been done.

With a prepared statement with parameters, the ? is replaced with an
escaped version of the string, and looks like this (effectively):

update mytable set description = "\"; drop table myTable;" where key =
"value"

Notice the backslash? This means the double-quote does NOT close the
string and so the semicolon is not treated as a statement break and the
statement executes as YOU intended, not as the hacker intended. Of
course, the description field will have some nonsense in it, but that's
okay, the SQL is not executing arbitrary code.

A simple way around this, BTW, is to code you own routine that escapes
the data entered by the user (that is, checks for double-quotes and puts
the backslash in), and then use the escaped data in your concatenation,
but that's not always practical and the problem is avoided with prepared
statements.

On the other hand, dynamic SQL statements can be too complex for
prepared statements, and that's when the escaping mechanism can be useful.

Hope this helps a little.

Joe


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.