DATABASE SECURITY

Prepared by: Dr. Alia Alabdulkarim

Scan the QR-code

https://www.hacksplaining.com/exercises/sql-injection
2
Database Security principles
Databases are the heart of virtually every modern web application
static and dynamic web pages

Attackers know the value of databases too

Input without
SQL has become the de facto standard for relational data storage sanitizing

3
What is SQL Injection?

User fields Application

https://lankadevelopers.com/ 4
Where does SQL Injection Happens?
Attackers try to find places in the web application where they can input their malicious
SQL query (payload) or enter a command to alter the current query.
Use userinput and passinput
Example:
without validations
Original query:
SELECT * FROM USERS WHERE USERNAME=‘userinput’ AND
PASSWORD=‘passinput’
What does --
mean?

The query with that input:

SELECT * FROM USERS WHERE USERNAME=‘hi’ OR 1=1 -- ’
AND PASSWORD=‘passinput’

8
https://www.xtrmhack.com/2011/01/sql-injection.html
Where does SQL Injection Happens?
How is SQLi different from XSS?

What did attackers do to find out if the web app is vulnerable to xss?

What did attackers do to find out if the web app is vulnerable to SQLi?

How do they think the original query for this form looks like ?

Select * from Users WHERE Username=‘user’ AND Password=‘password’

How attacker know the

metadata (table name
and column name)

No need to know tables names to exploit SQLi

9
Where does SQL Injection Happens?

10
Structured query Language (SQL) injection
SQL injection example:

Note: The single quotes ‘ ’

are part of the query code
User’s input

Attacker’s
input

Note: -- is the comment operator,

anything after it will be ignored. 11
SQL injection effects and CIA
Sometimes SQL injections may have multiple CIA effects

Classic example of SQL injection is the attack against the login procedure
User’s input

Attacker’s
input

It doesn’t matter if there is a user with a

username ‘foo’ and password ‘bar’?
What could be affected (from the CIA) ? No need for --

The query returns…………

If there is at least one user in the database the attacker will be granted access! 12
The query will return all
SQL injection effects and CIA
Attacker’s
input

What was affected (from the CIA) by the previous SQL injection?

How can it be fixed? Will it prevent future attacks?

13
SQL injection effects and CIA
What is affected (from the CIA) by the below SQL injections?

How can it be fixed?

Very hard to find what was changed and when did it happen (not obvious)
Attacker’s
input

Attacker’s
input

14
SQL injection effects and CIA
What is affected (from the CIA) by the below SQL injection?

How can it be fixed?

Attacker’s
input

very bad and the most damaging of all because

once the data is stolen, it can’t be un-stolen

15
SQL injection effects and CIA
The worst SQL injection attack payload is when the attacker can gain access to

a command-line shell on the database server

This is called owning or rooting the server

It will enable him to do just about anything he wanted to do to the server, and with
administrative privileges (remove admin, add user, command-line shell,…)
He could read, alter, or delete any data on the system (not limited to data in the database only)
The effects would reach server files
The attacker could install backdoors
The attacker can alter system logs to cover his tracks
All services are affected

What could be affected (from the CIA) ?

16
all the CIA at the highest possible levels
SQL injection effects and CIA
Most famous method of gaining shell access through SQL injection vulnerability
is to execute the Microsoft SQL Server stored procedure: xp_cmdshell

It takes a single string argument and then executes that as a command-line call

Example: This command will list the directories and files of the server’s C drive:

Attackers can use SQL injection to run this shell command:

Attacker’s
input

What is the equivalent command in Linux? 17

SQL injection effects and CIA
If you’re running Microsoft SQL Server, it’s strongly recommended that you disable
xp_cmdshell (Newer versions of SQL Server disabled the command by default):

However, attackers can re-enable it if the application database user is running with
administrative privileges (we will learn how to change that): Attacker’s
input

Therefore, it’s better to remove it entirely, although it still could be re-created from
scratch but not all attackers know how.
18
 Blended threat:
is bundles of

Blended Threat Attack malicious programs

that combine the
functionality of
different types of
Combining SQL injection data integrity attack with a persistent XSS. attacks.

Persistent XSS: attackers can directly send their malicious script to a target website which
stores that script in a persistent storage such as the comments section.

In these blended threat attacks, attackers insert HTML <script> tags into the database records.

find a website vulnerable to SQL injection attack and exploit it to insert HTML <script> tags into
the DB records Attacker’s
input

There is no where
clause means all
middlename will be
change to malicious
UPDATE TABLE UPDATE TABLE
19
java script code
Blended Threat Attack

The attacker (intruder) is assuming that the

application is pulling the database records to
display them in a web page.

When the victim views the compromised page,

the application will pull the malicious script from
DB and include it with the page contents.

Then the victim’s browser will silently fetch the

malware from the specified site and execute it.

