php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #18172 Mysql_query function RLIKE and REGEXP
Submitted: 2002-07-04 23:08 UTC Modified: 2002-07-08 11:42 UTC
Votes:1
Avg. Score:5.0 ± 0.0
Reproduced:1 of 1 (100.0%)
Same Version:0 (0.0%)
Same OS:0 (0.0%)
From: r dot denis at bell dot ca Assigned:
Status: Not a bug Package: MySQL related
PHP Version: 4.2.1 OS: Freebsd/Linux
Private report: No CVE-ID: None
 [2002-07-04 23:08 UTC] r dot denis at bell dot ca
I am not certain what the issue is however,

When mysql_query is called in the following manner it fails 
to return all regexp occurances of the the given variable;

Both REGEXP and RLIKE fail!
$query2="select * from report WHERE users REGEXP '$zcntlogin'";
//$query2="select * from report WHERE users RLIKE '$zcntlogin'"; 

This is, or from my understanding should be the equivalent of connecting to the 
mysql server and executing the command:

select * from report WHERE users RLIKE 'firstname.lastname'; //which works!

In my script $zcntlogin is set through pam_smbd and mod_auth_external

<?php
  if (!isset($PHP_AUTH_USER) || !zcntlogin) {
                          header("WWW-Authenticate: Basic realm=\"Bell Domain\"");
                          header("HTTP/1.0 401 Unauthorized");
                          echo "Text to send if user hits Cancel button\n";
                          exit;
                        } else {

$zcntlogin =$PHP_AUTH_USER;

                        }

?>

Hopefully that helps.
Cheers,
Rob


Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2002-07-05 03:55 UTC] mfischer@php.net
In PHP 4.2.0, the 'register_globals' setting default changed to
be off. See http://www.php.net/release_4_2_0.php for more info.
We are sorry about the inconvenience, but this change was a necessary
part of our efforts to make PHP scripting more secure and portable.
 [2002-07-05 09:57 UTC] r dot denis at bell dot ca
# locate php.ini
/usr/local/etc/php.ini
/usr/local/etc/php.ini-dist
ruoc4k# grep -i register /usr/local/etc/php.ini
; This directive describes the order in which PHP registers GET, POST, Cookie,
; Whether or not to register the EGPCS variables as global variables.  You may
; register_globals to be on;  Using form variables as globals can easily lead
register_globals = on
register_argc_argv = On
; autoregister constants of a components typlib on com_load()
;com.autoregister_typelib = true
; register constants casesensitive
;com.autoregister_casesensitive = false
;com.autoregister_verbose = true

Register locals was turned on because of the way some of the data was being passed.
 [2002-07-05 10:18 UTC] sander@php.net
