207
Example Language: (Attack)
SELECT * FROM items WHERE owner = 'wiley' AND itemname = 'name' OR 'a'='a';
The addition of the:
Example Language: (Attack)
OR 'a'='a
condition causes the WHERE clause to always evaluate to true, so the query becomes logically
equivalent to the much simpler query:
Example Language: (Attack)
SELECT * FROM items;
This simplification of the query allows the attacker to bypass the requirement that the query 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 the previous example.
If an attacker with the user name wiley enters the string:
Example Language: (Attack)
name'; DELETE FROM items; --
for itemName, then the query becomes the following two queries:
Example Language: SQL (Attack)
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. 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 the previous example.
If an attacker enters the string
Example Language: (Attack)
name'; DELETE FROM items; SELECT * FROM items WHERE 'a'='a
Then the following three valid statements will be created:
CWE Version 4.14
CWE-89: Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection')
CWE-89: Improper Neutralization of Special
Elements used in an SQL Command ('SQL Injection') 208
Example Language: (Attack)
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 allowlist of safe values or identify
and escape a denylist of potentially malicious values. Allowlists 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, denylisting
is riddled with loopholes that make it ineffective at preventing SQL injection attacks. For example,
attackers can:
• Target fields that are not quoted
• Find ways to bypass the need for certain escaped meta-characters
• Use stored procedures to hide the injected meta-characters.
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 do not
protect against many others. For example, the following PL/SQL procedure is vulnerable to the
same SQL injection attack shown in the first example.
Example Language: (Bad)
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;
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.
Example 4:
MS SQL has a built in function that enables shell command execution. An SQL injection in such a
context could be disastrous. For example, a query of the form:
Example Language: (Bad)
SELECT ITEM,PRICE FROM PRODUCT WHERE ITEM_CATEGORY='$user_input' ORDER BY PRICE
Where $user_input is taken from an untrusted source.
If the user provides the string:
Example Language: (Attack)
'; exec master..xp_cmdshell 'dir' --
The query will take the following form:
CWE Version 4.14
CWE-89: Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection')
CWE-89: Improper Neutralization of Special
Elements used in an SQL Command ('SQL Injection')