How to replace single quote in Java with Postgres?

Question

select * from where id in ('<45646300.KDSFJJSKJSDF95'fdgdfgdfgd>', 'fdgdfgdg');

I always use params like

select * from where id = ?;

But in this case i have problem, where i have 'in' statement with string passed to it.

I wish to replace all dangerous chars

Better to provide a table name. :)

user unknown
– user unknown

2011-04-21 10:30:26 +00:00
Commented Apr 21, 2011 at 10:30 — user unknown
– user unknown, Commented Apr 21, 2011 at 10:30

axtavt · Accepted Answer · 2011-04-21 10:39:22Z

3

It would be better to continue using PreparedStatements rather than to escape characters manually.

In the case of IN clause you can generate a query with appropriate number of ?s dynamically.

String[] input = ...;

StringBuilder b = new StringBuilder();
b.append("select * from where id in (");
b.append("?"); // Assume that input contains at least one element
for (int i = 1; i < input.length; i++) b.append(", ?");
b.append(")");

PreparedStatement s = c.prepareStatement(b.toString());

for (int i = 0; i < input.length; i++) s.setString(i + 1, input[i]);

edited Apr 21, 2011 at 10:39

answered Apr 21, 2011 at 10:24

axtavt

243k42 gold badges518 silver badges486 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

TerriJordan · Accepted Answer · 2011-04-21 10:25:40Z

1

Apache commons API provides multiples ways to remove dangerous chars for specific languages such as CSS, Javascript SQL, etc...

Take a look at this if it helps : http://commons.apache.org/lang/api-2.4/org/apache/commons/lang/StringEscapeUtils.html

answered Apr 21, 2011 at 10:25

TerriJordan

434 bronze badges

Comments

user330315 · Accepted Answer · 2011-04-21 10:26:27Z

1

Use the standard SQL quoting for single quotes:

select * 
from the_table
where id in ('<45646300.KDSFJJSKJSDF95''fdgdfgdfgd>', 'fdgdfgdg');

So any embedded single quote needs to be written twice.

answered Apr 21, 2011 at 10:26

user330315

Comments

Adnan · Accepted Answer · 2011-04-21 10:19:49Z

0

Did you try?

select * from TABLE_NAME where id in (?);

answered Apr 21, 2011 at 10:19

Adnan

26.4k18 gold badges83 silver badges112 bronze badges

2 Comments

userbb Over a year ago

Yes. But i have array or string delimited by ','. String param = "'aaa', 'bbb', 'ccc'". I dont know how to pass it

Adnan Over a year ago

like this: prepStat.setString(1, myString);

tim_yates · Accepted Answer · 2011-04-21 10:24:32Z

0

There's a page explaining the different options over here on the javaranch site

answered Apr 21, 2011 at 10:24

tim_yates

172k29 gold badges359 silver badges354 bronze badges

Collectives™ on Stack Overflow

How to replace single quote in Java with Postgres?

5 Answers 5

Comments

Comments

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related