Lecture 7 pt1 : Database Security

목차

 

 

Database Driven Websites

 

 

There is a web application running. The server processes the user input and access the databasse with the input. 

 

 

Student data stored into database

Access to database mediated by server

 

 

Handling Input in a Database

Databases organize data

A database management system (DBMS) is the system responsible for managing the data and handling the interaction with the user (데이터베이스 관리 시스템)

Most DBs are relational (but there are also key-value stored, e.g. NoSQL Database)

 

 

Relational Databases

Data organized using tables consisting of rows and columns

- Each column holds a particular type of data (attribute)

- Each row contains a specific value for each column (record)

Ideally has one one column where all values are unique, forming an identifier/key for that row (ID Column 필요)

- Enables the creation of multiple tables linked together by a unique identifier that is present in all tables

Use a query language to access the database

Allows the user to request data that fit a given set of criteria (i.e. search the data)

 

 

Standard Query Language (SQL)

Standardized language to define schema, manipulate, and query data in a relational database

SQL statements can be used to:

- Create tables

- Insert and delete data in tables

- Create views

- Retrieve data with query statements

 

 

SELECT Query

Find records in table (FROM clause) that satisfy a certain condition (WHERE clause)

Result returned as table (attributes given by SELECT)

 

 

UPDATE Function

Update records in table (UPDATE clause) that satisfy a certain condition (WHERE clause)

 

 

 

DELETE Function

Delete records in table (DELETE clause) that satisfy a certain condition (WHERE clause)

 

Data Flow

즉, 유저의 Input으로 HTTP Request를 만든다 (이 경우에는 POST). 그리고 DB에 있는 자료를 액세스하고 200 OK Response를 받으면 유저한테 Output 보여준다.

 

 

 

Authentication (인증)

DB에 있는 name, password 사용 

 

Student sets $username and $passwd 

Access granted if query returns non-empty table (either get nothing or 1 record (not more than one))

→ 이 SQL Query를 했을 때 텅 빈 response가 아니면, 즉 DB에 그 ID와 비밀번호가 저장되어 있다는 뜻이므로 접근이 승인됐다는 뜻이다.

 

 

SQL Injection

Attacker can bypass protections on database

- Via execution of unauthorized queries by injecting SQL code into the database

 

SQL Injection to Bypass Authentication

 

이렇게 되면 Name = 'A' 하고 OR 1 = 1이 붙는다. 이러면 1 = 1은 항상 참이므로 we can get acccess to records without any idea of the passwords. 

SQL에서 --은 comment이므로 -- 이후에 있는 것들은 모두 comment로 처리된다.

This bypasses the password check and potentially grants unauthorized access to the data

 

 

SQL Injection for Data Corruption

SELECT * FROM CSE3220 WHERE Name = 'A';

 This is the first query that is executed. It selects all rows from the CSE3220 table where the Name column is equal to 'A'. This query will return the information related to user 'A'.

UPDATE CSE3220 SET grade = 'A' WHERE Name = 'Lilika' - AND ...:

This is the second query that is executed. It updates the grade column of the CSE3220 table, setting it to 'A', for any rows where the Name column is equal to 'Lilika'. The -- begins a comment, which effectively comments out the rest of the query, preventing any SQL syntax errors that may have been caused by the incomplete query.

 

The second query gets executed so we are able to set the grade without knowing Lilkas's password

 

 

SQL Injection for Priviledge Escalation

; 중요

SELECT * FROM CSE3220 WHERE Name = 'A';: 

This query selects all rows from the CSE3220 table where the Name column is equal to 'A'. It retrieves the information related to user 'A'.

UPDATE CSE3220 SET admin = 1 WHERE Name = 'Lilika' - AND ...:

This query updates the admin column of the CSE3220 table, setting it to 1, for any rows where the Name column is equal to 'Lilika'. The -- begins a comment, which effectively comments out the rest of the query, preventing any SQL syntax errors that may have been caused by the incomplete query. 

즉, 이 query로 인해서 일반 유저였단 릴리카는 admin이 된다 (can do malicious things as an admin)

 

 

Question

 

A인 이유: For B and C, we need to access at least two records 

 

 

Blind SQL Injection

Performing SQL injection when application code is not available (즉, 테이블이 어떻게 생겼다던지 그런 정보 X)

Database schema may be learned through returned error messages

--> Inferring the schema through the error messages (e.g. group ID)

 

A typical countermeasure is to prohibit the display of error messages

Your application may still be vulnerable to blind SQL injection

- Trial and error

- Observe the behavior of the website

 

 

Input Sanitization

Escape potentially malicious characters

Result of escaping quotes in input M; Drop table user; --

 

EX) SELECT * FROM users WHERE user = 'M\'; Drop table user; --'

 

즉, escaping quotes는 attempt to escape the single quote (') character within the string M\, which could potentially prevent SQL injection attacks by neutralizing any injected quotes.

 

More generally, characters to escape include: ' " \ <newline> <return> <null>

 

Sanitizing input is tricky:

- Alternate character encodings may bypass default escape functions

 

 

Second-Order SQL Injection (이 부분은 책 읽기)

Sanitized input may be reused in other queries

Regular user selects username admin'--

Application:

- Escapes quote to prevent possible inject attack

- Stores value admin'-- into user attribute of database

- Later, application retrieves username with clause: WHERE username='admin'-- ...

 

Could be used to change administrator password to one chosen by attacker

 

In a Second Order SQL Injection, the malicious user-upplied injected input is stored in the Database and later it is used (without proper sanitization) in a new SQL query when a user accesses some other functionality of the same application

 

Question

 

A는 second-order SQL injection

 

 

Prepared Statements

Two-phase SQL command:

- Write SQL statements with ? placeholders

- Subsequently provide values that replace placeholders

 

Best practice for:

- Writing new applications

- Modifying existing ones

Maybe used for legacy code

 

Generally safe from SQL injections:

- Separation of code and data

- Values replacing placeholders always treated as data

 

Potential vulnerabiltiies when:

- Prepared statement is itself built from user input

- Prepared statement calls queries from library

 

 

Anomaly Detection

Observe queries on legitimate inputs

Determine properties of typical queries e.g. Result size

Reject inputs that yield atypical queries and outputs

 

E.g. For login using username and password, we expect result size of 0 or 1 but for malicious inputs, we may get a result of a different size