Provide clarification / standard method for escaping string for use in SOQL

[leewillis77]

Hi;

Hopefully I'm missing something here, however I've read through countless articles and not been able to find an answer to what I think is a rather basic question.

Consider the following code:

<?php
$client = new SforceEnterpriseClient();
$client->createConnection($this->wsdl);
$client->login($this->username, $this->password . $this->security_token);
$query = "SELECT Id
            FROM Contact
           WHERE Email = '$email'
        ORDER BY LastModifiedDate ASC";
$response = $client->query($query);

Assuming that $email is a user-supplied variable then it's clear that it should be escaped to prevent query-injection vulnerabilities. However - it's not clear from any of the documentation how that should be escaped.

This page looks the most promising:
https://developer.salesforce.com/page/Secure_Coding_SQL_Injection#SOQL_INJECTION_And_Force.com_APIs

However it basically says to use parameterizied prepared queries (Which aren't supported via the SOAP API), or PDO to escape the string (Which requires a database connection, and will escape only for the context of that particular database connection).

It doesn't help that none of the examples listed at https://developer.salesforce.com/page/Getting_Started_with_the_Force.com_Toolkit_for_PHP or https://developer.salesforce.com/page/Tksample.php include any dynamic variables, or any escaping.

Please can you clarify the escaping method that is required to escape data in SOQL strings?

Ideally this library should either:

a) Expose (and recommend the use of) a parameterized query method that handles escaping internally, e.g.

<?php
$query = "SELECT Id
                FROM Contact
               WHERE Email = :email
            ORDER BY LastModifiedDate ASC";
$response = $client->query($query, array(':email' => $email));

b) Provide a string escaping function so developers aren't encouraged to roll their own , e.g.

<?php
$email = $client->escapeString($email);
$query = "SELECT Id
            FROM Contact
           WHERE Email = $email
        ORDER BY LastModifiedDate ASC";
$response = $client->query($query);

Apologies if this is already covered somewhere - but as someone new to the SalesForce APIs it's not easy to find :)

[metadaddy]

The PHP toolkit is essentially a thin wrapper on the SOAP API and, as you've noticed, doesn't include any built-in query escape functions. This question on Salesforce StackExchange covers the basics - single quotes are the big one to watch. I'll leave this issue open, since it's a good idea to have an escape function for developers to use.

[hatzopoulos]

At least for the FIND {SearchQuery} functionality, the docs say reserved characters are ? & | ! { } [ ] ( ) ^ ~ * : \ " ' + - and to properly escape them. Please correct me if I'm mistaken but I assumed SOQL followed the same rules similar to Oracle (or MySQL) for escaping.

// $example_partial_sql .= "FIND {".soql_string_literal($email)."} IN EMAIL FIELDS";

function soql_string_literal($str) {

    // ? & | ! { } [ ] ( ) ^ ~ * : \ " ' + -
    $characters  = array(
        '\\',
        '?' ,
        '&' ,
        '|' ,
        '!' ,
        '{' ,
        '}' ,
        '[' ,
        ']' ,
        '(' ,
        ')' ,
        '^' ,
        '~' ,
        '*' ,
        ':' ,
        '"' ,
        '\'',
        '+' ,
        '-');
    $replacement = array(
        '\\\\',
        '\?',
        '\&',
        '\|',
        '\!',
        '\{',
        '\}',
        '\[',
        '\]',
        '\(',
        '\)',
        '\^',
        '\~',
        '\*',
        '\:',
        '\"',
        '\\\'',
        '\+',
        '\-'
    );
    return str_replace($characters, $replacement, $str);

}

[jschrab-lc]

I think the list of characters needing escaping is much shorter than that:
https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_quotedstringescapes.htm?search_text=escape

I ended up using this:

function soql_string_literal($str) {
    $characters  = array('"','\'' ,'\\' ,'_' ,'%');
    $replacement = array('\"',"\'","\\","\_","\%");
    return str_replace($characters, $replacement, $str);
}

... but your idea inspired me to come up with this solution, so thank you for sharing the above.