SQL Injection

I. SQL Injection Impact in the Real World

a. CardSystems, credit card payment processing

b. Ruined by SQL Injection attack in June 2005

c. 263.000 credit card #s stolen from its DB

d. #s stored unencrypted, 40 milion exposed

II. Attack Scenario

a. Pizza Site Reviewing Orders

HTTP Request: https://www.deliver-me-pizza.com/show_orders?month=10

b. Η εφαρμογή παράγει ένα SQL query από την παράμετρο

sql_query = “SELECT pizza, toppings, quantity, order_day “ + “FROM orders “ + “WHERE userid=” + session.getCurrentUserId() + “ “ + “AND order_month” + request.getParameter(“month”);

SELECT pizza, toppings, quantity, order_day FROM orders WHERE userid=4123 AND order_month=10

Attack: inputs 0 OR 1=1

Goes to encoded URL: (space -> %20, =->%3D)

https://www.deliver-me-pizza.com/show_orders?month=0%20OR%201%3D1

c. Malicious Query

SELECT pizza, toppings, quantity, order_day FROM orders WHERE userid=4123 AND order_month=0 OR 1=1

Όπου η συνθήκη είναι πάντα true.

d. More damaging attack

O hacker θέτει month = 0 AND 1=0

UNION SELECT cardholder, number, exp_month, exp_year FROM creditcards

Το query τώρα είναι:

SELECT pizza, toppings, quantity, order_day FROM orders WHERE userid=4123 AND order_month=0 AND 1=0

UNION SELECT cardholder, number, exp_month, exp_year FROM creditcards

O hacker μπορεί να συνδιάσει 2 queries: empty table (where fails), credit card #s of all users

e. Ever worse, attacker sets

month=0;

DROP TABLE creditcards;

Then DB executes:

SELECT pizza, toppings, quantity, order_day FROM orders WHERE userid=4123 AND order_month=0;

DROP TABLE creditcards;

Removes creditcards from schema, future orders fail (DoS)

III. Solutions

a. Why Blacklisting does not work

 - Eliminating quotes enough (blacklist them)

Sql_query = “SELECT pizza, toppings, quantity, order_day” + “FROM orders “ + “WHERE userid=” + session.getCurrentUserId() + “ “ + “AND topping LIKE ‘kill_quotes(request.getParameter (“topping”)) + “%’”;

 - kill_quotes (Java) remove single quotes

String kill_quotes(String str) {

StringBuffer result = new StringBuffer(str.length());

For (int I = 0; i < str.length(); i++) {

If (str.charAt(i) != ‘\’ )

result.append(str.charAt(i));

       }

return result.toString();

}

b. Pitfalls of Blacklisting

 - Filter quotes, semicolons, whitespace, and …?

• Could always miss a dangerous character
• Blacklisting not comprehensive solution
• kill_quotes() can’t prevent attacks against numeric parameters
• May conflict with functional requirements (e.g O’Brien)

c. Whitelisting-Based Input Validation

Whitelisting – only allow input within well-defined set of safe values

d. Escaping

 - Could escape quotes instead of blacklisting

 - Like kill_quotes only works for string inputs

 - Numeric parameters could still be vulnerable

e. Second Order SQL Injection

f. Prepared statements & Bind Variables

 - Metachars provide distinction between data & control in queries

• Most attacks: data interpreted as control
• Alters the semantics of a query

 - Bind variables: ? placeholders guaranteed to be data

 - Prepared statements allow creation of static queries with bind varialbes

• Preserves the structure of intended query
• Parameters not involved in query parsing/compiling

g. Java Prepared Statements

PreparedStatement ps = db.prepareStatement(“SELECT pizza, toppings, quantity, order_day” + “FROM orders WHERE userid=? AND order_month=?”);

Ps.setInt(1, session.getCurrentUserId());

Ps.setInt(2, Integer.parseInt(request.getParameters(“month”)));

ResultSet res = ps.executeQuery()

Query parsed without parameters, bind variables are type: input mstu be of expected type.

h. PHP Prepared Statements

$ps = @db->prepare(‘SELECT pizza, toppings, quantity, order_day ‘. ‘FROM orders WHERE userid=? AND order_month=?’);

$ps->execute(array($current_user_id, $month));

No explicit typing of parameters like in Java, have separate module for DB access.

i. SQL Stored Procedures

Stored procedure: sequence of SQL statements executing on specified inputs.

j. Mitigating the Impact of SQL Injection Attacks

 - Prevent Schema & Information Leaks

 - Limit Privileges 

 - Encrypt Sensitive Data stored in Database

 - Harden DB Server and Host O/S

k. Prevent Schema & Information Leaks

 - Knowing database schema makes attacker’s job easier

 - Blind SQL Injection: attacker attempts to interrogate system to figure out schema

 - Prevent leakages of schema information

 - Don’t display detailed error messages and stack traces to external users

l. Limiting Privileges

 - Apply Principle of Least Privilige

• Read access, tables/views user can query
• Commands

 - No more privileges than typical user needs

m. Encrypting Sensitive Data

 - Encrypt data stored in the database

 - Key management precautions: don’t store key in DB, attacker just SQL injects again to get it

 - Some databases allow automatic encryption but these still return plaintext queries

n. Hardening DB Server and Host O/S

 - Dangerous functions could be on by default

 - Microsoft SQL Server allows users to open inbound/outbound sockets, attacker could steal data, upload binaries, port scan victim’s network