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
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