20
The dangers of detailed errors
How the attackers find out the database table names?

Attackers hope that the web application is set to display detailed error messages

They will try to input a single special SQL character into the input field to produce

a syntax error

Failing closed
Fail securely

21
The dangers of detailed errors

22
The dangers of detailed errors
Attacker’s
Example: enter the following in the filed: ‘ or ; input

23
Blind SQL Injection (Reading
Assignment- P 227)
Attackers can still perform SQL injection attacks even if your system doesn’t display
detailed errors.
Example: Blind SQL injection attack
Ø common table names (users, sales, …..)

Ø ‘ or 1=1 – (no need to know the table name) when you gain access you will get crucial information.

§ Redirect attacker to salepage.html

Ø Try to know the branches in code → AND 1=2— (redirect to homepage.html)

So, not displaying detailed errors is not enough to protect against SQL injection attacks.

We need to address the root of the problem

24
SQL Injection Defenses
The root cause of SQL injection vulnerabilities is that an attacker can specify data that
is interpreted by the database engine as code. Examples of data:
Form field input values

URL querystring parameters

Web service method parameter values.

To prevent SQL injection, never treat user input as code.

SQL injection defenses [2]:

Validate or escape (or both) user input to make sure it doesn’t contain SQL syntax

Prepared Statements (with Parameterized Queries)

Stored Procedures 25
SQL Injection Defenses
1-Validate or 2- escape user input
validation –whitelisting blacklisting, escape – encoding for SQL syntax characters in the input.
Example [3]: use PHP’s method real_escape_string($input) to encode characters that has special
meaning in SQL queries such as \n, \r, \, ‘, “

Without validation or escaping [3]

With escaping [3]

Problems:
It can be bypassed by attackers (check always equals 1=1, ….)
It doesn’t solve the main issue (data can be interpreted as code) 26
SQL Injection Defenses
Prepared Statements (with Parameterized Queries)
It is better to avoid ad-hoc SQL altogether

Ad-hoc SQL - Without Prepared Statements [3]

With Prepared Statements [3]

27
SQL Injection Defenses
Prepared Statements (with Parameterized Queries)

With Prepared Statements [3]

1
2
3 If user enter tom' or '1'=‘1
The DBMS will literally
search for username
Steps: equals to tom' or '1'='1
1 Prepare a parameterized SQL query with empty values as placeholders (with ? for each value).
2 Bind variables to the placeholders by stating each variable, along with its type.
3 Execute query

28
All the previously explained SQLs are called dynamic query
SQL Injection Defenses
Stored procedures

All modern database engines support stored procedures:

This refers to routines containing multiple commands that are stored in the database itself
alongside the data.

Easier to maintain than dynamic SQL built into the application source code

Make changes to stored procedures without the need to recompile the application

Improves the security of the code

29
 SQL Injection Defenses
Stored procedures
Use parameterize SQL queries within the stored procedures, why?
The command EXECUTE will execute any string passed to it. If you use it, with ad-hoc query, you’ll create a stored
procedure that’s vulnerable to SQL injection attacks:

Stored procedure with ad-hoc query (vulnerable to Stored procedure with parameterize query (secure against
SQL injection): SQL injection):

Call the stored procedure from the application code:

Attacker’s
input

30
Setting database permissions
A good way to reduce the potential attack surface of your application is to explicitly
deny it the permissions to perform actions that’s not supposed to be able to do.
trade off between security and functionality.

Single Account Security

Most web applications use a single, highly privileged account to access a DB (Application User:
web_app_user)
1. To avoid multiple connections
2. Otherwise, it is hard to manage
3. Everyone is going to have the same permissions

Admins have full permissions à CRUD

Users won’t need full permissions à they need a small subset
Restricting rights à reduces potential damage
31
Setting database permissions
Single Account Security (cont)
Users rights over the Orders and Products tables

Grant select on Orders to web_app_user

32
Setting database permissions
Single Account Security (cont)
you can add or remove necessary privilege either by: GUI, SQL command
Ø Revoke or grant

System-level permissions example:

This way we reduce the potential damages

Ø An attacker may be able to extract orders data

Ø Can’t change products’ prices or run DoS by deleting tables

33
Setting database permissions
Separate Accounts For Separate Roles
Anonymous users

Authenticated users Admin functions

Administrators
Authenticated users
functions

Anonymous users
functions

34
Setting database permissions
Separate Accounts For Separate Roles (cont)
Single database user à one account with a lot of privileges

Serious potential damage

Create roles for different users. Ex: employees, managers, executives …etc.

35
Finally, to maximize the security of
your database:

37
References
[1] Web Application Security: A Beginner’s Guide

Chapter 7

[2] https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html

[3] https://websitebeaver.com/prepared-statements-in-php-mysqli-to-prevent-sql-injection

38