# Escaping data madness

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

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](<http://msdn.microsoft.com/en-us/library/ms713534\(VS.85\).aspx>)

---
[View this page online](https://www.commandprompt.com/blog/escaping_data_madness/)