Bug 32699

Summary: PCRE regression on PHP SQLite3
Product: Mageia Reporter: Peter Potrowl <peter017>
Component: RPM PackagesAssignee: Marc Krämer <mageia>
Status: NEW --- QA Contact:
Severity: normal    
Priority: Normal    
Version: 9   
Target Milestone: ---   
Hardware: All   
OS: Linux   
Whiteboard:
Source RPM: php-8.2.14-1.mga9.src.rpm CVE:
Status comment:
Attachments: PHP test file
SQLite3 DB test file

Description Peter Potrowl 2024-01-08 09:56:02 CET 
Description of problem:

When using the REGEXP function in a SQLite statement in a PHP script, I encounter the following error:

`SQLite3::prepare(): Unable to prepare statement: 1, no such function: REGEXP`

I am very surprised because this code has been working for years on previous Mageia versions and it broke when I upgraded to Mageia 9.

Also, on SQLiteBrowser, the same query works without error.

I looked at https://stackoverflow.com/questions/24037982/how-to-use-regexp-in-sqlite and I installed all *pcre* RPMs with no success.

I currently don't have a /usr/lib/sqlite3/pcre.so file on my system. I could not identify which package is supposed to provide for it.

Is it possible that the SQLite3 PHP module is now compiled without PCRE support?


Version-Release number of selected component (if applicable):
php-sqlite3-8.2.14-1.mga9.x86_64

How reproducible:
always

Steps to Reproduce:
1. Download the attached test database and PHP script
2. Open SQLiteBrowser and run "SELECT * FROM mytable WHERE myfield REGEXP 'a'", it works fine
3. Run `php test.php`, observe the error: "PHP Warning:  SQLite3::prepare(): Unable to prepare statement: 1, no such function: REGEXP"


test.db :
CREATE TABLE "mytable" (
	"myfield"	TEXT
);

test.php :
<?php

$db = new SQLite3('test.db');
$db->prepare("SELECT * FROM mytable WHERE myfield REGEXP 'a'");
Comment 1 Peter Potrowl 2024-01-08 09:56:30 CET 
Created attachment 14257 [details]
PHP test file
Comment 2 Peter Potrowl 2024-01-08 09:56:49 CET 
Created attachment 14258 [details]
SQLite3 DB test file
katnatek 2024-01-08 20:25:49 CET

CC: (none) => mageia

Comment 3 katnatek 2024-01-08 20:26:36 CET 
Mark I consult you about this bug because you nurse php
Comment 4 Lewis Smith 2024-01-08 21:09:57 CET 
I think the package in question is 'php-sqlite3' which is in the main PHP SRPM.

To try this, I installed more & more packages - PHP, web server, SQLite - until it ran at last; as described:
 $ /usr/bin/php test.php
PHP Warning:  SQLite3::prepare(): Unable to prepare statement: 1, no such function: REGEXP in /home/lewis/tmp/test.php on line 4

Was unsure about the truncated PHP script, but it did not seem to matter.

I would assign this anyway to Marc; the best candidate.

Source RPM: (none) => php-8.2.14-1.mga9.src.rpm
Assignee: bugsquad => mageia
CC: mageia => (none)

Comment 5 Marc Krämer 2024-01-08 23:21:50 CET 
from the manual (https://www.sqlite.org/lang_expr.html#regexp):
"The REGEXP operator is a special syntax for the regexp() user function. No regexp() user function is defined by default and so use of the REGEXP operator will normally result in an error message. If an application-defined SQL function named "regexp" is added at run-time, then the "X REGEXP Y" operator will be implemented as a call to "regexp(Y,X)"."

As stated here:
https://stackoverflow.com/questions/5071601/how-do-i-use-regex-in-a-sqlite-query

a function has to be added, so this script runs without error (regexp function NOT verified):
<?php

$db = new SQLite3('test.db');
$db->createFunction('regexp',
    function ($pattern, $data, $delimiter = '~', $modifiers = 'isuS')
    {
        if (isset($pattern, $data) === true)
        {
            return (preg_match(sprintf('%1$s%2$s%1$s%3$s', $delimiter, $pattern, $modifiers), $data) > 0);
        }

        return null;
    }
);
$db->prepare("SELECT * FROM mytable WHERE myfield REGEXP 'a'");



I am not aware something in php-sqlite has changed, since I really don't use it.