Re: Patch applied for SQL Injection vulnerability for setObject(int,Object,int)

Fernando Nasser wrote:
> Barry Lind wrote:
>
>> Fernando,
>>
>>
>> Fernando Nasser wrote:
>>
>>> What if my string (which is a string, not a list) contains the
>>> characters "('a1', 'b2', 'c3')"?   How do I set my parameter to such
>>> a string with setObject?
>>
>>
>>
>> OK, now I understand your question.  This will still work, just like
>> it always has.  The single quotes will be escaped before sending them
>> to the backend and the result will be what you would expect.
>>
>> So if the query was: insert into foo (bar) values (?)
>>
>> stmt.setObject(1, "('a1', 'b2', 'c3')", Types.VARCHAR);
>>
>> would result in the following statement sent to the server:
>>
>> insert into foo (bar) values ('(\'a1\', \'b2\', \'c3\')')
>>
>> which will result in the value ('a1', 'b2', 'c3') being inserted.
>>
>
> OK, so far so good.  And my other question is:
>
> Can we pass a set of strings?

No.

>
> stmt.setObject(1, "('a1', 'b2', 'c3')", Types.VARCHAR);
>
> will result into:
>
> ... where ... in (\'a1\', \'b2\', \'c3\') ...
The actual result will be:
... where ... in '(\'a1\', \'b2\', \'c3\')' ...

which isn't valid sql syntax

if you used stmt.setObject(1, "('a1', 'b2', 'c3')", Types.NUMERIC);
you would get:
... where ... in (\'a1\', \'b2\', \'c3\') ...

which is somewhat closer but still not valid sql as the backend needs
unescaped single quotes.  Basically there isn't a way to get unescaped
single quotes through to the backend which is required for what you are
trying to do.  And the reason there isn't a way to get unescaped single
quotes through is that would allow a SQL injection attack.

thanks,
--Barry