You're very likely to be messing up with quotation and/or other special characters. Print $query2 before sending it mysql to see what you're doing wrong.
Not a bug in PHP. For more, ask support on the appropriate places (see http://php.net/support for a list).
 [2002-07-05 10:36 UTC] r dot denis at bell dot ca
How hard is to write the following.
echo $query2;

It does not take a brain to figure out that both queries are 
identical.

I suspect that the issue might be in one of the php regex*.h files.

/usr/local/include/php/ext/mbstring/mbregex.h
/usr/local/include/php/main/php_regex.h
/usr/local/include/php/regex
/usr/local/include/php/regex/cclass.h
/usr/local/include/php/regex/cname.h
/usr/local/include/php/regex/regex.h
/usr/local/include/php/regex/regex2.h
/usr/local/include/php/regex/regex_extra.h
/usr/local/include/php/regex/utils.h

There is nothing wrong with the query.
I do not have to addslashes ie \"$zcntlogin\";
or should not have to add % to the query variable.

To further clarify the issue.  The query returns records,
however it does not return all of the records of the given 
userid zcntlogin.

Userids in this context is a list of space delineated userids.  Which is used to grant access to specific files.
RLIKE should work however it does not.
 [2002-07-05 11:49 UTC] r dot denis at bell dot ca
Here is a snippit of the actual code;
-----------------------------------------------
<?php
  if (!isset($PHP_AUTH_USER) || !zcntlogin) {
                          header("WWW-Authenticate: Basic realm=\"Bell Domain\"");
                          header("HTTP/1.0 401 Unauthorized");
                          exit;
                        } else {

$zcntlogin =$PHP_AUTH_USER;
//echo $zcntlogin;
                        }

?>
<?php
if ($zcntlogin) {
$id = "true";
$db  = mysql_connect("localhost", "foo")or exit("Could not connect");
$db1 = mysql_select_db("report",$db);
               ?>


<FORM>
<TABLE WIDTH=800>
<TR>
  <TD ALIGN="CENTER" COLSPAN=8><FONT SIZE=+2><B>Report Summary</B></FONT></TD>
</TR>
</TABLE>
<?php

$query2="select * from report WHERE users REGEXP '$zcntlogin'";
echo $query2;
$result2 = mysql_query($query2) or die(mysql_error());

                             for ($i = 0; $i < mysql_num_fields($result2); $i++) {

?>
<?php
if (($myrow2)){
$myrow2["file1"] = preg_replace("/\s/", "%20" , $myrow2["file1"]);
$records+=1;
?>
<TABLE>
<TR>
  <TD COLSPAN=4><sb><sb> <?php echo ($myrow2["file1"]) ? $myrow2["file1"] :"" ; ?><sb>
  <A HREF="../uploads/<?php echo $myrow2["file1"]; ?>">[expand]</A></TD>
</TR>
</TABLE>
<?php
}
?>
<?php
                             $myrow2=mysql_fetch_array($result2);
                             }
?>
<TABLE>
  <TD COLSPAN=4>Total Number of Records:<sb><sb> <?php echo $records ; ?><sb>
<TR>
  <TD COLSPAN=128><HR></TD>
</TR>
</TABLE>
<?php
mysql_close($db);
}
?>
------------------------------------------------------
 [2002-07-05 13:12 UTC] sniper@php.net
Make a SHORT example where you don't use any variables
but the exact same SQL line you tried with the mysql shell.

 [2002-07-05 13:22 UTC] r dot denis at bell dot ca
$query2="select * from report WHERE users REGEXP 'robert.denis'";
$query2="select * from report WHERE users RLIKE '%robert.denis'";
$query2="select * from report WHERE users RLIKE '%robert.denis%'";

Does not return everything containing "robert.denis"
//or $zcntlogin

4.5-RELEASE FreeBSD 4.5-RELEASE #0: Mon Jan 28 14:31:56 GMT 2002     murray@builder.freebsdmall.com:/usr/src/sys/compile/GENERIC  i386
 [2002-07-05 13:42 UTC] r dot denis at bell dot ca
# strings /usr/local/libexec/apache/libphp4.so | grep -i like

Call-time pass-by-reference has been deprecated - argument passed by value;  If you would like to pass it by reference, modify the declaration of %s().  If you would like to enable call-time pass-by-reference, you can set allow_call_time_pass_reference to true in your INI file.  

I tried for the fun of it to see whether or not the above had any bearing....appears not.
 [2002-07-05 13:46 UTC] sander@php.net
What does mysql_error() say about this query? Just call echo mysql_error() directly after the mysql_query() call.
 [2002-07-05 13:49 UTC] sander@php.net
Nevermind. You're just messing with the SQL syntax. Read MySQL's manual, especially the part about "String comparison functions"
Not a bug in PHP. 
 [2002-07-05 14:34 UTC] r dot denis at bell dot ca
In order for you to understand.  I am going to give you a more logical approch.

The mysql server
-----------------------------------------
mysql> connect report
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Connection id:    2887
Current database: report

mysql> select * from report WHERE users REGEXP 'robert.denis';
+----+---------------------------------------+--------------+------------+---------------------------------------------+
| id | file1                                 | sysstartdate | sysenddate | users                                       |
+----+---------------------------------------+--------------+------------+---------------------------------------------+
|  1 | 20020629.jdbgmgrE.htm                 | 2002-06-29   | 2002-06-29 | hbpritch robert.denis                       |
|  2 | 20020629.jdbgmgrE.htm                 | 2002-06-29   | 2002-06-29 | hbpritch robert.denis                       |
|  3 | 20020702.What is CyberArmor Suite.doc | 2002-07-02   | 2002-07-02 | benoit.joubert robert.denis scott.beauchamp |
|  4 | 20020702.jdbgmgrE.htm                 | 2002-07-02   | 2002-07-02 | robert.denis nancy.kolassa                  |
|  5 | 20020702.2002esrq1e.doc               | 2002-07-02   | 2002-07-02 | robert.denis melsegui                       |
|  6 | 20020702.putty.exe                    | 2002-07-02   | 2002-07-02 | robert.denis melsegui                       |
|  7 | 20020702.Rapport_5220111200 Theft.doc | 2002-07-02   | 2002-07-02 | robert.denis melsegui                       |
|  8 | 20020702.Maj-Eng logo elec final.dot  | 2002-07-02   | 2002-07-02 | robert.denis melsegui                       |
|  9 | 20020703.meeting4_map.pdf             | 2002-07-03   | 2002-07-03 | robert.denis robert.denis                   |
| 10 | 20020704.What is CyberArmor Suite.doc | 2002-07-04   | 2002-07-04 | robert.denis robert.denis                   |
| 11 | 20020704.What is CyberArmor Suite.doc | 2002-07-04   | 2002-07-04 | robert.denis robert.denis                   |
| 12 | 20020704.                             | 2002-07-04   | 2002-07-04 | robert.denis robert.denis                   |
| 13 | 20020704.jdbgmgrE.htm                 | 2002-07-04   | 2002-07-04 | robert.denis robert.denis                   |
| 14 | 20020704.jdbgmgrE.htm                 | 2002-07-04   | 2002-07-04 | robert.denis benoit.joubert                 |
| 15 | 20020704.jdbgmgrE.htm                 | 2002-07-04   | 2002-07-04 | robert.denis benoit.joubert                 |
| 16 | 20020704.jdbgmgrE.htm                 | 2002-07-04   | 2002-07-04 | robert.denis rdenis@ruoc4k.on.bell.ca       |
+----+---------------------------------------+--------------+------------+---------------------------------------------+
16 rows in set (0.00 sec)

---------------------------------
The apache webserver 
Server: Apache/1.3.26 (Unix) mod_ssl/2.8.10 OpenSSL/0.9.6a mod_perl/1.26 PHP/4.2.1

The php webpage
--------------------
Report Summary

select * from report WHERE users REGEXP 'robert.denis' 
 20020629.jdbgmgrE.htm [expand]

 20020629.jdbgmgrE.htm [expand]

 20020702.What%20is%20CyberArmor%20Suite.doc [expand]

 20020702.jdbgmgrE.htm [expand]

 Total Number of Records: 4 
------------------------------------------

Any ideas?  There is nothing with the implimentation of the POSIX regexp.  The same sort of thing can be done in almost all scripting languages.

--
Regards
Robert
 [2002-07-05 14:57 UTC] r dot denis at bell dot ca
.
 [2002-07-05 15:42 UTC] sniper@php.net
Try doing this:

$zcntlogin = preg_quote($zcntlogin);

From mysql manual:

"Note: Because MySQL uses the C escape syntax in strings (for example, `\n'), you must double any `\' that you use in your REGEXP strings"

a dot '.' is special..so you have to escape it.

If this didn't help either, please provide a SHORT but complete example script, max 5 lines, which does not work.



 [2002-07-05 15:46 UTC] sniper@php.net
forgot to ask..do you have any of those magic_quotes_*
directives set 'on' in php.ini?

 [2002-07-05 20:36 UTC] r dot denis at bell dot ca
Sniper,

I appreciate your assistance, 
The default install of FreeBSD turns on magic_quotes_*
It is a nice addition in my opinion.

It make things alot easier. Especially when you have peoples names like o\'brien It basically does all of the work for you.

I will look into the problem more Tomorrow or Monday.  However I do not see how preg_quote will have any bearing.
Especially since it Magic quotes takes care of the work.

instead of having robert.denis I would have "robert\.denis"
This does not explain why it returns some but not all.

Have a nice Weekend.
Cheers,
Robert
 [2002-07-07 22:30 UTC] sniper@php.net
let's keep this at 'feedback' status until we get the real feedback..

 [2002-07-08 11:37 UTC] r dot denis at bell dot ca
Sniper,
I appreciate everyones help.
I turns out that after carefully debugging /usr/local/libexec/apache/libphp4.so the regexp portion does not contain internal calls.  It was indeed a small glitch in my code.
the following line:
//for ($i = 0; $i < mysql_num_fields($result2); $i++) {
should have read:                             
for ($i = 0; $i <= mysql_num_rows($result2); $i++ ) {

OOPS!
Because of the many facilities that php provides
such as magic_quotes_* 

The use of additional functions such as pregquote is not required.

I am shocked that nobody picked on the fact that some of the data was returned but not all of the data.  The number of fields used in the database is 4 the number of records is the number of rows.

Have a great day
 [2002-07-08 11:42 UTC] sander@php.net
User error -> bogus
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Thu Mar 28 08:01:28 2024 UTC