Salesforce Developer Soql Basic

salesforce-developer-soql

// Salesforce - Developer - SOQL:

Account a = [select name from Account where id = :id];
List<Account> alist = [select name from Account];
update alist;
insert alist;
delete alist;

The above SOQL statements are static queries, and demonstrate the use 
of bind variables.

Using bind variables ensures that the user input is treated as data, not as 
executable element of the query.

With SOQL, we do not really write database statement to directly update
the database.  Instead, we write select statement to retrieve the data from
the database, automatically convert that data into objects, modify those 
objects, and then issue the update statement with:

Perhaps, it is possible to directly run a database statement to insert, update,
or delete rows from database like how we typically do it in the SQL world, 
without having to create objects in memory.  We may be able to do this perhaps 
by using the static methods of the Database class (this is beside the insert, 
and update methods that are used with list of objects or single object). I
need to dig more into this.

Using bind variables is recommended.  However, there are some limitations.  
They can only be use in these types of clauses:

1. The search string in the FIND clauses

2. The filter literals in the WHERE clauses

3. The value of the IN or NOT IN operator in WHERE clauses, enabling filtering 
   on  a dynamic set of values.  This is of particular use with a list of IDs or
   strings, though it works with lists of any type.

4. The division names in WITH DIVISION clauses.

5. The numeric value in LIMIT clauses

6. The numeric value in OFFSET clauses.

Beside using static queries with bind variables, there are other techniques that 
we can use to prevent SOQL injection attacks:

1. Type casting: We just have to make sure that we use appropriate data type.
   For example, age is typically should be of Integer type.  If the user input is
   '1 limit 1', we will see an error '"1 limit 1" is not considered an integer'.  
   Type-casting is only effective for non-string input.

2. Using String.escapeSingleQuotes: This function escapes any quote character 
   using the escape character.  This prevents an attacker's input from being 
   treated as code.  Using String.escapeSingleQuotes does not work if the input 
   does not contain any quote character.

3. Replacing characters: If you have a scenario where String.escapeSingleQuotes, 
   type-casting, and whitelisting are not valid defense, the final approach is 
   character replacement, also known as blacklisting.  This approach removes 
   bad characters from the user's input.

   In security, blacklisting will never be as strong as whitelisting because it is 
   far easier to predict a few good inputs than it is to predict all possible bad inputs.
   Blacklisting through character replacement can often effectively mitigate 
   simple problems.

4. Whitelisting:  Create a list of all known good values that the user is allowed to 
   supply.  If the user enters anything else, we reject the response.  Here, we 
   are talking about using an 'if' construct in the code such as:
   if ((obj == '...') || (obj == '...') || (obj == '...')) { ...}
   or something akin to that.

Account[] accts = [SELECT Name,Phone FROM Account];
SELECT Name,Phone FROM Account
SELECT fields FROM ObjectName [WHERE Condition]
SELECT Name,Phone FROM Account WHERE Name='SFDC Computing'
SELECT Name,Phone FROM Account 
  WHERE (Name='SFDC Computing' AND NumberOfEmployees>25)
SELECT Name,Phone FROM Account 
  WHERE (Name='SFDC Computing' OR (NumberOfEmployees>25 AND BillingCity='Los Angeles'))
SELECT Name,Phone FROM Account ORDER BY Name
SELECT Name,Phone FROM Account ORDER BY Name ASC
SELECT Name,Phone FROM Account ORDER BY Name DESC
SELECT Name,Phone FROM Account LIMIT 1
SELECT Name,Phone FROM Account 
  WHERE (Name = 'SFDC Computing' AND NumberOfEmployees>25)
  ORDER BY Name
   LIMIT 10
String targetDepartment = 'Wingo';
Contact[] techContacts = [SELECT FirstName,LastName 
  FROM Contact WHERE Department=:targetDepartment];

SELECT NAME, PHONE, ACCOUNT.NAME FROM CONTACT

SOQL is essentially a stripped down version of SQL written specifically for the 
Salesforce platform.  Here are some ways that SOQL differs from SQL:

1. No INSERT, UPDATE, or DELETE statement, only SELECT.

2. No command execution

3. No wild cards for fields.  All fields must be explicitly typed / mentioned.

4. No JOIN statements.  However, we can include information from parent 
   objects like: SELECT NAME, PHONE, ACCOUNT.NAME FROM CONTACT

5. No UNION operator

6. Queries cannot be chained together.

Even with the above restrictions, SOQL is still vulnerable to injection attacks 
if the developer incorrectly trust the user input, leading to exposure of 
information.

Example of SOQL statement that are vulnerable to SOQL injection attacks:

String query = 'select Name, Role__c, Title__c, Age__c from Personnel__c';
String whereClause = '';
whereClause += 'Title__c like  \'%'+textualTitle+'%\' ';
whereclause_records = database.query(query+' where '+whereClause);

SOQL statements can include the Translating Results, converCurrency(), and 
FORMAT() functions to support localized fields.

We can search for null values by using the null keyword:

SELECT AccountId FROM Event WHERE ActivityDate != null