CakePHP - 插入多个表错误

Question

I'm trying to save data to more tables using CakePHP (2.4.6).

I have Posts/add.tcp file:

<?php
echo $this->Form->create('Post');
echo $this->Form->input('title');
echo $this->Form->input('body', array('rows' => '3'));
echo $this->Form->end('Save Post');
?>

And method add in PostsController file:

public function add() {
    if ($this->request->is('post')) {
        $this->request->data['Post']['user_id'] = $this->Auth->user('id');

$titulek = $this->request->data['Post']['title'];
$uid=$this->request->data['Post']['user_id'];
$body=$this->request->data['Post']['body'];

$sqla = "INSERT INTO posts (title,created,user_id) VALUES ('$titulek', NOW(),'$uid')";
$sqlb = "INSERT INTO forums (user_id,post_id,text,created) VALUES ('$uid',NULL,'$body', NOW())";

if ($this->Post->query($sqla)) {
if ($this->Post->query($sqlb)) {
            $this->Session->setFlash(__('Your post has been saved.'));
            return $this->redirect(array('action' => 'index'));
}
        }
    }
}

But it doesn't work as intended. Only $sqla query is actually saved to the database. $sqlb query isn't executed at all and at the end I'm not redirected to another page. It's interesting that if I reverse the order of queries (e.g. current $sqlb query becomes $sqla query and $sqlb query becomes $sqla query the INSERT INTO forums query executes correctly but INSERT INTO posts isn't, so it seems that both queries are written correctly and for some reason only first of them executes). What am I doing wrong? I just want to insert custom query into more tables in one action. Thanks in advance for any help.

Answer 1

So let me clear this up, besides the obvious SQL injection problem that you have there (when executing raw queries, always use prepared statements so that the data is being bound in a safe way), you are not evaluating the return value of Model::query() properly.

Evaluating Model::query() results

Unfortunately the documentation is a little misleading/confusing/incorrect on this, an INSERT query will always return an empty array on success, so you would have to check for something like:

is_array($this->Post->query($sqla))

However that's pretty ugly if you ask me.

DboSource::execute() for INSERTs

For an INSERT query I'd always use DboSource::execute(), which also supports binding parameters:

$db = ConnectionManager::getDataSource('default');
$options = array();
$params = array(
    ':title' => $titulek,
    ':uid' => $uid,
);
$result = $db->execute(
    'INSERT INTO posts (title,created,user_id) VALUES (:title, NOW(), :uid)',
    $options,
    $params
);

Now $result will bei either an instance of PDOStatement on success, or false on failure, so it would be safe to simply test the value for being truly:

if($result)

CakePHP ORM to the rescue

If you use a framework, use the framework!

As already mentioned in the comments, if possible you should avoid executing queries manually, the ORM layer offers great comfort and security, and is as simple to use as:

$this->Post->save($this->request->data)

All you have to do is set up your model associations, and make sure that the view formats the data properly.

However, your DB schema seems pretty weird, I really don't get why you create a forum for every post, where the forum contains the post contents, lacking any association to the post o_O Maybe you should rethink that.

Further information:

• http://book.cakephp.org/2.0/en/models/associations-linking-models-together.html
• http://book.cakephp.org/2.0/en/models/saving-your-data.html