Breaking Databases via SQLi attacks Azqa Nadeem PhD Student @ - - PowerPoint PPT Presentation

▶
breaking databases
SMART_READER_LITE
LIVE PREVIEW

Breaking Databases via SQLi attacks Azqa Nadeem PhD Student @ - - PowerPoint PPT Presentation

Dec 24, 2023 379 likes •1.37k views

Breaking Databases via SQLi attacks Azqa Nadeem PhD Student @ Cyber Security Group The Cyber Security lecture series 1 About Cyber Security lecture series The Cyber Security lecture series 2 About Cyber Security lecture series A hot

slide-1
SLIDE 1

1

Breaking Databases

via SQLi attacks

Azqa Nadeem

PhD Student @ Cyber Security Group

The Cyber Security lecture series

slide-2
SLIDE 2

2

About Cyber Security lecture series

The Cyber Security lecture series

slide-3
SLIDE 3

3

About Cyber Security lecture series

  • A hot topic, a buzz term

The Cyber Security lecture series

slide-4
SLIDE 4

4

About Cyber Security lecture series

  • A hot topic, a buzz term
  • Introducing the Cyber Security lecture series

– Cyber security topics in existing courses – First of the (hopefully) many to come

The Cyber Security lecture series

slide-5
SLIDE 5

5

About Cyber Security lecture series

  • A hot topic, a buzz term
  • Introducing the Cyber Security lecture series

– Cyber security topics in existing courses – First of the (hopefully) many to come

  • Announcements

– Assignment 3 – Exam questions – Feedback form for the course

The Cyber Security lecture series

slide-6
SLIDE 6

6

Why would anyone ever hack a database?

The Cyber Security lecture series

slide-7
SLIDE 7

7

… In the news

https://www.forbes.com/sites/davidvolodzko/2018/12/04/marriott-breach-exposes-far-more-than- just-data/#1f0d3c276297

slide-8
SLIDE 8

8

… In the news

https://www.nbcnews.com/business/consumer/quora-hack-breach-crowdsourced-question-answer- site-exposes-100-million-n943496

slide-9
SLIDE 9

9

… In the news

https://steemit.com/bitcoin/@hacker0/how-i-hacked-hundreds-of-bitcoins-ama

slide-10
SLIDE 10

10

… In the news

https://informationisbeautiful.net/visualizations/worlds-biggest-data-breaches-hacks/

slide-11
SLIDE 11

11

What went wrong?

slide-12
SLIDE 12

12

What went wrong?

SQL Injection attack!

slide-13
SLIDE 13

13

SQL Injection

  • SQL Injection (SQLi) refers to an injection attack wherein

an attacker can execute malicious SQL statements that control a web application’s database server (also known as RDBMS).

https://www.acunetix.com/websitesecurity/sql-injection/

slide-14
SLIDE 14

14

SQL Injection

  • SQL Injection (SQLi) refers to an injection attack wherein

an attacker can execute malicious SQL statements that control a web application’s database server (also known as RDBMS).

  • Look out if you have:

– Web application – SQL-based database – User-controlled query parameter

https://www.acunetix.com/websitesecurity/sql-injection/

slide-15
SLIDE 15

15

Next up…

slide-16
SLIDE 16

16

Next up…

  • Quick recap of:

– Web application infrastructure – Who is to blame? – What can attackers do?

slide-17
SLIDE 17

17

Next up…

  • Quick recap of:

– Web application infrastructure – Who is to blame? – What can attackers do?

  • Injecting SQL queries ← Hands-on!
slide-18
SLIDE 18

18

Next up…

  • Quick recap of:

– Web application infrastructure – Who is to blame? – What can attackers do?

  • Injecting SQL queries ← Hands-on!
  • What causes SQLi?
slide-19
SLIDE 19

19

Next up…

  • Quick recap of:

– Web application infrastructure – Who is to blame? – What can attackers do?

  • Injecting SQL queries ← Hands-on!
  • What causes SQLi?
  • Best practices to avoid SQLi

