• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Issues with complex queries in Query Builder and protected identifiers

#1
I know most of these issues are resolved by disabling protected identifiers or using $this->db->query(), but that really limits the usefulness of Query Builder in the area where it should be the most helpful. My most recent run-in with this comes down to using the result of $this->db->get_compiled_select() in a call to from(), which, unlike select(), has no parameter to disable the use of protect/escape_identifiers().

Basically, my query builder call is similar to this:

PHP Code:
$this->db->select(
 
   array(
 
       'count(result_alias.table1_key) as table1_count'
 
       'result_alias.col1'
 
   )
)
 
        ->from('(
    SELECT DISTINCT 
        `table1`.`key` as `table1_key`, 
        `table2`.`col1` as `col1`
    FROM `table1`
    INNER JOIN `table3` ON `table3`.`fkey1` = `table1`.`key`
    INNER JOIN `table4` ON `table4`.`fkey` = `table3`.`fkey4`
    JOIN `table2` ON `table2`.`key` = `table4`.`fkey2`
    WHERE `table1`.`deleted` = 0
    ) as result_alias'
)
 
        ->group_by('result_alias.col1'); 

The output ends up being something like this:

Code:
SELECT
    count(result_alias.table1_key) as table1_count,
    `result_alias`.`col1`
FROM (
    SELECT DISTINCT
        `table1`.`key` as `table1_key`,
        `table2`.`col1`` as ``col``
    FROM ``table1``
    INNER JOIN ``table3`` ON ``table3`.`fkey1`` = ``table1`.`key``
    INNER JOIN ``table4`` ON ``table4`.`fkey`` = ``table3`.`fkey4``
    JOIN ``table2`` ON ``table2`.`key`` = ``table4`.`fkey2``
    WHERE ``table1`.`deleted`` =0
)` as `result_alias`
GROUP BY `result_alias`.`col1`


Notes:
- No issues until after the first comma in the from clause. This is because the from() method explodes the input on the comma (splitting this into 2 parts), then passes each portion of the clause into protect_identifiers, which finds the opening parenthesis and decides not to escape the first portion.
- The second line is ok until you get to the end of the first column name, then the double escape characters begin. I haven't narrowed down why it starts here rather than at the beginning of the line.
- From this point on, everything within the parentheses gets a double escape character unless the escape characters surround the . between the table and column names
- The closing parenthesis gets an escape character.
Reply

#2
protect identifiers are not needed unless you are using db/column names with MySQL reserved words, which you shouldn't anyway. Using the tickmarks only lets you use reserved words like 'select table.`order`' since "order" is a MySQL reserved word it would throw an error if it didn't have the ticks. I disable protect identifiers globally and just don't use reserved words Smile I see lots of people calling this "escaping", which it technically isn't.
Reply

#3
To clarify: the only thing in the examples I included which has anything to do with the use of MySQL is the use of ` as the escape character in the driver (as defined by the mysqli_driver) and the resulting output. If other drivers give a different result, it may be because there happens to be special behavior in escape_identifiers() related to the default escape character ("), which is used by the drivers for most other databases.

The use of the word "escape" in both the original post and this one is strictly because I am referencing the terminology used within the DB_driver and related code. The documentation implies that this is done for the purposes of producing safer queries, though, as you have mentioned, in many cases it does little to do so.

Of course, given the 200-something reserved words in MySQL (or the 800+ identified when using multiple sources), I'd really have to take a close look at all of my tables to make sure I'm not using any invalid column names before disabling the feature globally.
Reply

#4
Do not use query builder for queries with subqueries. It's pointless and CI's QB can't handle it.
Reply

#5
If you're on the CI3 version of your site, have you tried Query grouping? I haven't actually tried using that, yet, and the example doesn't show it working in a 'FROM' clause, but it might be worth looking into.

However, I tend to agree with gadelat on this one. For any complex queries, I typically build the query manually and use $this->db->query() with parameter binding. If it gets really complex, I'll leave some placeholders in the query, like it was a view template, almost, and then do string replacement with the correct queries until I have what I need. And inside of an app, I don't have a problem not using the query builder. I think the times when someone moves from one database to another are pretty slim and, when they do happen, they're part of a much larger change so it's expected to be a bit time-consuming.
Reply

#6
I checked the code for Query Grouping, it pushes parentheses/AND/OR/NOT directly into the where clause.

I understand that Query Builder isn't capable of handling complex cases, but I really think that it's over-sold in its capabilities and it really isn't clear what is gained through the protect/escape_identifiers() functionality. I use Query Builder because I like the syntax, and because building queries by concatenating strings is clumsy and a maintenance headache.

To execute the above query in CI2's Active Record required ~120 lines of code to replicate the functionality of CI3's Query Builder's get_compiled_select(). To execute it in CI3's Query Builder is impossible unless you disable protect_identifiers, so I ended up adding about 30 lines of code to my model to replicate functionality in Query Builder to build out portions of this query. Overall, an improvement in terms of reducing the number of lines used to perform functionality which belongs in the database layer, but I really hope I never have to revisit this query.

I've gotten past the point of believing that I'm ever going to be able to change databases easily just because I use Query Builder, since it does nothing to abstract the database in any significant way beyond the database library itself. The limited selection of functions may keep you from venturing into db-specific territory in some ways, but you rarely need much else. The area which limits the ability to transition between databases the most is probably DBForge, but I'm not sure there is much demand in the CI community to actually build a database layer which might someday provide real cross-platform capabilities.

What really seems significant here is that the biggest changes with an impact on the functionality of Query Builder seem to have taken place not within Query Builder (despite the lack of back-ports for most of the bug fixes in it), but within the escape_identifiers() method (previously _escape_identifiers()).
Reply

#7
There are so many edge cases such as this where CI's query builder will bork that the db library would be absolutely huge to make it work for all cases, and then it would have to be replicated for each DB driver so everything works the same across platforms. Then what do you do with Platform specific functions that don't have a similar counterpart in other platforms? It would be a monumental task.
Reply

#8
(05-12-2015, 10:37 AM)CroNiX Wrote: There are so many edge cases such as this where CI's query builder will bork that the db library would be absolutely huge to make it work for all cases, and then it would have to be replicated for each DB driver so everything works the same across platforms. Then what do you do with Platform specific functions that don't have a similar counterpart in other platforms? It would be a monumental task.

Most of the code responsible for the issues I described here is not contained in the individual drivers. Most of the functionality in the drivers handles:
- connecting to/disconnecting from the database
- configuring the connection
- retrieving some metadata (version, list_tables, list_columns, field_data)
- retrieving error messages
- retrieving the number of affected rows or inserted ID

One relevant method in the drivers is _escape_str(), though there is far more going on within DB_driver.php when calling protect_identifiers() and/or escape_identifiers() than in the individual _escape_str() methods.

Platform-specific functions without a similar counterpart in other platforms are largely irrelevant for a cross-platform system. For anything of that nature that couldn't be run through query() directly, you probably can't do it in the existing database layer, anyway.
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


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