Thread: rounded brackets in prepared statement

Hi folks,

my setup: Java 1.6, JDBC PostgreSQL JDBC4 driver 9.1-903.

Why when I do:

PreparedStatement ps = myConnection.prepareStatement("SELECT a,b,c FROM mytable WHERE category ~ ?");
ps.setString(1, "my/super/category/a(bcdef");
result = ps.executeQuery();

I need to escape rounded bracket in setString in this way:
ps.setString(1, "super/category/a(bcdef".replaceAll("\\(", "\\\\(")));

I think it has somethink to do with regular expessions but ot know much more about it.

--
With kind regards,

Ladislav DANKO

It appears that since the argument to your prepare statement is a WHERE
operation and you are using the ~, Match Regular Expression Case
Sensitive, operator then the input is deemed as such. So parenthesis
are valid constructs in these cases.

Example from Documentation: POSIX Regular Expression Patterns
Chapter 9. Functions & Operators

'abc' ~ '(b|d)' true
'abc' ~ '(^(b|c)' false

danap.

Ladislav DANKO wrote:
> Hi folks,
>
> my setup: Java 1.6, JDBC PostgreSQL JDBC4 driver 9.1-903.
>
> Why when I do:
>
> PreparedStatement ps = myConnection.prepareStatement("SELECT a,b,c FROM
> mytable WHERE category ~ ?");
> ps.setString(1, "my/super/category/a(bcdef");
> result = ps.executeQuery();
>
> I need to escape rounded bracket in setString in this way:
> ps.setString(1, "super/category/a(bcdef".replaceAll("\\(", "\\\\(")));
>
> I think it has somethink to do with regular expessions but ot know much
> more about it.
>
> --
> With kind regards,
>
> Ladislav DANKO

so JDBC driver is detecting matching type and if there's "=" (equal) then it's
doing escaping and if there is "~" (regular expression pattern) the i need to do
escaping by hand?

laco



On 13.2.2013 3:32, dmp wrote:
> It appears that since the argument to your prepare statement is a WHERE
> operation and you are using the ~, Match Regular Expression Case
> Sensitive, operator then the input is deemed as such. So parenthesis
> are valid constructs in these cases.
>
> Example from Documentation: POSIX Regular Expression Patterns
> Chapter 9. Functions & Operators
>
> 'abc' ~ '(b|d)' true
> 'abc' ~ '(^(b|c)' false
>
> danap.
>
> Ladislav DANKO wrote:
>> Hi folks,
>>
>> my setup: Java 1.6, JDBC PostgreSQL JDBC4 driver 9.1-903.
>>
>> Why when I do:
>>
>> PreparedStatement ps = myConnection.prepareStatement("SELECT a,b,c FROM
>> mytable WHERE category ~ ?");
>> ps.setString(1, "my/super/category/a(bcdef");
>> result = ps.executeQuery();
>>
>> I need to escape rounded bracket in setString in this way:
>> ps.setString(1, "super/category/a(bcdef".replaceAll("\\(", "\\\\(")));
>>
>> I think it has somethink to do with regular expessions but ot know much
>> more about it.
>>
>> --
>> With kind regards,
>>
>> Ladislav DANKO
>

Hello laco,

I would read futher in the documentation, but a little experiementing
will results.

Given column content: 'the(ishere'

col LIKE '%(%' (OK)
col='the(ishere' (OK)
col~'the(ishere' (FAILS)

So:

(=) - matches exact string input
(LIKE) - same as equal, but can use wild card character, etc, ie %
(~) - Throws the PQLException: Error: invalid regular expression:
       parentheses () not balanced

It not a matter of escaping. Certain operators, {=, LIKE, ?, ?, ... }
use the given input string of characters and do matching directly
without using Regular Expession Patterns looks like. Where as
{~, ?, ?, ... } interepret the input as being a Regular Expression
Pattern.

danap.

Ladislav DANKO wrote:
> so JDBC driver is detecting matching type and if there's "=" (equal)
> then it's
> doing escaping and if there is "~" (regular expression pattern) the i
> need to do
> escaping by hand?
>
> laco
>
>
>
> On 13.2.2013 3:32, dmp wrote:
>> It appears that since the argument to your prepare statement is a WHERE
>> operation and you are using the ~, Match Regular Expression Case
>> Sensitive, operator then the input is deemed as such. So parenthesis
>> are valid constructs in these cases.
>>
>> Example from Documentation: POSIX Regular Expression Patterns
>> Chapter 9. Functions & Operators
>>
>> 'abc' ~ '(b|d)' true
>> 'abc' ~ '(^(b|c)' false
>>
>> danap.
>>
>> Ladislav DANKO wrote:
>>> Hi folks,
>>>
>>> my setup: Java 1.6, JDBC PostgreSQL JDBC4 driver 9.1-903.
>>>
>>> Why when I do:
>>>
>>> PreparedStatement ps = myConnection.prepareStatement("SELECT a,b,c FROM
>>> mytable WHERE category ~ ?");
>>> ps.setString(1, "my/super/category/a(bcdef");
>>> result = ps.executeQuery();
>>>
>>> I need to escape rounded bracket in setString in this way:
>>> ps.setString(1, "super/category/a(bcdef".replaceAll("\\(", "\\\\(")));
>>>
>>> I think it has somethink to do with regular expessions but ot know much
>>> more about it.
>>>
>>> --
>>> With kind regards,
>>>
>>> Ladislav DANKO