06 - database security

Database security has not kept pace with the increased reliance on databases. The main reasons are:

• imbalance of complexity ⟶ here is a dramatic imbalance between the complexity of modern database management systems (DBMS) and the security technique used to protect these critical systems.
• complex interaction protocol ⟶ Structured Query Language (SQL), the interaction protocol that databases use, is complex
• heterogeneous environments ⟶ most enterprise environments consist of a heterogeneous mixture of database platforms, enterprise platforms, and OS platforms, creating an additional complexity hurdle for security personnel
• cloud reliance ⟶ increasing reliance on cloud technology to host part or all of the corporate database.
• lack of dedicated personnel ⟶ the typical organization lacks full-time database security personnel

Effective database security requires a strategy based on a full understanding of the security vulnerabilities of SQL.

SQL injection attacks (SQLi)

SQL injection attacks represent one of the most prevalent network-based security threats. They are designed to exploit the nature of web application pages by sending malicious SQL commands to the database server.

• the most common attack goal is bulk extraction of data
• depending on the environment, it can also be exploited to modify or delete data, execute arbitrary OS commands, or launch DoS attacks

typical SQL injection attack

injection technique

SQLi attacks typically work by prematurely terminating a text string and appending a new command.

• since the inserted command may have additional strings appended to it before its execution, attackers terminate the injected string with a comment mark --, so the subsequent text is ignored at execution time

SQL attack avenues

The main avenues for SQL attacks are:

• user input
• server variables ⟶ attackers can forge HTTP and network header values, placing data directly into them
• second-order injection ⟶ a malicious user could rely on data already present in the system to trigger an SQL injection attack (the input that modifies the query to cause an attack doesn’t come from the user but from within the system itself)
• cookies ⟶ an attacker could alter cookies so that when the application server builds an SQL query based on their content, its structure and function are modified
• physical user input ⟶ for example through I/O mechanisms (USB sticks etc)

attack types

inband attacks

Inband attacks use the same communication channel for injecting SQL code and retrieving results (presented in a web page).

Examples:

• tautology ⟶ injects conditional statements so that they always evaluate to true
• end-of-line comment ⟶ after injecting code, legitimate code that follows is nullified by end of line comments (--)
• piggybacked queries ⟶ the attacker adds additional queries beyond the intended one, piggy-backing the attack on top of a legitimate request

inferential attack

There is no transfer of data, but the attacker is able to reconstruct the information by sending particular requests and observing the resulting behaviour of the website/DB.

Examples:

• illegal/logically incorrect queries: (preliminary, information-gathering step for other attacks) the attacker uses incorrect queries to gathers important information about the type and structure of the backend DB
• blind SQL injections: data is inferred bit-by-bit by observing subtle changes in the page content or response time
• out-of-band attacks: data is retrieved using a different channel (the outbound connectivity from the database is lax) the malicious command forces the database engine to execute a function that attemps to contact a remote server

SQLi sinks

SQLi sinks are the points in a program where user-controlled data is incorporated into database queries, creating a vulnerability (sinks write results to the DB)

• the security goal is to ensure the source (the user input) never flows unchecked to the sink (the database execution function)

Some examples of sinks include User Input (GET/POST parameters), HTTP Headers, Cookies or the database itself in case of a second order injection.

target	description
identify injectable params (sources / sinks)	the attacker finds the vulnerable sources that feed into an unsafe sink, by testing inputs with simple characters like '
database footprinting	find out which DBMS is in use. (SQL syntax differs betweeen systems) (can be made easy by poorly configured applications that display verbose error messages)
discover DB schema	find names of important tables and the columns within them
data extraction	stealing the information. the attacker uses techniques like UNION to combine malicious queries with the original one
data manipulation	the attacker modifies the database’s integrity by changing existing records, deleting data, inserting new malicious data
denial of service	preventing legitimate user from using the web application by flooding the database with useless queries or deleting stuff or lock tables
authentication bypass	the attacker tricks the application into authenticating them without a valid password
remote command execution	(highest impact target) some DMBS allow the execution of OS commands via SQL; if the attacker reaches this target, they can run commands directly on the server’s OS

