Newer Version Available

This content describes an older version of this product. View Latest

Secure Coding SQL Injection

SQL and SOQL Injection: What is it?

SQL (Structured Query Language) injection is a common application security flaw that results from insecure construction of database queries with user-supplied data. When queries are built directly with user data inlined or concatenated directly with the query text, instead of using type-safe bind parameters, malicious input may be able to change the structure of the query to bypass or change application logic. SQL injection flaws are extremely serious. A single flaw anywhere in your application may allow an attacker to read, modify or delete your entire database.

Apex does not use SQL, but its own database query language, SOQL (Salesforce Object Query Language). SOQL was designed to give you most of the power of SQL, while also protecting against most attacks. For example, in SOQL you cannot update or delete, you can only use SELECT. Because of this, the most dangerous operations, such as deleting or modifying data, are not possible. . Therefore, the risks are much lower for SOQL injection than for SQL injection, but the attacks are nearly identical to traditional SQL injection.

Before we dig into more details of how SOQL injection works, you should know that we also have great training on Trailhead about it. If you prefer a more hands-on approach, Trailhead is a great way to learn new concepts. We have 2 trainings:

Sample Vulnerability

Consider the following code in an Apex controller for constructing a SOQL query to retrieve information about a custom object in Salesforce called Personnel. The userInputTitle variable is user input from a web page form, and is concatenated into the query string where clause to form the final request to the database.

1public List<Personnel__c> whereclause_records {get; set;}
2public String userInputTitle {get; set;}
3public PageReference whereclause_search(){
4    String query = 'select Name, Role__c, Title__c, Age__c from Personnel__c';
5    if(!Schema.sObjectType.Personnel__c.fields.Name.isAcessible() ||
6       !Schema.sObjectType.Personnel__c.fields.Role__c.isAcessible() || 
7       !Schema.sObjectType.Personnel__c.fields.Title__c.isAcessible() ||
8       !Schema.sObjectType.Personnel__c.fields.Age__c.isAcessible()){
9          return null;
10      }
11    String whereClause = '';
12    if(userInputTitle != null && userInputTitle!=''){
13        String qtitle = '%' + userInputTitle + '%';
14        whereClause += 'Title__c like  \'%'+userInputTitle+'%\’’;
15        whereclause_records = database.query(query+' where '+whereClause);
16    }
17}

Consider what would happen if someone entered the following into the userInputTitle:

1%' or Performance_rating__c<2 or name like '%

After concatenating the components together, the final query string would be:

1Select Name, Role__c, Title__c, Age__c from Personnel__c 
2where Title__c like '%%' or Performance_rating__c<2 or name like '%%'

The %' finishes up the wildcard matching for Title__c and ends the string. The rest of the user input gets appended into the query and adds a new field to filter on, the performance rating that is associated with the Personnel object we are querying. The attacker’s string has now changed the way the query is behaving and gives them access to information that the developer didn’t intend.

SOQL injection can be seen as a bypass of CRUD and FLS checks. Since the only action that is supported is SELECT, the worst that can happen is that a user gets access to data that they shouldn’t see. This is the same impact that you might see from not properly checking a user’s access levels before returning data to them.

Is My Application Vulnerable?

If you use dynamic database queries and do not strictly enforce a coding policy requiring only bind variables (also known as parameterized queries), your application may be vulnerable.

What Do I Need To Be Secure?

When designing SOQL queries, there are basically three places where you can customize the behavior of the query based on input from the user. The first is which fields are being selected from an object. The second is which object the query is running against. Finally, you may want to change the behavior of the WHERE clause to change which subset of objects get returned.

Securing The WHERE Clause

While the WHERE clause case may seem to be the most complicated, it is actually the most straightforward to secure. If all you need is to customize the WHERE clause, you can use what is known as a parameterized query. This feature exists in most if not all query language frameworks, for now we’ll just go over how to do this in Apex.

Let’s revisit the example from earlier, and see how it can be written securely.

1public List<Personnel__c> whereclause_records {get; set;}
2public String userInputTitle {get; set;}
3public PageReference whereclause_search(){
4    if(!Schema.sObjectType.Personnel__c.fields.Name.isAcessible() ||
5      !Schema.sObjectType.Personnel__c.fields.Role__c.isAcessible() || 
6      !Schema.sObjectType.Personnel__c.fields.Title__c.isAcessible() ||
7      !Schema.sObjectType.Personnel__c.fields.Age__c.isAcessible()){
8        return null;
9      }
10   if(userInputTitle != null && userInputTitle!=''){
11        String qtitle = '%' + userInputTitle + '%';
12        whereclause_records = [SELECT Name, Role__c, Title__c, Age__c from Personnel__c   
13            WHERE Title__c like :qTitle];
14    }
15}

