2014-10-09 2:38 GMT+02:00 Alok Menghrajani <[email protected]>:
>
>
> On Tue, Oct 7, 2014 at 11:23 PM, Lukas Eder <[email protected]> wrote:
>
>>
>>
>> 2014-10-07 19:49 GMT+02:00 Alok Menghrajani <[email protected]>:
>>
>>> > It looks like the setting is only exposed if it is active. The
>>> variable can
>>> > be obtained from SHOW VARIABLES LIKE 'sql_mode', or via SELECT
>>> > @@SESSION.sql_mode FROM DUAL. I suspect that if we support an AUTO
>>> mode, we
>>> > would need to check this flag prior to query execution (lazily, only
>>> the
>>> > first time we actually encounter an inlined string variable).
>>>
>>> batch queries also end up needing this. Do you know what other queries
>>> or features indirectly inline?
>>>
>>
>> I'm not sure what you mean...? How do batch queries need this?
>>
>>
>
> Sorry, I wasn't very clear. Given the following table:
>
> create table jooqtest(title varchar(255), primary key(title));
> insert into jooqtest (title) values ("hello"),("world"),("foo"),("bar");
> And a batch query:
>
> create.batch(
> create.update(table("jooqtest")).set(field("title"),
> value("aaa")).where(field("title").eq("something\\' or 1=1 ORDER BY title
> DESC LIMIT 1-- ")),
> create.update(table("jooqtest")).set(field("title"),
> value("bbb")).where(field("title").eq("whatever")))
> .execute();
>
> In theory, the batch query shouldn't do anything. However, the batch
> request is prone to SQLi and one of the rows (in this case "hello" which is
> the first row) gets overwritten with "aaa".
>
I see, you're probably right, there is an issue. Nice catch! I'm not aware
of how MySQL serialises static batch statements to the server. Will the
trailing comment ("--") really result in the subsequent statements to be
commented out? But even if it wasn't you could probably also just write:
"something\\'; drop database bobby_tables; --"
> I'm assuming the batch feature internally calls inline.
>
Yes, DSLContext.renderInlined() is used to generate the SQL. This will then
inline all bind values.
> My question was: are there other types of queries or other jOOQ features
> which call inline?
>
There are equals() and hashCode(), which are not security-relevant, and the
new Result.formatInsert(), which generates a set of INSERT statements from
a Result. There are probably a couple of other places, where inlining is
performed because the server doesn't accept bind values in that particular
place - e.g.
Unproblematic, because inlining numbers:
- DB2's / Ingres's OFFSET .. FETCH FIRST .. ROWS ONLY clause
- Informix's SKIP .. FIRST .. clause
- SQL Servers's Sybase's TOP .. START AT .. clause
Possibly problematic:
- If the one of the following (dialect: max number of bind variables per
statement) thresholds is surpassed, we inline ALL bind values: (MS Access:
768), (Sybase ASE: 2000), (Ingres: 1024), (SQL Server: 2100), (SQLite:
999). Details here: https://github.com/jOOQ/jOOQ/issues/1520 and here:
http://blog.jooq.org/2013/04/28/rare-uses-of-a-controlflowexception/
- The org.jooq.impl.BatchMultipe use-case that you've discovered
On a quick glance, these are the only cases where inlining is enforced -
unless of course, you enforce it yourself via Settings.statementType or via
explicit inlining
> Yes, this is why we usually keep a section for incompatible changes in the
>> release notes. This change will not be shipped with patch releases, only
>> with a minor release.
>>
>> From how I've perceived the discussion between Morgan Tocker and Bill
>> Karwin (on the previously linked blog article by Morgan), I suspect that
>> few people actually turned that flag on. So the risk / benefit ratio is
>> probably in favour of breaking compatibility this time - in particular
>> given that the setNString() method that you've pointed out to me also
>> assumes that the flag is not set.
>>
>
> :) for favoring security over backwards compatibility!
>
Well, with jOOQ, we still break a bit less clients than MySQL would :)
Compare:
- http://stackoverflow.com/questions/tagged/mysql (~300k questions)
- http://stackoverflow.com/questions/tagged/jooq (~300 questions)
--
You received this message because you are subscribed to the Google Groups "jOOQ
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
