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 . 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