– Input sanitization – Escaping input – Prepared statements

slide-20
SLIDE 20

20

How does a typical web app work?

slide-21
SLIDE 21

21

How does a typical web app work?

webshop.abc.xy

slide-22
SLIDE 22

22

How does a typical web app work?

webshop.abc.xy

slide-23
SLIDE 23

23

How does a typical web app work?

webshop.abc.xy

slide-24
SLIDE 24

24

How does a typical web app work?

SQL database webshop.abc.xy

slide-25
SLIDE 25

25

How does a typical web app work?

SQL database webshop.abc.xy

slide-26
SLIDE 26

26

How does a typical web app work?

SQL database webshop.abc.xy

slide-27
SLIDE 27

27

How does a typical web app work?

SQL database webshop.abc.xy

slide-28
SLIDE 28

28

How does a typical web app work?

SQL database webshop.abc.xy

slide-29
SLIDE 29

29

What can attackers do?

slide-30
SLIDE 30

30

What can attackers do?

  • Data Manipulation Language
slide-31
SLIDE 31

31

What can attackers do?

  • Data Manipulation Language

– INSERT INTO users (username, password) VALUES (‘attacker’, ‘youvebeenhacked’)

C

slide-32
SLIDE 32

32

What can attackers do?

  • Data Manipulation Language

– INSERT INTO users (username, password) VALUES (‘attacker’, ‘youvebeenhacked’) – SELECT * FROM users WHERE userType=‘admin’

C R

slide-33
SLIDE 33

33

What can attackers do?

  • Data Manipulation Language

– INSERT INTO users (username, password) VALUES (‘attacker’, ‘youvebeenhacked’) – SELECT * FROM users WHERE userType=‘admin’ – UPDATE users SET password=‘youvebeenhacked’ ;

C R U

slide-34
SLIDE 34

34

What can attackers do?

  • Data Manipulation Language

– INSERT INTO users (username, password) VALUES (‘attacker’, ‘youvebeenhacked’) – SELECT * FROM users WHERE userType=‘admin’ – UPDATE users SET password=‘youvebeenhacked’ ; – DELETE FROM users;

C R U D

slide-35
SLIDE 35

35

What can attackers do?

  • Data Manipulation Language

– INSERT INTO users (username, password) VALUES (‘attacker’, ‘youvebeenhacked’) – SELECT * FROM users WHERE userType=‘admin’ – UPDATE users SET password=‘youvebeenhacked’ ; – DELETE FROM users;

  • And much, much more…

– Root access, Denial of Service attack, etc.

C R U D

slide-36
SLIDE 36

36

Scenario…

webshop.abc.xy

slide-37
SLIDE 37

37

Scenario…

webshop.abc.xy

Search for an item

Keyword

slide-38
SLIDE 38

38

Scenario…

SQL database webshop.abc.xy

Search for an item

Keyword

slide-39
SLIDE 39

39

Scenario…

SQL database webshop.abc.xy

Search for an item

Keyword

itemName itemPicture Shirt X Pen X Car X

Inventory

slide-40
SLIDE 40

40

Scenario…

SQL database webshop.abc.xy

Search for an item

Keyword

itemName itemPicture Shirt X Pen X Car X

Inventory

slide-41
SLIDE 41

41

Scenario…

SQL database webshop.abc.xy

Search for an item

car

Keyword

itemName itemPicture Shirt X Pen X Car X

Inventory

car

slide-42
SLIDE 42

42

Scenario…

SQL database webshop.abc.xy

Search for an item

car

Keyword

itemName itemPicture Shirt X Pen X Car X

Inventory

car

slide-43
SLIDE 43

43

Search for an item

??

Keyword

Task1: How to list all items?

SQL database webshop.abc.xy

itemName itemPicture Shirt X Pen X Car X

??

Inventory

slide-44
SLIDE 44

44

Task1: How to list all items?

Search for an item

car’ OR 1 #

Keyword

SQL database webshop.abc.xy

