查询Postgres数据库(Querying Postgres database)

查询Postgres数据库(Querying Postgres database)

我想查询一个列匹配字符串的数据库，这个字符串有一个单引号字符'。 我无法运行以下查询

SELECT * from <table> where <column>='robocopy'w (ithout)backup.pcap';

原因是单引号用于搜索列; 我试过双引号但没有帮助。 如果我在字符串中的单个引号之前添加额外的单引号，我可以通过它。

SELECT * from <table> where <column>='robocopy''w (ithout)backup.pcap';

但我需要一个更好的解决方案，因为每次添加单引号是不切实际的，特别是如果它们很长并且有很多单引号。

I want to query a database where a column matches a string & this string is having a single quote character '. I am unable to run the following query

SELECT * from <table> where <column>='robocopy'w (ithout)backup.pcap';

The reason being that single quotes is used to search the column; I tried double quotes but no help. I can by pass this if I add a extra single quote just before the single in a string.

SELECT * from <table> where <column>='robocopy''w (ithout)backup.pcap';

But I need a better solution as it is not practical to add a single quote every time,specially if they are long and have many single quotes.

最满意答案

在将查询字符串发送到数据库之前，需要转义它。 我无法告诉你如何因为你没有发布代码，但如果你直接在psql控制台中运行这个查询，你可以通过将所有约束放在$$和$$中来转义引号，如文档中所引用的那样[1]。 例：

SELECT * from <table> where <column> = $$robocopy'w (ithout)backup.pcap$$;

[1] http://www.postgresql.org/docs/current/interactive/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING

You need to escape your query string before sending it to the database. I can't show you how because you didn't post the code, but if you are running this query directly in the psql console, you can escape the quotes by putting all constraint inside $$ and $$, as cited in the documentation[1]. Example:

SELECT * from <table> where <column> = $$robocopy'w (ithout)backup.pcap$$;

[1] http://www.postgresql.org/docs/current/interactive/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING