Invalid parameter while inserting into database

Question

I have an insert function part of CRUD class, I faced a problem after an update installed on ubuntu:

crud.php

public function insert($table, $fields = [])
{

    $field  = implode('", "', $fields);
    $field  = $this->strSafe($field); // protect against XSS
    $sql = 'INSERT INTO ' . $table . ' (' . implode(', ', array_keys($fields)) . ') VALUES ("' . $field . '")';

    $this->sql  = $sql;
    return $this->sql;
}

Function for bind: crud.php

public function bind($param, $value, $type = null)
{
    if (is_null($type)) :
        switch (true):
            case is_int($value):
                $type = PDO::PARAM_INT;
                break;
            case is_bool($value):
                $type = PDO::PARAM_BOOL;
                break;
            case is_null($value):
                $type = PDO::PARAM_NULL;
                break;
            default:
                $type = PDO::PARAM_STR;
        endswitch; // end switch (true):
    endif; // end if (is_null($type)):

    $value  = $this->strSafe($value); // protect value against XSS
    $this->stmt->bindParam($param, $value, $type); // line 179
}

prepare function

public function prepare()
{
    return $this->stmt = $this->dbh->prepare($this->sql);
}

All the variables are correct using the below code for insert:

register_check.php

$insertclient  = [
            'client_key' => $client_key,
            'client_tel' => $telephone,
            'client_email' => $email,
            'client_add_date' => $GMTTimeStamp,
        ];

        $inCus = $dbh->insert('clients', $insertclient);
        $dbh->prepare($inCus);

        foreach ($insertclient as $fvk => $fvv) :
            $dbh->bind(':' . $fvk, $fvv); // line 152
        endforeach;

        $dbh->execute();

Then I've got this error:

AH01071: Got error 'PHP message: PHP Fatal error:  Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number in crud.php:179\nStack trace:\n#0 crud.php(179): PDOStatement->bindParam()\n#1 register_check.php(152): Database->bind()\n#2 check.php(67): require_once('...')\n#3 {main}\n  thrown in crud.php on line 179', referer: register.html

You need to use `bindValue()`, not `bindParam()`. And XSS checks should be done when displaying data on a web page, not when saving to the DB. — Barmar, Jan 15 '21 at 17:17
The `insert()` function doesn't add `:` to the beginning of parameter names. — Barmar, Jan 15 '21 at 17:18
The `insert()` function isn't creating a query with placeholders, it's putting the literal values into the query, with quotes around them. So you can't bind the parameters to it. — Barmar, Jan 15 '21 at 17:21
@Barmar: changed to bindValue and removed the XSS and now getting an error `AH01071: Got error 'PHP message: PHP Fatal error: Uncaught Error: Call to a member function prepare() on null` — Immo Broker, Jan 15 '21 at 17:29
That sounds like an error in your `prepare()` method, which you haven't shown. — Barmar, Jan 15 '21 at 17:33
It seems like you're trying to implement your own ORM. If you can't debug this yourself, maybe you should use a pre-existing library. See https://stackoverflow.com/questions/108699/good-php-orm-library — Barmar, Jan 15 '21 at 17:34

Invalid parameter while inserting into database

0 Answers0