CODEWITHSUNDEEP
phpmysqlcakephp
user3555483
user3555483

Reputation: 190

Unable to update records in CakePHP

I have answers table with fields id player_id read_status

I am trying to update read_status:

$this->Answer->updateAll(
    array(
        'Answer.' . "'" . $data['field'] . "'" => "'" . trim(base64_decode($data['option'])) . "'"
    ),
    array(
        'Answer.id' => trim(base64_decode($data['id']))
    )
);

The following fields are dynamic and coming from AJAX Request:

$data['field'] contains read_status

$data['option'] contains base64 encoded yes

$data['id'] contains base64 encoded id

The error I am getting is:

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Answer.'read_status'' in 'field list'
SQL Query:

UPDATE
    `skii`.`answers` AS `Answer`
LEFT JOIN
    `skii`.`players` AS `Player` ON (`Answer`.`player_id` = `Player`.`id`)
SET
    `Answer`.`'read_status'` = 'yes'
WHERE
    `Answer`.`id` = 2

Notice: If you want to customize this error message, create app\View\Errors\pdo_error.ctp

Upvotes: 0

Views: 468

Answers (3)

AD7six
AD7six

Reputation: 66359

Don't do that

The conditions you're generating are:

WHERE
    `Answer`.`id` = 2

There is no reason to use updateAll for that, the primary use cases for updateAll are:

  • Updating multiple records
  • Updates by not-primary-key
  • Updates such as incrementing a counter atomically

Call save

Instead, just call saveField:

$this->Answer->id = $id;
$this->Answer->saveField($field, $val);

or save:

$this->Answer->id = $id;
$this->Answer->save(array($field => $val));

Validate your inputs

If you choose to continue using the code in the question know that it is dangerous.

Feeding user input directly to updateAll allows for the possibility to inject arbitrary SQL. Ensure the data is the data you expect:

With the updateAll call there's nothing to prevents someone submitting as $field:

"read_status = 'yes' AND correct ="

achieving this sql:

SET `Answer`.`read_status` = 'yes' AND correct = 'yes'

It is generally a bad idea to ever put user input in the key of a CakePHP model array (update, conditions, order) as the key is not expected to be potentially malicious by CakePHP and therefore not subject to the same escaping logic applied to values.

Therefore, escape all user input:

$ds = $this->Answer->getDatsource();

$id = (int)trim(base64_decode($data['id']));
$field = $this->Answer->escapeField((base64_decode($data['field']));
$val = $ds->value(trim(base64_decode($data['option']))), $field);

With the above logic and attempting the same injection as shown in the first example the result would be:

SET `Answer`.``read_status` = 'yes' AND correct =` = 'yes';

Which would simply fail rather than permit users to arbitrarily manipulate the database.

Note that the base64 encoding, if it's been added as a "security" measure, does nothing.

Upvotes: 3

Bharat Maheshwari
Bharat Maheshwari

Reputation: 418

Try this :- 

$this->Answer->updateAll(
array(
    'Answer.' . $data['field']  => "'" . trim(base64_decode($data['option'])) . "'"
),
array(
    'Answer.id' => trim(base64_decode($data['id']))
)
);

Upvotes: 0

cornelb
cornelb

Reputation: 6066

It seems the proble is that you are escaping the field name with single quotes:

`Answer`.`'read_status'` = 'yes'

should be

`Answer`.`read_status` = 'yes'

The following change should fix that:

$this->Answer->updateAll(
    array('Answer.' . $data['field'] => trim(base64_decode($data['option']))),
    array('Answer.id' => trim(base64_decode($data['id'])))
);

Upvotes: 0

Related Questions