postgresql postgres 转义字符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14748135/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
postgres escape characters
提问by ChampChris
I am new to postgres, how can I escape the RTF syntax in the MESSAGEparameter of the where clause?
我是 postgres 的新手，如何在 where 子句的MESSAGE参数中转义 RTF 语法？
select count(*)
from communicationoutgoingmessagescheduledetails
where email='[email protected]'
and message='{\rtf1\deff0{\fonttbl{\f0 Times New Roman;}}{\colortbl\red0\green0\blue0 ;\red0\green0\blue255 ;}{\*\listoverridetable}{\stylesheet {\ql\cf0 Normal;}{\*\cs1\cf0 Default Paragraph Font;}{\*\cs2\sbasedon1\cf0 Line Number;}{\*\cs3\ul\cf1 Hyperlink;}}\splytwnine\sectd\pard\plain\ql{\cf0 first }{\b\cf0 paymen}{\b\cf0 t bold }{\cf0 rem}{\cf0 inder}\par\pard\plain\ql{\ul\cf0 se}{\ul\cf0 cond PAYMENT }{\b\ul\cf0 reminder}\b\ul\par}'
and succeddful=-1
and senttime::timestamp::date='2/7/2013 12:00:00 AM'
Update
更新
this is the error I am getting
这是我得到的错误
WARNING: nonstandard use of escape in a string literal LINE 4: and message='{\rtf1\deff0{\fonttbl{\f0 Times New Roman;}}{\c...
HINT: Use the escape string syntax for escapes, e.g., E'\r\n'.
ERROR: invalid Unicode escape LINE 4: and message='{\rtf1\deff0{\fonttbl{\f0 Times New Roman;}}{\c...
HINT: Unicode escapes must be \uXXXX or \UXXXXXXXX.
********** Error **********
ERROR: invalid Unicode escape SQL state: 22025 Hint: Unicode escapes must be \uXXXX or \UXXXXXXXX. Character: 124
Update 2
更新 2
This is the code I am executing to get the result set from PostgreSQL:
这是我正在执行以从 PostgreSQL 获取结果集的代码：
string sql = "select count(*) from communicationoutgoingmessagescheduledetails " +
"where email='" + email + "' ";
if (message != string.Empty)
sql += String.Format("and message='{0}' ", message);
if (subject != string.Empty)
sql += String.Format("and subject='{0}' ", subject);
sql += "and successful=true " +
"and senttime::timestamp::date='" + date.Date + "'";
System.Data.IDbConnection connection = ORGen.Access.Config.Instance.Connection;
IDbCommand command = connection.CreateCommand();
connection.Open();
command.CommandText = sql;
bool retVal = Convert.ToInt16(command.ExecuteScalar()) > 0 ? true : false;
connection.Close();
command = null;
connection = null;
回答by Craig Ringer
On PostgreSQL 9.3 or newer and haven't manually changed the standard_conforming_stringssetting, you don't have to escape anything except single quotes, which you double like this:
在 PostgreSQL 9.3 或更新版本上并且没有手动更改standard_conforming_strings设置，除了单引号外，您不必转义任何内容，您可以像这样加倍：
'this is a single quote: ''. '
If you're on an older version, set standard_conforming_stringsto onin postgresql.conf, or double backslashes and use the E''string notation:
如果您使用的是旧版本，请设置standard_conforming_strings为oninpostgresql.conf或双反斜杠并使用E''字符串表示法：
E'{\rtf1'
This is all explained in the lexical structure section of the user manual.
这在用户手册的词法结构部分都有解释。
However, you should really be using your language's PostgreSQL client driver to send parameterized statementsthat take care of escaping automatically. Failure to do so can lead to SQL injectionholes - see this site.
但是，您确实应该使用您的语言的 PostgreSQL 客户端驱动程序来发送参数化语句，这些语句负责自动转义。不这样做可能会导致SQL 注入漏洞 - 请参阅此站点。
Since you are using C# with npgsql, you probably want http://bobby-tables.com/csharp.html. More detail is given in the npgsql user manual.
由于您将 C# 与 npgsql 一起使用，您可能需要http://bobby-tables.com/csharp.html。npgsql 用户手册中提供了更多详细信息。
