Salesforce Developer Soql Basic
// 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
page revision: 1, last edited: 21 Mar 2017 16:17