example: tautology

$q = "SELECT id FROM users WHERE user = '" .$user. "' AND pass = '" .$pass. "' ";
 
-- sent parameters:
$user = "admin"
$pass = "' OR '1'='1'"
 
-- executed query:
$sq = " SELECT id FROM users WHERE user='admin' AND pass='' OR '1'='1' ";

SQLi queries

tautologies

Common tautologies used are:

// choosing the first available user "blindly"
$pass ="' OR 1=1#";
$user = "' OR user LIKE '%' #";
$user = "' OR 1 #";
 
//choosing a known user
$user = "admin' OR 1#";
$user = "admin'#";
 
//IDS (intrusion detection system) evasion
$pass = "'OR 5>4 OR password='mypass";
$pass = "' OR 'vulnerability' > 'server' ";

UNION query

the UNION construct can be used to achieve data extraction:

example

$q = "SELECT id, name, price, description FROM products WHERE category='" .$cat. "' ";
 
$cat = "' 1 UNION SELECT 1, user, 1, pass FROM users --";
 
-- query (MySQL performs a cast)
$q = "SELECT id, name, price, description FROM products WHERE category=1 UNION SELECT 1, 1, user, pass FROM users"
 
-- 1s are placeholders for fields we don't know/care about, so we ensure compatibility with the original query's columns

the number and type of the columns returned by the two SELECT queries must match

• in MySQL, if the types do not match, a cast is performed automatically

second order inject

To perform a second order inject attack, a user with a malicious name is registered. Later on, the attacker asks to change its password, leading the DB to perform another query when retrieving the relative data.

example

-- malicious user 
$user = "admin' #";
 
-- update password query
$q = "UPDATE users SET pass='"$._POST['newPass']."' WHERE user='".$row['user']."'";
 
-- query if the data coming from the database is not properly sanitized
$q = UPDATE users SET pass='password' WHERE user='admin'#";

piggy-backing

example

-- query
$q = "SELECT id FROM users WHERE user='"user."' AND pass='"
 
-- injected parameters
$user = "'; DROP TABLE users --"
 
-- query executed
$q = "SELECT id FROM users WHERE user=''; DROP TABLE users --' AND pass=''"

• both queries are executed

information schema

Informations schema are metadata about the objects within a database. They can be used to gather data about any tables from the available databases.

INFORMATION_SCHEMA.TABLES is a view that provides information about the tables that exist within the database:

• TABLE_SCHEMA ⟶ DB to which the table belongs
• TABLE_NAME ⟶ name of the table
• TABLE_ROWS ⟶ number of rows in the table

INFORMATION_SCHEMA.COLUMNS is a view that provides information about the columns that exist within the database:

• TABLE_NAME ⟶ name of the table containing the column
• COLUMN_NAME ⟶ name of the column
• COLUMN_TYPE ⟶ data type of the column

example

-- vulnerable query
$q = "SELECT username FROM users WHERE user id=$id";
 
-- step 1: get the table's name
$id = "- 1 UNION SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema != 'mysql' AND table_schema != 'information_schema' -- ";
 
-- step 2: get the name of the columns inside the tables
$id = "-1 UNION SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'users' LIMIT 0,1 --";
 
-- using '-1' as the ID makes the query result for the original query to be empty, forcing the db to rely on the results of the UNION SELECT

blind sqli

Third, most common category of SQLi (the other two are error-based SQi and Union-Based SQLi): the attacker cannot directly see the output of the database query on the web page.

there are two types of blind SQLi:

• boolean-based
• time-based: used when there are no visible differences in the page content or status to indicate a TRUE or FALSE result.
  • the attacker uses conditional time-daly functions (IF clauses + SLEEP) and measures the response time to determine the outcome of the query.
  • this way, the attacker slowly extracts data one character at a time

SQL file operations

If the attacker achieves remote command execution, they could read/write files.

example

-- vulnerable query
$q = "SELECT username FROM users WHERE user id = $id";
 
-- LOAD_FILE() returns NULL upon failure
-- read file
$id = " -1' UNION SELECT LOAD_FILE('/etc/passwd')";
 
