Kingdom: Input Validation and Representation

Input validation and representation problems ares caused by metacharacters, alternate encodings and numeric representations. Security problems result from trusting input. The issues include: "Buffer Overflows," "Cross-Site Scripting" attacks, "SQL Injection," and many others.

12 items found
Weaknesses
Abstract
Constructing a dynamic SQL statement with input that comes from an untrusted source could allow an attacker to modify the statement's meaning or to execute arbitrary SQL commands.
Explanation
SQL injection errors occur when:

1. Data enters a program from an untrusted source.

2. The data used to dynamically construct a SQL query.
Example 1: The following code dynamically constructs and executes a SQL query designed to search for invoices belonging to a user. The query restricts the items displayed to those where user is equal to the user name of the currently authenticated user.


...
v_account = request->get_form_field( 'account' ).
v_reference = request->get_form_field( 'ref_key' ).

CONCATENATE `user = '` sy-uname `'` INTO cl_where.
IF v_account IS NOT INITIAL.
CONCATENATE cl_where ` AND account = ` v_account INTO cl_where SEPARATED BY SPACE.
ENDIF.
IF v_reference IS NOT INITIAL.
CONCATENATE cl_where "AND ref_key = `" v_reference "`" INTO cl_where.
ENDIF.

SELECT *
FROM invoice_items
INTO CORRESPONDING FIELDS OF TABLE itab_items
WHERE (cl_where).
...


The query this code intends to execute is the following(provided v_account and v_reference are not blanks):


SELECT *
FROM invoice_items
INTO CORRESPONDING FIELDS OF TABLE itab_items
WHERE user = sy-uname
AND account = <account>
AND ref_key = <reference>.


However, because the query is constructed dynamically by concatenating a constant base query string and a user input string, it is a candidate for SQL injection attacks. If an attacker enters the string "abc` OR MANDT NE `+" for v_reference and string '1000' for v_account, then the query becomes the following:


SELECT *
FROM invoice_items
INTO CORRESPONDING FIELDS OF TABLE itab_items
WHERE user = sy-uname
AND account = 1000
AND ref_key = `abc` OR MANDT NE `+`.


The addition of the OR MANDT NE `+` condition causes the WHERE clause to always evaluate to true because the client field can never be equal to literal +, so query becomes logically equivalent to the much simpler query:


SELECT * FROM invoice_items
INTO CORRESPONDING FIELDS OF TABLE itab_items.


This simplification of the query allows the attacker to bypass the requirement that the query must only return items owned by the authenticated user. The query now returns all entries stored in the invoice_items table, regardless of the specified user.

Example 2: In this example, we will consider the usage of ADBC API in a program that lets employees update their address.


PARAMETERS: p_street TYPE string,
p_city TYPE string.

Data: v_sql TYPE string,
stmt TYPE REF TO CL_SQL_STATEMENT.

v_sql = "UPDATE EMP_TABLE SET ".

"Update employee address. Build the update statement with changed details
IF street NE p_street.
CONCATENATE v_sql "STREET = `" p_street "`".
ENDIF.
IF city NE p_city.
CONCATENATE v_sql "CITY = `" p_city "`".
ENDIF.

l_upd = stmt->execute_update( v_sql ).



If a disgruntled employee inputs a string like "ABC` SALARY = `1000000" for the parameter p_street, the application lets the database be updated with revised salary!

One traditional approach to preventing SQL injection attacks is to handle them as an input validation problem and either accept only an allow list of safe values or identify and escape a list of potentially malicious values (deny list). Checking an allow list can be a very effective means of enforcing strict input validation rules, but parameterized SQL statements require less maintenance and can offer more guarantees with respect to security. As is almost always the case, implementing a deny list is riddled with loopholes that make it ineffective at preventing SQL injection attacks. For example, attackers may:

- Target fields that are not quoted
- Find ways to bypass the need for certain escaped metacharacters
- Use stored procedures to hide the injected metacharacters

Manually escaping characters in input to SQL queries can help, but it will not make your application secure from SQL injection attacks.

References
[1] SAP OSS notes 1520356, 1487337, 1502272 and related notes.
[2] S. J. Friedl SQL Injection Attacks by Example
[3] P. Litwin Stop SQL Injection Attacks Before They Stop You MSDN Magazine
[4] P. Finnigan SQL Injection and Oracle, Part One Security Focus
[5] M. Howard, D. LeBlanc Writing Secure Code, Second Edition Microsoft Press
desc.dataflow.abap.sql_injection
Abstract
Constructing a dynamic SQL statement with input that comes from an untrusted source might allow an attacker to modify the statement's meaning or to execute arbitrary SQL commands.
Explanation
SQL injection errors occur when:

1. Data enters a program from an untrusted source.



2. The data is used to dynamically construct a SQL query.

Example 1: The following code dynamically constructs and executes a SQL query that searches for items matching a specified name. The query restricts the items displayed to those where the owner matches the user name of the currently-authenticated user.


...
var params:Object = LoaderInfo(this.root.loaderInfo).parameters;
var username:String = String(params["username"]);
var itemName:String = String(params["itemName"]);
var query:String = "SELECT * FROM items WHERE owner = " + username + " AND itemname = " + itemName;

stmt.sqlConnection = conn;
stmt.text = query;
stmt.execute();
...


The query intends to execute the following code:


SELECT * FROM items
WHERE owner = <userName>
AND itemname = <itemName>;


However, because the query is constructed dynamically by concatenating a constant base query string and a user input string, the query only behaves correctly if itemName does not contain a single-quote character. If an attacker with the user name wiley enters the string "name' OR 'a'='a" for itemName, then the query becomes the following:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name' OR 'a'='a';


The addition of the OR 'a'='a' condition causes the where clause to always evaluate to true, so the query becomes logically equivalent to the much simpler query:


SELECT * FROM items;


This simplification of the query allows the attacker to bypass the requirement that the query must only return items owned by the authenticated user. The query now returns all entries stored in the items table, regardless of their specified owner.

Example 2: This example examines the effects of a different malicious value passed to the query constructed and executed in Example 1. If an attacker with the user name wiley enters the string "name'; DELETE FROM items; --" for itemName, then the query becomes the following two queries:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name';

DELETE FROM items;

--'


Many database servers, including Microsoft(R) SQL Server 2000, allow multiple SQL statements separated by semicolons to be executed at once. While this attack string results in an error on Oracle and other database servers that do not allow the batch-execution of statements separated by semicolons, on databases that do allow batch execution, this type of attack allows the attacker to execute arbitrary commands against the database.

Notice the trailing pair of hyphens (--), which specifies to most database servers that the remainder of the statement is to be treated as a comment and not executed [4]. In this case the comment character serves to remove the trailing single-quote left over from the modified query. On a database where comments are not allowed to be used in this way, the general attack could still be made effective using a trick similar to the one shown in Example 1. If an attacker enters the string "name'); DELETE FROM items; SELECT * FROM items WHERE 'a'='a", the following three valid statements will be created:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name';

DELETE FROM items;

SELECT * FROM items WHERE 'a'='a';


One traditional approach to preventing SQL injection attacks is to handle them as an input validation problem and either accept only characters from an allow list of safe values or identify and escape a list of potentially malicious values (deny list). Checking an allow list can be a very effective means of enforcing strict input validation rules, but parameterized SQL statements require less maintenance and can offer more guarantees with respect to security. As is almost always the case, implementing a deny list is riddled with loopholes that make it ineffective at preventing SQL injection attacks. For example, attackers may:

- Target fields that are not quoted
- Find ways to bypass the need for certain escaped metacharacters
- Use stored procedures to hide the injected metacharacters

Manually escaping characters in input to SQL queries can help, but it will not make your application secure from SQL injection attacks.

Another solution commonly proposed for dealing with SQL injection attacks is to use stored procedures. Although stored procedures prevent some types of SQL injection attacks, they fail to protect against many others. Stored procedures typically help prevent SQL injection attacks by limiting the types of statements that can be passed to their parameters. However, there are many ways around the limitations and many interesting statements that can still be passed to stored procedures. Again, stored procedures can prevent some exploits, but they will not make your application secure against SQL injection attacks.
References
[1] S. J. Friedl SQL Injection Attacks by Example
[2] P. Litwin Stop SQL Injection Attacks Before They Stop You MSDN Magazine
[3] P. Finnigan SQL Injection and Oracle, Part One Security Focus
[4] M. Howard, D. LeBlanc Writing Secure Code, Second Edition Microsoft Press
desc.dataflow.actionscript.sql_injection
Abstract
Constructing a dynamic SQL statement with input that comes from an untrusted source might allow an attacker to modify the statement's meaning or to execute arbitrary SQL commands.
Explanation
SQL injection errors occur when:

1. Data enters a program from an untrusted source.

2. The data is used to dynamically construct a SQL query.
Example 1: The following code dynamically constructs and executes a SQL query that searches for items matching a specified name. The query restricts the items displayed to those where owner matches the user name of the currently-authenticated user.


...
string userName = ctx.getAuthenticatedUserName();
string query = "SELECT * FROM items WHERE owner = '"
+ userName + "' AND itemname = '"
+ ItemName.Text + "'";
sda = new SqlDataAdapter(query, conn);
DataTable dt = new DataTable();
sda.Fill(dt);
...


The query intends to execute the following code:


SELECT * FROM items
WHERE owner = <userName>
AND itemname = <itemName>;


However, because the query is constructed dynamically by concatenating a constant base query string and a user input string, the query only behaves correctly if itemName does not contain a single-quote character. If an attacker with the user name wiley enters the string "name' OR 'a'='a" for itemName, then the query becomes the following:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name' OR 'a'='a';


The addition of the OR 'a'='a' condition causes the where clause to always evaluate to true, so the query becomes logically equivalent to the much simpler query:


SELECT * FROM items;


This simplification of the query allows the attacker to bypass the requirement that the query must only return items owned by the authenticated user. The query now returns all entries stored in the items table, regardless of their specified owner.

Example 2: This example examines the effects of a different malicious value passed to the query constructed and executed in Example 1. If an attacker with the user name wiley enters the string "name'); DELETE FROM items; --" for itemName, then the query becomes the following two queries:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name';

DELETE FROM items;

--'


Many database servers, including Microsoft(R) SQL Server 2000, allow multiple SQL statements separated by semicolons to be executed at once. While this attack string results in an error on Oracle and other database servers that do not allow the batch-execution of statements separated by semicolons, on databases that do allow batch execution, this type of attack allows the attacker to execute arbitrary commands against the database.

Notice the trailing pair of hyphens (--), which specifies to most database servers that the remainder of the statement is to be treated as a comment and not executed [4]. In this case the comment character serves to remove the trailing single-quote left over from the modified query. On a database where comments are not allowed to be used in this way, the general attack could still be made effective using a trick similar to the one shown in Example 1. If an attacker enters the string "name'); DELETE FROM items; SELECT * FROM items WHERE 'a'='a", the following three valid statements will be created:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name';

DELETE FROM items;

SELECT * FROM items WHERE 'a'='a';


One traditional approach to preventing SQL injection attacks is to handle them as an input validation problem and either accept only characters from an allow list of safe values or identify and escape a list of potentially malicious values (deny list). Checking an allow list can be a very effective means of enforcing strict input validation rules, but parameterized SQL statements require less maintenance and can offer more guarantees with respect to security. As is almost always the case, implementing a deny list is riddled with loopholes that make it ineffective at preventing SQL injection attacks. For example, attackers may:

- Target fields that are not quoted
- Find ways to bypass the need for certain escaped metacharacters
- Use stored procedures to hide the injected metacharacters

Manually escaping characters in input to SQL queries can help, but it will not make your application secure from SQL injection attacks.

Another solution commonly proposed for dealing with SQL injection attacks is to use stored procedures. Although stored procedures prevent some types of SQL injection attacks, they fail to protect against many others. Stored procedures typically help prevent SQL injection attacks by limiting the types of statements that can be passed to their parameters. However, there are many ways around the limitations and many interesting statements that can still be passed to stored procedures. Again, stored procedures can prevent some exploits, but they will not make your application secure against SQL injection attacks.
References
[1] S. J. Friedl SQL Injection Attacks by Example
[2] P. Litwin Stop SQL Injection Attacks Before They Stop You MSDN Magazine
[3] P. Finnigan SQL Injection and Oracle, Part One Security Focus
[4] M. Howard, D. LeBlanc Writing Secure Code, Second Edition Microsoft Press
desc.dataflow.dotnet.sql_injection
Abstract
Constructing a dynamic SQL statement with input that comes from an untrusted source could allow an attacker to modify the statement's meaning or to execute arbitrary SQL commands.
Explanation
SQL injection errors occur when:

1. Data enters a program from an untrusted source.

2. The data is used to dynamically construct a SQL query.
Example 1: The following code dynamically constructs and executes a SQL query that searches for items matching a specified name. The query restricts the items displayed to those where the owner matches the user name of the currently-authenticated user.


