Active_Record Is Changing Case of Table Names

[evermorian]

Upgrading from Adodb 5.20.16 to 5.21.3, I have run into an issue where ADODB_Active_Record is changing the case of the table name and field names to all-caps from provided lowercase in update and insert queries (select seems to be fine). In my case, this has caused previously-working code to start throwing errors about tables not existing (e.g., it is saying CLIENTS doesn’t exist when asked to update data in clients).

While I understand that there are server settings for making MySQL case-insensitive, ADODB should not be changing the case of provided table names or fields.

From the setup:

class Client extends ADOdb_Active_Record {

    var $_table = 'clients';
    …
 }

From the traceback:

INSERT INTO CLIENTS (…) VALUES (…) ) ) … Table ‘redacted_db_name.CLIENTS' doesn't exist

[mnewnham]

What is the value of $ADODB_QUOTE_FIELDNAMES ?. Please also review #610

[evermorian]

The value of $ADODB_QUOTE_FIELDNAMES in my executing code is NULL.

In the documentation, I see where there are five allowed values for $ADODB_QUOTE_FIELDNAMES – true, false, UPPER, LOWER and, NATIVE. As an aside and, possible other issue, I see that line 708 of adodb-lib.inc.php conditionally assigns an undocumented sixth value, BRACKETS.

I do see that setting it to NATIVE or LOWER causes ADODB to revert to the expected behavior. So, that is a usable workaround.

Here is minimal code to repro:

<?php

$server = 'localhost';
$user = ‘myuser’;
$password = ‘reallygoodpassword;
$database = ‘mydatabase’;
$driver ='mysqli';

#define('ADODB_ASSOC_CASE', ADODB_ASSOC_CASE_UPPER);
#define('ADODB_ASSOC_CASE', ADODB_ASSOC_CASE_LOWER);
define('ADODB_ASSOC_CASE', ADODB_ASSOC_CASE_NATIVE);

#$ADODB_QUOTE_FIELDNAMES = 'NATIVE';

include('adodb5/adodb.inc.php');
include("adodb5/adodb-exceptions.inc.php");
require_once('adodb5/adodb-active-record.inc.php');

$db = NewADOConnection("mysqli://$user:$password@localhost/$database?persist");
$db->debug = false;

$sql = "SELECT * FROM clients WHERE id = '228'";
$rs = $db->Execute($sql);
$client_data = $rs->GetRowAssoc(false);
echo "client_name: " . $client_data['client_name'] . "<br>";
echo "client_cell_phone_number: " . $client_data['client_cell_phone_number'] . "<br>";
echo "id: " . $client_data['id'] . "<br>";

ADOdb_Active_Record::SetDatabaseAdapter($db);

class Client extends ADOdb_Active_Record {

    var $_table = 'clients';

}

$client = new client();
$client->load("id=228");
$client_data = $rs->GetRowAssoc(false);
echo "client_name: " . $client->client_name . "<br>";

ADOdb_Active_Record munges the case of the query to uppercase table and field names regardless of the setting of ADODB_ASSOC_CASE and, throws errors:

Warning: Use of undefined constant ADODB_ASSOC_CASE_NATIVE - assumed 'ADODB_ASSOC_CASE_NATIVE' (this will throw an Error in a future version of PHP) in /junk.php on line 11
client_name: Devlyn Syde
client_cell_phone_number: 555-555-5557
id: 228

Fatal error: Uncaught ADODB_Exception: mysqli error: [1146: Table 'mydatabase.CLIENTS' doesn't exist] in EXECUTE("SELECT * FROM CLIENTS WHERE id=228") in /phpinclude/adodb5/adodb-exceptions.inc.php:83 Stack trace: #0 /phpinclude/adodb5/adodb.inc.php(1332): adodb_throw('mysqli', 'EXECUTE', 1146, 'Table ‘clients…’, 'SELECT * FROM C...', false, Object(ADODB_mysqli)) #1 /phpinclude/adodb5/adodb.inc.php(1295): ADOConnection->_Execute('SELECT * FROM C...', false) #2 /phpinclude/adodb5/adodb.inc.php(2046): ADOConnection->Execute('SELECT * FROM C...', false) #3 /phpinclude/adodb5/adodb-active-record.inc.php(810): ADOConnection->GetRow('SELECT * FROM C...', false) #4 /junk.php(37): ADODB_Active_Record->Load('id=228') #5 {main} thrown in /phpinclude/adodb5/adodb-exceptions.inc.php on line 83

Uncommenting the $ADODB_QUOTE_FIELDNAMES = 'NATIVE' line causes it to revert to the expected behavior. Also of note here is that setting $ADODB_QUOTE_FIELDNAMES to true does not work.

[dregad]

line 708 of adodb-lib.inc.php conditionally assigns an undocumented sixth value, BRACKETS.

BRACKETS was introduced in #246, which introduced a regression, see #721.

This quoting / case converting of fields is a bit of a mess at the moment, due to the fact that a $ADODB_QUOTE_FIELDNAMES (badly) attempts to control both. Fixing that is tracked in #745.

[dregad]

I do see that setting it to NATIVE or LOWER causes ADODB to revert to the expected behavior. So, that is a usable workaround.

After analysis, I believe that for the time being this is actually the only available workaround (until #745 is done), as I don't want to mess with changing default behavior at the risk of introducing other regressions.