One benefit of using a framework is that some of the most common problems we will encounter when building web applications from scratch is already taken cared of, that is if properly used. Today, we will discuss one of the most common problem we have experienced specially for beginners – SQL Injection.

Let’s begin first by defining SQL Injection. According to wikipedia, SQL injection is a technique that exploits a security vulnerability occurring in the database layer of an application. The vulnerability is present when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and thereby unexpectedly executed. It is in fact an instance of a more general class of vulnerabilities that can occur whenever one programming or scripting language is embedded inside another.

To protect against SQL injection, user input must not directly be embedded in SQL statements. Instead, user input must be escaped, or parameterized statements must be used.

CakePHP handles this in its data abstraction layer by using mysql_real_escape_string(). To use effectively, programmers must comply to its rules. Why did I say comply? Some of model functions are very loose with what kind of parameter type to accept. This setting has caused some of the newcommers to not see the benefit of passing conditions by array. Let’s take for an example the findAll() function.

Model::findAll ( $conditions = null, $fields = null, $order = null, $limit = null, $page = 1, $recursive = null )

Parameters:

	mixed	$conditions SQL conditions as a string or as an array(’field’=>’value’,…)
	mixed	$fields Either a single string of a field name, or an array of field names

The $conditions accepts both string and array for its passed values. Logically, only the data passed as an array will be escaped. Using arrays allows CakePHP to generate the most efficient query possible, ensure proper SQL syntax, and properly escape each individual part of the query.

For complex find queries, we may opt for the string condition and do all the dirty work. As of this writing, CakePHP is now offering a solution to build complex conditions using arrays.

Cake can parse out any valid SQL comparison operator, including match expressions using LIKE, BETWEEN, or REGEX, as long as we leave a space between the operator and the expression or value.

array("Post.title" => "<> This is a post")

Below is the adaptation of the IN (…)-style matches

array("Post.title" => array("First post", "Second post", "Third post"))

By default, the framework joins multiple conditions with boolean AND. To accept other boolean conditions, we could do the ffg:

array
("or" =>
    array
    (
        "Post.title" => array("First post", "Second post", "Third post"),
        "Post.created" => "> " . date('Y-m-d', strtotime("-2 weeks"))
    )
)

Or like this:

array 
("Author.name" => "Bob", "or" => array
    (
        "Post.title" => "LIKE %magic%",
        "Post.created" => "> " . date('Y-m-d', strtotime("-2 weeks")
    )
)

More explanation of this can be found at the Manual: Complex Find Conditions (using arrays).

Model::save() is more strict so I did not see any problem following its rules. If you have questions for a certain model function syntax, the API can be a great resource.

With the benefit of these Model functions, we must still have to adapt to the framework’s environment which maybe of a disadvantage at first. It will just be your choice which manners will best suit you. At the end of the day, what will matter is that the code we produce is readable, maintanable and most of all secure.

Source:

• SQL_injection
• Guid to PHP Security (PDF)
• mysql_real_escape_string vs addslashes
• mysql_real_escape_string vs addslashes Discussion
• CakePHP Manual: Models

Tags: Model, sql injection

by rachel Security, Tips & Tutorials Comments 2 Feb 11th, 2008