Lecture 16 SQL Injection and CAPTCHAs

Structure of Web Services

• Most websites need to store and retrieve data
  • Examples: User accounts, comments, prices, etc.
• The HTTP server only handles the HTTP requests, and it needs to have some way of storing and retrieving persisted data

SQL Injection

SQL Databases

1. A SQL database contains multiple tables
2. Each table is like a spreadsheet:
   • One row per entry
   • One column per attribute/field
3. Provide ACID (Atonomicity, Consistency, Isolation, Durability) properties

Basic Syntax of SQL

SELECT is used to select some columns from a table

SELECT [columns] FROM [table]

WHERE can be used to filter out certain rows

SELECT * FROM bots WHERE likes = 'pancakes'

INSERT INTO is used to add rows into a table ; VALUES is used for defining constant rows and columns, usually to be inserted

UPDATE is used to change the values of existing rows in a table (Usually combined with WHERE)

UPDATE [table]
SET [column] = [value]
WHERE [condition]

DELETE FROM is used to delete rows from a table

DELETE FROM [table] WHERE [condition]

CREATE is used to create tables (and sometimes databases)

CREATE TABLE cats ( 
    id INT,
    name VARCHAR(255), 
    likes VARCHAR(255), 
    age INT 
)

DROP is used to delete tables (and sometimes databases)

DROP TABLE [table]

UNION is a combination syntax, which extracts and then integrates the results in a new table

-- is syntax characters for comments

SQL Injection

SQL injection (SQLi): Injecting SQL into queries constructed by the server to cause malicious behavior

Allows the attacker to execute arbitrary SQL on the SQL server

• Leak data
• Add records
• Modify records
• Delete records/tables
• Basically anything that the SQL server can do

Blind SQL Injection

Not all SQL queries are used in a way that is visible to the user

• Visible: Shopping carts, comment threads, list of accounts
• Blind: Password verification, user account creation
• Some SQL injection vulnerabilities don't return much information to attacker

Blind SQL injection: SQL injection attacks where websites provides little to no feedback on results of query

• Many people believe: Attacks become more annoying to construct for there is no return information, but in fact, vulnerabilities are still exploitable :(
• To be specific, Automated SQL injection detection and exploitation tools can construct exploits

Blind SQL Injection Tools

sqlmap: An automated tool to find and exploit SQL injection vulnerabilities on web servers

Defense of Blind SQL Injection:

1. Input sanitization:
   • Difficult to build a good escaper that handles all edge cases
   • Never try by yourself :)
2. Prepared statements:
   • If you pass untrusted input in the arguments (rather than in the SQL statement), it will only be treated as data and never be parsed
   • Must rely on the API to correctly convert the prepared statement into implementation-specific protocol

Command Injection

Both XSS and SQLi are instances of an injection attack, where untrusted data can include stuff that will be treated as a command.

Typically happens when data and commands are sent in the same channel.

// system function in C is a good example for this
system()

Defense

• Input sanitization
• Use safe APIs that separate commands and data
  • In C, use execve instead of system
  • In Python, use subprocess.run instead of os.system
  • In Go, use exec.command()

CAPTCHAs

Background

1. Most websites are constructed for human usage, not robot's
2. Robot access can lead to attacks

CAPTCHA: A challenge that is easy for a human to solve, but hard for a computer to solve

Other Usage

Modern CAPTCHAs have another purpose: Training machine learning algorithms

Issue

Arms race: As computer algorithms get smarter, CAPTCHAs need to get harder

Attack

Outsourcing attack: Pay humans to solve CAPTCHAs for you