Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Bug: [QueryBuilder] unable to use regular expression inside a query using module class #4355

Closed
sandrocantagallo opened this issue Feb 26, 2021 · 8 comments · Fixed by #5118
Labels
bug Verified issues on the current code behavior or pull requests that will fix them database Issues or pull requests that affect the database layer

Comments

@sandrocantagallo
Copy link

sandrocantagallo commented Feb 26, 2021

Describe the bug
If you use a regular expression inside a query C4 add ` and space unwanted.

CodeIgniter 4 version
^4

Affected module(s)
CodeIgniter\Model

Expected behavior, and steps to reproduce if appropriate
If you write a query like this:

 $result =   $this->select('REGEXP_SUBSTR(ral_anno,"[0-9]{1,2}([,.][0-9]{1,3})([,.][0-9]{1,3})") AS ral')
                    ->where("period", trim($period)."-01-01")
                    ->where("livello", trim($livello))
                    ->orderBy("ral", "DESC")
                    ->limit(1)
                    ->find();

the final result is:

SELECT REGEXP_SUBSTR(ral_anno, "[0-9]{1, 2}([, .`][0-9]{1`, 3})([, .`][0-9]{1`, 3})") AS ral
FROM `ob_human_resources`
WHERE `period` = '2018-01-01'
AND `livello` = 'V'
ORDER BY `ral` DESC

The problem is in regular expression:

[0-9]{1, 2}([, .][0-9]{1, 3})([, .][0-9]{1, 3}) -> this is not original one i write.

` and space are added

Following documentation i also try to add second parameter to select

 $result =   $this->select('REGEXP_SUBSTR(ral_anno,"[0-9]{1,2}([,.][0-9]{1,3})([,.][0-9]{1,3})") AS ral', false)
                    ->where("period", trim($period)."-01-01")
                    ->where("livello", trim($livello))
                    ->orderBy("ral", "DESC")
                    ->limit(1)
                    ->find();

the result is no ` and it's ok but space are also added:

SELECT REGEXP_SUBSTR(ral_anno, "[0-9]{1, 2}([, .][0-9]{1, 3})([, .][0-9]{1, 3})") AS ral
FROM `ob_human_resources`
WHERE `period` = '2018-01-01'
AND `livello` = 'V'
ORDER BY `ral` DESC

Context

  • OS: Windows 10
  • Web server Apache2
  • PHP version 7.3
@sandrocantagallo sandrocantagallo added the bug Verified issues on the current code behavior or pull requests that will fix them label Feb 26, 2021
@sandrocantagallo
Copy link
Author

The only way is the use of Regular Query as describe in documentation.

$result = $this->query("SELECT REGEXP_SUBSTR(ral_anno,\"[0-9]{1,2}([,.][0-9]{1,3})([,.][0-9]{1,3})\") AS ral, ral_anno FROM `ob_human_resources` WHERE period = '".$period."-01-01' AND livello LIKE '%".trim($livello)."%' ORDER BY ral DESC")->getResult();

Only in this way query work fine.

@paulbalandan
Copy link
Member

Yes. A regular query call is your workaround for this. The issue arises when using a SELECT string with commas in it. CI4 will just blindly explode the string at the commas.

@MGatner MGatner added the database Issues or pull requests that affect the database layer label May 18, 2021
@iRedds
Copy link
Collaborator

iRedds commented Jan 22, 2022

At the moment, the problem has not yet been resolved.
I have two solutions.

  1. Add the selectRaw(string $query) method, which will add a string without parsing or modifying it.
  2. Use a wrapper class. Description here https://forum.codeigniter.com/thread-80720.html

@kenjis
Copy link
Member

kenjis commented Jan 23, 2022

I think we need to make it clear what the second parameter $escape = falsemeans.

If I design it from the scratch, $escape = false means do nothing to the first parameter.
The current implementation modifies the first parameter even when $escape = false.
Why and what should it do?

$builder->select() accepts an optional second parameter. If you set it to false, CodeIgniter will not try to protect your field or table names. This is useful if you need a compound select statement where automatic escaping of fields may break them.
https://codeigniter4.github.io/CodeIgniter4/database/query_builder.html#select

@kenjis
Copy link
Member

kenjis commented Jan 23, 2022

CI 3.1-stable:

$sql = $this->db->select('REGEXP_SUBSTR(ral_anno,"[0-9]{1,2}([,.][0-9]{1,3})([,.][0-9]{1,3})") AS ral', false)
            ->get_compiled_select('ob_human_resources');
SELECT REGEXP_SUBSTR(ral_anno, "[0-9]{1, 2}([, .][0-9]{1, 3})([, .][0-9]{1, 3})") AS ral
FROM "ob_human_resources"

@kenjis kenjis changed the title Bug: unable to use regular expression inside a query using module class Bug: [QueryBuilder] unable to use regular expression inside a query using module class Mar 21, 2022
@kenjis
Copy link
Member

kenjis commented Mar 21, 2022

It seems that the parameter $escape of select() means whether to quote identifier (table name/column name) or not.

So if $escape is false, QB still have to handle DBPrefix and swapPre.

@kenjis
Copy link
Member

kenjis commented Mar 21, 2022

I created a PR #5817
Your opinion is welcome.

@kenjis
Copy link
Member

kenjis commented May 4, 2022

@kenjis kenjis closed this as completed May 4, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Verified issues on the current code behavior or pull requests that will fix them database Issues or pull requests that affect the database layer
Projects
None yet
Development

Successfully merging a pull request may close this issue.

5 participants