Round bracket in string with JDBC prepared statement

Question

Here is my Java JDBC code (modified and simplified for example):

ps = connection.prepareStatement("SELECT a,b,c FROM mytable WHERE category ~ ?");
ps.setString(1, "my/super/category/abc(def");
                                      ^
                                      |
    +---------------------------------+
    |
//this character is problem
result = ps.executeQuery();

It didn't work because of round bracket in string.

How to escape round brackets in prepared statement?

EDIT: based on my answer (see below) I do correct to question.

Do you have any error message? Since it is a prepared statement, it should allow any string in my opinion (they will be escaped if needed). — Bastien Jansen, Jan 26 '13 at 15:18
Are you sure this doesn't work? The whole point of using parameters is to have built-in, bullet-proof escaping. — Tomasz Nurkiewicz, Jan 26 '13 at 15:18
That means there are no real records present. can you execute the actual query on database and see if any rows are returned ? — Subba, Jan 26 '13 at 15:22
@Subba yes, I was testing SQL on console (using PostgreSQL and PGAdmin3), round bracket need to be escaped: SELECT... category="my/super/category/abc\\\(def" and it works - will return expected records. — 1ac0, Jan 26 '13 at 15:24
A parenthese doesn't need to be escaped in SQL. Only single quotes need to be. And strings should be surrounded by single quotes, not double quotes. — JB Nizet, Jan 26 '13 at 15:31
@LadislavDANKO: you definitely do **not** need to "escape" a round bracket: http://www.sqlfiddle.com/#!12/1703d/1 — , Jan 26 '13 at 15:32
@JBNizet my problem isn't parenthese but round bracket - see my original post — 1ac0, Jan 26 '13 at 15:35
@LadislavDANKO *parentheses* is just the English for "round brackets". — Anton Kovalenko, Jan 26 '13 at 15:36
Sorry, it's called *parenthesis* and not *parenthese* in English. And it is a round bracket. I read your question. See http://en.wikipedia.org/wiki/Parenthese#Parentheses_.28_.29 — JB Nizet, Jan 26 '13 at 15:37
@JBNizet see now. back to subject: don't agree, it won't work. try and you will see. need somethink to make it work. — 1ac0, Jan 26 '13 at 15:41
@LadislavDANKO :i supposed that the problem in "(" , but i try it by myself and it working with me , so make sure that you have this **"my/super/category/abc(def"** in ***catageory*** coulmn — Alya'a Gamal, Jan 26 '13 at 15:52
I tried, and I saw. It works perfectly: `select f.* from mytable f where f.id = '12345('`. — JB Nizet, Jan 26 '13 at 16:04
@LadislavDANKO: see link to the example in SQLFiddle in my comment. You do ***not*** need to escape round brackets! Neither in a string literal nor with parameters. — , Jan 26 '13 at 16:17
@LadislavDANKO If this "isn't working", then something's wrong; there's zero need to do anything other than what you've done. What does "not working" mean, precisely? If there's a SQL error you'll get an exception. Are you swallowing any exceptions? Is your logging set to DEBUG level? Have you over-simplified to the point of making the example irrelevant? — Dave Newton, Jan 26 '13 at 16:23
have it! it's my typo in config file (where i store sql commands): select * from mytable where category = 'my/super/category(blabla' ->works select * from mytable where category ~ 'my/super/category(blabla' ->won't work select * from mytable where category ~ 'my/super/category\\(blabla' ->works it's difference in "=" and "~" in sql command, in fact it's difference between standard sql equal sign and sql pattern matching.. thanks, guys :-) — 1ac0, Jan 26 '13 at 17:49

1ac0 · Accepted Answer · 2013-06-29T11:06:41.190

Will answer myself - problem is in "~" (tilde mark).

After some elaboration there is interesting finding:

When SQL code is this (see "equal" mark in SQL code):

ps = connection.prepareStatement("SELECT a,b,c FROM mytable WHERE category = ?");

escaping is not needed. But when SQL code is this (see "tilde" mark in SQL code):

ps = connection.prepareStatement("SELECT a,b,c FROM mytable WHERE category ~ ?");

you need to do escaping if there are special character, in this case "(" or ")":

ps.setString(1, "super/category/abc(def".replaceAll("\\(", "\\\\(")));

It is because pattern matching: PostgreSQL Pattern Matching because with tilde mark JDBC driver don't know if round bracket is normal character (as in my case) or grouping symbol for pattern matching which group items into one logical item.

score 0 · Answer 2 · answered Jan 26 '13 at 16:03

0

You can escape round brackets here by putting them inside single quotes.

answered Jan 26 '13 at 16:03

Harshit Shrivastava

11
4

Roman C · Answer 3 · 2013-01-26T21:35:33.267

0

I thought the problem was the query didn't return the result because it required to set the escape character before parenthesis, it could be done via

ps.setString(1, "my/super/category/abc\\(def");

The SQL syntax allows to have escape characters in the string. In the Java you cannot escape parenthesises in the string.

edited Jan 26 '13 at 21:35

answered Jan 26 '13 at 16:19

Roman C

49,761
33
66
176

if your sql is with standard equal sign like "select ... where blabla = ?" and you do it this way, not needed escape. if your sql is like "select ... where blabla ~ ?" you definitely need to do: ps.setString(1, "category/abc\\(def".replaceAll("\\(", "\\\\(")); – 1ac0 Jan 26 '13 at 17:57
There is absolutely ***not*** reason to escape parenthesises in JDBC nor in SQL. – Jan 26 '13 at 18:37
@LadislavDANKO Could you specify what SQL and what to do as for your statement didn't compile. – Roman C Jan 26 '13 at 20:53
@a_horse_with_no_name I don't know what a reason but it's just not possible in Java. What I did is just escaped escape char to pass as value. – Roman C Jan 26 '13 at 21:39
You don't need to escape the `(` character. Neither in Java nor in SQL (as a matter of fact I think it will produce a wrong results). Your answer is plain wrong. – Jan 26 '13 at 22:57

Round bracket in string with JDBC prepared statement

3 Answers3

Linked