itemName itemPicture Shirt X Pen X Car X

car’ OR 1 #

Inventory

slide-45
SLIDE 45

45

Task1: How to list all items?

Search for an item

car’ OR 1 #

Keyword

SQL database webshop.abc.xy

itemName itemPicture Shirt X Pen X Car X

car’ OR 1 #

Inventory

slide-46
SLIDE 46

46

Task1: How to list all items?

Search for an item

car’ OR 1 #

Keyword

SQL database webshop.abc.xy

itemName itemPicture Shirt X Pen X Car X

car’ OR 1 #

Inventory

→ Tautology

slide-47
SLIDE 47

47

SQL database webshop.abc.xy

Log in Form

?? ??

Username Password

Go

?? ??

The login scenario…

slide-48
SLIDE 48

48

Another Tautology-based SQLi

SQL database webshop.abc.xy

Log in Form Blah’ OR 1#

Blah

Username Password

Go

Blah Blah’ OR 1 #

slide-49
SLIDE 49

49

Another Tautology-based SQLi

SQL database webshop.abc.xy

Log in Form Blah’ OR 1#

Blah

Username Password

Go

Blah Blah’ OR 1 #

slide-50
SLIDE 50

50

Running multiple queries

slide-51
SLIDE 51

51

Running multiple queries

  • Useful keywords:
slide-52
SLIDE 52

52

Running multiple queries

  • Useful keywords:

– JOIN (Append horizontally)

slide-53
SLIDE 53

53

Running multiple queries

  • Useful keywords:

– JOIN (Append horizontally) – UNION (Append vertically)

slide-54
SLIDE 54

54

Running multiple queries

  • Useful keywords:

– JOIN (Append horizontally) – UNION (Append vertically)

slide-55
SLIDE 55

55

Running multiple queries

  • Useful keywords:

– JOIN (Append horizontally) – UNION (Append vertically)

  • Fluffy

Bunny

slide-56
SLIDE 56

56

Running multiple queries

  • Useful keywords:

– JOIN (Append horizontally) – UNION (Append vertically)

  • Fluffy

Bunny

slide-57
SLIDE 57

57

Running multiple queries

  • Useful keywords:

– JOIN (Append horizontally) – UNION (Append vertically)

  • Fluffy

Bunny Fluffy Bunny 1 2

slide-58
SLIDE 58

58

Task 2: How to dump user data?

SQL database webshop.abc.xy

Search for an item

??

Keyword

slide-59
SLIDE 59

59

Task 2: How to dump user data?

SQL database webshop.abc.xy

Search for an item

??

Keyword

??

slide-60
SLIDE 60

60

Task 2: How to dump user data?

username password fluffyBunny cArR0T admin admin123

SQL database webshop.abc.xy

Search for an item

??

Keyword

??

itemName itemPicture Shirt X Pen X Car X

Inventory Users

slide-61
SLIDE 61

61

Task 2: How to dump user data?

SQL database webshop.abc.xy

Search for an item

car’ UNION SELECT password FROM users# Keyword

car’ UNION SELECT password FROM users# itemName itemPicture Shirt X Pen X Car X

Inventory Users

username password fluffyBunny cArR0T admin admin123

slide-62
SLIDE 62

62

Task 2: How to dump user data?

SQL database webshop.abc.xy

Search for an item

car’ UNION SELECT password FROM users# Keyword

car’ UNION SELECT password FROM users# itemName itemPicture Shirt X Pen X Car X

Inventory Users

username password fluffyBunny cArR0T admin admin123

slide-63
SLIDE 63

63

https://www.explainxkcd.com/wiki/index.php/327:_Exploits_of_a_Mom

slide-64
SLIDE 64

64

https://www.explainxkcd.com/wiki/index.php/327:_Exploits_of_a_Mom

Piggy-backed query

slide-65
SLIDE 65

65

Why is it happening?

  • Mixing of code and data
slide-66
SLIDE 66

66

Why is it happening?

  • Mixing of code and data

