Use an SQL Injection Cheat Sheet

XSS (Cross-Site Scripting) and CSRF (Cross-Site Request Forgeries), are at least as common and at least as dangerous.

We can provide some much-needed context, but because we don’t want to focus too much on one attack, we’ll first take a step back. Every developer should be familiar with good security practices, and apps should be designed with these practices in mind. A fundamental rule is to never trust data you receive from somewhere else. Another rule is to escape data before you send it somewhere else. Combined, these rules can be simplified to make up a basic tenet of security: filter input, escape output (FIEO).

The root cause of SQL injection is a failure to escape output. More specifically, it is when the distinction between the format of an SQL query and the data used by the SQL query is not carefully maintained. This is common in PHP apps that construct queries as follows:

$query = "SELECT * FROM articles WHERE  title = '$_GET['article_title']'";

The value of $_GET['article_title'] is provided by another source, the user, but it is neither filtered or escaped.

Escaping preserves data in a new context. The emphasis on escaping output is a reminder that data used outside of your Web app needs to be escaped, else it might be misinterpreted. By contrast, filtering ensures that data is valid before it’s used.

Assuming we’re using MySQL, the SQL injection vulnerability can be mitigated by escaping the name with mysql_real_escape_string(). If the name is also filtered, there is an additional layer of security. (Implementing multiple layers of security is called “defense in depth” and is a very good security practice.) The following example demonstrates filtering input and escaping output, with naming conventions used for code clarity:

// Initialize arrays for filtered and escaped data, respectively.
$data_clean = array();
$data_sql    = array();

// Filter the article title. (For simplicity, we require alphabetic names.)
if (ctype_alpha($_GET['article_title'])) {
    $data_clean['article_title'] = $_GET['article_title'];
} else {
    // The article title is invalid.. Bla bla
}

// Escape the article title.
$data_sql['article_title'] = mysql_real_escape_string($data_clean['article_title']); 

// Construct the query.
$query = "SELECT * FROM articles WHERE  title = '$data_sql['article_title']'";

Difference Between Comparison Operators

This is a good tip, but it is missing a practical example that demonstrates when a non-strict comparison can cause problems.

If you use strpos() to determine whether a substring exists within a string (it returns FALSE if the substring is not found), the results can be misleading:

$author = 'mario & Design';

if (strpos($title, 'mario')) {
    echo 'mario is an author.';
} else {
    echo 'mario is not an author.';
}

Because the substring mario occurs at the very beginning of mario & Design, strpos() correctly returns 0, indicating the first position in the string. Because the conditional statement treats this as a Boolean, it evaluates to FALSE, and the condition fails. In other words, it looks like mario is not an author, but he is!

This can be corrected with a strict comparison:

if (strpos($author, 'mario') !== FALSE) {
    echo 'mario is an author.';
} else {
    echo 'mario is not an author.';
}

Use an MVC Framework

As the design of the World Wide Web was not inherently dynamic, early hypertext consisted of hand-coded HTML that was published on web servers. Any modifications to published pages needed to be performed by the pages’ author. To provide a dynamic web page that reflected user inputs, the Common Gateway Interface (CGI) standard was introduced for interfacing external applications with web servers.^[2] CGI could adversely affect server load, though, since each request had to start a separate process.

Programmers wanted tighter integration with the web server to enable high traffic web applications. The Apache HTTP Server, for example, supports modules that can extend the web server with arbitrary code executions (such as mod perl) or forward specific requests to a web server that can handle dynamic content (such as mod jk). Some web servers (such as Apache Tomcat) were specifically designed to handle dynamic content by executing code written in some languages, such as Java.

Around the same time, new languages were being developed specifically for use in the web, such as ColdFusion, PHP and Active Server Pages.

While the vast majority of languages available to programmers to use in creating dynamic web pages have libraries to help with common tasks, web applications often require specific libraries that are useful in web applications, such as creating HTML (for example, JavaServer Faces).

Eventually, mature, “full stack” frameworks appeared, that often gathered multiple libraries useful for web development into a single cohesive software stack for web developers to use. Examples of this include JavaEE (Servlets), WebObjects, OpenACS, Catalyst, Ruby on Rails, Django, and Zend Framework.

The Pros of using an MVC Framework

The best part about using an MVC framework is that everything in the web application has a standard structure and it is compartmentalized. If you ever have any bugs that need fixing, they are very easy to track down. It’s also great if you ever need to update the code, as changing one piece won’t affect anything on the site. The MVC structure is also very handy for large scale projects that have several people working on them. The design department could be making the views while the programming department is working on the models, and nobody would be stepping on anybody’s toes.

The Cons of using an MVC Framework

One of the problems is that MVC frameworks can be incredibly bulky. They require a lot of files for everything to work properly. Ruby on Rails generates several files before you even enter your first line of code! Also, sometimes MVC applications can become compartmentalized to a fault. In addition to the models, views, and controllers, you can also have templates (sort of a global view), and helper files (which when there are complex views that can’t be handled by a typical controller). Now one web page could require up to 5 files just for it. If you are not careful, MVC projects can get out of hand pretty quickly. This can also end up affecting performance.