Statement could not be executed (42S22 - 1054 - Unknown column '' in 'field list') zf2

Question

I am new to ZF2, and I am trying to delete a data to database, but data can't delete , error Statement could not be executed (42S22 - 1054 - Unknown column '' in 'field list')

my view

<?php foreach ($this->list as $data): ?>

    <tr>
        <td>
             <?php echo $data->id ?>
        </td>

 <a href="<?php echo $this->url('mif',array('action'=>'delete', 'id' => $data->id));?>">Delete</a>

my controller

public function deleteAction()  
    {  
        $request = $this->getRequest();
        $post = (int) $this->params()->fromRoute('id', null);
        $storage = MiffModel\Storage::factory($this->getDb(), $this->getConfig());
        $user = new MiffModel($storage);
        $del = $user->del($post);

        if($del){
            $success = true;
            $msg = 'Data sudah dihapus.';
        }else{
            $success = false;
            $msg = 'gagal.';
        }     

        $view = new ViewModel();
        $view->setTemplate('mif/index');

my model

public function del($post){
        $delete = "DELETE from test where id = $post";
        $db = $this->_db;
        $result = $this->_db->query($delete, $db::QUERY_MODE_EXECUTE);
        return $result;
    }
}

Possible duplicate of question [1346209](https://stackoverflow.com/questions/1346209/unknown-column-in-field-list-error-on-mysql-update-query). Is it possible that `$post` is NULL? To check that, would you mind including an if statement in the body of the `del()` function? It would look something like this... `If ($post) { }` Next, - as was suggested in the link - try surround the string you are appending into the query with single quotes - `$delete = "DELETE from test where id = '$post'";` — Troy Witthoeft, Aug 01 '17 at 01:51

score 1 · Answer 1 · answered Aug 02 '17 at 11:11

As the accepted answer is a security breach I propose this :

This solution is based on using PDO.

public function del($post){

$stmt = $this->_db->prepare('DELETE from test where id = :id');

// Check if there is a post exists, if not throw exception
if(empty($post)) {
    throw new \Exception('wrong or empty data provided');
}

$stmt->bindParam(':id', $post);
return $stmt->execute();
}

From this link
Quote The parameters to prepared statements don't need to be quoted; the driver automatically handles this. If an application exclusively uses prepared statements, the developer can be sure that no SQL injection will occur (however, if other portions of the query are being built up with unescaped input, SQL injection is still possible).

giolliano sulit · Accepted Answer · 2017-08-02T23:37:31.540

0

Looks like somebody has posted an answer in the comment, but it doesn't seem that clear and they're not escaping the string properly to use the $post value.

Here is what I would suggest doing:

Edited to use best practise

public function del($post)
{
    $stmt = $this->_db->prepare('DELETE from test where id = :id');

    // Check if there is a post exists, if not throw exception
    if(empty($post)) {
        throw new \Exception('wrong or empty data provided');
    }

    $stmt->bindParam(':id', $post);
    return $stmt->execute();
}

edited Aug 02 '17 at 23:37

answered Aug 01 '17 at 02:34

giolliano sulit

996
1
6
11

And what happenned if $post = ";'DROP DATABASE;'" ? or any SQL injection ? Your condition is not filtering the input... – Greco Jonathan Aug 02 '17 at 11:00
Well yep. I only added in the part that answers his question, the **unkown column** error, the rest of it is his code. But I agree the PDO example you've posted is more complete, if he changes the accepted answer to that I'll remove this answer. – giolliano sulit Aug 02 '17 at 12:08
careful in your post, I didn't see first but you missed a closed bracket near the empty test – Greco Jonathan Aug 02 '17 at 13:12
He can't move the accepted answer, you can edit yours to fit with good practises. – Greco Jonathan Aug 02 '17 at 13:13

Statement could not be executed (42S22 - 1054 - Unknown column '' in 'field list') zf2

2 Answers2