Author Topic: Problems on Windows with MSSQL  (Read 6329 times)

Offline SMBiker

  • Jr. Member
  • **
  • Posts: 11
Problems on Windows with MSSQL
« on: May 26, 2020, 01:35:13 PM »
I am having issues running the newest release of Roundcube on my Windows server with MSSQL 2012 as a backend. It ran version 1.3.4 with no issues at all. It is running under PHP 7.2.24.

The problem is this: anytime Roundcube attempts to write something to the database, it reports an error back to the user. For instance, the first time a user logs into Roundcube, it reports that it can't create the user. Or, when you try to add a contact, it tells you that it failed. However...it doesn't fail, the SQL Insert actually succeeds - the row is inserted into the SQL database. What I suspect is that the rcube_db.affected_rows function is always reporting back zero, so that RoundCube thinks the insert failed, when it actually succeeded.

For instance, when a user tries to log in for the first time, Roundcube reports "Login failed" to the user, and the error log reports:

[May/25/2020 13:12:48 -0400]: <l61ucvcr> PHP Error: Failed to create new user in D:\inetpub\wwwroot\RoundCube\program\lib\Roundcube\rcube_user.php on line 703 (POST /?_task=login&_action=login)
[May/25/2020 13:12:48 -0400]: <l61ucvcr> PHP Error: Failed to create a user record. Maybe aborted by a plugin? in D:\inetpub\wwwroot\Roundcube\program\include\rcmail.php on line 652 (POST /?_task=login&_action=login)

Despite these reports, the user record did actually get created in the SQL table. So if they try to log in again using the exact same credentials, the login then succeeds.

This is a bone stock Roundcube install, fresh out of the box, no other plugins.

Doing a little bit of debugging, it appears to be that MSSQL does not return the inserted row as the resultset, at least by default. Looking at line 639 of /program/lib/Roundcube/rcube_user.php:

Code: [Select]
        if ($dbh->affected_rows($insert) && ($user_id = $dbh->insert_id('users'))) {
            // create rcube_user instance to make plugin hooks work
            $user_instance = new rcube_user($user_id, array(

My debugging shows that while the row is created successfully, $dbh->insert_id('users') contains nothing, and therefore Roundcube thinks the insert failed. It appears to be the case for EVERY insert function within Roundcube. I will admit that PHP is very far from my language of choice.

I suspect that nobody actually tested this version of Roundcube under MSSQL? I can't be the only one experiencing this.

Offline alec

  • Hero Member
  • *****
  • Posts: 1,363
Re: Problems on Windows with MSSQL
« Reply #1 on: May 26, 2020, 02:13:36 PM »
What driver are you using? Try using "sqlsrv:" prefix in db_dsn*.

Offline SMBiker

  • Jr. Member
  • **
  • Posts: 11
Re: Problems on Windows with MSSQL
« Reply #2 on: May 26, 2020, 03:31:43 PM »
What driver are you using? Try using "sqlsrv:" prefix in db_dsn*.

That is the prefix I'm using, so it's using the PDO sqlsrv driver.

My old mail server, running the same OS, SQL Server version and PHP version (but version 1.3.4) also used the exact same SQL driver with no problems.

Looking at the 1.3.4 code, it too pulls back the ID in the exact same way (the code is identical), so unless there's something different in underlying DB abstraction code, I'm not sure what might be different.

Offline SMBiker

  • Jr. Member
  • **
  • Posts: 11
Re: Problems on Windows with MSSQL
« Reply #3 on: May 27, 2020, 10:16:36 PM »
OK, it didn't look like I was going to get a response to this problem, so I did my own debugging and research, and found the problem.

The issue was with the PDO MSSQL lastInsertId() function. Up until version 4.3 of the PHP PDO driver for MSSQL, the lastInsertId function was passed a table name.

Starting with version 5.0, this function was instead expecting a sequence name. If you passed it no parameter, it looked up the non-nullable identity column of the affected table and returned that ID instead.

The problem is that RoundCube is passing it a table name, but PDO MSSQL interprets this as a sequence name instead - and the database is not constructed with sequences (at least not for MSSQL).

To fix the problem, I added an overriding function to /program/lib/Roundcube/db/mssql.php:

Code: [Select]
    /**
     * Get last inserted record ID
     *
     * @param string $table Table name (unused)
     *
     * @return mixed ID or false on failure
     */
    public function insert_id($table = null)
    {
        if (!$this->db_connected || $this->db_mode == 'r') {
            return false;
        }

        $id = $this->dbh->lastInsertId();

        return $id;
    }

This accepts any parameter being passed to it, throws that parameter away, and instead calls the lastInsertId function with no parameter, which returns the correct ID.

Once I added this code, the problem was fixed.

This should probably get added into the mainline code for MSSQL.

Offline alec

  • Hero Member
  • *****
  • Posts: 1,363
Re: Problems on Windows with MSSQL
« Reply #4 on: May 28, 2020, 01:32:32 AM »
Good work! I think the overriding method should be added to sqlsrv.php driver file, not the other one. Would you create a pull request on https://github.com/roundcube/roundcubemail/ ?

Offline alec

  • Hero Member
  • *****
  • Posts: 1,363