...
ctx.getAuthUserName(&userName); {
CString query = "SELECT * FROM items WHERE owner = '"
+ userName + "' AND itemname = '"
+ request.Lookup("item") + "'";
dbms.ExecuteSQL(query);
...
Example 2:Alternatively, a similar result could be obtained with SQLite using the following code:


...
sprintf (sql, "SELECT * FROM items WHERE owner='%s' AND itemname='%s'", username, request.Lookup("item"));
printf("SQL to execute is: \n\t\t %s\n", sql);
rc = sqlite3_exec(db,sql, NULL,0, &err);
...


The query intends to execute the following code:


SELECT * FROM items
WHERE owner = <userName>
AND itemname = <itemName>;


However, because the query is constructed dynamically by concatenating a constant base query string and a user input string, the query only behaves correctly if itemName does not contain a single-quote character. If an attacker with the user name wiley enters the string "name' OR 'a'='a" for itemName, then the query becomes the following:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name' OR 'a'='a';


The addition of the OR 'a'='a' condition causes the where clause to always evaluate to true, so the query becomes logically equivalent to the much simpler query:


SELECT * FROM items;


This simplification of the query allows the attacker to bypass the requirement that the query must only return items owned by the authenticated user. The query now returns all entries stored in the items table, regardless of their specified owner.

Example 3: This example examines the effects of a different malicious value passed to the query constructed and executed in Example 1. If an attacker with the user name wiley enters the string "name'); DELETE FROM items; --" for itemName, then the query becomes the following two queries:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name';

DELETE FROM items;

--'


Many database servers, including Microsoft(R) SQL Server 2000, allow multiple SQL statements separated by semicolons to be executed at once. While this attack string results in an error on Oracle and other database servers that do not allow the batch-execution of statements separated by semicolons, on databases that do allow batch execution, this type of attack allows the attacker to execute arbitrary commands against the database.

Notice the trailing pair of hyphens (--), which specifies to most database servers that the remainder of the statement is to be treated as a comment and not executed [4]. In this case the comment character serves to the trailing single-quote left over from the modified query. On a database where comments are not allowed to be used in this way, the general attack could still be made effective using a trick similar to the one shown in Example 1. If an attacker enters the string "name'); DELETE FROM items; SELECT * FROM items WHERE 'a'='a", the following three valid statements will be created:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name';

DELETE FROM items;

SELECT * FROM items WHERE 'a'='a';


One traditional approach to preventing SQL injection attacks is to handle them as an input validation problem and either accept only characters from an allow list of safe values or identify and escape a list of potentially malicious values (deny list). Checking an allow list can be a very effective means of enforcing strict input validation rules, but parameterized SQL statements require less maintenance and can offer more guarantees with respect to security. As is almost always the case, implementing a deny list is riddled with loopholes that make it ineffective at preventing SQL injection attacks. For example, attackers may:

- Target fields that are not quoted
- Find ways to bypass the need for certain escaped metacharacters
- Use stored procedures to hide the injected metacharacters

Manually escaping characters in input to SQL queries can help, but it will not make your application secure from SQL injection attacks.

Another solution commonly proposed for dealing with SQL injection attacks is to use stored procedures. Although stored procedures prevent some types of SQL injection attacks, they fail to protect against many others. Stored procedures typically help prevent SQL injection attacks by limiting the types of statements that can be passed to their parameters. However, there are many ways around the limitations and many interesting statements that can still be passed to stored procedures. Again, stored procedures can prevent some exploits, but they will not make your application secure against SQL injection attacks.
References
[1] S. J. Friedl SQL Injection Attacks by Example
[2] P. Litwin Stop SQL Injection Attacks Before They Stop You MSDN Magazine
[3] P. Finnigan SQL Injection and Oracle, Part One Security Focus
[4] M. Howard, D. LeBlanc Writing Secure Code, Second Edition Microsoft Press
[5] Parameterized CRecordset and CDatabase for SQL Server
[6] Parameterizing a Recordset Microsoft
[7] ODBC API Reference: SQLNumParams() Microsoft
[8] ODBC API Reference: SQLBindParameter() Microsoft
[9] OLE DB Reference: ICommandWithParameters Microsoft
desc.dataflow.cpp.sql_injection
Abstract
Constructing a dynamic SQL statement with input that comes from an untrusted source could allow an attacker to modify the statement's meaning or to execute arbitrary SQL commands.
Explanation
SQL injection errors occur when:

1. Data enters a program from an untrusted source.

2. The data used to dynamically construct a SQL query.
Example 1: The following code dynamically constructs and executes a SQL query designed to search for items matching a specified name. The query restricts the items displayed to those where owner is equal to the user name of the currently authenticated user.


...
ACCEPT USER.
ACCEPT ITM.
MOVE "SELECT * FROM items WHERE owner = '" TO QUERY1.
MOVE "' AND itemname = '" TO QUERY2.
MOVE "'" TO QUERY3.

STRING
QUERY1, USER, QUERY2, ITM, QUERY3 DELIMITED BY SIZE
INTO QUERY
END-STRING.

EXEC SQL
EXECUTE IMMEDIATE :QUERY
END-EXEC.
...


The query this code intends to execute is the following:


SELECT * FROM items
WHERE owner = <userName>
AND itemname = <itemName>;


However, because the query is constructed dynamically by concatenating a constant base query string and a user input string, the query only behaves correctly if itemName does not contain a single-quote character. If an attacker with the user name wiley enters the string "name' OR 'a'='a" for itm, then the query becomes the following:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name' OR 'a'='a';


The addition of the OR 'a'='a' condition causes the where clause to always evaluate to true, so query becomes logically equivalent to the much simpler query:


SELECT * FROM items;


This simplification of the query allows the attacker to bypass the requirement that the query must only return items owned by the authenticated user. The query now returns all entries stored in the items table, regardless of their specified owner.

Example 2: In this example, we will consider the effects of a different malicious value passed to the query constructed and executed in Example 1. If an attacker with the user name wiley enters the string "name'; DELETE FROM items; --" for itemName, then the query becomes the following two queries:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name';

DELETE FROM items;

--'


Many database servers, including Microsoft(R) SQL Server 2000, allow multiple SQL statements separated by semicolons to be executed at once. While this attack string would result in an error on Oracle and other database servers that do not allow the batch-execution of statements separated by semicolons, on supported databases this type of attack will allow the execution of arbitrary commands against the database.

Notice the trailing pair of hyphens (--); these indicate to most database servers that the remainder of the statement is to be treated as a comment and not executed [4]. In this case the comments are used to remove the trailing single-quote leftover from the modified query. On a database where comments are not allowed to be used in this way, the general attack could still be made effective using a trick similar to the one shown in Example 1. If an attacker enters the string "name'); DELETE FROM items; SELECT * FROM items WHERE 'a'='a", the following three valid statements will be created:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name';

DELETE FROM items;

SELECT * FROM items WHERE 'a'='a';


One traditional approach to preventing SQL injection attacks is to handle them as an input validation problem and either accept only an allow list of safe values or identify and escape a list of potentially malicious values (deny list). Checking an allow list can be a very effective means of enforcing strict input validation rules, but parameterized SQL statements require less maintenance and can offer more guarantees with respect to security. As is almost always the case, implementing a deny list is riddled with loopholes that make it ineffective at preventing SQL injection attacks. For example, attackers may:

- Target fields that are not quoted
- Find ways to bypass the need for certain escaped metacharacters
- Use stored procedures to hide the injected metacharacters

Manually escaping characters in input to SQL queries can help, but it will not make your application secure from SQL injection attacks.

Another solution commonly proposed for dealing with SQL injection attacks is to use stored procedures. Although stored procedures prevent some types of SQL injection attacks, they fail to protect against many others. Stored procedures typically help prevent SQL injection attacks by limiting the types of statements that can be passed to their parameters. However, there are many ways around the limitations and many interesting statements that can still be passed to stored procedures. Again, stored procedures can prevent some exploits, but they will not make your application secure against SQL injection attacks.
References
[1] S. J. Friedl SQL Injection Attacks by Example
[2] P. Litwin Stop SQL Injection Attacks Before They Stop You MSDN Magazine
[3] P. Finnigan SQL Injection and Oracle, Part One Security Focus
[4] M. Howard, D. LeBlanc Writing Secure Code, Second Edition Microsoft Press
desc.dataflow.cobol.sql_injection
Abstract
Constructing a dynamic SQL statement with input that comes from an untrusted source could allow an attacker to modify the statement's meaning or to execute arbitrary SQL commands.
Explanation
SQL injection errors occur when:

1. Data enters a program from an untrusted source.

2. The data used to dynamically construct a SQL query.
Example 1: The following code dynamically constructs and executes a SQL query that searches for items matching a specified name. The query restricts the items displayed to those where owner matches the user name of the currently-authenticated user.


...
<cfquery name="matchingItems" datasource="cfsnippets">
SELECT * FROM items
WHERE owner='#Form.userName#'
AND itemId=#Form.ID#
</cfquery>
...


The query intends to execute the following code:


SELECT * FROM items
WHERE owner = <userName>
AND itemId = <ID>;


However, because the query is constructed dynamically by concatenating a constant base query string and a user input string, the query only behaves correctly if Form.ID does not contain a single-quote character. If an attacker with the user name wiley enters the string "name' OR 'a'='a" for Form.ID, then the query becomes the following:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemId = 'name' OR 'a'='a';


The addition of the OR 'a'='a' condition causes the where clause to always evaluate to true, so the query becomes logically equivalent to the much simpler query:


SELECT * FROM items;


This simplification of the query allows the attacker to bypass the requirement that the query must only return items owned by the authenticated user. The query now returns all entries stored in the items table, regardless of their specified owner.

Example 2: This example examines the effects of a different malicious value passed to the query constructed and executed in Example 1. If an attacker with the user name hacker enters the string "hacker'); DELETE FROM items; --" for Form.ID, then the query becomes the following two queries:


SELECT * FROM items
WHERE owner = 'hacker'
AND itemId = 'name';

DELETE FROM items;

--'


Many database servers, including Microsoft(R) SQL Server 2000, allow multiple SQL statements separated by semicolons to be executed at once. While this attack string results in an error on Oracle and other database servers that do not allow the batch-execution of statements separated by semicolons, on databases that do allow batch execution, this type of attack allows the attacker to execute arbitrary commands against the database.

Notice the trailing pair of hyphens (--), which specifies to most database servers that the remainder of the statement is to be treated as a comment and not executed [4]. In this case the comment character serves to remove the trailing single-quote left over from the modified query. On a database where comments are not allowed to be used in this way, the general attack could still be made effective using a trick similar to the one shown in Example 1. If an attacker enters the string "name'); DELETE FROM items; SELECT * FROM items WHERE 'a'='a", the following three valid statements will be created:


SELECT * FROM items
WHERE owner = 'hacker'
AND itemId = 'name';

DELETE FROM items;

SELECT * FROM items WHERE 'a'='a';


One traditional approach to preventing SQL injection attacks is to handle them as an input validation problem and either accept only characters from an allow list of safe values or identify and escape a list of potentially malicious values (deny list). Checking an allow list can be a very effective means of enforcing strict input validation rules, but parameterized SQL statements require less maintenance and can offer more guarantees with respect to security. As is almost always the case, implementing a deny list is riddled with loopholes that make it ineffective at preventing SQL injection attacks. For example, attackers may:

- Target fields that are not quoted
- Find ways to bypass the need for certain escaped metacharacters
- Use stored procedures to hide the injected metacharacters

Manually escaping characters in input to SQL queries can help, but it will not make your application secure from SQL injection attacks.

Another solution commonly proposed for dealing with SQL injection attacks is to use stored procedures. Although stored procedures prevent some types of SQL injection attacks, they fail to protect against many others. Stored procedures typically help prevent SQL injection attacks by limiting the types of statements that can be passed to their parameters. However, there are many ways around the limitations and many interesting statements that can still be passed to stored procedures. Again, stored procedures can prevent some exploits, but they will not make your application secure against SQL injection attacks.
References
[1] S. J. Friedl SQL Injection Attacks by Example
[2] P. Litwin Stop SQL Injection Attacks Before They Stop You MSDN Magazine
[3] P. Finnigan SQL Injection and Oracle, Part One Security Focus
[4] M. Howard, D. LeBlanc Writing Secure Code, Second Edition Microsoft Press
desc.dataflow.cfml.sql_injection
Abstract
Using the Java J2EE PersistenceAPI to execute a dynamic SQL statement built with input coming from an untrusted source can enable an attacker to modify the statement's meaning or to execute arbitrary SQL commands.
Explanation
SQL injection errors occur when:

1. Data enters a program from an untrusted source.



2. The data is used to dynamically construct a SQL query.

Example 1: The following code dynamically constructs and executes a SQL query that searches for items matching a specified name. The query restricts the items displayed to those where the owner matches the user name of the currently-authenticated user.


...
final server = await HttpServer.bind('localhost', 18081);
server.listen((request) async {
final headers = request.headers;
final userName = headers.value('userName');
final itemName = headers.value('itemName');
final query = "SELECT * FROM items WHERE owner = '"
+ userName! + "' AND itemname = '"
+ itemName! + "'";
db.query(query);
}
...


The query intends to execute the following code:


SELECT * FROM items
WHERE owner = <userName>
AND itemname = <itemName>;


However, because the query is constructed dynamically by concatenating a constant base query string and a user input string, the query only behaves correctly if itemName does not contain a single-quote character. If an attacker with the user name wiley enters the string "name' OR 'a'='a" for itemName, then the query becomes the following:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name' OR 'a'='a';


The addition of the OR 'a'='a' condition causes the where clause to always evaluate to true, so the query becomes logically equivalent to the much simpler query:


SELECT * FROM items;


This simplification of the query enables the attacker to bypass the requirement that the query must only return items owned by the authenticated user. The query now returns all entries stored in the items table, regardless of their specified owner.

Example 2: This example examines the effects of a different malicious value passed to the query constructed and executed in Example 1. If an attacker with the user name wiley enters the string "name'; DELETE FROM items; --" for itemName, then the query becomes the following two queries:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name';

DELETE FROM items;

--'


Many database servers, including Microsoft(R) SQL Server 2000, allow multiple SQL statements separated by semicolons to be executed at once. While this attack string results in an error on Oracle and other database servers that do not allow the batch-execution of statements separated by semicolons, on databases that do allow batch execution, this type of attack enables the attacker to execute arbitrary commands against the database.

Notice the trailing pair of hyphens (--), which specifies to most database servers that the remainder of the statement is to be treated as a comment and not executed [4]. In this case, the comment character serves to remove the trailing single-quote left over from the modified query. On a database where comments are not allowed to be used in this way, the general attack could still be made effective using a trick similar to the one shown in Example 1. If an attacker enters the string "name'); DELETE FROM items; SELECT * FROM items WHERE 'a'='a", the following three valid statements will be created:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name';

DELETE FROM items;

SELECT * FROM items WHERE 'a'='a';


One traditional approach to preventing SQL injection attacks is to handle them as an input validation problem and either accept only characters from an allow list of safe values or identify and escape a list of potentially malicious values (deny list). Checking an allow list can be an effective means of enforcing strict input validation rules, but parameterized SQL statements require less maintenance and can offer more guarantees with respect to security. As is almost always the case, implementing a deny list is riddled with loopholes that make it ineffective at preventing SQL injection attacks. For example, attackers might:

- Target fields that are not quoted
- Find ways to bypass the need for certain escaped metacharacters
- Use stored procedures to hide the injected metacharacters

Manually escaping characters in input to SQL queries can help, but it will not make your application secure from SQL injection attacks.

Another solution commonly proposed to deal with SQL injection attacks is to use stored procedures. Although stored procedures prevent some types of SQL injection attacks, they fail to protect against many others. Stored procedures typically help prevent SQL injection attacks by limiting the types of statements that can be passed to their parameters. However, there are many ways around the limitations and many interesting statements that can still be passed to stored procedures. Again, stored procedures can prevent some types of exploits, but they will not make your application secure against SQL injection attacks.
desc.dataflow.dart.sql_injection
Abstract
Constructing a dynamic SQL statement with input that comes from an untrusted source enables an attacker to modify the statement's meaning or to execute arbitrary SQL commands.
Explanation
SQL injection errors occur when:

1. Data enters a program from an untrusted source.

2. The data is used to dynamically construct a SQL query.
Example 1: The following code dynamically constructs and executes a SQL query that searches for items matching a specified name. The query restricts the items displayed to those where the owner matches the user name of the currently-authenticated user.


...
rawQuery := request.URL.Query()
username := rawQuery.Get("userName")
itemName := rawQuery.Get("itemName")
query := "SELECT * FROM items WHERE owner = " + username + " AND itemname = " + itemName + ";"

db.Exec(query)
...


The query intends to execute the following code:


SELECT * FROM items
WHERE owner = <userName>
AND itemname = <itemName>;


However, because the code dynamically constructs the query by concatenating a constant base query string and a user input string, the query only behaves correctly if itemName does not contain a single-quote character. If an attacker with the user name wiley enters the string "name' OR 'a'='a" for itemName, then the query becomes the following:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name' OR 'a'='a';


The addition of the OR 'a'='a' condition causes the where clause to always evaluate to true, so the query becomes logically equivalent to the much simpler query:


SELECT * FROM items;


This simplification of the query enables the attacker to bypass the requirement that the query must only return items owned by the authenticated user. The query now returns all entries stored in the items table, regardless of their specified owner.

Example 2: This example examines the effects of a different malicious value passed to the query constructed and executed in Example 1. If an attacker with the user name wiley enters the string "name'; DELETE FROM items; --" for itemName, then the query becomes the following two queries:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name';

DELETE FROM items;

--'


Many database servers, including Microsoft(R) SQL Server 2000, allow the simultaneous execution of multiple SQL statements separated by semicolons. While this attack string results in an error on Oracle and other database servers that do not allow the batch-execution of statements separated by semicolons, on databases that do allow batch execution, this type of attack enables the attacker to execute arbitrary commands against the database.

Notice the trailing pair of hyphens (--), which specifies to most database servers to treat the remainder of the statement as a comment and to not execute it. [4]. In this case, the comment character serves to remove the trailing single-quote left over from the modified query. On a database where comments are not allowed to be used in this way, the general attack could still be made effective using a trick similar to the one shown in Example 1. If an attacker enters the string "name'; DELETE FROM items; SELECT * FROM items WHERE 'a'='a", the following three valid statements are created:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name';

DELETE FROM items;

SELECT * FROM items WHERE 'a'='a';


One traditional approach to prevent SQL injection attacks is to handle them as an input validation problem and either accept only characters from an allow list of safe values or identify and escape a list of potentially malicious values (deny list). Checking an allow list can be a very effective means of enforcing strict input validation rules, but parameterized SQL statements require less maintenance and can offer more guarantees with respect to security. As is almost always the case, implementing a deny list is riddled with loopholes that make it ineffective to prevent SQL injection attacks. For example, attackers can:

- Target fields that are not quoted
- Find ways to bypass the need for certain escaped metacharacters
- Use stored procedures to hide the injected metacharacters

Manually escaping characters in input to SQL queries can help, but it does not make your application secure from SQL injection attacks.

Another solution commonly proposed to deal with SQL injection attacks is to use stored procedures. Although stored procedures prevent some types of SQL injection attacks, they fail to protect against many others. Stored procedures typically help prevent SQL injection attacks by limiting the types of statements that can be passed to their parameters. However, there are many ways around the limitations and many interesting statements that can still be passed to stored procedures. Again, stored procedures can prevent some exploits, but they do not make your application secure against SQL injection attacks.
References
[1] S. J. Friedl SQL Injection Attacks by Example
[2] P. Litwin Stop SQL Injection Attacks Before They Stop You MSDN Magazine
[3] P. Finnigan SQL Injection and Oracle, Part One Security Focus
[4] M. Howard, D. LeBlanc Writing Secure Code, Second Edition Microsoft Press
desc.dataflow.golang.sql_injection
Abstract
Constructing a dynamic SQL statement with input that comes from an untrusted source could allow an attacker to modify the statement's meaning or to execute arbitrary SQL commands.
Explanation
SQL injection errors occur when:

1. Data enters a program from an untrusted source.



2. The data is used to dynamically construct a SQL query.

Example 1: The following code dynamically constructs and executes a SQL query that searches for items matching a specified name. The query restricts the items displayed to those where the owner matches the user name of the currently-authenticated user.


...
String userName = ctx.getAuthenticatedUserName();
String itemName = request.getParameter("itemName");
String query = "SELECT * FROM items WHERE owner = '"
+ userName + "' AND itemname = '"
+ itemName + "'";
ResultSet rs = stmt.execute(query);
...


The query intends to execute the following code:


SELECT * FROM items
WHERE owner = <userName>
AND itemname = <itemName>;


However, because the query is constructed dynamically by concatenating a constant base query string and a user input string, the query only behaves correctly if itemName does not contain a single-quote character. If an attacker with the user name wiley enters the string "name' OR 'a'='a" for itemName, then the query becomes the following:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name' OR 'a'='a';


The addition of the OR 'a'='a' condition causes the where clause to always evaluate to true, so the query becomes logically equivalent to the much simpler query:


SELECT * FROM items;


This simplification of the query allows the attacker to bypass the requirement that the query must only return items owned by the authenticated user. The query now returns all entries stored in the items table, regardless of their specified owner.

Example 2: This example examines the effects of a different malicious value passed to the query constructed and executed in Example 1. If an attacker with the user name wiley enters the string "name'; DELETE FROM items; --" for itemName, then the query becomes the following two queries:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name';

DELETE FROM items;

--'


Many database servers, including Microsoft(R) SQL Server 2000, allow multiple SQL statements separated by semicolons to be executed at once. While this attack string results in an error on Oracle and other database servers that do not allow the batch-execution of statements separated by semicolons, on databases that do allow batch execution, this type of attack allows the attacker to execute arbitrary commands against the database.

Notice the trailing pair of hyphens (--), which specifies to most database servers that the remainder of the statement is to be treated as a comment and not executed [4]. In this case the comment character serves to remove the trailing single-quote left over from the modified query. On a database where comments are not allowed to be used in this way, the general attack could still be made effective using a trick similar to the one shown in Example 1. If an attacker enters the string "name'); DELETE FROM items; SELECT * FROM items WHERE 'a'='a", the following three valid statements will be created:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name';

DELETE FROM items;

SELECT * FROM items WHERE 'a'='a';


Some think that in the mobile world, classic web application vulnerabilities, such as SQL injection, do not make sense -- why would the user attack themself? However, keep in mind that the essence of mobile platforms is applications that are downloaded from various sources and run alongside each other on the same device. The likelihood of running a piece of malware next to a banking application is high, which necessitates expanding the attack surface of mobile applications to include inter-process communication.

Example 3: The following code adapts Example 1 to the Android platform.


...
PasswordAuthentication pa = authenticator.getPasswordAuthentication();
String userName = pa.getUserName();
String itemName = this.getIntent().getExtras().getString("itemName");
String query = "SELECT * FROM items WHERE owner = '"
+ userName + "' AND itemname = '"
+ itemName + "'";
SQLiteDatabase db = this.openOrCreateDatabase("DB", MODE_PRIVATE, null);
Cursor c = db.rawQuery(query, null);
...


One traditional approach to preventing SQL injection attacks is to handle them as an input validation problem and either accept only characters from an allow list of safe values or identify and escape a list of potentially malicious values (deny list). Checking an allow list can be a very effective means of enforcing strict input validation rules, but parameterized SQL statements require less maintenance and can offer more guarantees with respect to security. As is almost always the case, implementing a deny list is riddled with loopholes that make it ineffective at preventing SQL injection attacks. For example, attackers may:

- Target fields that are not quoted
- Find ways to bypass the need for certain escaped metacharacters
- Use stored procedures to hide the injected metacharacters

Manually escaping characters in input to SQL queries can help, but it will not make your application secure from SQL injection attacks.

Another solution commonly proposed for dealing with SQL injection attacks is to use stored procedures. Although stored procedures prevent some types of SQL injection attacks, they fail to protect against many others. Stored procedures typically help prevent SQL injection attacks by limiting the types of statements that can be passed to their parameters. However, there are many ways around the limitations and many interesting statements that can still be passed to stored procedures. Again, stored procedures can prevent some exploits, but they will not make your application secure against SQL injection attacks.
References
[1] S. J. Friedl SQL Injection Attacks by Example
[2] P. Litwin Stop SQL Injection Attacks Before They Stop You MSDN Magazine
[3] P. Finnigan SQL Injection and Oracle, Part One Security Focus
[4] M. Howard, D. LeBlanc Writing Secure Code, Second Edition Microsoft Press
[5] IDS00-J. Prevent SQL Injection CERT
[6] INJECT-2: Avoid dynamic SQL Oracle
desc.dataflow.java.sql_injection
Abstract
Constructing a dynamic SQL statement with input that comes from an untrusted source might allow an attacker to modify the statement's meaning or to execute arbitrary SQL commands.
Explanation
SQL injection errors occur when:

1. Data enters a program from an untrusted source.



2. The data is used to dynamically construct a SQL query.

Example 1: The following code dynamically constructs and executes a SQL query that searches for items matching a specified name. The query restricts the items displayed to those where the owner matches the user name of the currently-authenticated user.


...
var username = document.form.username.value;
var itemName = document.form.itemName.value;
var query = "SELECT * FROM items WHERE owner = " + username + " AND itemname = " + itemName + ";";
db.transaction(function (tx) {
tx.executeSql(query);
}
)
...


The query intends to execute the following code:


SELECT * FROM items
WHERE owner = <userName>
AND itemname = <itemName>;


However, because the query is constructed dynamically by concatenating a constant base query string and a user input string, the query only behaves correctly if itemName does not contain a single-quote character. If an attacker with the user name wiley enters the string "name' OR 'a'='a" for itemName, then the query becomes the following:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name' OR 'a'='a';


The addition of the OR 'a'='a' condition causes the where clause to always evaluate to true, so the query becomes logically equivalent to the much simpler query:


SELECT * FROM items;


This simplification of the query allows the attacker to bypass the requirement that the query must only return items owned by the authenticated user. The query now returns all entries stored in the items table, regardless of their specified owner.

Example 2: This example examines the effects of a different malicious value passed to the query constructed and executed in Example 1. If an attacker with the user name wiley enters the string "name'; DELETE FROM items; --" for itemName, then the query becomes the following two queries:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name';

DELETE FROM items;

--'


Many database servers, including Microsoft(R) SQL Server 2000, allow multiple SQL statements separated by semicolons to be executed at once. While this attack string results in an error on Oracle and other database servers that do not allow the batch-execution of statements separated by semicolons, on databases that do allow batch execution, this type of attack allows the attacker to execute arbitrary commands against the database.

Notice the trailing pair of hyphens (--), which specifies to most database servers that the remainder of the statement is to be treated as a comment and not executed [4]. In this case the comment character serves to remove the trailing single-quote left over from the modified query. On a database where comments are not allowed to be used in this way, the general attack could still be made effective using a trick similar to the one shown in Example 1. If an attacker enters the string "name'); DELETE FROM items; SELECT * FROM items WHERE 'a'='a", the following three valid statements will be created:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name';

DELETE FROM items;

SELECT * FROM items WHERE 'a'='a';


One traditional approach to preventing SQL injection attacks is to handle them as an input validation problem and either accept only characters from an allow list of safe values or identify and escape a list of potentially malicious values (deny list). Checking an allow list can be a very effective means of enforcing strict input validation rules, but parameterized SQL statements require less maintenance and can offer more guarantees with respect to security. As is almost always the case, implementing a deny list is riddled with loopholes that make it ineffective at preventing SQL injection attacks. For example, attackers may:

- Target fields that are not quoted
- Find ways to bypass the need for certain escaped metacharacters
- Use stored procedures to hide the injected metacharacters

Manually escaping characters in input to SQL queries can help, but it will not make your application secure from SQL injection attacks.

Another solution commonly proposed for dealing with SQL injection attacks is to use stored procedures. Although stored procedures prevent some types of SQL injection attacks, they fail to protect against many others. Stored procedures typically help prevent SQL injection attacks by limiting the types of statements that can be passed to their parameters. However, there are many ways around the limitations and many interesting statements that can still be passed to stored procedures. Again, stored procedures can prevent some exploits, but they will not make your application secure against SQL injection attacks.
References
[1] S. J. Friedl SQL Injection Attacks by Example
[2] P. Litwin Stop SQL Injection Attacks Before They Stop You MSDN Magazine
[3] P. Finnigan SQL Injection and Oracle, Part One Security Focus
[4] M. Howard, D. LeBlanc Writing Secure Code, Second Edition Microsoft Press
desc.dataflow.javascript.sql_injection
Abstract
Constructing a dynamic SQL statement with input that comes from an untrusted source might allow an attacker to modify the statement's meaning or to execute arbitrary SQL commands.
Explanation
SQL injection errors occur when:

1. Data enters a program from an untrusted source.



2. The data is used to dynamically construct a SQL query.

Example 1: The following code dynamically constructs and executes a SQL query that searches for items matching a specified name. The query restricts the items displayed to those where the owner matches the user name of the currently-authenticated user.


...
$userName = $_SESSION['userName'];
$itemName = $_POST['itemName'];
$query = "SELECT * FROM items WHERE owner = '$userName' AND itemname = '$itemName';";
$result = mysql_query($query);
...


The query intends to execute the following code:


SELECT * FROM items
WHERE owner = <userName>
AND itemname = <itemName>;


However, because the query is constructed dynamically by concatenating a constant query string and a user input string, the query only behaves correctly if itemName does not contain a single-quote character. If an attacker with the user name wiley enters the string "name' OR 'a'='a" for itemName, then the query becomes the following:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name' OR 'a'='a';


The addition of the OR 'a'='a' condition causes the where clause to always evaluate to true, so the query becomes logically equivalent to the much simpler query:


SELECT * FROM items;


This simplification of the query allows the attacker to bypass the requirement that the query must only return items owned by the authenticated user. The query now returns all entries stored in the items table, regardless of their specified owner.

Example 2: This example examines the effects of a different malicious value passed to the query constructed and executed in Example 1. If an attacker with the user name wiley enters the string "name'; DELETE FROM items; --" for itemName, then the query becomes the following two queries:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name';

DELETE FROM items;

--'


Many database servers, including Microsoft(R) SQL Server 2000, allow multiple SQL statements separated by semicolons to be executed at once. While this attack string results in an error on Oracle and other database servers that do not allow the batch-execution of statements separated by semicolons, on databases that do allow batch execution, this type of attack allows the attacker to execute arbitrary commands against the database.

Notice the trailing pair of hyphens (--), which specifies to most database servers that the remainder of the statement is to be treated as a comment and not executed [4]. In this case the comment character serves to remove the trailing single-quote left over from the modified query. On a database where comments are not allowed to be used in this way, the general attack could still be made effective using a trick similar to the one shown in Example 1. If an attacker enters the string "name'); DELETE FROM items; SELECT * FROM items WHERE 'a'='a", the following three valid statements will be created:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name';

DELETE FROM items;

SELECT * FROM items WHERE 'a'='a';


One traditional approach to preventing SQL injection attacks is to handle them as an input validation problem and either accept only characters from an allow list of safe values or identify and escape a list of potentially malicious values (deny list). Checking an allow list can be a very effective means of enforcing strict input validation rules, but parameterized SQL statements require less maintenance and can offer more guarantees with respect to security. As is almost always the case, implementing a deny list is riddled with loopholes that make it ineffective at preventing SQL injection attacks. For example, attackers may:

- Target fields that are not quoted
- Find ways to bypass the need for certain escaped metacharacters
- Use stored procedures to hide the injected metacharacters

Manually escaping characters in input to SQL queries can help, but it will not make your application secure from SQL injection attacks.

Another solution commonly proposed for dealing with SQL injection attacks is to use stored procedures. Although stored procedures prevent some types of SQL injection attacks, they fail to protect against many others. Stored procedures typically help prevent SQL injection attacks by limiting the types of statements that can be passed to their parameters. However, there are many ways around the limitations and many interesting statements that can still be passed to stored procedures. Again, stored procedures can prevent some exploits, but they will not make your application secure against SQL injection attacks.
References
[1] S. J. Friedl SQL Injection Attacks by Example
[2] P. Litwin Stop SQL Injection Attacks Before They Stop You MSDN Magazine
[3] P. Finnigan SQL Injection and Oracle, Part One Security Focus
[4] M. Howard, D. LeBlanc Writing Secure Code, Second Edition Microsoft Press
desc.dataflow.php.sql_injection
Abstract
Constructing a dynamic SQL statement with input that comes from an untrusted source could allow an attacker to modify the statement's meaning or to execute arbitrary SQL commands.
Explanation
SQL injection errors occur when:

1. Data enters a program from an untrusted source.

2. The data used to dynamically construct a SQL query.
Example 1: The following code dynamically constructs and executes a SQL query designed to search for items matching a specified name. The query restricts the items displayed to those where owner is equal to the user name of the currently authenticated user.


procedure get_item (
itm_cv IN OUT ItmCurTyp,
usr in varchar2,
itm in varchar2)
is
open itm_cv for ' SELECT * FROM items WHERE ' ||
'owner = '''|| usr || '''' ||
' AND itemname = ''' || itm || '''';
end get_item;


The query this code intends to execute is the following:


SELECT * FROM items
WHERE owner = <userName>
AND itemname = <itemName>;


However, because the query is constructed dynamically by concatenating a constant base query string and a user input string, the query only behaves correctly if itemName does not contain a single-quote character. If an attacker with the user name wiley enters the string "name' OR 'a'='a" for itm, then the query becomes the following:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name' OR 'a'='a';


The addition of the OR 'a'='a' condition causes the where clause to always evaluate to true, so query becomes logically equivalent to the much simpler query:


SELECT * FROM items;


This simplification of the query allows the attacker to bypass the requirement that the query must only return items owned by the authenticated user. The query now returns all entries stored in the items table, regardless of their specified owner.

Example 2: In this example, we will consider the effects of a different malicious value passed to the query constructed and executed in Example 1. If an attacker with the user name wiley enters the string "name'; DELETE FROM items; --" for itemName, then the query becomes the following two queries:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name';

DELETE FROM items;

--'


Many database servers, including Microsoft(R) SQL Server 2000, allow multiple SQL statements separated by semicolons to be executed at once. While this attack string would result in an error on Oracle and other database servers that do not allow the batch-execution of statements separated by semicolons, on supported databases this type of attack will allow the execution of arbitrary commands against the database.

Notice the trailing pair of hyphens (--); these indicate to most database servers that the remainder of the statement is to be treated as a comment and not executed [4]. In this case the comments are used to remove the trailing single-quote leftover from the modified query. On a database where comments are not allowed to be used in this way, the general attack could still be made effective using a trick similar to the one shown in Example 1. If an attacker enters the string "name'); DELETE FROM items; SELECT * FROM items WHERE 'a'='a", the following three valid statements will be created:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name';

DELETE FROM items;

SELECT * FROM items WHERE 'a'='a';


One traditional approach to preventing SQL injection attacks is to handle them as an input validation problem and either accept only an allow list of safe values or identify and escape a list of potentially malicious values (deny list). Checking an allow list can be a very effective means of enforcing strict input validation rules, but parameterized SQL statements require less maintenance and can offer more guarantees with respect to security. As is almost always the case, implementing a deny list is riddled with loopholes that make it ineffective at preventing SQL injection attacks. For example, attackers may:

- Target fields that are not quoted
- Find ways to bypass the need for certain escaped metacharacters
- Use stored procedures to hide the injected metacharacters

Manually escaping characters in input to SQL queries can help, but it will not make your application secure from SQL injection attacks.

Another solution commonly proposed for dealing with SQL injection attacks is to use stored procedures. As this series of examples has shown, stored procedures can be just as vulnerable as other kinds of code. Stored procedures can help prevent certain types of exploits, but they will not make your application inherently secure from SQL injection attacks.
References
[1] S. J. Friedl SQL Injection Attacks by Example
[2] P. Litwin Stop SQL Injection Attacks Before They Stop You MSDN Magazine
[3] P. Finnigan SQL Injection and Oracle, Part One Security Focus
[4] M. Howard, D. LeBlanc Writing Secure Code, Second Edition Microsoft Press
[5] David Litchfield Lateral SQL Injection: A New Class of Vulnerability in Oracle
desc.dataflow.sql.sql_injection
Abstract
Constructing a dynamic SQL statement with input that comes from an untrusted source might allow an attacker to modify the statement's meaning or to execute arbitrary SQL commands.
Explanation
SQL injection errors occur when:

1. Data enters a program from an untrusted source.



2. The data is used to dynamically construct a SQL query.

Example 1: The following code dynamically constructs and executes a SQL query that searches for items matching a specified name. The query restricts the items displayed to those where the owner matches the user name of the currently-authenticated user.


...
userName = req.field('userName')
itemName = req.field('itemName')
query = "SELECT * FROM items WHERE owner = ' " + userName +" ' AND itemname = ' " + itemName +"';"
cursor.execute(query)
result = cursor.fetchall()
...


The query intends to execute the following code:


SELECT * FROM items
WHERE owner = <userName>
AND itemname = <itemName>;


However, because the query is constructed dynamically by concatenating a constant query string and a user input string, the query only behaves correctly if itemName does not contain a single-quote character. If an attacker with the user name wiley enters the string "name' OR 'a'='a" for itemName, then the query becomes the following:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name' OR 'a'='a';


The addition of the OR 'a'='a' condition causes the where clause to always evaluate to true, so the query becomes logically equivalent to the much simpler query:


SELECT * FROM items;


This simplification of the query allows the attacker to bypass the requirement that the query must only return items owned by the authenticated user. The query now returns all entries stored in the items table, regardless of their specified owner.

Example 2: This example examines the effects of a different malicious value passed to the query constructed and executed in Example 1. If an attacker with the user name wiley enters the string "name'; DELETE FROM items; --" for itemName, then the query becomes the following two queries:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name';

DELETE FROM items;

--'


Many database servers, including Microsoft(R) SQL Server 2000, allow multiple SQL statements separated by semicolons to be executed at once. While this attack string results in an error on Oracle and other database servers that do not allow the batch-execution of statements separated by semicolons, on databases that do allow batch execution, this type of attack allows the attacker to execute arbitrary commands against the database.

Notice the trailing pair of hyphens (--), which specifies to most database servers that the remainder of the statement is to be treated as a comment and not executed [4]. In this case the comment character serves to remove the trailing single-quote left over from the modified query. On a database where comments are not allowed to be used in this way, the general attack could still be made effective using a trick similar to the one shown in Example 1. If an attacker enters the string "name'); DELETE FROM items; SELECT * FROM items WHERE 'a'='a", the following three valid statements will be created:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name';

DELETE FROM items;

SELECT * FROM items WHERE 'a'='a';


One traditional approach to preventing SQL injection attacks is to handle them as an input validation problem and either accept only characters from an allow list of safe values or identify and escape a list of potentially malicious values (deny list). Checking an allow list can be a very effective means of enforcing strict input validation rules, but parameterized SQL statements require less maintenance and can offer more guarantees with respect to security. As is almost always the case, implementing a deny list is riddled with loopholes that make it ineffective at preventing SQL injection attacks. For example, attackers may:

- Target fields that are not quoted
- Find ways to bypass the need for certain escaped metacharacters
- Use stored procedures to hide the injected metacharacters

Manually escaping characters in input to SQL queries can help, but it will not make your application secure from SQL injection attacks.

Another solution commonly proposed for dealing with SQL injection attacks is to use stored procedures. Although stored procedures prevent some types of SQL injection attacks, they fail to protect against many others. Stored procedures typically help prevent SQL injection attacks by limiting the types of statements that can be passed to their parameters. However, there are many ways around the limitations and many interesting statements that can still be passed to stored procedures. Again, stored procedures can prevent some exploits, but they will not make your application secure against SQL injection attacks.
References
[1] S. J. Friedl SQL Injection Attacks by Example
[2] P. Litwin Stop SQL Injection Attacks Before They Stop You MSDN Magazine
[3] P. Finnigan SQL Injection and Oracle, Part One Security Focus
[4] M. Howard, D. LeBlanc Writing Secure Code, Second Edition Microsoft Press
desc.dataflow.python.sql_injection
Abstract
Constructing a dynamic SQL statement with input that comes from an untrusted source could allow an attacker to modify the statement's meaning or to execute arbitrary SQL commands.
Explanation
SQL injection errors occur when:

1. Data enters a program from an untrusted source.

In this case, Fortify Static Code Analyzer could not determine that the source of the data is trusted.

2. The data is used to dynamically construct a SQL query.

Example 1: The following code dynamically constructs and executes a SQL query that searches for items matching a specified name. The query restricts the items displayed to those where the owner matches the user name of the currently-authenticated user.


...
userName = getAuthenticatedUserName()
itemName = params[:itemName]
sqlQuery = "SELECT * FROM items WHERE owner = '#{userName}' AND itemname = '#{itemName}'"
rs = conn.query(sqlQuery)
...


The query intends to execute the following code:


SELECT * FROM items
WHERE owner = <userName>
AND itemname = <itemName>;


However, because the query is constructed dynamically by concatenating a constant base query string and a user input string, the query only behaves correctly if itemName does not contain a single-quote character. If an attacker with the user name wiley enters the string "name' OR 'a'='a" for itemName, then the query becomes the following:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name' OR 'a'='a';


The addition of the OR 'a'='a' condition causes the where clause to always evaluate to true, so the query becomes logically equivalent to the much simpler query:


SELECT * FROM items;


This simplification of the query allows the attacker to bypass the requirement that the query must only return items owned by the authenticated user. The query now returns all entries stored in the items table, regardless of their specified owner.

Due to the fact that Ruby is not statically typed also enables other points of injection into SQL queries that may not be available in statically typed languages.
Example 2: The following code dynamically constructs and executes a SQL query that searches for items matching a specified name. The query restricts the items displayed to those where the owner matches the user name of the currently-authenticated user.


...
id = params[:id]
itemName = Mysql.escape_string(params[:itemName])
sqlQuery = "SELECT * FROM items WHERE id = #{userName} AND itemname = '#{itemName}'"
rs = conn.query(sqlQuery)
...


In this case, the expected SQL query to be run is:


SELECT * FROM items WHERE id=<id> AND itemname = <itemName>;

You can see this time that we've protected against an attacker specifying a single quote inside itemName and seemingly prevented the SQL injection vulnerability. However as Ruby is not a statically typed language, even though we are expecting id to be an integer of some variety, as this is assigned from user input it won't necessarily be a number. If an attacker can instead change the value of id to 1 OR id!=1--, since there is no check that id is in fact numeric, the SQL query now becomes:


SELECT * FROM items WHERE id=1 OR id!=1-- AND itemname = 'anyValue';


Notice the trailing pair of hyphens (--), which specifies to most database servers that the remainder of the statement is to be treated as a comment and not executed [4]. Due to this, it's now just running a SQL query consisting of:


SELECT * FROM items WHERE id=1 OR id!=1;


We are now just selecting everything from that table whether the value of id is equal to 1 or not, which of course equates to everything within the table.

Many database servers allow multiple SQL statements separated by semicolons to be executed at once. While this attack string results in an error on Oracle and other database servers that do not allow the batch-execution of statements separated by semicolons, on databases that do allow batch execution, this type of attack allows the attacker to execute arbitrary commands against the database.

One traditional approach to preventing SQL injection attacks is to handle them as an input validation problem and either accept only characters from an allow list of safe values or identify and escape a list of potentially malicious values (deny list). Checking an allow list can be a very effective means of enforcing strict input validation rules, but parameterized SQL statements require less maintenance and can offer more guarantees with respect to security. As is almost always the case, implementing a deny list is riddled with loopholes that make it ineffective at preventing SQL injection attacks. For example, attackers may:

- Target fields that are not quoted
- Find ways to bypass the need for certain escaped metacharacters
- Use stored procedures to hide the injected metacharacters

Manually escaping characters in input to SQL queries can help, but it will not make your application secure from SQL injection attacks.

Another solution commonly proposed for dealing with SQL injection attacks is to use stored procedures. Although stored procedures prevent some types of SQL injection attacks, they fail to protect against many others. Stored procedures typically help prevent SQL injection attacks by limiting the types of statements that can be passed to their parameters. However, there are many ways around the limitations and many interesting statements that can still be passed to stored procedures. Again, stored procedures can prevent some exploits, but they will not make your application secure against SQL injection attacks.
References
[1] S. J. Friedl SQL Injection Attacks by Example
[2] P. Litwin Stop SQL Injection Attacks Before They Stop You MSDN Magazine
[3] P. Finnigan SQL Injection and Oracle, Part One Security Focus
[4] M. Howard, D. LeBlanc Writing Secure Code, Second Edition Microsoft Press
desc.dataflow.ruby.sql_injection
Abstract
Constructing a dynamic SQL statement with input that comes from an untrusted source could allow an attacker to modify the statement's meaning or to execute arbitrary SQL commands.
Explanation
SQL injection errors occur when:

1. Data enters a program from an untrusted source.



2. The data is used to dynamically construct a SQL query.

Example 1: The following code dynamically constructs and executes a SQL query that searches for users matching a specified name. The query restricts the items displayed to those where the owner matches the user name provided as a path parameter.


def doSQLQuery(value:String) = Action.async { implicit request =>
val result: Future[Seq[User]] = db.run {
sql"select * from users where name = '#$value'".as[User]
}
...
}


The query intends to execute the following code:


SELECT * FROM users
WHERE name = <userName>


However, because the query is constructed dynamically by concatenating a constant base query string and a user input string, the query only behaves correctly if userName does not contain a single-quote character. If an attacker with the user name wiley enters the string "name' OR 'a'='a" for userName, then the query becomes the following:


SELECT * FROM users
WHERE name = 'name' OR 'a'='a';


The addition of the OR 'a'='a' condition causes the where clause to always evaluate to true, so the query becomes logically equivalent to the much simpler query:


SELECT * FROM users;


This simplification of the query allows the attacker to bypass the requirement that the query must only return users owned by the specified user; the query now returns all entries stored in the users table, regardless of their specified user.

One traditional approach to preventing SQL injection attacks is to handle them as an input validation problem and either accept only characters from an allow list of safe values or identify and escape a list of potentially malicious values (deny list). Checking an allow list can be a very effective means of enforcing strict input validation rules, but parameterized SQL statements require less maintenance and can offer more guarantees with respect to security. As is almost always the case, implementing a deny list is riddled with loopholes that make it ineffective at preventing SQL injection attacks. For example, attackers may:

- Target fields that are not quoted
- Find ways to bypass the need for certain escaped metacharacters
- Use stored procedures to hide the injected metacharacters

Manually escaping characters in input to SQL queries can help, but it will not make your application secure from SQL injection attacks.

Another solution commonly proposed for dealing with SQL injection attacks is to use stored procedures. Although stored procedures prevent some types of SQL injection attacks, they fail to protect against many others. Stored procedures typically help prevent SQL injection attacks by limiting the types of statements that can be passed to their parameters. However, there are many ways around the limitations and many interesting statements that can still be passed to stored procedures. Again, stored procedures can prevent some exploits, but they will not make your application secure against SQL injection attacks.
References
[1] S. J. Friedl SQL Injection Attacks by Example
[2] P. Litwin Stop SQL Injection Attacks Before They Stop You MSDN Magazine
[3] P. Finnigan SQL Injection and Oracle, Part One Security Focus
[4] M. Howard, D. LeBlanc Writing Secure Code, Second Edition Microsoft Press
[5] IDS00-J. Prevent SQL Injection CERT
[6] INJECT-2: Avoid dynamic SQL Oracle
desc.dataflow.scala.sql_injection
Abstract
Constructing a dynamic SQL statement with input that comes from an untrusted source could allow an attacker to modify the statement's meaning or to execute arbitrary SQL commands.
Explanation
SQL injection errors occur when:

1. Data enters a program from an untrusted source.

2. The data is used to dynamically construct a SQL query.
Example 1: The following code dynamically constructs and executes a SQL query that searches for items matching a specified name. The query restricts the items displayed to those where the owner matches the user name of the currently-authenticated user.


...
let queryStatementString = "SELECT * FROM items WHERE owner='\(username)' AND itemname='\(item)'"
var queryStatement: OpaquePointer? = nil
if sqlite3_prepare_v2(db, queryStatementString, -1, &queryStatement, nil) == SQLITE_OK {
if sqlite3_step(queryStatement) == SQLITE_ROW {
...
}
}
...


The query intends to execute the following code:


SELECT * FROM items
WHERE owner = '<userName>'
AND itemname = '<itemName>'


However, because the query is constructed dynamically by concatenating a constant base query string and a user input string, the query only behaves correctly if itemName does not contain a single-quote character. If an attacker with the user name wiley enters the string "name' OR 'a'='a" for itemName, then the query becomes the following:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name' OR 'a'='a';


The addition of the OR 'a'='a' condition causes the where clause to always evaluate to true, so the query becomes logically equivalent to the much simpler query:


SELECT * FROM items;


This simplification of the query allows the attacker to bypass the requirement that the query must only return items owned by the authenticated user. The query now returns all entries stored in the items table, regardless of their specified owner.

Example 3: This example examines the effects of a different malicious value passed to the query constructed and executed in Example 1. If an attacker with the user name wiley enters the string "name'); DELETE FROM items; --" for itemName, then the query becomes the following two queries:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name';

DELETE FROM items;

--'


Many database servers, including Microsoft(R) SQL Server 2000, allow multiple SQL statements separated by semicolons to be executed at once. While this attack string results in an error on Oracle and other database servers that do not allow the batch-execution of statements separated by semicolons, on databases that do allow batch execution, this type of attack allows the attacker to execute arbitrary commands against the database.

Notice the trailing pair of hyphens (--), which specifies to most database servers that the remainder of the statement is to be treated as a comment and not executed [4]. In this case the comment character serves to the trailing single-quote left over from the modified query. On a database where comments are not allowed to be used in this way, the general attack could still be made effective using a trick similar to the one shown in Example 1. If an attacker enters the string "name'); DELETE FROM items; SELECT * FROM items WHERE 'a'='a", the following three valid statements will be created:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name';

DELETE FROM items;

SELECT * FROM items WHERE 'a'='a';


One traditional approach to preventing SQL injection attacks is to handle them as an input validation problem and either accept only characters from an allow list of safe values or identify and escape a list of potentially malicious values (deny list). Checking an allow list can be a very effective means of enforcing strict input validation rules, but parameterized SQL statements require less maintenance and can offer more guarantees with respect to security. As is almost always the case, implementing a deny list is riddled with loopholes that make it ineffective at preventing SQL injection attacks. For example, attackers may:

- Target fields that are not quoted
- Find ways to bypass the need for certain escaped metacharacters
- Use stored procedures to hide the injected metacharacters

Manually escaping characters in input to SQL queries can help, but it will not make your application secure from SQL injection attacks.

Another solution commonly proposed for dealing with SQL injection attacks is to use stored procedures. Although stored procedures prevent some types of SQL injection attacks, they fail to protect against many others. Stored procedures typically help prevent SQL injection attacks by limiting the types of statements that can be passed to their parameters. However, there are many ways around the limitations and many interesting statements that can still be passed to stored procedures. Again, stored procedures can prevent some exploits, but they will not make your application secure against SQL injection attacks.
References
[1] S. J. Friedl SQL Injection Attacks by Example
[2] P. Litwin Stop SQL Injection Attacks Before They Stop You MSDN Magazine
[3] P. Finnigan SQL Injection and Oracle, Part One Security Focus
[4] M. Howard, D. LeBlanc Writing Secure Code, Second Edition Microsoft Press
[5] Parameterized CRecordset and CDatabase for SQL Server
[6] Parameterizing a Recordset Microsoft
[7] ODBC API Reference: SQLNumParams() Microsoft
[8] ODBC API Reference: SQLBindParameter() Microsoft
[9] OLE DB Reference: ICommandWithParameters Microsoft
desc.dataflow.swift.sql_injection
Abstract
Constructing a dynamic SQL statement with input that comes from an untrusted source could allow an attacker to modify the statement's meaning or to execute arbitrary SQL commands.
Explanation
SQL injection errors occur when:

1. Data enters a program from an untrusted source.



2. The data is used to dynamically construct a SQL query.

Example 1: The following code dynamically constructs and executes a SQL query that searches for items matching a specified name. The query restricts the items displayed to those where the owner matches the user name of the currently-authenticated user.


...
username = Session("username")
itemName = Request.Form("itemName")
strSQL = "SELECT * FROM items WHERE owner = '"& userName &"' AND itemname = '" & itemName &"'"
objRecordSet.Open strSQL, strConnect, adOpenDynamic, adLockOptimistic, adCmdText
...


The query intends to execute the following code:


SELECT * FROM items
WHERE owner = <userName>
AND itemname = <itemName>;


However, because the query is constructed dynamically by concatenating a constant base query string and a user input string, the query only behaves correctly if itemName does not contain a single-quote character. If an attacker with the user name wiley enters the string "name' OR 'a'='a" for itemName, then the query becomes the following:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name' OR 'a'='a';


The addition of the OR 'a'='a' condition causes the where clause to always evaluate to true, so the query becomes logically equivalent to the much simpler query:


SELECT * FROM items;


This simplification of the query allows the attacker to bypass the requirement that the query must only return items owned by the authenticated user. The query now returns all entries stored in the items table, regardless of their specified owner.

Example 2: This example examines the effects of a different malicious value passed to the query constructed and executed in Example 1. If an attacker with the user name wiley enters the string "name'; DELETE FROM items; --" for itemName, then the query becomes the following two queries:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name';

DELETE FROM items;

--'


Many database servers, including Microsoft(R) SQL Server 2000, allow multiple SQL statements separated by semicolons to be executed at once. While this attack string results in an error on Oracle and other database servers that do not allow the batch-execution of statements separated by semicolons, on databases that do allow batch execution, this type of attack allows the attacker to execute arbitrary commands against the database.

Notice the trailing pair of hyphens (--), which specifies to most database servers that the remainder of the statement is to be treated as a comment and not executed [4]. In this case the comment character serves to remove the trailing single-quote left over from the modified query. On a database where comments are not allowed to be used in this way, the general attack could still be made effective using a trick similar to the one shown in Example 1. If an attacker enters the string "name'); DELETE FROM items; SELECT * FROM items WHERE 'a'='a", the following three valid statements will be created:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name';

DELETE FROM items;

SELECT * FROM items WHERE 'a'='a';


One traditional approach to preventing SQL injection attacks is to handle them as an input validation problem and either accept only characters from an allow list of safe values or identify and escape a list of potentially malicious values (deny list). Checking an allow list can be a very effective means of enforcing strict input validation rules, but parameterized SQL statements require less maintenance and can offer more guarantees with respect to security. As is almost always the case, implementing a deny list is riddled with loopholes that make it ineffective at preventing SQL injection attacks. For example, attackers may:

- Target fields that are not quoted
- Find ways to bypass the need for certain escaped metacharacters
- Use stored procedures to hide the injected metacharacters

Manually escaping characters in input to SQL queries can help, but it will not make your application secure from SQL injection attacks.

Another solution commonly proposed for dealing with SQL injection attacks is to use stored procedures. Although stored procedures prevent some types of SQL injection attacks, they fail to protect against many others. Stored procedures typically help prevent SQL injection attacks by limiting the types of statements that can be passed to their parameters. However, there are many ways around the limitations and many interesting statements that can still be passed to stored procedures. Again, stored procedures can prevent some exploits, but they will not make your application secure against SQL injection attacks.
References
[1] S. J. Friedl SQL Injection Attacks by Example
[2] P. Litwin Stop SQL Injection Attacks Before They Stop You MSDN Magazine
[3] P. Finnigan SQL Injection and Oracle, Part One Security Focus
[4] M. Howard, D. LeBlanc Writing Secure Code, Second Edition Microsoft Press
desc.dataflow.vb.sql_injection
Abstract
Constructing a dynamic Castle ActiveRecord statement with input coming from an untrusted source might allow an attacker to modify the statement's meaning or to execute arbitrary SQL commands.
Explanation
SQL injection errors related to Castle ActiveRecord occur when:

1. Data enters a program from an untrusted source.

2. The data is used to dynamically construct a query.
Example 1: The following code dynamically constructs and executes a Castle ActiveRecord query that searches for items matching a specified name. The query restricts the items displayed to those where owner matches the user name of the currently-authenticated user.


...
string userName = ctx.getAuthenticatedUserName();
string queryString = "SELECT * FROM items WHERE owner = '"
+ userName + "' AND itemname = '"
+ ItemName.Text + "'";

SimpleQuery<Item> queryObject = new SimpleQuery(queryString);
Item[] items = (Item[])queryObject.Execute(query);

...


The query intends to execute the following code:


SELECT * FROM items
WHERE owner = <userName>
AND itemname = <itemName>;


However, because the query is constructed dynamically by concatenating a constant base query string and a user input string, the query only behaves correctly if itemName does not contain a single-quote character. If an attacker with the user name wiley enters the string "name' OR 'a'='a" for itemName, then the query becomes the following:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name' OR 'a'='a';


The addition of the OR 'a'='a' condition causes the where clause to always evaluate to true, so the query becomes logically equivalent to the much simpler query:


SELECT * FROM items;


This simplification of the query allows the attacker to bypass the requirement that the query must only return items owned by the authenticated user. The query now returns all entries stored in the items table, regardless of their specified owner.

One traditional approach to preventing Castle ActiveRecord injection attacks is to handle them as an input validation problem and either accept only characters from an allow list of safe values or identify and escape a list of potentially malicious values (deny list). Checking an allow list can be a very effective means of enforcing strict input validation rules, but parameterized Castle ActiveRecord statements require less maintenance and can offer more guarantees with respect to security. As is almost always the case, implementing a deny list is riddled with loopholes that make it ineffective at preventing Castle ActiveRecord SQL injection attacks. For example, attackers may:

- Target fields that are not quoted
- Find ways to bypass the need for certain escaped metacharacters
- Use stored procedures to hide the injected metacharacters

Manually escaping characters in input to Castle ActiveRecord queries can help, but it will not make your application secure from Castle ActiveRecord SQL injection attacks.

Another solution commonly proposed for dealing with Castle ActiveRecord injection attacks is to use stored procedures. Although stored procedures prevent some types of Castle ActiveRecord injection attacks, they fail to protect against many others. Stored procedures typically help prevent Castle ActiveRecord SQL injection attacks by limiting the types of statements that can be passed to their parameters. However, there are many ways around the limitations and many interesting statements that can still be passed to stored procedures. Again, stored procedures can prevent some exploits, but they will not make your application secure against Castle ActiveRecord injection attacks.
References
[1] S. J. Friedl SQL Injection Attacks by Example
[2] P. Litwin Stop SQL Injection Attacks Before They Stop You MSDN Magazine
[3] P. Finnigan SQL Injection and Oracle, Part One Security Focus
[4] M. Howard, D. LeBlanc Writing Secure Code, Second Edition Microsoft Press
[5] Standards Mapping - Common Weakness Enumeration CWE ID 89
[6] Standards Mapping - Common Weakness Enumeration Top 25 2019 [6] CWE ID 089
[7] Standards Mapping - Common Weakness Enumeration Top 25 2020 [6] CWE ID 089
[8] Standards Mapping - Common Weakness Enumeration Top 25 2021 [6] CWE ID 089
[9] Standards Mapping - Common Weakness Enumeration Top 25 2022 [3] CWE ID 089
[10] Standards Mapping - Common Weakness Enumeration Top 25 2023 [3] CWE ID 089
[11] Standards Mapping - DISA Control Correlation Identifier Version 2 CCI-001310, CCI-002754
[12] Standards Mapping - FIPS200 SI
[13] Standards Mapping - General Data Protection Regulation (GDPR) Indirect Access to Sensitive Data
[14] Standards Mapping - NIST Special Publication 800-53 Revision 4 SI-10 Information Input Validation (P1)
[15] Standards Mapping - NIST Special Publication 800-53 Revision 5 SI-10 Information Input Validation
[16] Standards Mapping - OWASP Top 10 2004 A6 Injection Flaws
[17] Standards Mapping - OWASP Top 10 2007 A2 Injection Flaws
[18] Standards Mapping - OWASP Top 10 2010 A1 Injection
[19] Standards Mapping - OWASP Top 10 2013 A1 Injection
[20] Standards Mapping - OWASP Mobile 2014 M1 Weak Server Side Controls
[21] Standards Mapping - OWASP Top 10 2017 A1 Injection
[22] Standards Mapping - OWASP Top 10 2021 A03 Injection
[23] Standards Mapping - OWASP Application Security Verification Standard 4.0 5.3.4 Output Encoding and Injection Prevention Requirements (L1 L2 L3), 5.3.5 Output Encoding and Injection Prevention Requirements (L1 L2 L3)
[24] Standards Mapping - OWASP Mobile Application Security Verification Standard 2.0 MASVS-CODE-4, MASVS-PLATFORM-1
[25] Standards Mapping - Payment Card Industry Data Security Standard Version 1.1 Requirement 6.5.6
[26] Standards Mapping - Payment Card Industry Data Security Standard Version 1.2 Requirement 6.3.1.1, Requirement 6.5.2
[27] Standards Mapping - Payment Card Industry Data Security Standard Version 2.0 Requirement 6.5.1
[28] Standards Mapping - Payment Card Industry Data Security Standard Version 3.0 Requirement 6.5.1
[29] Standards Mapping - Payment Card Industry Data Security Standard Version 3.2 Requirement 6.5.1
[30] Standards Mapping - Payment Card Industry Data Security Standard Version 3.2.1 Requirement 6.5.1
[31] Standards Mapping - Payment Card Industry Data Security Standard Version 3.1 Requirement 6.5.1
[32] Standards Mapping - Payment Card Industry Data Security Standard Version 4.0 Requirement 6.2.4
[33] Standards Mapping - Payment Card Industry Software Security Framework 1.0 Control Objective 4.2 - Critical Asset Protection
[34] Standards Mapping - Payment Card Industry Software Security Framework 1.1 Control Objective 4.2 - Critical Asset Protection, Control Objective B.3.1 - Terminal Software Attack Mitigation, Control Objective B.3.1.1 - Terminal Software Attack Mitigation
[35] Standards Mapping - Payment Card Industry Software Security Framework 1.2 Control Objective 4.2 - Critical Asset Protection, Control Objective B.3.1 - Terminal Software Attack Mitigation, Control Objective B.3.1.1 - Terminal Software Attack Mitigation, Control Objective C.3.2 - Web Software Attack Mitigation
[36] Standards Mapping - SANS Top 25 2009 Insecure Interaction - CWE ID 089
[37] Standards Mapping - SANS Top 25 2010 Insecure Interaction - CWE ID 089
[38] Standards Mapping - SANS Top 25 2011 Insecure Interaction - CWE ID 089
[39] Standards Mapping - Security Technical Implementation Guide Version 3.1 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II
[40] Standards Mapping - Security Technical Implementation Guide Version 3.4 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II
[41] Standards Mapping - Security Technical Implementation Guide Version 3.5 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II
[42] Standards Mapping - Security Technical Implementation Guide Version 3.6 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II
[43] Standards Mapping - Security Technical Implementation Guide Version 3.7 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II
[44] Standards Mapping - Security Technical Implementation Guide Version 3.9 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II
[45] Standards Mapping - Security Technical Implementation Guide Version 3.10 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II
[46] Standards Mapping - Security Technical Implementation Guide Version 4.1 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[47] Standards Mapping - Security Technical Implementation Guide Version 4.2 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[48] Standards Mapping - Security Technical Implementation Guide Version 4.3 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[49] Standards Mapping - Security Technical Implementation Guide Version 4.4 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[50] Standards Mapping - Security Technical Implementation Guide Version 4.5 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[51] Standards Mapping - Security Technical Implementation Guide Version 4.6 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[52] Standards Mapping - Security Technical Implementation Guide Version 4.7 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[53] Standards Mapping - Security Technical Implementation Guide Version 4.8 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[54] Standards Mapping - Security Technical Implementation Guide Version 4.9 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[55] Standards Mapping - Security Technical Implementation Guide Version 4.10 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[56] Standards Mapping - Security Technical Implementation Guide Version 4.11 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[57] Standards Mapping - Security Technical Implementation Guide Version 5.1 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[58] Standards Mapping - Security Technical Implementation Guide Version 5.2 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[59] Standards Mapping - Security Technical Implementation Guide Version 5.2 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[60] Standards Mapping - Web Application Security Consortium Version 2.00 SQL Injection (WASC-19)
[61] Standards Mapping - Web Application Security Consortium 24 + 2 SQL Injection
desc.dataflow.dotnet.sql_injection_castleActiveRecord
Abstract
Using Hibernate to execute a dynamic SQL statement built with input coming from an untrusted source can allow an attacker to modify the statement's meaning or to execute arbitrary SQL commands.
Explanation
SQL injection errors occur when:

1. Data enters a program from an untrusted source.



2. The data is used to dynamically construct a HQL query.

Example 1: The following code dynamically constructs and executes a HQL query that searches for items matching a specified name. The query restricts the items displayed to those where owner matches the user name of the currently-authenticated user.


...
String userName = ctx.getAuthenticatedUserName();
String itemName = request.getParameter("itemName");
String query = "FROM items WHERE owner = '"
+ userName + "' AND itemname = '"
+ itemName + "'";
List items = sess.createQuery(query).list();
...


The query intends to execute the following code:


SELECT * FROM items
WHERE owner = <userName>
AND itemname = <itemName>;


However, because the query is constructed dynamically by concatenating a constant base query string and a user input string, the query only behaves correctly if itemName does not contain a single-quote character. If an attacker with the user name wiley enters the string "name' OR 'a'='a" for itemName, then the query becomes the following:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name' OR 'a'='a';


The addition of the OR 'a'='a' condition causes the where clause to always evaluate to true, so the query becomes logically equivalent to the much simpler query:


SELECT * FROM items;


This simplification of the query allows the attacker to bypass the requirement that the query must only return items owned by the authenticated user. The query now returns all entries stored in the items table, regardless of their specified owner.

Example 2: This example examines the effects of a different malicious value passed to the query constructed and executed in Example 1. If an attacker with the user name wiley enters the string "name'; DELETE FROM items; --" for itemName, then the query becomes the following two queries:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name';

DELETE FROM items;

--'


Many database servers, including Microsoft(R) SQL Server 2000, allow multiple SQL statements separated by semicolons to be executed at once. While this attack string results in an error on Oracle and other database servers that do not allow the batch-execution of statements separated by semicolons, on databases that do allow batch execution, this type of attack allows the attacker to execute arbitrary commands against the database.

Notice the trailing pair of hyphens (--), which specifies to most database servers that the remainder of the statement is to be treated as a comment and not executed [4]. In this case the comment character serves to remove the trailing single-quote left over from the modified query. On a database where comments are not allowed to be used in this way, the general attack could still be made effective using a trick similar to the one shown in Example 1. If an attacker enters the string "name'); DELETE FROM items; SELECT * FROM items WHERE 'a'='a", the following three valid statements will be created:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name';

DELETE FROM items;

SELECT * FROM items WHERE 'a'='a';


One traditional approach to preventing SQL injection attacks is to handle them as an input validation problem and either accept only characters from an allow list of safe values or identify and escape a list of potentially malicious values (deny list). Checking an allow list can be an effective means of enforcing strict input validation rules, but parameterized SQL statements require less maintenance and can offer more guarantees with respect to security. As is almost always the case, implementing a deny list is riddled with loopholes that make it ineffective at preventing SQL injection attacks. For example, attackers may:

- Target fields that are not quoted
- Find ways to bypass the need for certain escaped metacharacters
- Use stored procedures to hide the injected metacharacters

Manually escaping characters in input to SQL queries can help, but it will not make your application secure from SQL injection attacks.

Another solution commonly proposed for dealing with SQL injection attacks is to use stored procedures. Although stored procedures prevent some types of SQL injection attacks, they fail to protect against many others. Stored procedures typically help prevent SQL injection attacks by limiting the types of statements that can be passed to their parameters. However, there are many ways around the limitations and many interesting statements that can still be passed to stored procedures. Again, stored procedures can prevent some types of exploits, but they will not make your application secure against SQL injection attacks.
References
[1] S. J. Friedl SQL Injection Attacks by Example
[2] P. Litwin Stop SQL Injection Attacks Before They Stop You MSDN Magazine
[3] P. Finnigan SQL Injection and Oracle, Part One Security Focus
[4] M. Howard, D. LeBlanc Writing Secure Code, Second Edition Microsoft Press
[5] Hibernate API Documentation
[6] IDS00-J. Prevent SQL Injection CERT
[7] INJECT-2: Avoid dynamic SQL Oracle
[8] Standards Mapping - Common Weakness Enumeration CWE ID 564
[9] Standards Mapping - Common Weakness Enumeration Top 25 2019 [6] CWE ID 089
[10] Standards Mapping - Common Weakness Enumeration Top 25 2020 [6] CWE ID 089
[11] Standards Mapping - Common Weakness Enumeration Top 25 2021 [6] CWE ID 089
[12] Standards Mapping - Common Weakness Enumeration Top 25 2022 [3] CWE ID 089
[13] Standards Mapping - Common Weakness Enumeration Top 25 2023 [3] CWE ID 089
[14] Standards Mapping - DISA Control Correlation Identifier Version 2 CCI-001310, CCI-002754
[15] Standards Mapping - FIPS200 SI
[16] Standards Mapping - General Data Protection Regulation (GDPR) Indirect Access to Sensitive Data
[17] Standards Mapping - NIST Special Publication 800-53 Revision 4 SI-10 Information Input Validation (P1)
[18] Standards Mapping - NIST Special Publication 800-53 Revision 5 SI-10 Information Input Validation
[19] Standards Mapping - OWASP Top 10 2004 A6 Injection Flaws
[20] Standards Mapping - OWASP Top 10 2007 A2 Injection Flaws
[21] Standards Mapping - OWASP Top 10 2010 A1 Injection
[22] Standards Mapping - OWASP Top 10 2013 A1 Injection
[23] Standards Mapping - OWASP Mobile 2014 M1 Weak Server Side Controls
[24] Standards Mapping - OWASP Top 10 2017 A1 Injection
[25] Standards Mapping - OWASP Top 10 2021 A03 Injection
[26] Standards Mapping - OWASP Application Security Verification Standard 4.0 5.3.4 Output Encoding and Injection Prevention Requirements (L1 L2 L3), 5.3.5 Output Encoding and Injection Prevention Requirements (L1 L2 L3)
[27] Standards Mapping - OWASP Mobile Application Security Verification Standard 2.0 MASVS-CODE-4, MASVS-PLATFORM-1
[28] Standards Mapping - Payment Card Industry Data Security Standard Version 1.1 Requirement 6.5.6
[29] Standards Mapping - Payment Card Industry Data Security Standard Version 1.2 Requirement 6.3.1.1, Requirement 6.5.2
[30] Standards Mapping - Payment Card Industry Data Security Standard Version 2.0 Requirement 6.5.1
[31] Standards Mapping - Payment Card Industry Data Security Standard Version 3.0 Requirement 6.5.1
[32] Standards Mapping - Payment Card Industry Data Security Standard Version 3.2 Requirement 6.5.1
[33] Standards Mapping - Payment Card Industry Data Security Standard Version 3.2.1 Requirement 6.5.1
[34] Standards Mapping - Payment Card Industry Data Security Standard Version 3.1 Requirement 6.5.1
[35] Standards Mapping - Payment Card Industry Data Security Standard Version 4.0 Requirement 6.2.4
[36] Standards Mapping - Payment Card Industry Software Security Framework 1.0 Control Objective 4.2 - Critical Asset Protection
[37] Standards Mapping - Payment Card Industry Software Security Framework 1.1 Control Objective 4.2 - Critical Asset Protection, Control Objective B.3.1 - Terminal Software Attack Mitigation, Control Objective B.3.1.1 - Terminal Software Attack Mitigation
[38] Standards Mapping - Payment Card Industry Software Security Framework 1.2 Control Objective 4.2 - Critical Asset Protection, Control Objective B.3.1 - Terminal Software Attack Mitigation, Control Objective B.3.1.1 - Terminal Software Attack Mitigation, Control Objective C.3.2 - Web Software Attack Mitigation
[39] Standards Mapping - SANS Top 25 2009 Insecure Interaction - CWE ID 116
[40] Standards Mapping - Security Technical Implementation Guide Version 3.1 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II
[41] Standards Mapping - Security Technical Implementation Guide Version 3.4 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II
[42] Standards Mapping - Security Technical Implementation Guide Version 3.5 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II
[43] Standards Mapping - Security Technical Implementation Guide Version 3.6 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II
[44] Standards Mapping - Security Technical Implementation Guide Version 3.7 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II
[45] Standards Mapping - Security Technical Implementation Guide Version 3.9 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II
[46] Standards Mapping - Security Technical Implementation Guide Version 3.10 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II
[47] Standards Mapping - Security Technical Implementation Guide Version 4.1 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[48] Standards Mapping - Security Technical Implementation Guide Version 4.2 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[49] Standards Mapping - Security Technical Implementation Guide Version 4.3 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[50] Standards Mapping - Security Technical Implementation Guide Version 4.4 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[51] Standards Mapping - Security Technical Implementation Guide Version 4.5 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[52] Standards Mapping - Security Technical Implementation Guide Version 4.6 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[53] Standards Mapping - Security Technical Implementation Guide Version 4.7 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[54] Standards Mapping - Security Technical Implementation Guide Version 4.8 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[55] Standards Mapping - Security Technical Implementation Guide Version 4.9 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[56] Standards Mapping - Security Technical Implementation Guide Version 4.10 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[57] Standards Mapping - Security Technical Implementation Guide Version 4.11 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[58] Standards Mapping - Security Technical Implementation Guide Version 5.1 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[59] Standards Mapping - Security Technical Implementation Guide Version 5.2 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[60] Standards Mapping - Security Technical Implementation Guide Version 5.2 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[61] Standards Mapping - Web Application Security Consortium Version 2.00 SQL Injection (WASC-19)
[62] Standards Mapping - Web Application Security Consortium 24 + 2 SQL Injection
desc.dataflow.java.sql_injection_hibernate
Abstract
Constructing a dynamic SQL statement with input that comes from an untrusted source could allow an attacker to modify the statement's meaning or to execute arbitrary SQL commands.
Explanation
SQL injection errors occur when:

1. Data enters a program from an untrusted source.

2. The data is used to dynamically construct a SQL query.



iBatis Data Maps allow you to specify dynamic parameters in SQL statements and are typically defined by using the # characters, as follows:


<select id="getItems" parameterClass="MyClass" resultClass="items">
SELECT * FROM items WHERE owner = #userName#
</select>


The # characters around the variable name indicate that iBatis will create a parameterized query with the userName variable. However, iBatis also allows you to concatenate variables directly to SQL statements using $ characters, opening the door for SQL injection.

Example 1: The following code dynamically constructs and executes a SQL query that searches for items matching a specified name. The query restricts the items displayed to those where the owner matches the user name of the currently-authenticated user.


<select id="getItems" parameterClass="MyClass" resultClass="items">
SELECT * FROM items WHERE owner = #userName# AND itemname = '$itemName$'
</select>


However, because the query is constructed dynamically by concatenating a constant base query string and a user input string, the query only behaves correctly if itemName does not contain a single-quote character. If an attacker with the user name wiley enters the string "name' OR 'a'='a" for itemName, then the query becomes the following:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name' OR 'a'='a';


The addition of the OR 'a'='a' condition causes the where clause to always evaluate to true, so the query becomes logically equivalent to the much simpler query:


SELECT * FROM items;


This simplification of the query allows the attacker to bypass the requirement that the query must only return items owned by the authenticated user. The query now returns all entries stored in the items table, regardless of their specified owner.

Example 2: This example examines the effects of a different malicious value passed to the query constructed and executed in Example 1. If an attacker with the user name wiley enters the string "name'; DELETE FROM items; --" for itemName, then the query becomes the following two queries:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name';

DELETE FROM items;

--'


Many database servers, including Microsoft(R) SQL Server 2000, allow multiple SQL statements separated by semicolons to be executed at once. While this attack string results in an error on Oracle and other database servers that do not allow the batch-execution of statements separated by semicolons, on databases that do allow batch execution, this type of attack allows the attacker to execute arbitrary commands against the database.

Notice the trailing pair of hyphens (--), which specifies to most database servers that the remainder of the statement is to be treated as a comment and not executed [4]. In this case the comment character serves to remove the trailing single-quote left over from the modified query. On a database where comments are not allowed to be used in this way, the general attack could still be made effective using a trick similar to the one shown in Example 1. If an attacker enters the string "name'); DELETE FROM items; SELECT * FROM items WHERE 'a'='a", the following three valid statements will be created:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name';

DELETE FROM items;

SELECT * FROM items WHERE 'a'='a';


One traditional approach to preventing SQL injection attacks is to handle them as an input validation problem and either accept only characters from an allow list of safe values or identify and escape a list of potentially malicious values (deny list). Checking an allow list can be a very effective means of enforcing strict input validation rules, but parameterized SQL statements require less maintenance and can offer more guarantees with respect to security. As is almost always the case, implementing a deny list is riddled with loopholes that make it ineffective at preventing SQL injection attacks. For example, attackers may:

- Target fields that are not quoted
- Find ways to bypass the need for certain escaped metacharacters
- Use stored procedures to hide the injected metacharacters

Manually escaping characters in input to SQL queries can help, but it will not make your application secure from SQL injection attacks.

Another solution commonly proposed for dealing with SQL injection attacks is to use stored procedures. Although stored procedures prevent some types of SQL injection attacks, they fail to protect against many others. Stored procedures typically help prevent SQL injection attacks by limiting the types of statements that can be passed to their parameters. However, there are many ways around the limitations and many interesting statements that can still be passed to stored procedures. Again, stored procedures can prevent some exploits, but they will not make your application secure against SQL injection attacks.
References
[1] iBatis Working with Data Maps
[2] iBatis Data Mapper Developer Guide
[3] S. J. Friedl SQL Injection Attacks by Example
[4] P. Litwin Stop SQL Injection Attacks Before They Stop You MSDN Magazine
[5] P. Finnigan SQL Injection and Oracle, Part One Security Focus
[6] M. Howard, D. LeBlanc Writing Secure Code, Second Edition Microsoft Press
[7] IDS00-J. Prevent SQL Injection CERT
[8] Standards Mapping - Common Weakness Enumeration CWE ID 89
[9] Standards Mapping - Common Weakness Enumeration Top 25 2019 [6] CWE ID 089
[10] Standards Mapping - Common Weakness Enumeration Top 25 2020 [6] CWE ID 089
[11] Standards Mapping - Common Weakness Enumeration Top 25 2021 [6] CWE ID 089
[12] Standards Mapping - Common Weakness Enumeration Top 25 2022 [3] CWE ID 089
[13] Standards Mapping - Common Weakness Enumeration Top 25 2023 [3] CWE ID 089
[14] Standards Mapping - DISA Control Correlation Identifier Version 2 CCI-001310, CCI-002754
[15] Standards Mapping - FIPS200 SI
[16] Standards Mapping - General Data Protection Regulation (GDPR) Indirect Access to Sensitive Data
[17] Standards Mapping - NIST Special Publication 800-53 Revision 4 SI-10 Information Input Validation (P1)
[18] Standards Mapping - NIST Special Publication 800-53 Revision 5 SI-10 Information Input Validation
[19] Standards Mapping - OWASP Top 10 2004 A6 Injection Flaws
[20] Standards Mapping - OWASP Top 10 2007 A2 Injection Flaws
[21] Standards Mapping - OWASP Top 10 2010 A1 Injection
[22] Standards Mapping - OWASP Top 10 2013 A1 Injection
[23] Standards Mapping - OWASP Mobile 2014 M1 Weak Server Side Controls
[24] Standards Mapping - OWASP Top 10 2017 A1 Injection
[25] Standards Mapping - OWASP Top 10 2021 A03 Injection
[26] Standards Mapping - OWASP Application Security Verification Standard 4.0 5.3.4 Output Encoding and Injection Prevention Requirements (L1 L2 L3), 5.3.5 Output Encoding and Injection Prevention Requirements (L1 L2 L3)
[27] Standards Mapping - Payment Card Industry Data Security Standard Version 1.1 Requirement 6.5.6
[28] Standards Mapping - Payment Card Industry Data Security Standard Version 1.2 Requirement 6.3.1.1, Requirement 6.5.2
[29] Standards Mapping - Payment Card Industry Data Security Standard Version 2.0 Requirement 6.5.1
[30] Standards Mapping - Payment Card Industry Data Security Standard Version 3.0 Requirement 6.5.1
[31] Standards Mapping - Payment Card Industry Data Security Standard Version 3.2 Requirement 6.5.1
[32] Standards Mapping - Payment Card Industry Data Security Standard Version 3.2.1 Requirement 6.5.1
[33] Standards Mapping - Payment Card Industry Data Security Standard Version 3.1 Requirement 6.5.1
[34] Standards Mapping - Payment Card Industry Data Security Standard Version 4.0 Requirement 6.2.4
[35] Standards Mapping - Payment Card Industry Software Security Framework 1.0 Control Objective 4.2 - Critical Asset Protection
[36] Standards Mapping - Payment Card Industry Software Security Framework 1.1 Control Objective 4.2 - Critical Asset Protection, Control Objective B.3.1 - Terminal Software Attack Mitigation, Control Objective B.3.1.1 - Terminal Software Attack Mitigation
[37] Standards Mapping - Payment Card Industry Software Security Framework 1.2 Control Objective 4.2 - Critical Asset Protection, Control Objective B.3.1 - Terminal Software Attack Mitigation, Control Objective B.3.1.1 - Terminal Software Attack Mitigation, Control Objective C.3.2 - Web Software Attack Mitigation
[38] Standards Mapping - SANS Top 25 2009 Insecure Interaction - CWE ID 089
[39] Standards Mapping - SANS Top 25 2010 Insecure Interaction - CWE ID 089
[40] Standards Mapping - SANS Top 25 2011 Insecure Interaction - CWE ID 089
[41] Standards Mapping - Security Technical Implementation Guide Version 3.1 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II
[42] Standards Mapping - Security Technical Implementation Guide Version 3.4 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II
[43] Standards Mapping - Security Technical Implementation Guide Version 3.5 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II
[44] Standards Mapping - Security Technical Implementation Guide Version 3.6 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II
[45] Standards Mapping - Security Technical Implementation Guide Version 3.7 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II
[46] Standards Mapping - Security Technical Implementation Guide Version 3.9 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II
[47] Standards Mapping - Security Technical Implementation Guide Version 3.10 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II
[48] Standards Mapping - Security Technical Implementation Guide Version 4.1 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[49] Standards Mapping - Security Technical Implementation Guide Version 4.2 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[50] Standards Mapping - Security Technical Implementation Guide Version 4.3 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[51] Standards Mapping - Security Technical Implementation Guide Version 4.4 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[52] Standards Mapping - Security Technical Implementation Guide Version 4.5 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[53] Standards Mapping - Security Technical Implementation Guide Version 4.6 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[54] Standards Mapping - Security Technical Implementation Guide Version 4.7 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[55] Standards Mapping - Security Technical Implementation Guide Version 4.8 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[56] Standards Mapping - Security Technical Implementation Guide Version 4.9 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[57] Standards Mapping - Security Technical Implementation Guide Version 4.10 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[58] Standards Mapping - Security Technical Implementation Guide Version 4.11 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[59] Standards Mapping - Security Technical Implementation Guide Version 5.1 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[60] Standards Mapping - Security Technical Implementation Guide Version 5.2 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[61] Standards Mapping - Security Technical Implementation Guide Version 5.2 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[62] Standards Mapping - Web Application Security Consortium Version 2.00 SQL Injection (WASC-19)
[63] Standards Mapping - Web Application Security Consortium 24 + 2 SQL Injection
desc.config.java.sql_injection_ibatis_data_map
Abstract
Using Java Data Objects (JDO) to execute a dynamic SQL or JDOQL statement built with input coming from an untrusted source can allow an attacker to modify the statement's meaning or to execute arbitrary SQL commands.
Explanation
SQL injection errors occur when:

1. Data enters a program from an untrusted source.



2. The data is used to dynamically construct a SQL or JDOQL query.

Example 1: The following code dynamically constructs and executes a SQL query that searches for items matching a specified name. The query restricts the items displayed to those where owner matches the user name of the currently-authenticated user.


...
String userName = ctx.getAuthenticatedUserName();
String itemName = request.getParameter("itemName");
String sql = "SELECT * FROM items WHERE owner = '"
+ userName + "' AND itemname = '"
+ itemName + "'";
Query query = pm.newQuery(Query.SQL, sql);
query.setClass(Person.class);
List people = (List)query.execute();
...


The query intends to execute the following code:


SELECT * FROM items
WHERE owner = <userName>
AND itemname = <itemName>;


However, because the query is constructed dynamically by concatenating a constant base query string and a user input string, the query only behaves correctly if itemName does not contain a single-quote character. If an attacker with the user name wiley enters the string "name' OR 'a'='a" for itemName, then the query becomes the following:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name' OR 'a'='a';


The addition of the OR 'a'='a' condition causes the where clause to always evaluate to true, so the query becomes logically equivalent to the much simpler query:


SELECT * FROM items;


This simplification of the query allows the attacker to bypass the requirement that the query must only return items owned by the authenticated user. The query now returns all entries stored in the items table, regardless of their specified owner.

Example 2: This example examines the effects of a different malicious value passed to the query constructed and executed in Example 1. If an attacker with the user name wiley enters the string "name'; DELETE FROM items; --" for itemName, then the query becomes the following two queries:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name';

DELETE FROM items;

--'


Many database servers, including Microsoft(R) SQL Server 2000, allow multiple SQL statements separated by semicolons to be executed at once. While this attack string results in an error on Oracle and other database servers that do not allow the batch-execution of statements separated by semicolons, on databases that do allow batch execution, this type of attack allows the attacker to execute arbitrary commands against the database.

Notice the trailing pair of hyphens (--), which specifies to most database servers that the remainder of the statement is to be treated as a comment and not executed [4]. In this case the comment character serves to remove the trailing single-quote left over from the modified query. On a database where comments are not allowed to be used in this way, the general attack could still be made effective using a trick similar to the one shown in Example 1. If an attacker enters the string "name'); DELETE FROM items; SELECT * FROM items WHERE 'a'='a", the following three valid statements will be created:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name';

DELETE FROM items;

SELECT * FROM items WHERE 'a'='a';


One traditional approach to preventing SQL injection attacks is to handle them as an input validation problem and either accept only characters from an allow list of safe values or identify and escape a list of potentially malicious values (deny list). Checking an allow list can be an effective means of enforcing strict input validation rules, but parameterized SQL statements require less maintenance and can offer more guarantees with respect to security. As is almost always the case, implementing a deny list is riddled with loopholes that make it ineffective at preventing SQL injection attacks. For example, attackers may:

- Target fields that are not quoted
- Find ways to bypass the need for certain escaped metacharacters
- Use stored procedures to hide the injected metacharacters

Manually escaping characters in input to SQL queries can help, but it will not make your application secure from SQL injection attacks.

Another solution commonly proposed for dealing with SQL injection attacks is to use stored procedures. Although stored procedures prevent some types of SQL injection attacks, they fail to protect against many others. Stored procedures typically help prevent SQL injection attacks by limiting the types of statements that can be passed to their parameters. However, there are many ways around the limitations and many interesting statements that can still be passed to stored procedures. Again, stored procedures can prevent some types of exploits, but they will not make your application secure against SQL injection attacks.
References
[1] S. J. Friedl SQL Injection Attacks by Example
[2] P. Litwin Stop SQL Injection Attacks Before They Stop You MSDN Magazine
[3] P. Finnigan SQL Injection and Oracle, Part One Security Focus
[4] M. Howard, D. LeBlanc Writing Secure Code, Second Edition Microsoft Press
[5] JDO API Documentation
[6] IDS00-J. Prevent SQL Injection CERT
[7] INJECT-2: Avoid dynamic SQL Oracle
[8] Standards Mapping - Common Weakness Enumeration CWE ID 89
[9] Standards Mapping - Common Weakness Enumeration Top 25 2019 [6] CWE ID 089
[10] Standards Mapping - Common Weakness Enumeration Top 25 2020 [6] CWE ID 089
[11] Standards Mapping - Common Weakness Enumeration Top 25 2021 [6] CWE ID 089
[12] Standards Mapping - Common Weakness Enumeration Top 25 2022 [3] CWE ID 089
[13] Standards Mapping - Common Weakness Enumeration Top 25 2023 [3] CWE ID 089
[14] Standards Mapping - DISA Control Correlation Identifier Version 2 CCI-001310, CCI-002754
[15] Standards Mapping - FIPS200 SI
[16] Standards Mapping - General Data Protection Regulation (GDPR) Indirect Access to Sensitive Data
[17] Standards Mapping - NIST Special Publication 800-53 Revision 4 SI-10 Information Input Validation (P1)
[18] Standards Mapping - NIST Special Publication 800-53 Revision 5 SI-10 Information Input Validation
[19] Standards Mapping - OWASP Top 10 2004 A6 Injection Flaws
[20] Standards Mapping - OWASP Top 10 2007 A2 Injection Flaws
[21] Standards Mapping - OWASP Top 10 2010 A1 Injection
[22] Standards Mapping - OWASP Top 10 2013 A1 Injection
[23] Standards Mapping - OWASP Mobile 2014 M1 Weak Server Side Controls
[24] Standards Mapping - OWASP Top 10 2017 A1 Injection
[25] Standards Mapping - OWASP Top 10 2021 A03 Injection
[26] Standards Mapping - OWASP Application Security Verification Standard 4.0 5.3.4 Output Encoding and Injection Prevention Requirements (L1 L2 L3), 5.3.5 Output Encoding and Injection Prevention Requirements (L1 L2 L3)
[27] Standards Mapping - Payment Card Industry Data Security Standard Version 1.1 Requirement 6.5.6
[28] Standards Mapping - Payment Card Industry Data Security Standard Version 1.2 Requirement 6.3.1.1, Requirement 6.5.2
[29] Standards Mapping - Payment Card Industry Data Security Standard Version 2.0 Requirement 6.5.1
[30] Standards Mapping - Payment Card Industry Data Security Standard Version 3.0 Requirement 6.5.1
[31] Standards Mapping - Payment Card Industry Data Security Standard Version 3.2 Requirement 6.5.1
[32] Standards Mapping - Payment Card Industry Data Security Standard Version 3.2.1 Requirement 6.5.1
[33] Standards Mapping - Payment Card Industry Data Security Standard Version 3.1 Requirement 6.5.1
[34] Standards Mapping - Payment Card Industry Data Security Standard Version 4.0 Requirement 6.2.4
[35] Standards Mapping - Payment Card Industry Software Security Framework 1.0 Control Objective 4.2 - Critical Asset Protection
[36] Standards Mapping - Payment Card Industry Software Security Framework 1.1 Control Objective 4.2 - Critical Asset Protection, Control Objective B.3.1 - Terminal Software Attack Mitigation, Control Objective B.3.1.1 - Terminal Software Attack Mitigation
[37] Standards Mapping - Payment Card Industry Software Security Framework 1.2 Control Objective 4.2 - Critical Asset Protection, Control Objective B.3.1 - Terminal Software Attack Mitigation, Control Objective B.3.1.1 - Terminal Software Attack Mitigation, Control Objective C.3.2 - Web Software Attack Mitigation
[38] Standards Mapping - SANS Top 25 2009 Insecure Interaction - CWE ID 089
[39] Standards Mapping - SANS Top 25 2010 Insecure Interaction - CWE ID 089
[40] Standards Mapping - SANS Top 25 2011 Insecure Interaction - CWE ID 089
[41] Standards Mapping - Security Technical Implementation Guide Version 3.1 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II
[42] Standards Mapping - Security Technical Implementation Guide Version 3.4 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II
[43] Standards Mapping - Security Technical Implementation Guide Version 3.5 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II
[44] Standards Mapping - Security Technical Implementation Guide Version 3.6 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II
[45] Standards Mapping - Security Technical Implementation Guide Version 3.7 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II
[46] Standards Mapping - Security Technical Implementation Guide Version 3.9 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II
[47] Standards Mapping - Security Technical Implementation Guide Version 3.10 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II
[48] Standards Mapping - Security Technical Implementation Guide Version 4.1 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[49] Standards Mapping - Security Technical Implementation Guide Version 4.2 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[50] Standards Mapping - Security Technical Implementation Guide Version 4.3 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[51] Standards Mapping - Security Technical Implementation Guide Version 4.4 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[52] Standards Mapping - Security Technical Implementation Guide Version 4.5 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[53] Standards Mapping - Security Technical Implementation Guide Version 4.6 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[54] Standards Mapping - Security Technical Implementation Guide Version 4.7 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[55] Standards Mapping - Security Technical Implementation Guide Version 4.8 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[56] Standards Mapping - Security Technical Implementation Guide Version 4.9 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[57] Standards Mapping - Security Technical Implementation Guide Version 4.10 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[58] Standards Mapping - Security Technical Implementation Guide Version 4.11 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[59] Standards Mapping - Security Technical Implementation Guide Version 5.1 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[60] Standards Mapping - Security Technical Implementation Guide Version 5.2 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[61] Standards Mapping - Security Technical Implementation Guide Version 5.2 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[62] Standards Mapping - Web Application Security Consortium Version 2.00 SQL Injection (WASC-19)
[63] Standards Mapping - Web Application Security Consortium 24 + 2 SQL Injection
desc.dataflow.java.sql_injection_jdo
Abstract
Constructing a dynamic LINQ statement with input coming from an untrusted source might allow an attacker to modify the statement's meaning or to execute arbitrary SQL commands.
Explanation
Injection errors related to LINQ occur when:

1. Data enters a program from an untrusted source.

2. The data is used to dynamically construct a query.
Example 1: The following code dynamically constructs and executes an LINQ query that searches for items matching a specified name. The query restricts the items displayed to those where owner matches the user name of the currently-authenticated user.


...
string userName = ctx.getAuthenticatedUserName();
string query = "SELECT * FROM items WHERE owner = '"
+ userName + "' AND itemname = '"
+ ItemName.Text + "'";

var items = dataContext.ExecuteCommand<Item>(query);
...


The query intends to execute the following code:


SELECT * FROM items
WHERE owner = <userName>
AND itemname = <itemName>;


However, because the query is constructed dynamically by concatenating a constant base query string and a user input string, the query only behaves correctly if itemName does not contain a single-quote character. If an attacker with the user name wiley enters the string "name' OR 'a'='a" for itemName, then the query becomes the following:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name' OR 'a'='a';


The addition of the OR 'a'='a' condition causes the where clause to always evaluate to true, so the query becomes logically equivalent to the much simpler query:


SELECT * FROM items;


This simplification of the query allows the attacker to bypass the requirement that the query must only return items owned by the authenticated user. The query now returns all entries stored in the items table, regardless of their specified owner.

Example 2: This example examines the effects of a different malicious value passed to the query constructed and executed in Example 1. If an attacker with the user name wiley enters the string "name'); DELETE FROM items; --" for itemName, then the query becomes the following two queries:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name';

DELETE FROM items;

--'


Many database servers, including Microsoft(R) SQL Server 2000, allow multiple SQL statements separated by semicolons to be executed at once. While this attack string results in an error on Oracle and other database servers that do not allow the batch-execution of statements separated by semicolons, on databases that do allow batch execution, this type of attack allows the attacker to execute arbitrary commands against the database.

Notice the trailing pair of hyphens (--), which specifies to most database servers that the remainder of the statement is to be treated as a comment and not executed [4]. In this case the comment character serves to remove the trailing single-quote left over from the modified query. On a database where comments are not allowed to be used in this way, the general attack could still be made effective using a trick similar to the one shown in Example 1. If an attacker enters the string "name'); DELETE FROM items; SELECT * FROM items WHERE 'a'='a", the following three valid statements will be created:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name';

DELETE FROM items;

SELECT * FROM items WHERE 'a'='a';


One traditional approach to preventing LINQ injection attacks is to handle them as an input validation problem and either accept only characters from an allow list of safe values or identify and escape a list of potentially malicious values (deny list). Checking an allow list can be a very effective means of enforcing strict input validation rules, but parameterized LINQ statements require less maintenance and can offer more guarantees with respect to security. As is almost always the case, implementing a deny list is riddled with loopholes that make it ineffective at preventing LINQ injection attacks. For example, attackers may:

- Target fields that are not quoted
- Find ways to bypass the need for certain escaped metacharacters
- Use stored procedures to hide the injected metacharacters

Manually escaping characters in input to LINQ queries can help, but it will not make your application secure from LINQ injection attacks.

Another solution commonly proposed for dealing with LINQ injection attacks is to use stored procedures. Although stored procedures prevent some types of LINQ injection attacks, they fail to protect against many others. Stored procedures typically help prevent LINQ injection attacks by limiting the types of statements that can be passed to their parameters. However, there are many ways around the limitations and many interesting statements that can still be passed to stored procedures. Again, stored procedures can prevent some exploits, but they will not make your application secure against LINQ injection attacks.
References
[1] S. J. Friedl SQL Injection Attacks by Example
[2] P. Litwin Stop SQL Injection Attacks Before They Stop You MSDN Magazine
[3] P. Finnigan SQL Injection and Oracle, Part One Security Focus
[4] M. Howard, D. LeBlanc Writing Secure Code, Second Edition Microsoft Press
[5] Standards Mapping - Common Weakness Enumeration CWE ID 89
[6] Standards Mapping - Common Weakness Enumeration Top 25 2019 [6] CWE ID 089
[7] Standards Mapping - Common Weakness Enumeration Top 25 2020 [6] CWE ID 089
[8] Standards Mapping - Common Weakness Enumeration Top 25 2021 [6] CWE ID 089
[9] Standards Mapping - Common Weakness Enumeration Top 25 2022 [3] CWE ID 089
[10] Standards Mapping - Common Weakness Enumeration Top 25 2023 [3] CWE ID 089
[11] Standards Mapping - DISA Control Correlation Identifier Version 2 CCI-001310, CCI-002754
[12] Standards Mapping - FIPS200 SI
[13] Standards Mapping - General Data Protection Regulation (GDPR) Indirect Access to Sensitive Data
[14] Standards Mapping - NIST Special Publication 800-53 Revision 4 SI-10 Information Input Validation (P1)
[15] Standards Mapping - NIST Special Publication 800-53 Revision 5 SI-10 Information Input Validation
[16] Standards Mapping - OWASP Top 10 2004 A6 Injection Flaws
[17] Standards Mapping - OWASP Top 10 2007 A2 Injection Flaws
[18] Standards Mapping - OWASP Top 10 2010 A1 Injection
[19] Standards Mapping - OWASP Top 10 2013 A1 Injection
[20] Standards Mapping - OWASP Mobile 2014 M7 Client Side Injection
[21] Standards Mapping - OWASP Top 10 2017 A1 Injection
[22] Standards Mapping - OWASP Top 10 2021 A03 Injection
[23] Standards Mapping - OWASP Application Security Verification Standard 4.0 5.3.4 Output Encoding and Injection Prevention Requirements (L1 L2 L3), 5.3.5 Output Encoding and Injection Prevention Requirements (L1 L2 L3)
[24] Standards Mapping - OWASP Mobile Application Security Verification Standard 2.0 MASVS-CODE-4
[25] Standards Mapping - Payment Card Industry Data Security Standard Version 1.1 Requirement 6.5.6
[26] Standards Mapping - Payment Card Industry Data Security Standard Version 1.2 Requirement 6.3.1.1, Requirement 6.5.2
[27] Standards Mapping - Payment Card Industry Data Security Standard Version 2.0 Requirement 6.5.1
[28] Standards Mapping - Payment Card Industry Data Security Standard Version 3.0 Requirement 6.5.1
[29] Standards Mapping - Payment Card Industry Data Security Standard Version 3.2 Requirement 6.5.1
[30] Standards Mapping - Payment Card Industry Data Security Standard Version 3.2.1 Requirement 6.5.1
[31] Standards Mapping - Payment Card Industry Data Security Standard Version 3.1 Requirement 6.5.1
[32] Standards Mapping - Payment Card Industry Data Security Standard Version 4.0 Requirement 6.2.4
[33] Standards Mapping - Payment Card Industry Software Security Framework 1.0 Control Objective 4.2 - Critical Asset Protection
[34] Standards Mapping - Payment Card Industry Software Security Framework 1.1 Control Objective 4.2 - Critical Asset Protection, Control Objective B.3.1 - Terminal Software Attack Mitigation, Control Objective B.3.1.1 - Terminal Software Attack Mitigation
[35] Standards Mapping - Payment Card Industry Software Security Framework 1.2 Control Objective 4.2 - Critical Asset Protection, Control Objective B.3.1 - Terminal Software Attack Mitigation, Control Objective B.3.1.1 - Terminal Software Attack Mitigation, Control Objective C.3.2 - Web Software Attack Mitigation
[36] Standards Mapping - SANS Top 25 2009 Insecure Interaction - CWE ID 089
[37] Standards Mapping - SANS Top 25 2010 Insecure Interaction - CWE ID 089
[38] Standards Mapping - SANS Top 25 2011 Insecure Interaction - CWE ID 089
[39] Standards Mapping - Security Technical Implementation Guide Version 3.1 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II
[40] Standards Mapping - Security Technical Implementation Guide Version 3.4 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II
[41] Standards Mapping - Security Technical Implementation Guide Version 3.5 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II
[42] Standards Mapping - Security Technical Implementation Guide Version 3.6 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II
[43] Standards Mapping - Security Technical Implementation Guide Version 3.7 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II
[44] Standards Mapping - Security Technical Implementation Guide Version 3.9 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II
[45] Standards Mapping - Security Technical Implementation Guide Version 3.10 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II
[46] Standards Mapping - Security Technical Implementation Guide Version 4.1 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[47] Standards Mapping - Security Technical Implementation Guide Version 4.2 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[48] Standards Mapping - Security Technical Implementation Guide Version 4.3 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[49] Standards Mapping - Security Technical Implementation Guide Version 4.4 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[50] Standards Mapping - Security Technical Implementation Guide Version 4.5 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[51] Standards Mapping - Security Technical Implementation Guide Version 4.6 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[52] Standards Mapping - Security Technical Implementation Guide Version 4.7 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[53] Standards Mapping - Security Technical Implementation Guide Version 4.8 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[54] Standards Mapping - Security Technical Implementation Guide Version 4.9 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[55] Standards Mapping - Security Technical Implementation Guide Version 4.10 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[56] Standards Mapping - Security Technical Implementation Guide Version 4.11 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[57] Standards Mapping - Security Technical Implementation Guide Version 5.1 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[58] Standards Mapping - Security Technical Implementation Guide Version 5.2 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[59] Standards Mapping - Security Technical Implementation Guide Version 5.2 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[60] Standards Mapping - Web Application Security Consortium Version 2.00 SQL Injection (WASC-19)
[61] Standards Mapping - Web Application Security Consortium 24 + 2 SQL Injection
desc.dataflow.dotnet.sql_injection_linq
Abstract
Constructing a dynamic SQL statement with input that comes from an untrusted source could allow an attacker to modify the statement's meaning or to execute arbitrary SQL commands.
Explanation
SQL injection errors occur when:

1. Data enters a program from an untrusted source.

2. The data is used to dynamically construct a SQL query.



MyBatis Mapper XML files allow you to specify dynamic parameters in SQL statements and are typically defined by using the # characters, as follows:


<select id="getItems" parameterType="domain.company.MyParamClass" resultType="MyResultMap">
SELECT *
FROM items
WHERE owner = #{userName}
</select>


The # character with braces around the variable name indicate that MyBatis will create a parameterized query with the userName variable. However, MyBatis also allows you to concatenate variables directly to SQL statements using the $ character, opening the door for SQL injection.

Example 1: The following code dynamically constructs and executes a SQL query that searches for items matching a specified name. The query restricts the items displayed to those where the owner matches the user name of the currently-authenticated user.


<select id="getItems" parameterType="domain.company.MyParamClass" resultType="MyResultMap">
SELECT *
FROM items
WHERE owner = #{userName}
AND itemname = ${itemName}
</select>


However, because the query is constructed dynamically by concatenating a constant base query string and a user input string, the query only behaves correctly if itemName does not contain a single-quote character. If an attacker with the user name wiley enters the string "name' OR 'a'='a" for itemName, then the query becomes the following:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name' OR 'a'='a';


The addition of the OR 'a'='a' condition causes the WHERE clause to always evaluate to true, so the query becomes logically equivalent to the much simpler query:


SELECT * FROM items;


This simplification of the query allows the attacker to bypass the requirement that the query should only return items owned by the authenticated user. The query now returns all entries stored in the items table, regardless of their specified owner.

Example 2: This example examines the effects of a different malicious value passed to the query constructed and executed in Example 1. If an attacker with the user name wiley enters the string "name'; DELETE FROM items; --" for itemName, then the query becomes the following two queries:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name';

DELETE FROM items;

--'


Many database servers, including Microsoft(R) SQL Server 2000, allow multiple SQL statements separated by semicolons to be executed at once. While this attack string results in an error on Oracle and other database servers that do not allow the batch-execution of statements separated by semicolons, on databases that do allow batch execution, this type of attack allows the attacker to execute arbitrary commands against the database.

Notice the trailing pair of hyphens (--), which specifies to most database servers that the remainder of the statement is to be treated as a comment and not executed [4]. In this case the comment character serves to remove the trailing single-quote left over from the modified query. On a database where comments are not allowed to be used in this way, the general attack could still be made effective using a trick similar to the one shown in Example 1. If an attacker enters the string "name'); DELETE FROM items; SELECT * FROM items WHERE 'a'='a", the following three valid statements will be created:


SELECT * FROM items
WHERE owner = 'wiley'
AND itemname = 'name';

DELETE FROM items;

SELECT * FROM items WHERE 'a'='a';


One traditional approach to preventing SQL injection attacks is to handle them as an input validation problem and either accept only characters from an allow list of safe values or identify and escape a list of potentially malicious values (deny list). Checking an allow list can be a very effective means of enforcing strict input validation rules, but parameterized SQL statements require less maintenance and can offer more guarantees with respect to security. As is almost always the case, implementing a deny list is riddled with loopholes that make it ineffective at preventing SQL injection attacks. For example, attackers may:

- Target fields that are not quoted
- Find ways to bypass the need for certain escaped metacharacters
- Use stored procedures to hide the injected metacharacters

Manually escaping characters in input to SQL queries can help, but it will not guarantee that an application is secure against SQL injection attacks.

Another solution commonly proposed for dealing with SQL injection attacks is to use stored procedures. Although stored procedures prevent some types of SQL injection attacks, they fail to protect against many others. Stored procedures typically help prevent SQL injection attacks by limiting the types of statements that can be passed to their parameters. However, there are many ways around the limitations and many interesting statements that can still be passed to stored procedures. Again, stored procedures can prevent some exploits, but they will not make your application secure against SQL injection attacks.
References
[1] MyBatis MyBatis 3 | Mapper XML Files
[2] MyBatis MyBatis 3 | Dynamic SQL
[3] S. J. Friedl SQL Injection Attacks by Example
[4] P. Litwin Stop SQL Injection Attacks Before They Stop You MSDN Magazine
[5] P. Finnigan SQL Injection and Oracle, Part One Security Focus
[6] M. Howard, D. LeBlanc Writing Secure Code, Second Edition Microsoft Press
[7] IDS00-J. Prevent SQL Injection CERT
[8] INJECT-2: Avoid dynamic SQL Oracle
[9] Standards Mapping - Common Weakness Enumeration CWE ID 89
[10] Standards Mapping - Common Weakness Enumeration Top 25 2019 [6] CWE ID 089
[11] Standards Mapping - Common Weakness Enumeration Top 25 2020 [6] CWE ID 089
[12] Standards Mapping - Common Weakness Enumeration Top 25 2021 [6] CWE ID 089
[13] Standards Mapping - Common Weakness Enumeration Top 25 2022 [3] CWE ID 089
[14] Standards Mapping - Common Weakness Enumeration Top 25 2023 [3] CWE ID 089
[15] Standards Mapping - DISA Control Correlation Identifier Version 2 CCI-001310, CCI-002754
[16] Standards Mapping - FIPS200 SI
[17] Standards Mapping - General Data Protection Regulation (GDPR) Indirect Access to Sensitive Data
[18] Standards Mapping - NIST Special Publication 800-53 Revision 4 SI-10 Information Input Validation (P1)
[19] Standards Mapping - NIST Special Publication 800-53 Revision 5 SI-10 Information Input Validation
[20] Standards Mapping - OWASP Top 10 2004 A6 Injection Flaws
[21] Standards Mapping - OWASP Top 10 2007 A2 Injection Flaws
[22] Standards Mapping - OWASP Top 10 2010 A1 Injection
[23] Standards Mapping - OWASP Top 10 2013 A1 Injection
[24] Standards Mapping - OWASP Mobile 2014 M1 Weak Server Side Controls
[25] Standards Mapping - OWASP Top 10 2017 A1 Injection
[26] Standards Mapping - OWASP Top 10 2021 A03 Injection
[27] Standards Mapping - OWASP Application Security Verification Standard 4.0 5.3.4 Output Encoding and Injection Prevention Requirements (L1 L2 L3), 5.3.5 Output Encoding and Injection Prevention Requirements (L1 L2 L3)
[28] Standards Mapping - OWASP Mobile Application Security Verification Standard 2.0 MASVS-CODE-4, MASVS-PLATFORM-1
[29] Standards Mapping - Payment Card Industry Data Security Standard Version 1.1 Requirement 6.5.6
[30] Standards Mapping - Payment Card Industry Data Security Standard Version 1.2 Requirement 6.3.1.1, Requirement 6.5.2
[31] Standards Mapping - Payment Card Industry Data Security Standard Version 2.0 Requirement 6.5.1
[32] Standards Mapping - Payment Card Industry Data Security Standard Version 3.0 Requirement 6.5.1
[33] Standards Mapping - Payment Card Industry Data Security Standard Version 3.2 Requirement 6.5.1
[34] Standards Mapping - Payment Card Industry Data Security Standard Version 3.2.1 Requirement 6.5.1
[35] Standards Mapping - Payment Card Industry Data Security Standard Version 3.1 Requirement 6.5.1
[36] Standards Mapping - Payment Card Industry Data Security Standard Version 4.0 Requirement 6.2.4
[37] Standards Mapping - Payment Card Industry Software Security Framework 1.0 Control Objective 4.2 - Critical Asset Protection
[38] Standards Mapping - Payment Card Industry Software Security Framework 1.1 Control Objective 4.2 - Critical Asset Protection, Control Objective B.3.1 - Terminal Software Attack Mitigation, Control Objective B.3.1.1 - Terminal Software Attack Mitigation
[39] Standards Mapping - Payment Card Industry Software Security Framework 1.2 Control Objective 4.2 - Critical Asset Protection, Control Objective B.3.1 - Terminal Software Attack Mitigation, Control Objective B.3.1.1 - Terminal Software Attack Mitigation, Control Objective C.3.2 - Web Software Attack Mitigation
[40] Standards Mapping - SANS Top 25 2009 Insecure Interaction - CWE ID 089
[41] Standards Mapping - SANS Top 25 2010 Insecure Interaction - CWE ID 089
[42] Standards Mapping - SANS Top 25 2011 Insecure Interaction - CWE ID 089
[43] Standards Mapping - Security Technical Implementation Guide Version 3.1 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II
[44] Standards Mapping - Security Technical Implementation Guide Version 3.4 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II
[45] Standards Mapping - Security Technical Implementation Guide Version 3.5 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II
[46] Standards Mapping - Security Technical Implementation Guide Version 3.6 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II
[47] Standards Mapping - Security Technical Implementation Guide Version 3.7 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II
[48] Standards Mapping - Security Technical Implementation Guide Version 3.9 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II
[49] Standards Mapping - Security Technical Implementation Guide Version 3.10 APP3510 CAT I, APP3540.1 CAT I, APP3540.3 CAT II
[50] Standards Mapping - Security Technical Implementation Guide Version 4.1 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[51] Standards Mapping - Security Technical Implementation Guide Version 4.2 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[52] Standards Mapping - Security Technical Implementation Guide Version 4.3 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[53] Standards Mapping - Security Technical Implementation Guide Version 4.4 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[54] Standards Mapping - Security Technical Implementation Guide Version 4.5 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[55] Standards Mapping - Security Technical Implementation Guide Version 4.6 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[56] Standards Mapping - Security Technical Implementation Guide Version 4.7 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[57] Standards Mapping - Security Technical Implementation Guide Version 4.8 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[58] Standards Mapping - Security Technical Implementation Guide Version 4.9 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[59] Standards Mapping - Security Technical Implementation Guide Version 4.10 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[60] Standards Mapping - Security Technical Implementation Guide Version 4.11 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[61] Standards Mapping - Security Technical Implementation Guide Version 5.1 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[62] Standards Mapping - Security Technical Implementation Guide Version 5.2 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[63] Standards Mapping - Security Technical Implementation Guide Version 5.2 APSC-DV-002540 CAT I, APSC-DV-002560 CAT I
[64] Standards Mapping - Web Application Security Consortium Version 2.00 SQL Injection (WASC-19)
[65] Standards Mapping - Web Application Security Consortium 24 + 2 SQL Injection
desc.config.java.sql_injection_mybatis_mapper