The BINARY operator has been deprecated in MySQL 8.0.27 [1].

This is used in Sessions management to force a case-sensitive comparison on the Session key.

I noticed this while analyzing #941 (see https://github.com/ADOdb/ADOdb/issues/941#issuecomment-1481638054).

Great ! Then I can apply that as a fix for now. Could you also confirm with the CAST() ?

I'll remove the BINARY cast then.

I'm having second thoughts about this.

Technically, a PHP session id can have characters matching [A-Za-z0-9-,], so we could in theory have multiple ids differing only by case, which would be a problem with a non-binary (case insensitive) comparison, e.g. SELECT * FROM sessions2 WHERE sesskey = 'ab2' would match session 'Ab2'.

Of course there can be only one ab2 session stored in the table (regardless of case) due to the primary key constraint on sesskey column, but this could nevertheless cause the handler to return data from someone else's session.

So if we were to remove the BINARY cast, we would need the sesskey column to have a binary collation (e.g. utf8_bin). This would break backwards compatibility, requiring all ADOdb Sessions users to update their sessions2 table's structure.

Alternatively, if we apply the cast to the expression's right side, I believe the index would apply.

@mariobernheim could you please test with

UPDATE sessions2 SET expiry = NOW() + INTERVAL 23328000 SECOND ,expireref='', modified = NOW() WHERE sesskey = /*! BINARY */ 'cbadsafveiim47emnj0mhi17qk' AND expiry >= NOW()

And also, for MySQL 8.0.27+ compatibility

UPDATE sessions2 SET expiry = NOW() + INTERVAL 23328000 SECOND ,expireref='', modified = NOW() WHERE sesskey = CAST('cbadsafveiim47emnj0mhi17qk' AS BINARY) AND expiry >= NOW()

Great, thanks for the feedback. I'll remove the BINARY cast then.

Thanks for your feedback. I'm aware of all that and rest assured I'm trying my best to fix this as soon as I can.

Could you confirm that your sessions2 table matches the definition in the documentation

If I create the table as described in the documentation, and populate it with a large number of rows, I'm getting a full index (not table) scan

+----+-------------+-----------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
|  1 | UPDATE      | sessions2 | NULL       | index | sess2_expiry  | PRIMARY | 258     | NULL | 288429 |   100.00 | Using where |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+

I do get a full table scan, if I drop all indexes

+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | UPDATE      | sessions2 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 282982 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+

On my dev box, with 280k test rows, actually executing the update statement takes 0.43 sec with indexes, and 0.47 without, so only marginally worse, and a half second for a simple update is definitely too much.

So I'm thinking the problem may be caused by the BINARY cast; if I remove it:

mysql> explain UPDATE sessions2 SET expiry = NOW() + INTERVAL 23328000 SECOND ,expireref='', modified = NOW() WHERE sesskey = 'cbadsafveiim47emnj0mhi17qk' AND expir
y >= NOW();
+----+-------------+-----------+------------+-------+----------------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys        | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+----------------------+---------+---------+-------+------+----------+-------------+
|  1 | UPDATE      | sessions2 | NULL       | range | PRIMARY,sess2_expiry | PRIMARY | 258     | const |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+-------+----------------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.02 sec)

Considering that the BINARY operator is deprecated in MySQL 8.0 [1], I'm thinking we might remove it... we're comparing character data, so I'm not sure it's actually necessary anyway.

Could you please test on your end ?

Fix markdown syntax in bug report template

@ProfCyberNaught If you see a fixed Issue in resolved state on our tracker, it means not yet released (Status changes to closed when we cut the release). You can also see from the Changelog that 2.25.7 is not yet released. That will happen at some point but I currently have other, more pressing priorities.

As for PHP 8 support, you make a valid point but again, I do this in my spare time and sadly I do not have the bandwidth to finalize a (long-overdue) 2.26.0 release at the moment. But it will happen eventually.

MantisBT 2.25 should work fine on 8.0 but not 8.1+; 2.26.0-dev nightly build should work on 8.2, feel free to try and report any incompatibility issues you find on our tracker.

Coding guidelines

Remove code blocks commented out since 2008

Fix #877: remove obsolete/dead code

Update Changelog

ADODB_FETCH_DEFAULT should be treated as ASSOC

Ensures that the recordset is returned in the default provided by the PHP driver as per the documentation.

Fixes #886

Bump version to 5.22.5-dev

Merge branch 'hotfix/5.22'

Conflicts:

adodb.inc.php

docs/changelog.md

Use IF NOT EXISTS when adding a new column

Executing a schema update that adds a column more than once will generate a column already exists error.

This prevents the error by appending IF NOT EXISTS to the generated SQL on PostgreSQL 9.6 or later.

Fixes #897

Signed-off-by: Damien Regad dregad@mantisbt.org

Reworded commit message

Improve autoExecute() PHPDoc, fix $where param type

$where was wrongly typed as bool. Changed it to string and updated the default value from false to ''.

Fixes #915

PHP 8.2 fixes for PostgreSQL driver

Fixes #913 (PR #920)

PHP 8.2 fixes for PostgreSQL driver

Fixes #913 (PR #920)

(cherry picked from commit 14a4a5a47805f152f56205c1ab4ae9ce98de5ad1)

Merge branch 'hotfix/5.22'

Fix PHP 8.2 compatibility issues for PDO driver

Creation of dynamic property is deprecated

• Line 605 - ADODB_pdo::$_stmt
• Line 788 - ADORecordSet_pdo::$adodbFetchMode

PR #917

Signed-off-by: Damien Regad dregad@mantisbt.org

Update Changelog

Merge branch 'hotfix/5.22'

