SOQL Injection - Demo and Approaches to Prevent it

 In this post let us see a sample use case of SOQL Injection and the different methods to get rid of SOQL injection.

1. What is SOQL Injection?

This will happen when we try to execute Dynamic Query in Apex, accepting input from the User. And the user can modify input value based on his need, thus resulting in unintended query results.

2. Sample Use Case

We have got an LWC Component to Search for Accounts, accepting Account Name as the input parameter.

On successful search, the component will display resulting accounts matching search criteria.

Code Details below:

accountNameSearch.html

<template>

<h1>Search Account</h1>

<lightning-card title="Search Account Using Name">

<lightning-input label="Enter Account Name" onchange={handleFilter}

class="slds-size--2-of-8"></lightning-input>

<br/>

<lightning-button label="SEARCH" onclick={handleSearch}></lightning-button>

</lightning-card>

<template if:true={isAccountavailable}>

<h1>Search Results</h1>

</template>

<lightning-datatable if:true={isAccountavailable}

key-field="Id"

data={acclist}

columns={columns}

hide-checkbox-column="true"

>

</lightning-datatable>

</template>

accountNameSearch.js

import { LightningElement } from 'lwc';

import NAME_FIELD from '@salesforce/schema/Account.Name';

import TYPE_FIELD from '@salesforce/schema/Account.Type';

import RATING_FIELD from '@salesforce/schema/Account.Rating';

import ACTIVE_FIELD from '@salesforce/schema/Account.Active__c';

import myfilteredaccounts from '@salesforce/apex/AccountList.getAccountListByName';

const COLUMNS = [

{ label: 'Account Name', fieldName: NAME_FIELD.fieldApiName, type: 'text' },

{ label: 'Account Type', fieldName: TYPE_FIELD.fieldApiName, type: 'text' },

{ label: 'Rating', fieldName: RATING_FIELD.fieldApiName, type: 'text' },

{ label: 'Active', fieldName: ACTIVE_FIELD.fieldApiName, type: 'text' }

];

export default class AccountNameSearch extends LightningElement {

columns = COLUMNS;

accSearchName;

acclist;

isAccountavailable=false;

handleFilter(event){

this.accSearchName = event.target.value;

}

handleSearch()

{

//display account

this.isAccountavailable = false;

myfilteredaccounts({Name:this.accSearchName})

.then(result=>{

this.acclist=result;

this.error=null;

if(this.acclist.length>0){

this.isAccountavailable = true;

}

})

.catch(error=>{

this.error=error;

this.acclist=null;

});

}

}

accountNameSearch.js-meta.xml

<?xml version="1.0" encoding="UTF-8"?>

<LightningComponentBundle xmlns="http://soap.sforce.com/2006/04/metadata">

<apiVersion>52.0</apiVersion>

<isExposed>true</isExposed>

<targets>

<target>lightning__AppPage</target>

<target>lightning__Tab</target>

</targets>

</LightningComponentBundle>

AccountList.cls

public class AccountList {

@AuraEnabled(cacheable=true)

public static List<Account> getAccountList() {

return [SELECT Id, Name,Type,Rating,Phone FROM Account];

}

@AuraEnabled(cacheable=true)

public static List<Account> getAccountListByName(String Name) {

//Query

String query = 'SELECT Id, Name,Type,Rating,Phone FROM Account where Name Like \'%'+Name+'%\' ';

List<Account> res = Database.query(query);

return res;

}

}

3. Test Scenarios

I created a LWC Tab to perform the search and let us see the search results now based on the different input parameters.

Scenario 1 

Search String - 'Oil'

The search is successful and you can see the results on screen as shown in below screenshot:

Scenario 2

Search String - Oil%' or Active__c='No' or name like '%Gen

The search is a success and the user gets results.

The above scenario is called SOQL Injection, where the user modified the input string to get additional information like all inactive Accounts in an org.

Similarly, suppose a user wants to retrieve the information of all partner Accounts present in an org. He can easily modify the input and get the required output.

Scenario 3

Search String - Oil%' or Active__c='No' or isPartner=true or name like '%Gen

Let us see how currently the dynamic query is getting executed:

String query = 'SELECT Id, Name,Type,Rating,Phone,Active__c FROM Account where Name Like \'%'+Name+'%\' ';

List<Account> res = Database.query(query);

Problem with this approach:

User can modify the input String based on their wish and apply filters based on their need and get access to unintended data especially when the current user's sharing access is bypassed.

4. How to avoid SOQL Injection

1. Use Bind variable instead of dynamic query and use static query

String searchText = '%'+Name+'%';

return [SELECT Id, Name,Type,Rating,Phone,Active__c FROM Account where Name Like :searchText];

Let us execute the same query again and see what happens:

2. Use escapeSingleQuotes method to sanitize user-supplied input

This method adds the escape character (\) to all single quotation marks in a string that is passed in from a user. The method ensures that all single quotation marks are treated as enclosing strings, instead of database commands.

Updated code:

String query = 'SELECT Id, Name,Type,Rating,Phone,Active__c FROM Account where Name Like

                \'%'+String.escapeSingleQuotes(Name)+'%\' ';

List<Account> res = Database.query(query);

return res;

The user won't be getting any results now.                           

But the normal search will continue to work as shown below:

3. WITH SECURITY ENFORCED

Also to make sure that the user is not getting access to unintended data add WITH SECURITY ENFORCED keyword

String searchText = '%'+Name+'%';

return [SELECT Id, Name,Type,Rating,Phone,Active__c FROM Account where Name Like :searchText

WITH SECURITY_ENFORCED];

References:

https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/pages_security_tips_soql_injection.htm