It’s as simple as that! Notice that we have less code than our manual example, too. In Apex, writing a query inside braces will directly execute the query inside it without calling database.query(). The variable prepended with a colon is known as a bind variable. This tells the database layer, in this case SOQL, to treat everything in that variable as data, no matter what characters are in it. This means that no matter what the user types in, they can’t break out of the intended behavior of the query and manipulate the query.

Securing Variable Object Names

One limitation to using parameterized queries is that they typically only allow you to bind variable inside the WHERE clause of the query. This means that if you want dynamic fields or object names, you can’t just replace your dynamic query with a parameterized one. So, how can you make sure that your queries are safe?

In Apex, we recommend writing a sanitizing function similar to the following:

1Public boolean isSafeObject(String objName){
2    public Map <String, Schema.SObjectType> schemaMap = Schema.getGlobalDescribe();
3    public SObjectType myObj = schemaMap.get(objName);
4    if (myObj.getDescribe().isAccessible() ) { 
5        return true; 
6    }else{
7        return false;
8    }
9}

For your actual database query, you could construct it thusly:

1Public PageReference doQuery(){
2     String myQuery = 'Select Name, Address from ' + objName + ' where Name like '%Sir%';
3     if(!isSafeObject(objName)){
4         return null;
5     }else{
6        if(!Schema.getGlobalDesribe().get(objName).fields.Name.isAccessible() ||
7           !Schema.getGlobalDesribe().get(objName).fields.Address.isAccessible()){
8            return null;
9        }
10        Records = database.query(myQuery);
11     }
12}

As you can see, this is a bit more complex than the parameterized query example. Here we are validating that the object name provided by the user is not only a valid object in the org that you are querying against, but also verifies that the current user has access to the object. This both checks that the object name doesn’t contain invalid characters that could be used for SOQL injection, but also that it is an object that the user would normally have access to. This is great, because it protects you from SOQL injection and also works as a CRUD check. As we mentioned earlier, SOQL injection can be considered just another flavor of CRUD/FLS bypass.

Securing Object Field Queries

There is one final case of customization that you might want for your SOQL queries. Maybe you know what object you want to query against and how you want to filter it, but you aren’t sure which fields you will need access to in the object. This case is very similar to the variable object name, which you can see from the code below:

1Public boolean isSafeField(String fieldName, String objName){
2    public Map <String, Schema.SObjectType> schemaMap = Schema.getGlobalDescribe();
3    public SObjectType myObj = schemaMap.get(objName);
4    if (myObj.getDescribe().isAccessible() ) { 
5        SObjectField myField = myObj.getDescribe().fields.getMap().get(fldName);
6        if(myField.getDescribe().isAccessible()){
7            return true;
8        }else{
9            return false;
10        }
11    return false;
12    }
13}

And then again, for the query:

1Public PageReference doQuery(){
2     String objName = 'myObj__c';
3     String myQuery = 'Select ' + field1 +',' + field2 +' from myObj__c where Name like '%Sir%';
4     if(!(isSafeField(field1, objName) && isSafeField(field2, objName))){
5         return null;
6     }else{
7        Records = database.query(myQuery);
8     }
9}

Again in this example, we have not only prevented SOQL injection but also have carried out our CRUD and FLS checks for the object and the associated fields.

The methods that we have discussed so far should be used in 99.9% of situations. If you think you need alternative ways to prevent SOQL injection, you may be thinking about the problem incorrectly, as it’s likely that these methods will work for you. However, for other languages and frameworks you may not have the types of APIs that Salesforce provides, so we will briefly cover other sanitization methods.

Alternate Sanitization Methods

Escape Single Quotes

Sometimes you may have a dynamic query with a variable in a String, such as:

1String query = 'select Name, Title from myObject__c where Name like \'%'+name+'%\'';

Here the name variable is being concatenated inside two single quotes in the query. One way to prevent injection here would be to escape single quotes, ideally using a native library for the language you’re developing in. In case of Apex, this can be done via the String.escapeSingleQuotes() function call. The result would be:

1String query = 'select Name, Title from myObject__c where Name like \'%'+String.escapeSingleQuotes(name)+'%\'';

