Introduction to SQL Injection Professor Larry Heimann Web Application - PowerPoint PPT Presentation

Introduction to SQL Injection Professor Larry Heimann Web Application Security Information Systems

Lab 4 Review • Fastest crackers • Jake Correa • Achal Channarasappa • Swathi Anand • Peter Podniesinski • Chanamon Ratanalert • Review of CSRF & path traversal lab

Onto SQL injection (SQLi) …

A couple of facts regarding SQLi • SQL Injection was the leading Web application risk of 2013 and ranks as one of the most common software vulnerabilities in survey after survey. • 97 percent of data breaches worldwide are due to SQL injection. • SQL injection has been responsible for 83% of all successful hacking-related data breaches from 2005-2013. • Web applications are attacked using SQL injection an average of 71 times every hour. • SQLi attacks become automated via the use of botnets. Mass website infection incidents include 500,000 reported in 2008, 210,000 in 2009, 500,000 in 2010, and 380,000 in 2011.

A couple of facts regarding SQLi • A sample of major reported breaches caused by SQL injection: • Card Systems Solutions (2005, 40M credit cards compromised) • TJX (2006, 94M cards) • Heartland Payment Systems (2008, 134M cards) • RockYou (2009, 32M users) • Sony (2011, 77M users)

SQL Injection in a nutshell Getting some part of a website to accept as input a SQL statements or some fragment of SQL in order to corrupt the intended SQL query and generate a different set of results.

Simple SQL injection example <?php require 'database.php'; $res = $mysqli->query("SELECT id FROM users WHERE username='". $_POST[ 'username' ] ."' AND password='". $_POST[ 'password' ] ."'"); if( $res->num_rows==1 ){ $row = $res->fetch_assoc(); $_SESSION[ 'user_id' ] = $row["id"]; }else{ echo "Login failed."; exit ; } ?>

Abusing this code... Ed Gruberman logs in with no password, just the username: $_POST[ 'username' ] = gruberman’; -- But why would he ever want to add the -- at the end of his username?

Abusing this code... <?php require 'database.php'; $res = $mysqli->query("SELECT id FROM users WHERE username='". $_POST[ 'username' ] ."' AND password='". $_POST[ 'password' ] ."'"); if( $res->num_rows==1 ){ $row = $res->fetch_assoc(); $_SESSION[ 'user_id' ] = $row["id"]; }else{ echo "Login failed."; exit ; } ?>

Abusing this code... <?php require 'database.php'; $res = $mysqli->query("SELECT id FROM users WHERE username='gruberman’; -- ' AND password=''"); if( $res->num_rows==1 ){ $row = $res->fetch_assoc(); $_SESSION[ 'user_id' ] = $row["id"]; }else{ echo "Login failed."; exit ; } ?>

Abusing this code... Ed Gruberman could also log in with the following password: $_POST[ 'username' ] = gruberman $_POST[ 'password' ] = ’ OR 1=1; -- What would this accomplish?

Abusing this code... <?php require 'database.php'; $res = $mysqli->query("SELECT id FROM users WHERE username='gruberman' AND password='’ OR 1=1; -- "); if( $res->num_rows==1 ){ $row = $res->fetch_assoc(); $_SESSION[ 'user_id' ] = $row["id"]; }else{ echo "Login failed."; exit ; } ?>

Where can I inject SQL? • In a URL that accepts a parameter • In a search box or advanced search functionality • In any text input element • In radio buttons, checkboxes, drop-down lists, etc. • In hidden fields In short, just about anywhere the app might accept input

How do I know if the app is vulnerable to injection? J UST T RY T O B REAK I T . Send the app a problematic string or character and see how it responds. � � Some SQL injection characters to be aware of:

What else can I do with SQL injection? “Exploits of a Mom” -- http://xkcd.com/327/

Some thing that can be done with SQL injection • DROP or CREATE TABLES • DROP or CREATE DATABASES • Bypass authentication or authorization • Insert records into a table (include XSS attacks) • Delete records from a table • List (dump) all records from a table • Gain other key information about the database

To exploit SQL, you need to know SQL ... • Functions in PostgreSQL that are informative • Functions in MySQL that are informative • Using the UNION and INTERSECTION clauses • Using GROUP_CONCAT (MySQL) or ARRAY_AGG (postgres)

Blind SQL injection • Sometimes an app may be vulnerable to SQL injection, but the results aren’t visible on the page. • Database can be probed by asking a series of TRUE/FALSE questions • Database can also be probed by pausing for a specified amount of time, then returning the results, indicating successful SQL query executing. • e.g., an attacker conducts timing attack by enumerating each letter of the desired piece of data using the following logic: If the first letter of the first database's name is an 'A', wait for 10 seconds. If the first letter of the first database's name is an 'B', wait for 10 seconds. etc.

Blind SQL injection example Starting with a URL: http://target.site.com/items.php?id=2 we can assume it sends the following query to the database: SELECT title, description, body FROM items WHERE ID = 2 The attacker may then try to inject a query that returns 'false': http://target.site.com/items.php?id=2 and 1=2 Now the SQL query should looks like this: SELECT title, description, body FROM items WHERE ID = 2 and 1=2 If the web application is vulnerable to SQL Injection, then it probably will not return anything. To make sure, the attacker will inject a query that will return 'true': http://target.site.com/items.php?id=2 and 1=1 If the content of the page that returns 'true' is di ff erent than that of the page that returns 'false', then the attacker is able to distinguish when the executed query returns true or false. Once this has been verified, the only limitations are privileges set up by the database administrator, di ff erent SQL syntax, and the attacker's imagination.

Blind SQL injection timing example MySQL gives us the following function: SELECT IF(expression, true, false) There are also some time-taking operations that are easily measured. For example: BENCHMARK(5000000,ENCODE('my_str','my_key_str')) will execute the ENCODE function 5,000,000 times, which will take a small but noticeable time Using a time-taking operation will delay server responses if the expression is true. The important thing is, from the attacker's point of view, to specify a high-enough number of BENCHMARK() function repetitions to a ff ect the database response time in a noticeable way. 1 UNION SELECT IF(SUBSTRING(user_password,1,1) = CHAR(50), BENCHMARK(5000000,ENCODE('my_str','my_key_str')),null) FROM users WHERE user_id = 1; If the database response took a long time, we may expect that the first user password character with user_id = 1 is character '2'.

Stopping SQL injection • Escaping inputs • Validating inputs • Using parameterized queries

Stopping SQL injection • Create database user with limited abilities • Limiting the user’s ability to access databases or execute admin privileges • Limiting the user’s ability to access certain tables • Limiting the user’s ability to execute certain SQL clauses • Limiting the user’s ability to access certain fields in approved tables • Remove the ‘test’ database that comes by default • Limit the ability for database to read/write to files