SQL Injection

Constructing a dynamic SQL statement with input from an untrusted source might allow an attacker to modify the statement's meaning or execute arbitrary SQL commands.

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.

Constructing a dynamic SQL statement with input from an untrusted source might allow an attacker to modify the statement's meaning or execute arbitrary SQL commands.

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.

Constructing a dynamic SQL statement with input from an untrusted source might allow an attacker to modify the statement's meaning or execute arbitrary SQL commands.

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.

Constructing a dynamic SQL statement with input from an untrusted source might allow an attacker to modify the statement's meaning or execute arbitrary SQL commands.

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.

Constructing a dynamic SQL statement with input from an untrusted source might allow an attacker to modify the statement's meaning or execute arbitrary SQL commands.

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.

Constructing a dynamic SQL statement with input from an untrusted source might allow an attacker to modify the statement's meaning or execute arbitrary SQL commands.

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.

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.

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.

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.

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.

Constructing a dynamic SQL statement with input from an untrusted source might allow an attacker to modify the statement's meaning or execute arbitrary SQL commands.

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.

Constructing a dynamic SQL statement with input from an untrusted source might allow an attacker to modify the statement's meaning or execute arbitrary SQL commands.

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.

Constructing a dynamic SQL statement with input from an untrusted source might allow an attacker to modify the statement's meaning or execute arbitrary SQL commands.

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.

Constructing a dynamic SQL statement with input from an untrusted source might allow an attacker to modify the statement's meaning or execute arbitrary SQL commands.

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.

Constructing a dynamic SQL statement with input from an untrusted source might allow an attacker to modify the statement's meaning or execute arbitrary SQL commands.

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.

Constructing a dynamic SQL statement with input from an untrusted source might allow an attacker to modify the statement's meaning or execute arbitrary SQL commands.

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.

Constructing a dynamic SQL statement with input from an untrusted source might allow an attacker to modify the statement's meaning or execute arbitrary SQL commands.

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.

Constructing a dynamic SQL statement with input from an untrusted source might allow an attacker to modify the statement's meaning or execute arbitrary SQL commands.

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.