+ Start a Discussion
KunlunKunlun 

Dynamic SOQL can not support security check, any one can help me?

Hello,

 

I create a apex class, which contains some dynamic SOQL. The function is similar as searching product in price book.

 

All of them works fine. But I tried the security check in my account and found dynamic SOQL caused security warning.



Security check link: http://security.force.com/sourcescanner

My SOQL:

public with sharing class GroupOperationController{
    public String Keyword {get; set;}
    public String FieldName {get; set;}
    public String Operator {get; set;}
    public String FieldValue {get; set;}
     
    private String getQuerySql() { 
        String fields = ' Id, Name, PCOL__Account__r.Id, PCOL__Account__r.Name, PCOL__modelS__c, PCOL__manufacturerS__c, PCOL__serialNumberS__c ';
        String strSql = 'select '+fields+' from PCOL__Device__c where Name != null ';
         
        strSql += getKeywordWhere();
        strSql += getMatchFiltersWhere();
        System.debug('xxxxxxxxxxxx ' + strSql + ' xxxxxxxxxxxxxxx');
        return strSql;
    }
    private String getKeywordWhere(){
        String sqlWhere = '';
        if(Keyword != null && !Keyword.equals('')){
            sqlWhere = ' and (';
            Integer i = 0;
            for(SelectOption so: FieldNames){
                if(i > 1){
                    sqlWhere += ' or ';
                }
                if(i > 0){
                    sqlWhere += ' ' + so.getValue() + ' like \'%' + Keyword + '%\' ';
                }
                i++;
            } 
            sqlWhere += ') '; 
        }   
        return sqlWhere;
    }
    private String getMatchFiltersWhere() { 
        String sqlWhere = '';
        if(FieldName != '' && Operator != '' && FieldValue != '' ) { // have a filter
            if(Operator == 'e'){
                sqlWhere += ' and ' + FieldName + ' = \'' + FieldValue + '\' '; 
            }else if(Operator == 'n'){
                sqlWhere += ' and ' + FieldName + ' != \'' + FieldValue + '\' '; 
            }else if(Operator == 's'){
                sqlWhere += ' and ' + FieldName + ' like \'' + FieldValue +  '%\' '; 
            }else if(Operator == 'c'){
                sqlWhere += ' and ' + FieldName + ' like \'%' + FieldValue + '%\' '; 
            }else if(Operator == 'z'){
                sqlWhere += ' and ' + FieldName + ' like \'%' + FieldValue + ' '; 
            }        
        } 
    } 
    private void BindObjList(){
        ObjList = new List<GroupOperationDevice>();
        for(PCOL__Device__c d: Database.query(getQuerySql())){  
                ObjList.add(new GroupOperationDevice(d, false, visible)); 
        } 
    }
    public PageReference submit(){
        BindObjList();
        return null;    
    }
}

 After security checking, some warning appearred. It said all the code like "sqlWhere +=" are not security.

 

Any one can help me how to use dynamic SOQL and support security check?

spraetzspraetz

Your images aren't working properly.

KunlunKunlun

My picture crashed, I changed something, hope it can make sence without picture.

Cory CowgillCory Cowgill

Dynamic SOQL is great feature, and yes that will show up in Secuirty Scanner if you don't properly protect it.

 

One thing that will alleviate this is to use escapeSingleQuotes method on the String inputs.

 

Just like SQL Injection, you need to escape you parameters with Dynamic SOQL to protect against SOQL Injection.

 

See Below:

 

        String sampleQuery = 'Select a.Type__c, a.OwnerId, a.Name, a.Some_Value32__c, a.Id, a.Sample3__c ';
        if(currentsample2.Id != null)
        {
            String sample2Id = String.escapeSingleQuotes(currentsample2.Id); //Escape Sring to protect against SOQL Inection
            sampleQuery = sampleQuery + ',(Select Id, Some_Price__c, sample2__c, sample__c from Some_Vals__r where sample2__c = \'' + sample2Id +'\') ';
        }else
        {
            sampleQuery = sampleQuery + ',(Select Id, Some_Price__c, sample2__c, sample__c from Some_Vals__r where sample2__c = null ) ';
        }
        sampleQuery = sampleQuery + ' From sample__c a ';

        if(sampleFilterSelected != null && sampleFilterSelected != '-Select One-')
        {
            sampleFilterSelected = String.escapeSingleQuotes(sampleFilterSelected); //Protect against SOQL Injection
            sampleQuery = sampleQuery + ' where a.Type__c INCLUDES(\'' + sampleFilterSelected + '\')';
        }
        sampleQuery = sampleQuery + ' order by Name';
        return sampleQuery;

 

 

This code passes a Security Scan cleanly. Try something similar to above.

Mitesh SuraMitesh Sura

Dear Cory,

 

I wish this were true. I have a dynamic SOQL and the parameteres do escape single quotes. I still cannot pass the security review.

This is year old post, has something changed recently?

 

This is stopping me to release beta version of the app. Please help. Thank you for your time.

 

regards

ISVforce Partner.