How to escape single-quotes in Query Filter

问题:

Using QBO V3 API and I'm trying to query for Customers with an apostrophe in their names but I cannot figure out how to properly quote the name.

I have used the API Explorer and when I enter a query like this:

select * from customer where displayname like '%O\'Halloran'

Then the API Explorer works and says the request-uri is really:

https://qb.sbfinance.intuit.com/v3/company/1002341430/query?query=select * from customer where displayname like '%25O%5C'Halloran'

Notice a couple of things in my query:

1. I have to use single-quotes around the value
2. I have to escape the single-quote myself.

But then notice in the URI that API Explorer gives back to me: it keeps the single-quote wrapper string but then does NOT escape the single quote inside the value.

If I turn around and use the exact request URI that the API Explorer is using - it does NOT work. I get a query filter exception. So clearly what API Explorer is showing me is not the real query.

Since none of this is documented I'm left trying to reverse-engineer what the incantation is but I cannot determine it. I've looked at the PHP SDK but its not addressed there either, they assume the callers (like us) will know what to do themselves.

回答1:

You need to escape it with \, and then also URL encode the actual URL request you're sending.

The actual URL you're hitting (for a customer query of LIKE '%Keith O'Malley%' should be something like:

https://quickbooks.api.intuit.com/v3/company/730176505/query?query=SELECT+%2A+FROM+Customer+WHERE+FullyQualifiedName+LIKE+%27%25Keith+O%5C%27Mally%25%27+ 

With a full HTTP request looking like:

GET https://quickbooks.api.intuit.com/v3/company/730176505/query?query=SELECT+%2A+FROM+Customer+WHERE+FullyQualifiedName+LIKE+%27%25Keith+O%5C%27Mally%25%27+ HTTP/1.1
Content-Type: text/plain
Authorization: OAuth realm="", oauth_signature_method="HMAC-SHA1", oauth_signature="C9mzgdURjATMEymtAySNe%2BYBW7Q%3D",  oauth_nonce="3XDdP", oauth_timestamp="1394038522", oauth_token="lvprdgeo8rx8r1Ul10Bf474KjHEjcLFSVYttLJjcR2pFtIiD", oauth_consumer_key="qyprdlGJ4gWv4sMW0syilH2o4KirQe", oauth_version="1.0"

If you're not committed to using the Intuit DevKit that doesn't escape things properly, there's an open-source QuickBooks PHP DevKit available which handles these things correctly:

• https://github.com/consolibyte/quickbooks-php

Here's an example of specifically what you're looking for:

• https://github.com/consolibyte/quickbooks-php/blob/master/docs/example_app_ipp_v3/example_escaped_customer_query.php

Along with a pile of other working examples:

• https://github.com/consolibyte/quickbooks-php/blob/master/docs/example_app_ipp_v3/

回答2:

Please use an escape character( \ ) for single quote(I think you're already doing it in your PHP code). There should be some libs(or functions) in PHP which do this url encoding.

You can refer this standard URL encoding guideline as well. http://www.w3schools.com/tags/ref_urlencode.asp

Devkit handles most of these encoding. https://developer.intuit.com/docs/0025_quickbooksapi/0055_devkits

Thanks

回答3:

I had success with simply replacing the single quote char with the URL encoding : %27

As in: query=SELECT%20Id%2CDisplayName%2CSyncToken%20FROM%20Customer%20WHERE%20Id%3E%270%27%20MAXRESULTS%201000

FYI, I too noticed that the API Console and API Explorer were forgiving (almost to a fault!) and will accept 'wider syntax variety' than real calls.

回答4:

If you are using PHP or Ruby API to create the call then you should escape both \ character and ' character in the string you create

For example with the Quickbooks Ruby API

find_by("CompanyName", "Bill\\\'s Windsurf Shop") 

produces the correct query to search for Bill's Windsurg Shop