CSIS 330
Questions:
L
AB
6 A
NSWER
T
EMPLATE
5. a. Weakness Prevalence: High
b. If software is all about the data: getting it into the database, pulling it from
the database, massaging it into information, and sending it elsewhere for fun and profit. If
attackers can influence the SQL that you use to communicate with your database, then
suddenly all your fun and profit belongs to them. If you use SQL queries in security
controls such as authentication, attackers could alter the logic of those queries to bypass
security. They could modify the queries to steal, corrupt, or otherwise change your
underlying data. They'll even steal data one byte at a time if they have to, and they have the
patience and know-how to do so.
c. Architecture and Design
If available, use structured mechanisms that automatically enforce the separation between
data and code. These mechanisms may be able to provide the relevant quoting, encoding,
and validation automatically, instead of relying on the developer to provide this capability
at every point where output is generated.
Process SQL queries using prepared statements, parameterized queries, or stored
procedures. These features should accept parameters or variables and support strong
typing. Do not dynamically construct and execute query strings within these features using
"exec" or similar functionality, since you may re-introduce the possibility of SQL injection.
d. Implementation
If you need to use dynamically-generated query strings or commands in spite of the risk,
properly quote arguments and escape any special characters within those arguments. The
most conservative approach is to escape or filter all characters that do not pass an
extremely strict whitelist (such as everything that is not alphanumeric or white space). If
some special characters are still needed, such as white space, wrap each argument in quotes
after the escaping/filtering step. Be careful of argument injection (CWE-88).
Instead of building your own implementation, such features may be available in the
database or programming language. For example, the Oracle DBMS_ASSERT package can
CSIS 330
check or enforce that parameters have certain properties that make them less vulnerable to
SQL injection. For MySQL, the mysql_real_escape_string() API function is available in
both C and PHP.
e. Related CWEs
CWE-566 Authorization Bypass Through User-Controlled SQL Primary Key
CWE-619 Dangling Database Cursor ('Cursor Injection')
6. a. The software constructs all or part of an SQL command using externally-
influenced input from an upstream component, but it does not neutralize or incorrectly
neutralizes special elements that could modify the intended SQL command when it is sent
to a downstream component.
b. The listings below show Technology Classes for which the given weakness
could appear. These may be for specific named Languages, Operating Systems,
Architectures, Paradigms, Technologies, or a class of such platforms. The platform is listed
along with how frequently the given weakness appears for that instance.
Languages
Class: Language-Independent (Undetermined Prevalence)
Technologies
Database Server (Undetermined Prevalence)
c. Likelihood Of Exploit : High
d. Automated Static Analysis - Binary or Bytecode
According to SOAR, the following detection techniques may be useful:
Highly cost effective:
Bytecode Weakness Analysis - including disassembler + source code weakness analysis
Binary Weakness Analysis - including disassembler + source code weakness analysis
Effectiveness: High
e. Demonstrative Example
CSIS 330
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.
(bad code)
Example Language: C#
...
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 that this code intends to execute follows:
(informative)
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:
(attack code)
name' OR 'a'='a
for itemName, then the query becomes the following:
(attack code)
SELECT * FROM items WHERE owner = 'wiley' AND itemname = 'name' OR 'a'='a';
The addition of the:
CSIS 330
(attack code)
OR 'a'='a
condition causes the WHERE clause to always evaluate to true, so the query becomes
logically equivalent to the much simpler query:
(attack code)
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.
