DB Security

by K. Yue

1. Database Security

• Protect the database from unauthorized access, modification, or destruction.
• The CIA Model of Security (or AIC Triad)
  1. Confidentiality: accessed only by authorized users.
  2. Integrity: modified only by authorized users.
  3. Availability: accessible when needed.
• Information system access control must address:
  1. Authorization: Who have what privileges to which objects?
  2. Identification: E.g., Account names.
  3. Authentication: E.g., Password.
  4. Accountability: E.g., Who have done what actions?
• Some database security mechanisms:
  1. Views: define better access controls.
  2. Security log: journals storing attempted security violations.
  3. Audit trail: Information about SQL operations are stored, such as by using triggers.
  4. Encryption: especially sensitive information such as passwords.
• SQL authorization language:
  • GRANT statement used for authorization
  • REVOKE statement used to retract authorization
  • MySQL directly supports ROLE, which can be used as the basis of a simple Role Based Access Control (RBAC) system.

Example:

CREATE USER 'temp'@'%' IDENTIFIED VIA mysql_native_password USING ....;
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES ON *.*
     TO 'temp'@'%' REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;

2. SQL Injection (SQLI)

• A code injection method that takes advantages of dynamic SQL construction in database-driven Web applications.
• One of the most common Web hacking techniques.
• Originated from improper filtering of special characters in the target languages (SQL in this case).
• Attackers enter input through Web forms to modify the intention of the SQL statements in the backend Web applications.

Example: adapted from the textbook and Wikipedia

Consider a Web form that accepts user names and passwords:

The back-end page may include unsafe code dynamically constructing a query.

The variables username and password get their values from the users through the Web form through the CGI protocol.

Thus, if the user enters (not considering encryption issues here):

username = yue
password = 1Bkm*2ce

the variable query will have a value of

"SELECT * FROM users WHERE name = 'yue' and password = '1Bkm*2ce';"

The query can be executed to get information about the user 'yue' if the right password is provided.

If someone enters:

username = yue
password = 1Bkm*2'ce

query becomes:

"SELECT * FROM users WHERE name = 'yue' and password = '1Bkm*2'ce';"

Executing the SQL query statements will result in a SQL syntax error in the server-side program since the single quote character ' is an escape character in SQL with special meanings.

For SQL injection, attacker may enter:

username = yue
password = ' OR '1'='1

query becomes:

"SELECT * FROM users WHERE name = 'yue' and password = '' OR '1'='1';"

Note that the structure of the SQL statement has been changed. Since the and operator has a higher precedence than the or operator, this is equivalent to:

"SELECT * FROM users WHERE (name = 'yue' and password = '') OR '1'='1';"

The condition in the WHERE clause will always be evaluated to true. The query will bypass the password checking and return information about all users, not just the user 'yue'.

For input:

username = yue
password = ' OR '1'='1’; DELETE * FROM student; --

query becomes essentially:

"SELECT * FROM users
 WHERE name = 'yue' and password = '' OR '1'='1';
 DELETE * FROM student; -- '; "

For input:

username = yue
password = ' OR '1'='1’; DROP TABLE users; SELECT * FROM account; --

query becomes essentially:

"SELECT * FROM users
 WHERE name = 'yue' and password = '' OR '1'='1';
DROP TABLE users;
SELECT * FROM account; -- '; "

In all of these examples, the intended structures of the SQL statements are changed by the attackers.

2.1 SQLI Mitigation

[1] Input validation: validate input parameter values, properly escaping special characters.

In the minimum, replace one ' by two ':

username = username.replace("'", "''")
password = password.replace("'", "''")

For input parameters:

username = yue
password = ' OR '1'='1

after filtering query becomes:

"SELECT * FROM users WHERE name = 'yue' and password = ''' OR ''1''=''1';"

The condition of the Where clause will be false (unless the password is really "' OR '1'='1".

• Besides possible SQLI, a Web page without proper input validation may result in syntax or runtime errors and reveals information about the back-end system.
• Input validation not only improves security. It has many other benefits.

[2] Using parameterized queries:

• Parameterized queries are usually prepared and compiled beforehand with placeholders for input parameters. The structure of the SQL statement cannot be changed. E.g., in Python, %s is a parameter placeholder.

query = "SELECT * FROM users WHERE name = %s and password = %s;"
cursor.execute(query,(username, password))

• Prepared statements provide many benefits and should always be considered as the first choice.

[3] Using intermediate mid-tiered objects instead of SQL for centralized checking.

• Example: instead of directly executing SQL statements, the Web applications can call methods of well-designed and well-tested classes to access the data.

[4] Use database security features and good practices

• Can be vendor specific.
• Apply the CIA principle to set up the database.