Search
Twitter

Preventing SOQL Injection in Salesforce Development

SQL/SOQL injection attacks are nasty and annoying. According to Open Web Application Security Project (OWASP), injection attacks are number one on the 2010 list of threats to be aware of in web application development. Injection flaws occur when untrusted data is sent straight to the interpreter as part of a query or command. In this case, the attacker is deliberately passing hostile data with the intent of tricking the interpreter into executing unintended commands or gaining access to unauthorized data.

 

With Salesforce development, we need to pay very special attention to SOQL injection. Organizations are putting their most trusted information into Salesforce with the understanding that the data is secure. Company financials, customer lists, products, employees are all examples of very sensitive data to a company. As a developer of Salesforce applications, part of your job is to uphold the security put in place by the Force.com platform. A big part of this is making sure that the custom code you write cannot be used to cause any hard to the organizations using your code.

Salesforce.com has provided us developers some great materials to read about SOQL Injection. I recommend studying that material in detail. This posting is not meant to be a comprehensive resource on the topic but rather a secondary source of information to complement what is already available by Salesforce.com and OWASP.

Example of a vulnerable SQL call:   

String sQuery = "SELECT * FROM customers WHERE accountID = "'" + request.getParameter("id") + "'";

In this case, the attacker can modify the "id" parameter in their browser address bar or through an program they wrote to send: ' or '1'=1. This would change the meaning of the query - resulting in all of the records from the customer database to be returned instead of only the intended customer.  

http://exampleofsqlinjection.com/customerView?id=' or '1'=1

Again, this is a very simple example. When you are developing in Salesforce.com you need to be very careful when using any dynamic SOQL.Dynamic SOQL is the creation of SOQL statements on he fly during the execution of Apex code. An example of dynamic SOQL would be building a custom search based on input from a form that the user has filled out.  Dynamic SOQL is very powerful and convenient but from a security standpoint it is something to approach with caution and care.

To create a dynamic SOQL query at runtime you will use the database query method provided. Below are two examples of building dynamic SOQL at runtime (both of these are taken from this page).

Return a single sObject when query comes back with one record:

sObject S = Database.query(string_limit_1);

Return a list of sObjects when query comes back with more than one record:

List<sObject> L = Database.query(string);

What you want to make sure of is that when you construct the actual query you use the escapeSingleQuotes method. This method is a major time saver and adds the escape character \ to al single quote marks in strings that are passed in from the user. This basically takes an single quote marks and treats them as enclosing strings rather than database commands. It's pretty slick and very important!

Take a look at this example of SOQL Injection Vulnerability in Apex (taken from here)

In this example, lets assume that there is a single input field where the user enters a name. The name is then used to find all Contacts with that name that have not been deleted.

String qryString  = 'SELECT Id FROM Contact WHERE (IsDeleted = false and Name like \'%' + name + '%')';

Now, under normal operation the user would enter something like "salesforcegeneral" and the resulting string would be:

qryString  = SELECT Id FROM Contact WHERE (IsDeleted = false andName like '%salesforcegeneral%')

The problem comes when a malicious user types in something like test%') or (Name like '

 In this case, we'd end up with something like this:

qryString  = SELECT Id FROM Contact WHERE (IsDeleted = false and Name like '%test%') or (Name like '%')

 The result of this query is not just deleted contacts but ALL contacts. The Salesforce documentation gives you a great method to combat this - avoid dynamic SOQL and user static queries with variables. Here is an example of how the above vulnerability is re-written:

String queryName = '%' + name + '%';

queryResult = [SELECT Id FROM Contact WHERE (IsDeleted = false and Name like :queryName)];

I've switched over to this method. If there is no way around dynamic SOQL, which there usually is, make sure to use the escapeSingleQuotes method talked about a few paragraphs up.

 
 Fantasic resource to review: Apex and Visualforce Security Tips

 If you find yourself stuck, post a code snippet to the developerforce boards. Someone is always willing to pitch in and help a fellow Salesforce developer prevent a SOQL injection hole.