php my sql security 2007
TRANSCRIPT
MySQL UC 2007 2
Why is web application security important?
MySQL UC 2007 3
Overview
• What do I mean by security?– Specifically security of web apps– Not how to secure your install– Not the security of MySQL or PHP– Programmers’ perspective
• Why is this important?• Basic principles• Common attacks and how to defend against them• Big picture prevention• Resources
MySQL UC 2007 4
Rationale
• Before the last couple of years nobody thought about this stuff, like many web related security issues. Lots of programmers drift along blissfully unaware of what can go wrong until something bad happens
• Some well known recent problems with popular web apps (large install base) and well known sites
• This talk:– Learn the basics (mostly applicable regardless of implementation
language)– Motivate you to learn more– Far, far too much to cover in the time. This is just an introduction.
MySQL UC 2007 5
Who am I, and why should you listen to me?
• Principal at OmniTI• Used MySQL and PHP since last century• More than a decade of web development experience in a
range of languages, using a range of databases• Long term developer and architect• What we will cover is not guru level knowledge, but
information that every web developer working with MySQL and PHP should know like the back of their hand.
MySQL UC 2007 6
MySQL Security Basics
MySQL UC 2007 7
Basic principles
• Configure MySQL securely• Understand the privilege system, and use it appropriately • Use encryption when needed• Don’t trust user data (more on this later)
MySQL UC 2007 8
Secure your configuration
• Simple principles:– Don’t run mysqld as (Unix) root. Run it as a user created
specifically for this purpose, e.g. mysql. Don’t use this account for anything else. (Note that the MySQL root user has nothing to do with Unix users so this doesn’t affect MySQL internally at all.)
– Set permissions on the database directories so that only your mysqld user (e.g. mysql) can access them.
– Disable symlinks to tables with --skip-symbolic-links.– Disallow access to port 3306 (or whatever port you have MySQL
running on) except from trusted hosts
MySQL UC 2007 9
Accounts and Privileges
• All MySQL accounts need a password, especially root. (Don’t forget anonymous users, either.)
• Grant users the minimum level of privilege required to do their job. (Principle of Least Privilege)
• Some privileges require special attention:– Only the root user should have access to the mysql database,
which contains privilege information– Keep FILE, PROCESS, and SUPER for administrative users. FILE
enables file creation, PROCESS allows you to see executing processes (including passwords in plaintext), and SUPER can be allowed to e.g. terminate client connections.
• Avoid wildcards in hostnames in the host table.• Use IPs instead of hostnames in the host table if you don’t
trust your DNS
MySQL UC 2007 10
Using encryption
• Don’t store application passwords in plaintext in the database. (Use one way hashing)
• Require database connections to be via ssh or tunneled through it
• Avoid old MySQL passwords (pre 4.1). (Disable with --secure-auth, and avoid use of --old-passwords.)
MySQL UC 2007 11
PHP Security Basics
MySQL UC 2007 12
Basic principles
• Consider illegitimate uses of your application
• Educate yourself
• If nothing else, filter all external data
(From the PHP Security Guide at http://phpsec.org/projects/guide/)
MySQL UC 2007 13
External Data
• External data is not to be trusted.• What’s external data?
– Anything from a form– Anything from $_GET, $_POST, $_REQUEST– Cookies– Some server variables (e.g. $_SERVER['SERVER_NAME'])– Database query results– Web services data– Files
• The basic principle is to filter input and escape output• Filter input using whitelisting where possible• Escape output according to where it’s going.
MySQL UC 2007 14
Attacks
MySQL UC 2007 15
Attacks
• Let’s consider some common problems:– SQL/Command/code Injection– XSS (Cross Site Scripting)– Session fixation– Session hijacking– Cross site request forgeries (CSRF)
MySQL UC 2007 16
SQL Injection
• Enter SQL in e.g. form fields in such a way that it is executed on the web app database.
• A variation is command injection, where user data is passed through system() or exec().
• It’s basically the same attack.• (Code injection is also a variation, but we’ll talk about that
separately)
MySQL UC 2007 17
Example
$username = $_POST['username'];
$password = $_POST['password'];
$query = "select * from auth where username = '".$username
."' and password = sha1('".$password."')";
echo $query;
$db = new mysqli('localhost', 'demo',
'secret', 'security_demo');
$result = $db->query($query);
if ($result && $result->num_rows) {
echo "<br />Logged in successfully";
} else {
echo "<br />Login failed";
}
MySQL UC 2007 18
Preventing SQL injection
• Options:– Filter data using mysql[i]_real_escape_string() – Manually check each piece of data is the right type– Use prepared statements and bind variables
• I recommend the use of prepared statements.– You don’t have to worry about filtering data– Used as a coding standard, helps to limit problems caused by
novice or naïve developers within your organization.– Gives you other advantages: where queries will be performed
multiple times, allows reuse of query plan; uses binary protocol– Tip: use PDO with prepared statement emulation turned on to
leverage MySQL’s query cache• Note that prepared statements don’t protect you against
everything (column/table name injection)
MySQL UC 2007 19
Prepared statementsmysqli
$query = 'select name, district from city where countrycode=?';if ($stmt = $db->prepare($query) ){ $countrycode = 'AUS'; $stmt->bind_param("s", $countrycode); $stmt->execute(); $stmt->bind_result($name, $district); while ($stmt->fetch()) { echo $name.', '.$district; echo '<br />'; } $stmt->close(); } $db->close();
MySQL UC 2007 20
Prepared statementsPDO
try {
$db = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
echo 'Connect failed:'. $e->getMessage();
}
$stmt = $db->prepare(“insert into customers (name, address) values (:name, :address)");
$stmt->bindParam(‘:name’, $name);
$stmt->bindParam(‘:address’, $address);
$stmt->execute();
MySQL UC 2007 21
XSS
• XSS = Cross Site Scripting
• An attack by a malicious user where they enter some data to your web application that includes a client side script (generally JavaScript).
• If you output this data to a web page without filtering it, this script will be executed.
MySQL UC 2007 22
Example – part 1
<?php
if (file_exists('comments')) {
$comments = file_get_contents('comments');
} else {
$comments = '';
}
if (isset($_POST['comment'])) {
$comments .= '<br />' . $_POST['comment'];
file_put_contents('comments', $comments);
}
?>
MySQL UC 2007 23
Example – part 2
<form action='xss.php' method='POST'>
Enter your comments here: <br />
<textarea name='comment'></textarea> <br />
<input type='submit' value='Post comment' />
</form><hr /><br />
<?php echo $comments; ?>
MySQL UC 2007 24
So what?
• So it’s JavaScript (or even plain old HTML), I hear you saying, so what? What can I do with that?
• Heaps of badness:– Annoying popups – Meta-refresh– Dubious forms– Steal cookies (which can then set up a session attack)– AJAX (XMLHttpRequest)
MySQL UC 2007 25
How do I prevent this?
• Basically: Filter output to the browser through htmlentities().• Not that basic• See the XSS Cheatsheet:
http://ha.ckers.org/xss.html
MySQL UC 2007 26
Session fixation
• Session security works on the basis that a PHPSESSID is hard to guess. If you don’t have to guess it life is much easier.
• PHP can either accept a session id through a cookie or through the URL
• Typically this appears as a phishing attack• “Go to this cool site:
http://www.example.com/fixate.php?PHPSESSID=...”
• Solution: use session_regenerate_id() whenever a user logs in or changes their level of privilege.
MySQL UC 2007 27
Session hijacking
• Same idea but involves somehow obtaining the session id.• Refer back to XSS and stealing cookies through JavaScript• Session ids can be sniffed, or obtained from proxy servers if
contained in the URL
• Solutions:– Regenerate ids– If using sessions, always use SSL– Use configuration directive session.use_only_cookies (which will
irritate some users)
MySQL UC 2007 28
CSRF
• CSRF = Cross Site Request Forgeries• A request for a page that looks as though it was initiated by
a site's trusted user, but wasn't (deliberately). Many, many variations.
• Example: <img src='http://example.com/single_click_to_buy.php?item=12345'>• Avoid using GET for actions that cause any kind of change to
data• In general, make sure that users come through your forms,
and each form submission is matched to an individual form that you send out.
• Generate a one-time token and embed it in the form, save it in the session, and check it on submission.
• Not trivial to protect against
MySQL UC 2007 29
Code injection
• While this can be grouped with SQL injection and command injection, it’s a serious enough and common enough problem to merit its own slide
• Problem occurs when you accidentally execute arbitrary code, typically via file inclusion
• Poorly written code can allow a remote file to be included and executed as though it were a trusted local file
• Remember that many PHP functions such as require can take an URL or a filename.
• Passing user input as a filename or part of a filename invites users to start filenames with http …
MySQL UC 2007 30
Example: Theme Selector
<form>Choose Theme:<select name = theme><option value = blue>Blue</option><option value = green>Green</option><option value = red>Red</option></select><input type = submit></form><?php if($theme) { require($theme.'.txt'); }?>
MySQL UC 2007 31
Prevention
• Filter user input• Disable allow_url_fopen and/or allow_url_include setting in
php.ini. This disables require/include/fopen of remote files.• (allow_url_include new in 5.2.0)
MySQL UC 2007 32
Big picture prevention
• Some basic principles (again):– Don’t rely on server configuration to protect you (e.g. magic
quotes) (always/especially) if you are writing distributable apps– Design your application with security from the ground up: for
example, use a single line of execution that begins with a single point of data cleaning.
– Review your colleagues’ code and have them review yours– Seek advice from experts where possible (scanning / auditing)– Educate yourself and your developers and where possible make
it easy for your staff to do the right thing. – Keep your code up to date. Stay on top of patches and
advisories.
MySQL UC 2007 33
Resources
• Open Web Application Security Project http://www.owasp.org
• PHP Security Consortium Guide http://phpsec.org/projects/guide/
• Hardened PHP Patch / Suhosin http://www.hardened-php.net/
• Chris Shiflett’s “Essential Security” from O’Reilly (2005)
MySQL UC 2007 34
Final words
• Slides available for download (after the talk)http://omniti.com/resources/talks
• These slides are available for use under a Creative Commons license.
• You may use them for any purpose, but must give credit
• http://creativecommons.org/licenses/by/1.0/
MySQL UC 2007 35
Questions?
?