This would protect from tampering with the query, but would not guarantee that the user can’t access data they shouldn’t. It is also worth noting that this only works when you have a variable that is inside single quotes. If you have a boolean or otherwise unquoted field with user input, escaping single quotes won’t help protect against injection. Thus, it is not recommended to use this method.

Typecasting / Whitelisting

One other method that you might consider would be typecasting and/or whitelisting variables. Typecasting is fairly straightforward, if you are expecting an input type such as boolean or integer, you can cast the user input value to the expected type. If there is a type casting exception thrown, you know you have invalid data and can gracefully exit.

Whitelisting is similar, if you have an input that you know the structure of. For example, if you want to support dynamically selecting which fields from the object you will query, but you also know what all of the possible fields are, you can check that the user input is one of those field names.

Typecasting example:

1String query = 'Select Name, Address from Object__c where isActive = ' + Boolean(input);

Whitelisting example:

1Set<String> fields = new Set<String>();
2fields.add('myField1');
3fields.add('myField2');
4fields.add('myField3');
5if(!(fields.contains(inputField)){
6  .. Throw an error ..
7}

These methods are good for preventing injection attacks, but do not guarantee that the user will have access to the objects returned. Hence we also do not recommend using these methods except in edge cases.

Final Recommendations

There are a number of third party libraries that can help you write SOQL queries. In general, if you want to use these, you should refactor them before you try to use them. It is safest and easiest to verify injection fixes when you are doing field validation in the same function/class that you are performing the database query.

Most libraries will expose a SOQL layer that is easy to use, but does not provide any validation. If you want to use these libraries, you will need to modify them so that the framework level is secure. This will allow you to use the library without having to worry about sanitizing every database call in your code.

SOQL INJECTION and APIs

The REST and SOAP APIs allow end users to submit arbitrary SOQL strings. However, this does not lead to SOQL injection because the APIs include built in checks for sharing and CRUD/FLS permissions. This means that end users are only allowed to see or modify records and fields that they already have access to. On the other hand, when making SOQL calls in Apex Code, no CRUD/FLS checks are performed (and sharing checks are only performed if the 'with sharing' keyword is used). Therefore it is a serious security vulnerability to allow end users to control the contents of a SOQL query issued in Apex code, but not for end users to control the contents of a SOQL query via the API.

How Do I Protect My Non-Salesforce Application?

General Guidance

Application of platform and language-specific solutions for strongly-typed, parameterized queries is always the preferred solution to prevent SQL injection. Filtering and sanitizing user input before passing it to a query is always a good practice, and may unfortunately be your only defense if you are using a platform or database engine that does not natively support parameterized queries.

Allowing only “known good” characters (such as with a regular expression, where that is sufficient) is the best defense strategy. For example, a phone number could be validated to only include numerals and a name to include only letters and spaces. Attempting to filter out “known bad” characters or strings (also known as “blacklisting”) can be prone to error. Attackers may be able to use alternate encodings, double-up quotes or other tricks to foil such filters. It is a good idea to remove single-quote, double-quote, hyphen, NULL and newline characters, but do not rely on this as your only defense.

For more information on SQL injection attacks and defense see:

ASP.NET

Microsoft provides a free static analysis tool, CAT.NET. CAT.NET is a snap-in to Visual Studio that helps identify SQL injection as well as several other classes of security flaw. Version 1 of the tool is available as a Community Technical Preview from the Microsoft download site:

http://www.microsoft.com/downloads/details.aspx?FamilyId=0178e2ef-9da8-445e-9348-c93f24cc9f9d&displaylang=en

ASP.NET applications should constrain and sanitize input data before passing it to a query and always use type-safe SQL parameters for data access, whether with stored procedures or dynamic SQL. Parameter collections such as SqlParameterCollection provide type checking and length validation.

For more information on how to protect from SQL injection in ASP.NET 1.1 and 2.0, see the following guidance from Microsoft:

http://msdn.microsoft.com/en-us/library/ms998271.aspx

Language Integrated Query (LINQ) is also an excellent way to prevent SQL injection attacks in ASP.NET applications. LINQ technology, available in Visual Studio “Orcas” and the .NET Framework 3.5, enables database constructs to be treated as native objects in .NET programming languages. LINQ to SQL abstracts an application’s interactions with the database into an object model that avoids all possibility of SQL injection by automatically building parameterized queries. Learn more about LINQ here:

http://msdn.microsoft.com/en-us/library/bb425822.aspx

Java

A large number of commercial source code analysis tools for Java are available from vendors such as Checkmarx, Coverity, Fortify, Klocwork and Ounce Labs. These tools require considerable investment, but may be appropriate for very large applications and codebases, and are able to find a variety of security flaws, not just SQL injection. For smaller applications and code bases, manual review and enforcement of coding standards may be sufficient to protect against SQL injection. Findbugs is a free and open source Java code scanner that can find SQL injection in Java code.

Sanitizing user data before passing it to a query is a standard best practice, but proper construction of queries is the most important and reliable defense. Review all JDBC code. Any use of java.sql.Statement for queries handling user data is a likely SQL injection risk. Use java.sql.CallableStatement and java.sql.PreparedStatement exclusively when handling user data and avoid constructing any part of the query string by concatenating unsanitized data. The following is an example of a safe database construct in Java:

1PreparedStatement pstmt = 
2     con.prepareStatement("UPDATE USERS SET SALARY =&nbsp;? WHERE ID =&nbsp;?");
3pstmt.setBigDecimal(1, 30000.00);
4pstmt.setInt(2, 20487);   
5pstmt.executeQuery();

Hibernate and other ORM frameworks offer some protection from SQL injection by their internal use of prepared statements, but be cautious when directly using query language constructs in these systems (such as HQL in Hibernate). If using Hibernate, do not use the deprecated session.find method — use one of the overloads that support bind variables.

PHP

The PHP Data Objects (PDO) extension defines an abstract database interface that offers parameterized queries for prepared statements and stored procedures. It is available from PHP 5. Use of PDO::prepare will provide good SQL injection defenses, with some exceptions. See the following pages for additional information:

Note that this only guarantees protection from SQL injection if the underlying PDO driver and database support parameterized queries natively. PDO will attempt to emulate parameterized queries for drivers that do not provide them, using the PDO::quote function internally to attempt to prevent SQL injection. If using a driver that does not support native parameterization or PDO::quote (such as PDO_ODBC), you will not be protected.

Always sanitize data before passing it to PDO::prepare as a defense-in-depth measure. Use a regular expression to limit input values to their expected format, such as only alpha-numeric characters, and never allow single or double quotes, the NUL character or line breaks.

Ruby on Rails

Ruby on Rails Active Record objects provide limited automatic protection from SQL injection. When using Model.find(id) or Model.find_by_X(X), an escaping routine is applied automatically to eliminate ', ", the NUL character and line breaks. When using SQL fragments, such as conditions fragments (:conditions => "..."), connection.execute or Model.find_by_sql, this sanitization must be applied manually.

For conditions fragments with a Model instance, passing the conditions as an array or hash form will apply the sanitization, e.g.:

1Model.find(:first, :conditions => ["login = ? AND password = ?",entered_user_name, entered_password])

In other cases, you can call sanitize_sql_array or sanitize_sql_for_conditions manually (for Rails 2.0) or use the deprecated sanitize_sql for earlier versions.

How Can I Test My Application?

Some testing for SQL injection can be performed in a black-box manner. Putting characters like single quotes and dashes into form fields and looking for database error messages will find the most obvious SQL injection flaws. Unfortunately, these techniques cannot find all SQL injection flaws. Client-side validation, escaping or double-quoting may block simple attacks but be bypassed easily by an attacker.

The most reliable way to identify SQL injection flaws is through manual code review or with a static code analysis tool. Code analysis tools (commercial and free) are listed for individual development platforms below. Developers on the Lightning Platform can make use of the first on-demand source code analysis tool build solely for Platform as a Service. Visit the Force.com Security Source Code Scanner page for more details.

If performing manual source code review, verify that all queries that include user data are built using bind variables instead of string concatenation. A bind variable is a placeholder in a query that allows the database engine to insert dynamic values in a type-safe manner. The exact syntax varies somewhat from platform to platform, but typically these placeholders are question marks or a colon-prefixed variable name. For example, the following construct is safe from SQL injection:

1PreparedStatement query = "select * from users where userid =&nbsp;:user and password =&nbsp;:password";
2query.bindInt("user", Request.form("user").intValue());
3query.bindString("password", getSaltedHash(Request.form("password")));
4Database.executePreparedStatement(query);

Stored procedures that only use static SQL text are also acceptable, but beware of stored procedures that use exec or similar constructs to build dynamic SQL internally.

Secure Coding Guide