What is it?
SQL injection is a common application security flaw that results from insecure construction of database queries with user-supplied data. When queries are built directly with user data inlined or concatenated directly with the query text, instead of using type-safe bind parameters, malicious input may be able to change the structure of the query to bypass or change application logic. SQL injection flaws are extremely serious. A single flaw anywhere in your application may allow an attacker to read, modify or delete your entire database.
Apex does not use SQL, but its own database query language, SOQL. SOQL is much simpler and more limited in functionality than SQL. Therefore, the risks are much lower for SOQL injection than for SQL injection, but the attacks are nearly identical to traditional SQL injection.
Sample Vulnerability
Consider the following code for constructing a SQL query to handle a login. The user and password variables are user input from a web page form, and are concatenated into the query string to form the final request to the database.
1 | query = "select * from users where user = '" + |
2 | Request.form("user") + "' and password = '" + |
3 | getSaltedHash(Request.form("password")) + "'"; |
5 | queryResult = Database.executeQuery(query); |
Consider what would happen if somebody entered the following text into the username field on the web page form:
After concatenating the components together, the final query string would be:
1 | select * from users where user = 'administrator'<i>--' and password = ''</i> |
The single quote at the end of the username closes the variable declaration, and the double-dash comments out the remainder of the query. The user will be logged on as “administrator” without having to provide a password.
This simple manipulation is devastating, but worse may be possible. An attacker might use a semicolon to insert additional commands to add new accounts, delete data or use sub-selects to extract information from other tables. Even if the full results of a query aren’t shown directly to the user, the entire contents of a database can be methodically extracted using the computational capabilities built into SQL, using a technique known as blind SQL injection.
Is My Application Vulnerable?
If you use dynamic database queries and do not strictly enforce a coding policy requiring only bind variables (also known asparameterized queries), your application may be vulnerable.
How Can I Test My Application?
Some testing for SQL injection can be performed in a black-box manner. Putting characters like single quotes and dashes into form fields and looking for database error messages will find the most obvious SQL injection flaws. Unfortunately, these techniques cannot find all SQL injection flaws. Client-side validation, escaping or double-quoting may block simple attacks but be bypassed easily by an attacker.
The most reliable way to identify SQL injection flaws is through manual code review or with a static code analysis tool. Code analysis tools (commercial and free) are listed for individual development platforms below.
Developers on the Force.com platform can make use of the first on-demand source code analysis tool build solely for Platform as a Service. Visit the
Force.com Security Source Code Scanner page for more details.
If performing manual source code review, verify that all queries that include user data are built using bind variables instead of string concatenation. A bind variable is a placeholder in a query that allows the database engine to insert dynamic values in a type-safe manner. The exact syntax varies somewhat from platform to platform, but typically these placeholders are question marks or a colon-prefixed variable name. For example, the following construct is safe from SQL injection:
1 | PreparedStatement query = "select * from users where userid = :user and password = :password"; |
2 | query.bindInt("user", Request.form("user").intValue()); |
3 | query.bindString("password", getSaltedHash(Request.form("password"))); |
4 | Database.executePreparedStatement(query); |
Stored procedures that only use static SQL text are also acceptable, but beware of stored procedures that use exec or similar constructs to build dynamic SQL internally.
How Do I Protect My Application?
Apex and Visualforce Applications
Unlike other database interfaces, SOQL/SOSL in the force.com platform does not allow for code injection or taking over the database process, so the benefit to an attacker of changing the structure of the SOQL query consists in accessing fields that the programmer did not intend and so did not perform proper CRUD/FLS checks for. In this sense, SOQL injection is a special case of CRUD/FLS bypass. Developers must be careful to perform proper isAccessible checks on the results of queries returned to the user and in order to do these checks, developers need to know which objects and fields are being queried.
Below is a simple example of Apex and Visualforce code vulnerable to SOQL injection:
01 | <apex:page controller="SOQLController" > |
03 | <apex:outputText value="Enter Name" /> |
04 | <apex:inputText value="{!name}" /> |
05 | <apex:commandButton value="Query" action="{!query}" /> |
09 | public with sharing class SOQLController { |
14 | public PageReference query() { |
15 | if (!Schema.sObjectType.Contact.fields.Name.isAccessible()){ |
18 | String qryString = 'SELECT Id FROM Contact WHERE |
19 | (IsDeleted = false and Name like \'%' + name + '%\')'; |
21 | queryResult = Database.query(qryString); |
This is a very simple example but illustrates the logic. The code is intended to search for contacts that have not been deleted. The user provides one input value called “name”. The value can be anything provided by the user and it is never validated. The SOQL query is built dynamically and then executed with the Database.query method. The isAccessible() check is sufficient only if the contact Name field is being queried. But in this case, the attacker can force a query against other fields as well, by manipulating the name method. For example, suppose the Contact Object has been decorated with a 'CreditCard__c' method and the attacker submits:
1 | name = John Smith' and CreditCard__c like '1 |
Now the attacker can determine whether the first credit number of John Smith starts with a 1. The attacker could then launch a linear time attack against the credit card field, even though they do not have CRUD/FLS permission to view the credit card field. Note that 'sharing' does not help, since 'sharing' is object level access and not field level access. There is a lot of confusion in thinking that 'with sharing' can somehow protect against SOQL injection, when SOQL injection is a CRUD/FLS bypass, not a sharing issue.
To prevent a SOQL injection attack, avoid using dynamic SOQL queries. Instead, use static queries and bind variables. The vulnerable example above could be re-written using static SOQL as follows:
01 | public with sharing class SOQLController { |
06 | public PageReference query() { |
07 | if (!Schema.sObjectType.Contact.fields.Name.isAccessible()){ |
10 | String queryName = '%' + name + '%' |
11 | queryResult = [SELECT Id FROM Contact |
12 | WHERE (IsDeleted = false and Name like :queryName)]; |
If you must use dynamic SOQL, use the escapeSingleQuotes method to sanitize user-supplied input in a quoted context and verify that the user has the appropriate CRUD access to fields and objects. EscapeSingleQuotes 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.
Please avoid the user of third party query builder SOQL generation libraries or query builder libraries, as these are vulnerable to SOQL injection and ARE NOT SAFE FOR PRODUCTION USE without substantial refactoring.
See the following pages from the Apex documentation for more information:
SOQL INJECTION And Force.com APIs
The Force.com REST and SOAP APIs allow end users to submit arbitrary SOQL strings. However, this does not lead to SOQL injection because the APIs include built in checks for sharing and CRUD/FLS permissions. This means that end users are only allowed to see or modify records and fields that they already have access to. On the other hand, when making SOQL calls in Apex Code, no CRUD/FLS checks are performed (and sharing checks are only performed if the 'with sharing' keyword is used). Therefore it is a serious security vulnerability to allow end users to control the contents of a SOQL query issued in Apex code, but not for end users to control the contents of a SOQL query via the API
SOQL Injection Information
ReplyDeleteSOQL injection is a technique by which user effects your application to execute the database methods and you did not intend by passing SOQL statements into your script. Means any user can hack your Database or do fake login in to your secure account without any knowing your password.
This occur in an Apex script whenever your application relies on end user input to the construct a dynamic SOQL statement and you do not handle the input properly. This is the most secure thing you should know about your code of
To prevent SOQL injection, use the escapeSingleQuotes (like ‘\’) method in the Dynamic SOQL. This method adds the escape character (\) to all single quotation marks in a string that is passed in from any user. The method ensures that all single quotation marks are treated as enclosing strings, instead of database commands.
Code:
public Account[] getAccountInfo() {
String userInput = Apexpages.currentPage().getParameters().get('nameofAccount');
Account[] accs = database.query('SELECT name,address,city FROM Account WHERE name = \'' + userInput + '\'');
return accs;
}
Description:
Above code explain it self user enters Account name and Dynamic SOQL used this name and returns the information about Account.
However if there is hacker user enter Account name like ‘Accoun1’ or ‘xxxxx’ so he can get your secure Account information. We can prevent this write the Class as “with sharing”.