php - How to prevent SQL Injection in parameters with CakePHP

637

How to prevent SQL Injection while fetching data from the database when using parameters received from the user input:

if(isset($_GET['cityval']) && $_GET['cityval'] !=''){

    $city = $this->request->query('cityval'); 

      $searching .= " and college_city in ($city) ";
    } else {
        $searching .= "";
    }
    if(isset($_GET['scholarship']) && $_GET['scholarship'] !=''){
        $searching .= " and college_scholarship = '".$_GET['scholarship']."' ";
    } else {
        $searching .= "";
    }

And my main query is below

$search = $this->Search->query("select * from  colleges where college_id!='' and status='active' $searching order by $order desc limit $start, 10 ");
98

Answer

Solution:

Don't use raw queries. Simply use the query builder CakePHP provides, and it will prevent injection for you. See the online CakePHP book for more information.

It is SUPER rare to need to use raw queries in CakePHP.

337

Answer

Solution:

What you try to do is obviously to search by get parameters. There is a wonderful plugin that makes it pretty easy https://github.com/FriendsOfCake/search

It could be actually that easy with the plugin:

$query = $this->Colleges->find('search', [
    'search' => $this->request->query
]);
$this->set('results', $this->Paginator->paginate($query));

The search params itself will be handled in the model layer, check the plugins documentation on that. And the framework will take care of sanitizing the input.

957

Answer

Solution:

It seems that Cake ORM uses PDO:

Underneath the covers, the query builder uses PDO prepared statements which protect against SQL injection attacks.

Reference: https://book.cakephp.org/3.0/en/orm/query-builder.html

Unfortunately the way you're creating the query is vulnerable as you're not using Cake ORM neither PDO prepared statements.

If you want to use raw queries you could do something like this to protect your code:

// Add this in the beginning of the file/code:
use Cake\Datasource\ConnectionManager;

// Replace connection_name with the name of your connection (maybe it's "default")
$connection = ConnectionManager::get('connection_name');

$bindList = [];

$city = $this->request->query('cityval');

// PDO as a limitation for the parameter markers. See in the comments below
$cityList = array_filter(explode(',', $city), function($item) {
     return preg_match('/^\d+$/', $item);
});

$csvCity = implode(',', $cityList);


$scholarship = $this->request->query('scholarship');

if (!empty($csvCity)) {
    $searching .= " and college_city in ($csvCity)";
}

if (!empty($scholarship)) {
    $searching .= " and college_scholarship = :scholarship";
    $bindList['scholarship'] = $scholarship;
}

$stmt = $connection->prepare($searching);
$stmt->bind($bindList);
$stmt->execute();

// Read all rows.
$rows = $stmt->fetchAll('assoc');

// Read rows through iteration.
foreach ($rows as $row) {
    // Do work
}

PDO has a limitation and because of that there's no proper way to use the SQLIN() clause in a prepared statement (to bind the values to it), so we need to parse manually the values to be inside that clause as I did in the code.

From the PDO Prepare manual page:

Note: Parameter markers can represent a complete data literal only. Neither part of literal, nor keyword, nor identifier, nor whatever arbitrary query part can be bound using parameters. For example, you cannot bind multiple values to a single parameter in the IN() clause of an SQL statement.

References

People are also looking for solutions to the problem: javascript - How to get value from html select tag option value using onChange event using jQuery data table?

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.