SELECT profile FROM users WHERE uname= ‘Blah‘ AND pwd= ‘Blah‘

slide-67
SLIDE 67

67

Why is it happening?

  • Mixing of code and data

SELECT profile FROM users WHERE uname=‘Blah‘ OR 1=1 # ‘ AND pwd= ‘Blah‘ SELECT profile FROM users WHERE uname= ‘Blah‘ AND pwd= ‘Blah‘

slide-68
SLIDE 68

68

Why is it happening?

  • Mixing of code and data

SELECT profile FROM users WHERE uname= ‘Blah‘ AND pwd= ‘Blah‘ SELECT profile FROM users WHERE uname=‘Blah‘ OR 1=1 # ‘ AND pwd= ‘Blah‘

slide-69
SLIDE 69

69

SQLi Avoidance

  • Input sanitization

– Clean the input in order to use it

slide-70
SLIDE 70

70

SQLi Avoidance

  • Input sanitization

– Clean the input in order to use it

slide-71
SLIDE 71

71

SQLi Avoidance

  • Input sanitization

– Clean the input in order to use it

  • Problem:

– Not all scenarios are known

slide-72
SLIDE 72

72

SQLi Avoidance

  • Escaping the input

– To avoid data being mistaken as code

slide-73
SLIDE 73

73

SQLi Avoidance

  • Escaping the input

– To avoid data being mistaken as code – Input:

slide-74
SLIDE 74

74

SQLi Avoidance

  • Escaping the input

– To avoid data being mistaken as code – Input: – Must be processed as:

slide-75
SLIDE 75

75

SQLi Avoidance

  • Escaping the input

– To avoid data being mistaken as code – Input: – Must be processed as:

  • Problem:

– Possibly a 2nd Order SQLi attack

  • Effect not seen immediately
slide-76
SLIDE 76

76

SQLi Avoidance

  • 2nd order SQLi

Users

username password fluffyBunny cArR0T admin admin123

slide-77
SLIDE 77

77

SQLi Avoidance

  • 2nd order SQLi

Robert’; Drop table users;# Blah

Username Password

Register

Users

username password fluffyBunny cArR0T admin admin123

1)

slide-78
SLIDE 78

78

SQLi Avoidance

  • 2nd order SQLi

Robert’; Drop table users;# Blah

Username Password

Register

Users

username password fluffyBunny cArR0T admin admin123

1)

slide-79
SLIDE 79

79

SQLi Avoidance

  • 2nd order SQLi

Robert’; Drop table users;# Blah

Username Password

Register

Users

username password fluffyBunny cArR0T admin admin123 Robert’; Drop table users;# Blah username password fluffyBunny cArR0T admin admin123

1)

slide-80
SLIDE 80

80

SQLi Avoidance

  • 2nd order SQLi

Robert’; Drop table users;# Blah

Username Password

Register

Users

username password fluffyBunny cArR0T admin admin123 Robert’; Drop table users;# Blah username password fluffyBunny cArR0T admin admin123

1) 2)

Robert’; Drop table users;# Blah

Username Password

Login

slide-81
SLIDE 81

81

SQLi Avoidance

  • 2nd order SQLi

Robert’; Drop table users;# Blah

Username Password

Register

Users

username password fluffyBunny cArR0T admin admin123 Robert’; Drop table users;# Blah username password fluffyBunny cArR0T admin admin123

1) 2)

Robert’; Drop table users;# Blah

Username Password

Login

slide-82
SLIDE 82

82

SQLi Avoidance

  • 2nd order SQLi

Robert’; Drop table users;# Blah

Username Password

Register

Users

username password fluffyBunny cArR0T admin admin123 Robert’; Drop table users;# Blah username password fluffyBunny cArR0T admin admin123

Welcome, Robert’; Drop table users;# 1) 2)

Robert’; Drop table users;# Blah

Username Password

Login

3)

slide-83
SLIDE 83

83

SQLi Avoidance

  • 2nd order SQLi

Robert’; Drop table users;# Blah

Username Password

Register

Users

username password fluffyBunny cArR0T admin admin123 Robert’; Drop table users;# Blah username password fluffyBunny cArR0T admin admin123

Welcome, Robert’; Drop table users;# 1) 2)

Robert’; Drop table users;# Blah

Username Password

Login

Blah2 Blah2

Password Confirm

Update password

3)

slide-84
SLIDE 84

84

SQLi Avoidance

  • 2nd order SQLi

Robert’; Drop table users;# Blah

Username Password

Register

Users

username password fluffyBunny cArR0T admin admin123 Robert’; Drop table users;# Blah username password fluffyBunny cArR0T admin admin123

Welcome, Robert’; Drop table users;# 1) 2)

Robert’; Drop table users;# Blah

Username Password

Login

Blah2 Blah2

Password Confirm

Update password

3)

slide-85
SLIDE 85

85

SQLi Avoidance

  • 2nd order SQLi

Robert’; Drop table users;# Blah

Username Password

Register

Users

username password fluffyBunny cArR0T admin admin123 Robert’; Drop table users;# Blah username password fluffyBunny cArR0T admin admin123

Welcome, Robert’; Drop table users;# 1) 2)

Robert’; Drop table users;# Blah

Username Password

Login

Blah2 Blah2

Password Confirm

Update password

3)

slide-86
SLIDE 86

86

SQLi Avoidance

  • Prepared statements

– Separation of concerns

Code Data Query

slide-87
SLIDE 87

87

SQLi Avoidance

  • Prepared statements

– Separation of concerns – Pre-compile legitimate query – Add placeholders for data

Code Data Query

slide-88
SLIDE 88

88

SQLi Avoidance

  • Prepared statements

– Separation of concerns – Pre-compile legitimate query – Add placeholders for data

Code Data Query

slide-89
SLIDE 89

89

SQLi Avoidance

  • Prepared statements

– Separation of concerns – Pre-compile legitimate query – Add placeholders for data

Code Data Query

slide-90
SLIDE 90

90

Summary

slide-91
SLIDE 91

91

Summary

  • Executing SQL code on a data is called an SQL Injection

attack

slide-92
SLIDE 92

92

Summary

  • Executing SQL code on a data is called an SQL Injection

attack

– Tautology-based query – Union-based query – Piggy-backed query – 2nd Order attack

slide-93
SLIDE 93

93

Summary

  • Executing SQL code on a data is called an SQL Injection

attack

– Tautology-based query – Union-based query – Piggy-backed query – 2nd Order attack

  • SQLi is caused by mixing of code and data
slide-94
SLIDE 94

94

Summary

  • Executing SQL code on a data is called an SQL Injection

attack

– Tautology-based query – Union-based query – Piggy-backed query – 2nd Order attack

  • SQLi is caused by mixing of code and data
  • Prepared statements are the most useful in avoiding SQLi
slide-95
SLIDE 95

95

Summary

  • Executing SQL code on a data is called an SQL Injection

attack

– Tautology-based query – Union-based query – Piggy-backed query – 2nd Order attack

  • SQLi is caused by mixing of code and data
  • Prepared statements are the most useful in avoiding SQLi
  • However, user input must always be sanitized
slide-96
SLIDE 96

96

Additional material

  • https://codecurmudgeon.com/wp/sql-injection-hall-of-shame/
  • https://www.youtube.com/watch?v=ciNHn38EyRc
  • https://www.csoonline.com/article/2130877/data-breach/the-

biggest-data-breaches-of-the-21st-century.html

  • A Classification of SQL Injection Attacks and Countermeasures:

https://www.cc.gatech.edu/fac/Alex.Orso/papers/halfond.viegas.o rso.ISSSE06.pdf

  • Try it yourself:

– https://www.codingame.com/playgrounds/154/sql-injection- demo/sql-injection – http://leettime.net/sqlninja.com/ – https://www.veracode.com/security/sql-injection

slide-97
SLIDE 97

97

Time for questions