912 sqlite deprecated creation of dynamic property adofieldobject%24scale is deprecated in driversadodb sqlite3incphp on line 194 (#931)

• Fixed Partially-supported callable are deprecated in PHP 8.2

• Fix deprecated undefined property #912 #911 #920 #923

Co-authored-by: raortegar raquel.ortega@moodle.com

Update SECURITY.md

Fix broken link to private chat following Gitter migration to Matrix.

Add Bug Report issue template (#921)

New issues will have label triage.

Revert commit fa572e96d20ac233e9dfc2d8f770234475381107

Nothing wrong with the code changes, but the commit message is bit of a mess, so I'll re-apply it after rewording.

Fix PHP 8.2 deprecation warnings

Creation of dynamic property

• ADORecordset_sqlite3::$adodbFetchMode in adodb-sqlite3.inc.php (#911)
• ADOFieldObject::$scale in adodb-sqlite3.inc.php (#912)

Partially-supported callable

• in adodb-pdo-inc.php (#928)

This is a re-do of fa572e96d20ac233e9dfc2d8f770234475381107 (PR #921) with an improved commit message.

Co-authored-by: raortegar raquel.ortega@moodle.com Signed-off-by: Damien Regad dregad@mantisbt.org

At this time, ending support for PHP 7.3 and older is only "on paper", i.e. it is not actually enforced by Composer and the code base does not yet make use of any language features available in later releases.

The library could therefore technically still be used on older PHP versions, but this is considered deprecated and not supported by the project team.

See related #797 for end of PHP 5.x support.

Drop support for PHP < 7.4 (#868)

@castor4bit many thanks for your contribution, and apologies for taking so long to merge it.

Fix #880

Problems summary

Pull Request #706 forces PDO bind parameter to be numeric array, but this makes named parameters unusable and some applications may not work.

Issue #705 says that the PDO driver requires numeric array, but in fact it seems that it is only required when the SQL contains a question mark. The documentation also says that named parameters are available.

Expected

ADOdb with PDO driver allows named parameters. (It means that I can execute SQL like in this comment)

Environment Information

(This is the environment I tested)

• PHP 7.4 / 8.0 (with PDO MySQL driver)
• MySQL 5.7 / 8.0
• ADOdb 5.22.3
  • probably occurs in 5.21.1 and later

How to reproduce the problem

• Here is a sample code showing that PHP's PDO Driver can use named parameters.
  • https://github.com/castor4bit/pdo-driver-allows-named-parameters
  • Tests using PDO directly pass, fail with ADOdb

PDO bind support both '?'-style and named parameters

• bind parameter conversion behavior is selectable with
  $bindParameterStyle property and associated BIND_USE_* constants
• default style is BIND_USE_BOTH
• outputs a message in debug mode to warn about performance

Fixes #880

Thanks for the feedback. Was it the pro version that proposed the fixes, or did you code them by hand ?

When calling ADODB_postgres64::MetaIndexes() on a table that has an index having an expression column, a PHP warning is thrown:

Warning: Undefined array key 0 in .../adodb/drivers/adodb-postgres64.inc.php on line 666

This can be reproduced as follows:

CREATE TABLE test (
    id integer NOT NULL,
    name character varying(100)
);
ALTER TABLE ONLY test
    ADD CONSTRAINT test_pkey PRIMARY KEY (id);

CREATE UNIQUE INDEX name_idx ON test USING btree (name);
CREATE INDEX test_lower_name_idx ON test USING btree (lower((name)::text));
CREATE INDEX test_2colcalc_idx ON test USING btree (id, lower((name)::text));

$db = ADONewConnection('pgsql');
$db->connect('localhost', $username, $password, $database);
var_export($db->metaindexes('test'));

// Warning: Undefined array key 0 in .../adodb/drivers/adodb-postgres64.inc.php on line 694

Problem was initially reported by @Unifex in PR #930, root cause analysis in https://github.com/ADOdb/ADOdb/pull/930#issuecomment-1475768749. Opening a new issue to follow-up, as the proposed fix is not satisfactory.

Fix PHP warning in ADODB_postgres64::MetaIndexes()

When calling the function on a table having an index on an expression column, a PHP warning is thrown:

Undefined array key 0 in ./drivers/adodb-postgres64.inc.php on line 666

Fixes #940

Closing this as won't fix - follow-up in #940.

When running our behat tests we are seeing a PHP Notice that we tracked down to this location.

It appears that $col_names is an array and it is not zero-indexed. We appear to have a $row of the form:

0: "behat_usr_fir_ix"
1: "f"
2: "0"

In this case $row[2] is just a "0" so the first $col is "0" and there is no $col_names[0], hence the notice.

I admit that I'm not up to speed with what is happening here, and I don't know how or why this is coming about in the first place. But this fix doesn't appear to be breaking anything else and the scenario that this showed up in was one that starts with an empty database, builds the tables, does a lot of CRUD, and uninstalls the tables at the end.

When calling ADODB_postgres64::MetaIndexes() on a table that has an index having an expression column, a PHP warning is thrown:

Warning: Undefined array key 0 in .../adodb/drivers/adodb-postgres64.inc.php on line 666

This can be reproduced as follows:

CREATE TABLE test (
    id integer NOT NULL,
    name character varying(100)
);
ALTER TABLE ONLY test
    ADD CONSTRAINT test_pkey PRIMARY KEY (id);

CREATE UNIQUE INDEX name_idx ON test USING btree (name);
CREATE INDEX test_lower_name_idx ON test USING btree (lower((name)::text));
CREATE INDEX test_2colcalc_idx ON test USING btree (id, lower((name)::text));

$db = ADONewConnection('pgsql');
$db->connect('localhost', $username, $password, $database);
var_export($db->metaindexes('test'));

// Warning: Undefined array key 0 in .../adodb/drivers/adodb-postgres64.inc.php on line 694

Problem was initially reported by @Unifex in PR #930, root cause analysis in https://github.com/ADOdb/ADOdb/pull/930#issuecomment-1475768749. Opening a new issue to follow-up, as the proposed fix is not satisfactory.

OK, I was able to replicate the problem with the following

CREATE TABLE test (
    id integer NOT NULL,
    name character varying(100)
);
ALTER TABLE ONLY test
    ADD CONSTRAINT test_pkey PRIMARY KEY (id);

CREATE UNIQUE INDEX name_idx ON test USING btree (name);
CREATE INDEX test_lower_name_idx ON test USING btree (lower((name)::text));
CREATE INDEX test_2colcalc_idx ON test USING btree (id, lower((name)::text));

$db = ADONewConnection('pgsql');
$db->connect('localhost', $username, $password, $database);
var_export($db->metaindexes('test'));

// Warning: Undefined array key 0 in .../adodb/drivers/adodb-postgres64.inc.php on line 694

That said, I don't like the proposed fix, because MetaIndexed() entirely skips the index column if it does not match the table column, which could be confusing; IMO it would be more appropriate to set it to NULL so the caller knows that the index has an expression-based column.

@mnewnham OK if I merge this ? Target 5.23.0 ?

@ioigoume are you still working on this ?

@Unifex any chance you can provide feedback on this by tomorrow night ? I'm almost ready to cut 5.22.5 and this could go in if I have confirmation of the behavior's root cause

All issues raised in this PR have been addressed in separate commits.