Originally asked by Harold Oconitrillo on 07 November 2023 (original question)
We receive the below error in Salesforce when trying to Sync from Salesforce to JIRA:
Bad response when GET to /services/data/v54.0/query/?q=SELECT+Global_Industry_c%2cCompany_Revenue_c+FROM+Account+WHERE+Name=%27Coeur d' Alene Tribe%27 - 400 [{"message":"
nWHERE Name='Coeur d' Alene Tribe'
n ^
nERROR at Row:1:Column:91
nline 1:91 mismatched character '' expecting '''","errorCode":"MALFORMED_QUERY"}]
Answer by Sherine on 01 December 2023
We removed the Single quote from the Account name but again we received error for another Account name. Why Exalate is not supporting special characters? Is there any way to resolve this issue.
Bad response when GET to /services/data/v54.0/query/?q=SELECT+Global_Industry__c%2cCompany_Revenue__c+FROM+Account+WHERE+Name=%27FARO Consulting & Solutions, LLC.%27 - 400 [{“message”:“\nAccount WHERE Name=‘FARO Consulting \n ^\nERROR at Row:1:Column:85\nline 1:85 mismatched character ‘’ expecting ‘’’”,“errorCode”:“MALFORMED_QUERY”}]
Could someone please look into this issue?
Hello Sherine,
The error you are encountering is due to a “Malformed Query”. This is commonly caused by incorrect handling or escaping of special characters in the Salesforce SOQL (Salesforce Object Query Language) query.
Per the SOQL documentation, the only reserved characters are the single quote and the backslash characters. I will update you tomorrow on how to resolve this
Hi, Do we have an update on how to resolve this?
Hello Sherine,
I apologize for the delay in response. I have a couple of questions: What Jira are you using? Also, the error is coming from a trigger or the Outgoing Sync of Salesforce? If so, could you provide the Outgoing Sync script or the Trigger?
Hi Javier,
We use Jira Data Center and the error we get from Outgoing sync of Salesforce.
Outgoing script:
if(replica.AccountId != null)
{
def Account_Info = httpClient.get(“/services/data/v54.0/query/?q=SELECT+Global_Industry__c%2cCompany_Revenue__c+FROM+Account+WHERE+Name=%27${replica.AccountId}%27”)
replica.AccountGlobal=Account_Info.records.Global_Industry__c[0]
replica.Accountcompanyrevenue=Account_Info.records.Company_Revenue__c[0]
//debug.error(“${contact_name2.records.Global_Industry__c}”)
}
else
{
replica.AccountGlobal = “None”
replica.Accountcompanyrevenue = “None”
}
Could you replace def Account_Info with the following and test it?
def Account_Info = httpClient.get("/services/data/v54.0/query/?q=SELECT+Global_Industry__c%2cCompany_Revenue__c+FROM+Account+WHERE+Name='${replica.AccountId}'")
Hi Javier,
I tried the script you added above but still I get the following message.
Bad response when GET to /services/data/v54.0/query/?q=SELECT+Global_Industry__c%2cCompany_Revenue__c+FROM+Account+WHERE+Name=‘Coeur d’Alene Tribe’ - 400 [{“message”:“\nWHERE Name=‘Coeur d’Alene Tribe’\n ^\nERROR at Row:1:Column:90\nline 1:90 mismatched character ‘’ expecting ‘’'”,“errorCode”:“MALFORMED_QUERY”}]
Hello Sherine,
Let’s try something else, we will try escaping the single quote by doing the following:
def escapedAccountId = replica.AccountId.replaceAll("'", "\\\\'")
def query = "/services/data/v54.0/query/?q=SELECT+Global_Industry__c%2cCompany_Revenue__c+FROM+Account+WHERE+Name='${escapedAccountId}'"
def Account_Info = httpClient.get(query)
Let me know how it goes
Thank you Javier for your help. I tried the script you added above and I get the following error
Bad response when GET to /services/data/v54.0/query/?q=SELECT+Global_Industry__c%2cCompany_Revenue__c+FROM+Account+WHERE+Name=‘FARO Consulting & Solutions, LLC.’ - 400 [{“message”:“\nAccount WHERE Name=‘FARO Consulting \n ^\nERROR at Row:1:Column:85\nline 1:85 mismatched character ‘’ expecting ‘’’”,“errorCode”:“MALFORMED_QUERY”}]
I can see that it’s giving an error with a different account name, the previous error was with the following value: **‘Coeur d’Alene Tribe’.**Did the solution that I provided fixed the error with that value?
yes the solution solved the other account name with single quotes
Answer by Sherine on 27 November 2023
Hi,
Still face the same issue. Tried the following: ‘Coeur d\’ Alene Tribe’ but still get the same error message.
Thank you
Answer by Javier Pozuelo on 13 November 2023
The Single quote must be escaped using backslash, so try the following: ‘Coeur d\’ Alene Tribe’
Answer by Sherine on 13 November 2023
Do we have any update here?
Answer by Sherine on 09 November 2023
Could someone please look into this issue?