We had an interesting issue crop up this past week. The question was, "How do we properly escape the following string...?". The string was:

You can't have it that way can you?

That seems like a pretty simple string right? On insert you would do one of the following:

   (E'You can\'t have it that way can you?');
   ($$You can't have it that way can you?$$);
   ('You can''t have it that way can you?');

You would think that would be the end of it. However, If you are using ODBC with a pass through query you will receive the error, "The # of binded parameters is < than the # of parameter markers." Yes, that's right. ODBC will parse the ? and interpret it as a parameter. This affects psqlodbc and ODBCng. Apparently it is actually not a bug [1]. I am not sure I agree with that, regardless of what Microsoft says. What is particularly interesting here is that it is specifically the ? that is the problem. Not the single quote. To work around this problem you can execute the query like this:

INSERT INTO foo VALUES ($$You can't have it that way can you?$$);
INSERT INTO foo VALUES ('You can''t have it that way can you?');

Of course neither of those are actually standard (the E'\'' is standard). 1. MSDN Data Platform developer center