Chapter 5

Relational database

- The basic building block is a table of data, consisting of rows and columns, similar to a spreadsheet. + Each column holds a particular type of data + each row contains a specific value for each column. + Ideally, the table has at least one column in which each value is unique, forming as an identifier for a given entry (or row) - enables the creation of multiple tables tied together by a unique identifier that is present in all tables. - use a relational query language to access the database. + uses declarative statements rather than the procedural instructions of a programming language. + allows the user to request selected items of data from all records that fit a given set of criteria.

Structured Query Language (SQL)

- a standardized language that can be used to define schema, manipulate, and query data in a relational database. - several versions of the ANSI/ISO standard and a variety of different implementations, but all follow the same basic syntax and semantics. - SQL statements can be used to create tables, insert and delete data in tables, create views, and retrieve data with query statements.

Database

- a structured collection of data stored for use by one or more applications. - contains the relationships between data items and groups of data items. - can contain sensitive data that needs to be secured - use database management system (DBMS) , + a suite of programs for constructing and maintaining the database + offer ad hoc query facilities to multiple users and applications. - A query language provides a uniform interface to the database for users and applications.

out-of-band attack

- data are retrieved using a different channel (e.g., an email with the results of the query is generated and sent to the tester). - This can be used when there are limitations on information retrieval, but outbound connectivity from the database server is lax.

SQLi Countermeasures

- defensive coding • Manual defensive coding practices --> input type checking --> performs pattern matching to try to distinguish normal input from abnormal input. • Parameterized query insertion: --> attempts to prevent SQLi by allowing the application developer to more accurately specify the structure of an SQL query, and pass the value parameters to it separately such that any unsanitary user input is not allowed to modify the query structure. • SQL DOM: --> a set of classes that enables automated data type validation and escaping --> uses encapsulation of database queries to provide a safe and reliable way to access databases. --> the query-building process uses a systematic one that uses a type-checked API. --> can apply input filtering and rigorous type checking of user input. - Detection • Signature based: attempts to match specific attack patterns. --> must be constantly updated and may not work against self-modifying attacks. • Anomaly based: attempts to define normal behavior and then detect behavior patterns outside the normal range. --> a training phase, in which the system learns the range of normal behavior, followed by the actual detection phase. • Code analysis: involve the use of a test suite to detect SQLi vulnerabilities --> designed to generate a wide range of SQLi attacks and assess the response of the system. - Run-time prevention --> check queries at runtime to see if they conform to a model of expected queries.

SQL Injection Attacks (SQLi)

- one of the most prevalent and dangerous network-based security threats. - designed to exploit the nature of Web application pages. - most current Web sites have dynamic components and content. + ask for information, such as location, personal identity information, and credit card information. + This dynamic content is usually transferred to and from back-end databases that contain volumes of information - designed to send 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 operating system commands, or launch denial-of-service (DoS) attacks.

Role-Based Access Control (RBAC)

- provides a means of easing the administrative burden and improving security. - can classify database users in three broad categories: • Application owner: An end user who owns database objects (tables, columns, rows) as part of an application. • End user other than application owner: An end user who operates on database objects via a particular application but does not own any of the database objects. • Administrator: User who has administrative responsibility for part or all of the database. --> responsible for more sensitive or general roles A database RBAC facility needs to provide the following capabilities: • Create and delete roles. • Define permissions for a role. • Assign and cancel assignment of users to roles.

SQL Access Controls

- provides two commands for managing access rights, GRANT and REVOKE. + GRANT command has the following syntax: GRANT { privileges | role } [ON table] TO { user | role | PUBLIC } [IDENTIFIED BY password] [WITH GRANT OPTION] --> used to grant one or more access rights or can be used to assign a user to a role. + REVOKE --> revokes the access rights - Typical access rights • Select: Grantee may read entire database; individual tables; or specific columns in a table. • Insert: • Update: • Delete: • References: Grantee is allowed to define foreign keys in another table that refer to the specified columns.

Relational database elements

