Prepared Queries

Hi,

Ci4 is getting rather frustrating now.



I've been trying for over 1 day now to complete a fairly simple insert and return any errors.



After submitting my POST via AJAX and doing all the necessary validation from my controller I am calling:

PHP Code:

$register = new \App\Models\User\Register;
$register_user=$register->registerUser($this->$data);
if($register_user!=true) {
    $this->$data['error']=$register_user;
} 


My Model

PHP Code:

<?php namespace App\Models\User;

use CodeIgniter\Model;
use CodeIgniter\Database\Query;

class Register extends Model {
    
    public function __construct() {
        $db = db_connect();
    }
    
    
    public function registerUser ($data) {
        
        $sql="INSERT INTO user_register (salutation, first_name, last_name, telephone, email, password, question, answer, status, level, confirm, ip) VALUES ('" . (int)$data['salutation'] . "',  '" . $db->escapeString($data['first_name']) . "',  '" . $db->escapeString($data['last_name']) . "',  '" . (int)$data['telephone'] . "',  '" . $db->escapeString($data['email']) . "',  '" . $db->escapeString($data['password_hash']) . "',  '" . (int)$data['question'] . "',  '" . $db->escapeString($data['answer_hash']) . "',  '" . $db->escapeString($data['status']) . "',  '" . $db->escapeString($data['level']) . "',  '" . $db->escapeString($data['confirm']) . "',  '" . $db->escapeString($data['ip']) . "' )";
        
        if(!$db->query($sql)) {
            return $db->error();
        }
    }
} 


All seems great as the row is inserted in the database, but Here is the twist,



if(!$db->query($sql)) only seems to tell me if the query is "formatted" correctly.



If for example insert fails because email is not unique, no errors are collected by $db->error because the query is formatted correctly.






So, I try to use a prepared statement because I can use hasError(), [b]getErrorCode() and getErrorMessage(), [/b]and I suppose this is the better (more secure) way to do things.






Now my model (as per the documentation), but this just flat out completely fails on all levels.

PHP Code:

class Register extends Model {
    
    public function __construct() {
        $db = db_connect();
    }
    
    
    public function registerUser ($data) {
        $options=([
            $data['salutation'],
            $data['first_name'],
            $data['last_name'],
            $data['telephone'],
            $data['email'],
            $data['password_hash'],
            $data['question'],
            $data['answer_hash'],
            $data['status'],
            $data['level'],
            $data['confirm'],
            $data['ip']
            ]);
            
        
        $pQuery = $db->prepare(function($db) {
            $sql = "INSERT INTO user_register (salutation, first_name, last_name, telephone, email, password, question, answer, status, level, confirm, ip) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        
            return (new Query($db))->setQuery($sql);
        }, $options);
        
        $results = $pQuery->execute();
        
        if($results->hasError()) {
             return $results->getErrorMessage();
        }
        
    }
} 


sorry PLEASE HELP this is so frustrating for a simple task.

Thanks to jreklund,

I made changes to the mysqli drivers on my server.

It would appear that in addition to enabling php-json, php-mysqlnd and php-xml, and ensuring database is MySQL (5.1+) via the MySQLi driver (in my case). I was required to:

use nd_msqli driver instead of the standard mysqli driver which comes installed as standard with my hosting account.

Having updated the driver, the above was NOT an issue when executed properly.

public function registerUser ($data) {

$pQuery = $db->prepare(function($db) {
$sql = "INSERT INTO user_register (salutation, first_name, last_name, telephone, email, password, question, answer, status, level, confirm, ip) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

return (new Query($db))->setQuery($sql);
});

$results = $pQuery->execute(
$data['salutation'],
$data['first_name'],
$data['last_name'],
$data['telephone'],
$data['email'],
$data['password_hash'],
$data['question'],
$data['answer_hash'],
$data['status'],
$data['level'],
$data['confirm'],
$data['ip']
);

if($results->hasError()) {
return $results->getErrorMessage();
}