• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Prepared Queries

#1
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.
Reply

#2
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();
}
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.