Pass a variable into a select query in SOQL

Question

I am running a Python script that uses the simple-salesforce package to query all data in my Salesforce account. For context, the script reads a dictionary stored in a CSV file and stores a specific value, which is a string, in a variable (see below).

    with open('/Users/username/Documents/filename.csv', 'rU') as f:
        mydict = dict(filter(None, csv.reader(f)))
    myString = mydict['key']

Then, I want to pass the variable (myString) into an SOQL select query. However, when I try to pass the variable into the query, I get the following error: Bind variables only allowed in Apex code. Below, you can see the query I tried running in simple-salesforce's format for SOQL queries.

    sf.query("SELECT Id, Name FROM deal__c WHERE contact__c = myString")

My question is: How do I pass my variable into the SOQL select query? I am open to using Apex or Dynamic SOQL if that is the best solution, but if that is the best solution, please advise how to use Apex code in my Python script (i.e. if I need to specify where I switch to Apex or install Apex somehow).

Luke Woodward Luke Woodward · Accepted Answer · 2016-02-10T22:51:57

@harfel's answer is close, but it has the quotes the wrong way around, as string literals in SOQL must be surrounded by single quotes.

Here's a poor first attempt at fixing the problem:

# What happens if myString is "John O'Groats"?
sf.query("SELECT Id, Name FROM deal__c WHERE contact__c = '%s'" % myString)

While Salesforce's proprietary language Apex supports parameterised SOQL, there is no way to use parameterised SOQL when calling Salesforce via the REST API, as simple-salesforce does. If we want to use an unknown value in a SOQL query, string concatenation is our only option.

However, if we are going to concatenate string values into a SOQL query, we must 'escape' any reserved characters to avoid SOQL injection. The Salesforce documentation on this is fairly clear: there are only two reserved characters: backslash \ and single quote '. A function that will escape string values is as follows:

def soqlEscape(someString):
    # Escape backslashes and single-quotes, in that order.
    return someString.replace("\\", "\\\\").replace("'", "\\'")

We can use this function to give a better attempt at fixing the problem:

sf.query("SELECT Id, Name FROM deal__c WHERE contact__c = '%s'" % soqlEscape(myString))

Alternatively, if you know that the value of the contact__c parameter will only ever be alphanumeric, then you can validate the value beforehand instead of escaping it:

if myString.isalnum():
    # Value is alphanumeric, safe to concatenate in.
    sf.query("SELECT Id, Name FROM deal__c WHERE contact__c = '%s'" % myString)
else:
    raise ValueError("Invalid contact__c: '%s'" % myString)

'Installing' Apex isn't an option: it runs only on Salesforce's own servers.

Pass a variable into a select query in SOQL

2 Answers