Drills › Query the org (SOQL)

Search safely with dynamic SOQL

Query the org (SOQL) · Intermediate · 18 min · Runs in your Salesforce Developer org · Apex Path

Scenario. A search box passes whatever the user types to countByName. Right now a user can type a quote and read Accounts they did not ask for.

Fix a dynamic SOQL search so a quote in the input cannot rewrite the query, using queryWithBinds or escapeSingleQuotes.

What you will learn

Dynamic SOQL builds query text while the code runs. If you glue user input into it, a quote in the input can end your string and add its own filter. This is SOQL injection. Pass the input as a bind instead:

Map<String, Object> binds = new Map<String, Object>{ 'wanted' => name };
String query = 'SELECT Id FROM Account WHERE Name = :wanted';
List<Account> rows = Database.queryWithBinds(query, binds, AccessLevel.USER_MODE);

Gotcha. The name after : must match a key in the Map.

The task

Fix countByName(String nameFilter) so the name it gets can't change its dynamic SOQL query.

Examples:

• countByName(tag + '-Acme') → 1, printed as Acme matches: 1
• a name no Account has, like tag + '-Missing' → 0
• the attack countByName(tag + '-Acme\' OR Name LIKE \'%') → 0, printed as Attack matches: 0

Requirements:

1. Keep the seed lines, and the method's name, input and Integer return type.
2. Stop gluing nameFilter into the query text. Bind it with Database.countQueryWithBinds (or queryWithBinds), or clean it first with String.escapeSingleQuotes.
3. Count only Accounts whose Name equals nameFilter exactly.
4. Print Acme matches: and Attack matches:. The grader also calls your method with other names, some of them hidden.

Starter code

Integer countByName(String nameFilter) {
    String query = 'SELECT COUNT() FROM Account WHERE Name = \'' + nameFilter + '\'';
    return Database.countQuery(query);
}

String tag = 'AS-Q313-' + UserInfo.getUserId() + '-' +
    String.valueOf(Datetime.now().getTime());
insert new List<Account>{
    new Account(Name = tag + '-Acme'),
    new Account(Name = tag + '-Globex'),
    new Account(Name = tag + '-O\'Brien')
};
System.debug('Acme matches: ' + countByName(tag + '-Acme'));
System.debug('Attack matches: ' + countByName(tag + '-Acme\' OR Name LIKE \'%'));

When it passes

Each real name finds its one Account, a missing name finds none, and an input with quotes in it is treated as plain text, so the attack finds 0.

Try this drill free

You write the Apex yourself. ApexSensei runs it and tells you what passed and what did not.

‹ Loop over Accounts in chunks · Filter with date literals ›