- the basic building block is a relation , which is a flat table or file - Rows are referred to as tuples or record - columns are referred to as attributes or field - A primary key + uniquely identify a row in a table; + consists of one or more column names. - To create a relationship between two tables, the attributes that define the primary key in one table must appear as attributes in another table --> a foreign key + can appear multiple times in a table --> one-to-many relationship between a row in the table with the primary key and rows in the table with the foreign key. - A view is a virtual table. + the result of a query that returns selected rows and columns from one or more tables. + used for security purposes. + can provide restricted access to a relational database so that a user or application only has access to certain rows or columns.

Inferential Attack

- there is no actual transfer of data, but the attacker is able to reconstruct the information by sending particular requests and observing the resulting behavior of the Website/database server. • Illegal/logically incorrect queries: - lets an attacker gather important information about the type and structure of the backend database of a Web application. - considered a preliminary, information-gathering step for other attacks. - the default error page returned by application servers is often overly descriptive. - an error messages can often reveal vulnerable/injectable parameters to an attacker. • Blind SQL injection: - allows attackers to infer the data present in a database system even when the system is sufficiently secure to not display any erroneous information back to the attacker. - The attacker asks the server true/false questions. - If the injected statement evaluates to true, the site continues to function normally. - If the statement evaluates to false, although there is no descriptive error message, the page differs significantly from the normally functioning page.

Database access control

- to determine whether a user may have access to the database as a whole. - For users who are authenticated and granted access to the database, it provides a specific capability that controls access to portions of the database. --> What access rights the user has (create, insert, delete, update, read, write) - can support a range of administrative policies, • Centralized administration: A small number of privileged users may grant and revoke access rights. • Ownership-based administration: The owner (creator) of a table may grant and revoke access rights to the table. • Decentralized administration: In addition to granting and revoking access rights to a table, the owner of the table may grant and revoke authorization rights to other users, allowing them to grant and revoke access rights to the table.

Injection Techniques

- typically works by prematurely terminating a text string and appending a new command. - Because the inserted command may have additional strings appended to it before it is executed, the attacker terminates the injected string with a comment mark "--". - Subsequent text is ignored at execution time.

Inband Attacks

- uses the same communication channel for injecting SQL code and retrieving results. - The retrieved data are presented directly in the application Web page. • Tautology: This form of attack injects code in one or more conditional statements so that they always evaluate to true. • End-of-line comment: After injecting code into a particular field, legitimate code that follows are nullified through usage of end of line comments. --> An example would be to add "- -" after inputs so that remaining queries are not treated as executable code, but comments. The preceding tautology example is also of this form. • Piggybacked queries: The attacker adds additional queries beyond the intended query, piggy-backing the attack on top of a legitimate request. --> relies on server configurations that allow several different queries within a single string of code. The example in the preceding section is of this form.

DBMS Architecture

-Slide 4 - use a data definition language (DDL) to define the database logical structure and procedural properties, which are represented by a set of database description tables. - A data manipulation language (DML) provides a powerful set of tools for application developers. - Query languages are declarative languages designed to support end-users. - use of the database description tables to manage the physical database. - uses authorization tables to ensure the user has permission to execute the query language statement on the database.

SQLi Attack Avenues

• User input: - attackers inject SQL commands by providing suitably crafted user input. - typically comes from form submissions that are sent to the Web application via HTTP GET or POST requests. • Server variables: - a collection of variables that contain HTTP headers, network protocol headers, and environmental variables. - attackers can forge the values that are placed in HTTP and network headers, they can exploit this vulnerability by placing data directly into the headers. • Second-order injection: - occurs when incomplete prevention mechanisms against SQL injection attacks are in place. - a malicious user could rely on data already present in the system or database to trigger an SQL injection attack, so when the attack occurs, the input that modifies the query to cause an attack does not come from the user, but from within the system itself. • Cookies: - used to restore the client's state information when user visits a web application. - an attacker could alter cookies such that when the application server builds an SQL query based on the cookie's content, the structure and function of the query is modified. • Physical user input: - supplying user input that constructs an attack outside the realm of web requests. - could take the form of conventional barcodes, RFID tags, or even paper forms which are scanned using optical character recognition and passed to a database management system.