0

When I am trying to find all records in details table which have Linux present in the array column operating_systems.

Query select * from details where 'Linux' = ANY(operating_systems); works perfectly and returns all records which have Linux in operating_systems column.

But if I want to get all records where I don't know is present in operating_systems I am unable to form a correct query.

Query select * from details where 'I don\'t know' = ANY(operating_systems); does not escape single quotes and takes it literally, resulting in an incomplete query.

abhishek77in
  • 1,848
  • 21
  • 41

2 Answers2

2

Found that single quotes can be escaped by adding another single quote before it. So select count(*) from details where 'I don''t know' = ANY(operating_systems); works.

This is acceptable for ad-hoc queries, or queries where a data literal is hard coded.

It's absolutely not OK if the string comes from an application user. Do not do this. See http://bobby-tables.com/ for why.

Use parameterised queries (often incorrectly called prepared statements, though they're not quite the same thing). Exactly how to do that depends on your programming language and client, which you have not mentioned, but it usually involves "preparing" a statement then executing it with parameters.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
1

Found that single quotes can be escaped by adding another single quote before it. So select count(*) from details where 'I don''t know' = ANY(operating_systems); works.

abhishek77in
  • 1,848
  • 21
  • 41
  • http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS –  Apr 17 '15 at 12:19