alpha 4->5 query param binding

[nowackipawel]

This line called from the model

$this->db->query('UPDATE user_table SET `x` = NOW() WHERE `id` = :id: ',['id' => 22]);

produces (grabbed inside catch block):

this lines called from the Controller:

	try
		{
			$db = \Config\Database::connect(DB_ZTN_GROUP);
			$db->query('UPDATE user_table SET `x` = NOW() WHERE `id` = :id: ',['id' => 22]);
		}
		catch (\mysqli_sql_exception $e)
		{
			d($db->getLastQuery());die();
		}

produces:

Issue showed up in the newest git version.

[nowackipawel]

[nowackipawel]

I've noticed that array_walk in setQuery method is executed if query is called\Config\Database::connect(DB_ZTN_GROUP)->query()
and not if query is called from the model like this: $this->db->query()

[lonnieezell]

@nowackipawel Please confirm the fix works.

[nowackipawel]

@lonnieezell : Result is exactly the same as it was ... :( sorry.
(I use mysql db ).

I added d($this->binds); at the end of Query's setQuery method and when I called query from controller I get: ['id' => [22, true]] and from model: ['id' => 22]. I am not sure if this is the reason.... but looks like it is :/

[nowackipawel]

@lonnieezell I had to add
$this->db->setEscapeFlags(true)->query('UPDATE user_table SET `x` = NOW() WHERE `id` = :id: ',['id' => 22]);
or 2nd way to make it work:

$this->db->setEscapeFlags(false)->query('UPDATE user_table SET `x` = NOW() WHERE `id` = :id: ',['id' => [22,true]]);

to make my query works if it is called from the model.

How to reopen the issue ?

[nowackipawel]

@lonnieezell
I am experiencing the same or very similar problem problem running spark.

Type:        ErrorException
Message:     Uninitialized string offset: 1
Filename:    /home/pc/Development/PhpstormProjects/LOCAL-/system/Database/Query.php
Line Number: 426

[lonnieezell]

@nowackipawel Please create a new task then, with code samples to recreate. Though that's a query issue - doesn't sound like anything to do with spark?

[lonnieezell]

And I think what's going on with your issue is that once a query is ran, the flag is flipped, so the next query would hit the same issue. Refactoring it again currently.

[lonnieezell]

Just pushed another fix. I believe this should finally solve it. PLease let me know @nowackipawel

[nowackipawel]

After I've updated files from your fix. I noticed that

    return $this->db
57                 ->setEscapeFlags(true)
58                 ->query(
59                     ' SELECT `drs2usr_' . $rev . '_id`, `drs2usr_permission` ' .
60                     ' FROM `' . $this->table . '` ' .
61                     ' WHERE `' . $this->table . '`.`drs2usr_' . $key . '_id` = :id: ',
62                     ['id' => $id]
63                 )->getResultObject();

is not working anymore.

I did two tests. I removed setEscapeFlags from one spark file and it is working as expected. ... as well as removing it from query which was called from model. What I wanted to say that it seams to work now.

[lonnieezell]

Excellent! Glad to hear that. Thanks!

[srickett]

@lonnieezell I seem to be getting a variation of the problem still (dev-master a388e63). Note that this has been introduced since alpha4

If I try to bind an integer i get an error, but if I cast it to a string it works fine.

For example, this query executes fine:

$sql = "SELECT * FROM `ci_sessions` LIMIT :limit:";
 $query = $this->db->query($sql, array(
     "limit" => (string) $limit
 ));

However, as an integer, it does not get bound and the query fails

$sql = "SELECT * FROM `ci_sessions` LIMIT :limit:";
$query = $this->db->query($sql, array(
     "limit" => (int) $limit
));

To make it more complicated, if I setEscapeFlags(), it works for the integer, but not the string

$sql = "SELECT * FROM `ci_sessions` LIMIT :limit:";
$query = $this->db->setEscapeFlags(true)->query($sql, array(
    "limit" => (int) $limit
 ));

Thanks