MySQL error on special column names

[sargac]

Hi!

I have a table with 3 columns, named id, type, default :

| id | type | default |

The simple query below returns an error, due to a column named default :

$dt->query('select default from table')

So I tried to escape the column name using backticks, but it doesn't work :

$dt->query('select `default` from table')

Then I echoed the working SQL query on a type column -- once with backticks, and once without :

[sql] => SELECT type FROM (select `type` from feature)t ORDER BY type asc
[sql] => SELECT type FROM (select type from feature)t ORDER BY type asc

It's obvious that the backticks I used in a first query disappeared, so that might be the problem.

I have the following questions:

• How can I escape special column names, like -- order, default etc?
• Why are there 2 queries (query + subquery) arising from 1 query I wrote?
• As I am using Codeigniter4Adapter, I'd like to know whether it is possible to use CI Query Builder or plain queries only?

There is additional error using Codeigniter4Adapter for a query using asterisk :

SELECT * FROM table

Thank you and best regards!

[n1crack]

How can I escape special column names, like -- order, default etc?

• I will look into it tonight and will respond. Why are there 2 queries (query + subquery) arising from 1 query I wrote?
• thats how the library works under the hood. There are some reasons behind it. As your query becomes more complex, the features such as sorting, pagination and searching becomes difficult. In addtion to that if your query has joins or group by statements, it becomes even harder. SELECT * FROM table
• The library currently doesn't support to use asterisk yet. (I aggree it is needed, It needs to be added in the most smooth way without affecting other parts. ) As I am using Codeigniter4Adapter, I'd like to know whether it is possible to use CI Query Builder or plain queries only?
• The library always use plain queries. (the reason to behind that is performance issues).

Codeigniter4Adapter uses the CI4's database class. And runs the queries like this:

$this->db = \Config\Database::connect();
$this->db->query($query, $query->escapes);

Lately, In LaravelAdapter, I added an helper method to be able to convert builder class to sql query string. You can do the same with Codeigniter4Adapter by adding this method to end of it. (i will add this to the library asap)

   /**
     * @param $query
     * @return string
     */
    public function getQueryString($query)
    {
        if ($query instanceof \CodeIgniter\Database\BaseBuilder) {
            return $query->getCompiledSelect();
        }

        return $query;
    }

[n1crack]

Hi again,

How can I escape special column names, like -- order, default etc?

• It should be fixed now. you can try it like

$dt->query('select `default` from table')

As I am using Codeigniter4Adapter, I'd like to know whether it is possible to use CI Query Builder or plain queries only?

It is now avaiable. An example added to http://datatables.ozdemir.be.test/codeigniter4 even though it is not live.

You can update the library via "composer update" to see the changes.

About the usage of asterisks, I don't think it's practical to use.

[sargac]

Hi and thank you very much for the update!

I've just tested column escaping and CI4 query builder and both are working fine. Below is the code used.

// DB connections

$dt = new Datatables(new Codeigniter4Adapter);
$db = db_connect();

// plain query

$q = "select `default` from table";
$dt->query($q);
$dt->generate();

// CI4 query builder

$qb = $db->table('table');
$qb->select('`default`');
$q = $qb->get();
$dt->query($q);
$dt->generate();

Please feel free to close this issue, if you think the select * from doesn't belong here.

[n1crack]

No need to run the query with the get method. Just use the query builder instance.

$qb = $db->table('table');
$qb->select('`default`');
//$q = $qb->get();
//$dt->query($q);
$dt->query($qb);
$dt->generate();

[sargac]

Oh, thank you for the hint! That's even better!