phpwiki-talk

[Phpwiki-talk] DbAuthParams[is_member] causing Fatal Error on query

[Roy L. <rl...@bi...>]

PhpWiki v1.3.10

I'm attempting to configure wiki to allow authenticated and self-registered users with mysql 
as the backend. So far, I can login as the admin account...however attempting to signin with 
any other wiki word gives the following error:

Fatal PhpWiki Error

lib/WikiDB/backend/PearDB.php:778: Fatal[256]: wikidb_backend_mysql: fatal database 
error

    * DB Error: syntax error
    * (SELECT userid FROM wiki_user WHERE userid='$userid' AND group='$groupname' 
[nativecode=1064 ** You have an error in your SQL syntax. Check the manual that 
corresponds to your MySQL server version for the right syntax to use near 
'group='$groupname'' at line 1])


The mysql statement it's referring to, from DBAUTH_AUTH_IS_MEMBER, seems about as 
correct as possible. The mysql scheme didn't include for a "group" varchar in the table, so I 
added that along with other things to make it compatible, yet still I receive the error. I'm 
assuming that either $groupname or $userid contains perhaps a quote or something that 
would invalidate the string.

How do I track where the peardb error callback is being called from to debug?
Please note that everything else seems to work other than authentication
Also, the following are my config settings for authentication:

ALLOW_ANON_USER = true
ALLOW_ANON_EDIT = false
ALLOW_BOGO_LOGIN = false,
ALLOW_USER_PASSWORDS = true.
USER_AUTH_ORDER = "Db"
GROUP_METHOD = DB
DBAUTH_AUTH_CHECK = "SELECT passwd FROM wiki_user where userid='$userid'"
DBAUTH_AUTH_USER_EXISTS = "SELECT userid FROM wiki_user WHERE 
userid='$userid'"
DBAUTH_AUTH_CRYPT_METHOD = crypt
DBAUTH_AUTH_UPDATE = "UPDATE wiki_user SET passwd='$password' WHERE 
userid='$userid'"
DBAUTH_AUTH_CREATE = "INSERT INTO wiki_user SET 
passwd=PASSWORD('$password'),userid='$userid'"
DBAUTH_PREF_SELECT = "SELECT prefs FROM wiki_user WHERE userid='$userid'"
DBAUTH_IS_MEMBER = "SELECT userid FROM wiki_user WHERE userid='$userid' AND 
group='$groupname'"
DBAUTH_GROUP_MEMBERS = "SELECT userid FROM wiki_user WHERE 
group='$groupname'"
DBAUTH_USER_GROUPS = "SELECT group FROM wiki_user WHERE userid='$userid'"

-- Roy "Kylratix" Laurie

Re: [Phpwiki-talk] DbAuthParams[is_member] causing Fatal Error on query

[Reini U. <ru...@x-...>]

Roy Laurie schrieb:
> PhpWiki v1.3.10
> 
> I'm attempting to configure wiki to allow authenticated and self-registered users with mysql 
> as the backend. So far, I can login as the admin account...however attempting to signin with 
> any other wiki word gives the following error:
> 
> Fatal PhpWiki Error
> 
> lib/WikiDB/backend/PearDB.php:778: Fatal[256]: wikidb_backend_mysql: fatal database 
> error
> 
>     * DB Error: syntax error
>     * (SELECT userid FROM wiki_user WHERE userid='$userid' AND group='$groupname' 
> [nativecode=1064 ** You have an error in your SQL syntax. Check the manual that 
> corresponds to your MySQL server version for the right syntax to use near 
> 'group='$groupname'' at line 1])

The DBAUTH_AUTH_IS_MEMBER processing in 1.3.10 is still using the 
old-style quoting to replace the $userid and $groupname variables.
See lib/WikiGroup.php: GroupDB:GroupDB

You must use:
DBAUTH_AUTH_IS_MEMBER = 'SELECT userid FROM wiki_user WHERE 
userid="$userid" AND group="$groupname"'

or fix the str_replace function in GroupDB:GroupDB to use ' instead of "
This holds for all GroupDB sql statements.

DBAUTH_IS_MEMBER = 'SELECT userid FROM wiki_user WHERE userid="$userid" 
AND group="$groupname"'
DBAUTH_GROUP_MEMBERS = 'SELECT userid FROM wiki_user WHERE 
group="$groupname"'
DBAUTH_USER_GROUPS = 'SELECT group FROM wiki_user WHERE userid="$userid"'

The current CVS version uses a much better SQL pre-processor.

> The mysql statement it's referring to, from DBAUTH_AUTH_IS_MEMBER, seems about as 
> correct as possible. The mysql scheme didn't include for a "group" varchar in the table, so I 
> added that along with other things to make it compatible, yet still I receive the error. I'm 
> assuming that either $groupname or $userid contains perhaps a quote or something that 
> would invalidate the string.
> 
> How do I track where the peardb error callback is being called from to debug?
> Please note that everything else seems to work other than authentication
> Also, the following are my config settings for authentication:
> 
> ALLOW_ANON_USER = true
> ALLOW_ANON_EDIT = false
> ALLOW_BOGO_LOGIN = false,
> ALLOW_USER_PASSWORDS = true.
> USER_AUTH_ORDER = "Db"
> GROUP_METHOD = DB
> DBAUTH_AUTH_CHECK = "SELECT passwd FROM wiki_user where userid='$userid'"
> DBAUTH_AUTH_USER_EXISTS = "SELECT userid FROM wiki_user WHERE 
> userid='$userid'"
> DBAUTH_AUTH_CRYPT_METHOD = crypt
> DBAUTH_AUTH_UPDATE = "UPDATE wiki_user SET passwd='$password' WHERE 
> userid='$userid'"
> DBAUTH_AUTH_CREATE = "INSERT INTO wiki_user SET 
> passwd=PASSWORD('$password'),userid='$userid'"
> DBAUTH_PREF_SELECT = "SELECT prefs FROM wiki_user WHERE userid='$userid'"
> DBAUTH_IS_MEMBER = "SELECT userid FROM wiki_user WHERE userid='$userid' AND 
> group='$groupname'"
> DBAUTH_GROUP_MEMBERS = "SELECT userid FROM wiki_user WHERE 
> group='$groupname'"
> DBAUTH_USER_GROUPS = "SELECT group FROM wiki_user WHERE userid='$userid'"
> 
> -- Roy "Kylratix" Laurie

-- 
Reini Urban
http://xarch.tu-graz.ac.at/home/rurban/