-- INTO OUTFILE can trigger a MySQL error
-- write file
$id = "-1 UNION SELECT 'hi' INTO OUTFILE  '/tmp/hi'";

SQL countermeasures

There are three types of countermeasures against SQL injections:

• parameterized query insertion
• manual defensive coding practices
• SQL DOM

parameterized query insertion

parameterized queries are essential for defense against SQLis, as they address the fundamental flaw of SQLis: the ambiguity between code and data

They work by forcing the application to send the SQL command structure to the database separately from any user-supplied data:

$q = "SELECT * FROM users WHERE user_id = ? and password = ?";

the application then sends the user’s input variables to those placeholders, and the db treats the input strictly as data, regardless of what it contains:

-- if the input is "' OR 1=1 --", the db sees it as a single, long string literal
$q = "SELECT * FROM users WHERE user_id = '' OR 1-=1 -- ' AND password='user-password' ";

manual defensive coding practices

supplementary to parameterized queries or used in scenarios where the parameterization is impossible, they include:

• escaping user input: the code manually iterates through the user input and prepends a \ to any special characters(like ' or ")
• type enforcement: ensuring that if the code expects a number, any non-numeric characters are immediately rejected or cast to an integer, which strips injection payloads
• whitelisting: only allowing input that matches a strict set of approved characters or values
• principle of least principle: acts on the configuration of a DB to limit the impact of a successful SQLi attack (the application’s db should only have the minimum privileges necessary for its operation)

SQL DOM

SQL DOM (Data-Oriented Modeling) is a more formal approach to preventing SQL.

It enforces a fundamental rule: query structure must be defined separately from query data. This design pattern is typically implemented as a framework.

Instead of letting developers build queries using string concatenation, the language environment requires the use of constructor methods to build the query piece by piece.

• this approach makes it virtually impossible for developers to accidentally create an unsafe sink

DB access control

Database access control determines:

• which portions of the DB a user has access to
• what access rights the user has to those portions

There are a range of administrative policies supported by DB access control:

• centralized administration ⟶ a small number of privileged users may grant/revoke access rights
• ownership-based administration ⟶ the creator of a table may grant/revoke access rights to the table
• decentralized administration ⟶ the owner of a table may grank/revoke permissions to other users, allowing them to grant/revoke access rights to the table
  • in this case, cascading authorization happens: if access rights cascade through a number of users, the revocation of privileges also cascades: when user A revokes an access right, any cascaded access right is also revoked (unless that access right would exist even if the original grant from A had never occurred)

There are two commands for managing access rights:

• GRANT ⟶ used to grant one or more access rights, or can be used to assign a role to a user
  • GRANT {privileges | role} ON {table} TO {user | role | PUBLIC} [IDENTIFIED BY {password} WITH {GRANT OPTION}
• REVOKE ⟶ revokes access rights
  • REVOKE {privileges | role} ON {table} FROM {user | role | PUBLIC}

(typical access rights are SELECT, INSERT, UPDATE, DELETE)

inference

In database security, inference refers to the process of performing authorised queries to deduce unauthorised information.

Inference can be detected:

• during database design by altering the database structure to prevent inference (often results in unnecessarily stricter access control)
• at query time (if an inference channel is detected, the query is denied or altered)

Some inference detection algorithm is needed for both options.

database encryption

While it is protected by multiple layers of security (firewalls, authentication, various access control systems), it is possible to encrypt the database as a last line of defense.

Encryption can be applied to the entire database, at the record level, the attribute level, or the level of the individual field.

Encryption causes two main disadvantages:

• key management: authorized users must have access to the decryption key for the data to which they have access
• inflexibility: it is more difficult to perform record searching

To provide more flexibility, it must be possible to work with the database in its encrypted form

encryption and indexing

• each record (row) of the db is encrypted as a block (the db cannot search or index individual attributes, as only random-looking ciphertext is available)
  • for each row of the original db, there is an encrypted row
• for any attribute, the range of attribute values is divided into a set of non-overlapping partitions that encompass all possible values, and an index value is assigned to each partition

when a user asks to search for a range, the system translates that range into the corresponding index values and the db executes a simple, fast query on the unencrypted index column.

example