postgres escape characters

Question

I am new to postgres, how can I escape the RTF syntax in the MESSAGE parameter of the where clause?

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

This is the code I am executing to get the result set from 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;

Answer 1

On PostgreSQL 9.3 or newer and haven't manually changed the standard_conforming_strings setting, you don't have to escape anything except single quotes, which you double like this:

'this is a single quote: ''. '

If you're on an older version, set standard_conforming_strings to on in postgresql.conf, or double backslashes and use the E'' string notation:

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 statements that take care of escaping automatically. Failure to do so can lead to SQL injection holes - see this site.

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.