SQL_LITE Support: Can't access wiki page, Internal Error: SQL error"

[Moanrisy]

Actual behaviour

When try to access wiki page 'http://localhost/kanban/?controller=WikiController&action=show&plugin=wiki&project_id=6'

, there is an error messgae

Internal Error: SQL error

Im sorry i can't produce specific error detail, can you tell me how to "Turning on debugging yields" ?

Expected behaviour

Show project Wiki

Open dashboard kanboard, open wiki project / Open project, open wiki from menu

Remove error
` /**
* list for wikipages
*/
public function show()
{
$project = $this->getProject();

    $this->response->html($this->helper->layout->project('wiki:wiki/show', array(
        'project' => $project,
        'title' => t('Wiki'),

#1 'wikipages' => $this->wiki->getWikipages($project['id']),
), 'wiki:wiki/sidebar'));

    // ,array(
    //     'wikipages' => $this->wiki->getWikipages($project['id'])
    // )
}

When im commend #1, Error message is gone and i can create new wiki (success, i check with sqlitebrowser the wiki created

im un comment #1, acccess wiki, error message come again

Configuration

Copy and paste the configuration section from the Kanboard settings page or supply values for these fields:

Application version: 1.2.0
PHP version: 7.2.2
PHP SAPI: apache2handler
OS version: Linux 4.15.3-1-ARCH
Database driver: sqlite
Database version: 3.22.0
Browser: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) QtWebEngine/5.10.0 Chrome/61.0.3163.140 Safari/537.36

[RoelNoorman]

I have the same issue.
Enabled debugging en log to file in config.php
got on screen:
Internal Error: SQL error: SQLSTATE[HY000]: General error: 1 near "order": syntax error
and in log:
...
[2018-02-20 10:56:31] [debug] SQL: SELECT c.name as creator_name, c.username as creator_username, mod.name as modifier_name, mod.username as modifier_username, wikipage.id, wikipage.title, wikipage.content, wikipage.project_id, wikipage.is_active, wikipage.order, wikipage.creator_id, wikipage.date_creation, wikipage.date_modification, wikipage.editions, wikipage.current_edition, wikipage.modifier_id FROM "wikipage" LEFT JOIN "users" AS "c" ON "c"."id"="wikipage"."creator_id" LEFT JOIN "users" AS "mod" ON "mod"."id"="wikipage"."modifier_id" WHERE "project_id" = ? ORDER BY "order" ASC
[2018-02-20 10:56:31] [debug] SQL: SQLSTATE[HY000]: General error: 1 near "order": syntax error

Is wikipage.order allowed as field name in Sqlite? Don't know but maybe it likes to have some quotes, since it contains a reserved word....

[RoelNoorman]

tried something....
in Wiki.getWikipages() in wiki.php, i replaced
self::WIKITABLE . '.order',
by
self::WIKITABLE . '".order"',
That worked. Of course, it is not the right solution since escaping depends on the database used
After looking at Database.escapeIdentifier(), i also tried just
'order',
That worked as well, but the table name was not added in SQL.
Would this be suitable?
$this->db->escapeIdentifier('order', self::WIKITABLE),

[lastlink]

This was heavily tested using mysql & I verified that the sqlite & postgress scripts were valid, I'll create a sqlite instance to test it further and fix this bug.

[RoelNoorman]

i think there are situations where escaping is omitted. but as long as the database engine accepts the SQL, it is difficult to test. good luck

[lastlink]

next version will have this fix, you can download it from the releases and unzip in your plugins folder or wait for the kanboard website to accept my change/pull request and update the version. I tested it on postgres, sqlite & mysql, v0.2.3 should solve this.

[lastlink]

I just renamed the order column, sqlite worked by giving the escape e.g. 'order', but I was concerned on the escape character being different for the other db systems. The database engine used may need to be updated to support this & not my script.

[Moanrisy]

Thank you so much :D
last commit working so good.

[RoelNoorman]

@lastlink From what i have seen, the PicoDB system hás a mechanism for escaping identifiers and it seems to work fine, also for Sqlite. But it explicitly skips escaping if the identifier is in the form of tablename.fieldname . Obviously, there must be a reason for this and likely a preferred way of using the system in such a way that still all identifiers are automatically escaped by PicoDb (and, indeed, not by you code)
Of course, it is not my business, but i have the feeling that this is not a bug but a matter of convention.
But i agree with you, if you can avoid problems by choosing unambiguous names, it is always nice.
Thanks for updating the code, we can wiki!