Avoiding SQL injections with cake.

It is quite simple as long as you do it correctly.

Quoting 2 of my friends here on this post: Juan and Vlad.

Cake automatically will escape the variables when you use arrays. Examples:

// Wrong
$conditions = “Model.field = ‘$variable'”;
// Correct
$conditions[‘Model.field’] = $variable;

// Wrong
$conditions[] = “Model.field = ‘$variable'”;
// Correct
$conditions[‘Model.field’] = $variable;
// Wrong
$conditions = “Model.field LIKE ‘%$variable%'”;
// Correct
$conditions[‘Model.field LIKE’] = ‘%’ . $variable . ‘%’;
// Wrong
$conditions = “Model.field >= $variable”;
// Correct
$conditions[‘Model.field >=’] = $variable;
// Wrong
$conditions = “Model.field1 = $variable1 OR Model.field2 = $variable2”;
// Correct
$conditions[‘OR’] = array(‘Model.field1’ => $variable1, ‘Model.field2’ => $variable2);
// Wrong
$conditions = ‘Model.field IN (“‘ . implode(‘”,”‘, $myArray) . ‘”)’;
// Correct
$conditions[‘Model.field’] = $myArray; // Cake will implode the values

When you are comparing 2 fields you MUST use string. Note that in this case you don’t have problem with injections, but if you use the array key/value the cake will test the second model as string. Example:

// Wrong
$conditions[‘Model1.field’] = ‘Model2.field’;
// Correct
$conditions = ‘Model1.field = Model2.field’;

If you can’t use this form by any reason, you can sanitize the variable as below:

App::import(‘Lib’, ‘Sanitize’);
$myVar = Sanitize::escape($myVar);

Note: using arrays is a good practice. Sometimes the use of string not cause a sql injection issue, examples:

$variable = 1 + (float)$otherVariable;
$conditions = “Model.field = ‘$variable'”;

That’s why it is very important to rely on ORM and proper cake notation (arrays) to build queries. Avoid custom SQL at all cost!….
Also, if you do write any custom SQL, never put it in the controller and always escape all fields with backticks, i.e:
`Model`.`field`,

If you do custom SELECT’s always add WHERE 1 = 1 to the end of your query.

Advertisements

About mcloide

Making things simpler, just check: http://www.mcloide.com View all posts by mcloide

One response to “Avoiding SQL injections with cake.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: