SQL Error after autologin using authentication plugin

Discussion forum for MOD Writers regarding MOD Development.
Locked
nev7n
Registered User
Posts: 8
Joined: Tue Oct 28, 2008 1:41 pm

SQL Error after autologin using authentication plugin

Post by nev7n » Wed Dec 03, 2008 8:30 pm

Support Request Template
Your board's URL: http://orb.uk.net/forums
Version of phpBB3: 3.0.3
Was this a fresh install or a(n) update/upgrade/conversion (please be specific)?
Originally it was an upgrade from 3.0.2, but I reinstalled all the files fresh to ensure it wasn't one of the files corrupted
If update, what package(s) did you use?
N/A
Did you use an automated wizard provided by your host to install phpBB?
No
MODs you have installed:
I've installed a custom authentication plugin
When the problem started:
Trying to login via the custom authentication
Your level of expertise (be honest):
10 Years Software Development experience, I've been using phpbb for a number of years now

Hi,
I'm trying to seamlessly integrate phpbb into a site that has it's own authentication system. It was confusing users too much having to maintain separate logins for the site and the forum. I've written an authentication plugin based on the useful instructions here: http://wiki.phpbb.com/Authentication_plugins.

When I change the authentication method to use my plugin I get this SQL error after a succesful call to my autologin function:-
General Error
SQL ERROR [ mysql4 ]

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 'AND t.template_id = s.template_id AND c.theme_id = s.theme_i [1064]

SQL

SELECT s.style_id, t.template_storedb, t.template_path, t.template_id, t.bbcode_bitfield, t.template_inherits_id, t.template_inherit_path, c.theme_path, c.theme_name, c.theme_storedb, c.theme_id, i.imageset_path, i.imageset_id, i.imageset_name FROM phpbb_styles s, phpbb_styles_template t, phpbb_styles_theme c, phpbb_styles_imageset i WHERE s.style_id = AND t.template_id = s.template_id AND c.theme_id = s.theme_id AND i.imageset_id = s.imageset_id

BACKTRACE

FILE: includes/db/mysql.php
LINE: 174
CALL: dbal_mysql->sql_error()

FILE: includes/session.php
LINE: 1558
CALL: dbal_mysql->sql_query()

FILE: index.php
LINE: 26
CALL: user->setup()
If I hit F5 and refresh the page then the error dissapears and I'm logged in fine.

The error doesn't occur when I login manually via the usual phpbb login screen and login occurs using my authentication plugin's login method (rather than autologin).

I've done some debugging and the user row data I'm passing back from the login and autologin functions look correct and is identical between each (the login row being wrapped in an array with status code and error text as per the wiki page).

Code: Select all

<?php
	if (!defined('IN_PHPBB')) {
		exit;
	}

	function errlog($msg) {
		session_start();		
		$userid="<unknown>";
		if( isset($_SESSION['userid']))  $userid = $_SESSION['userid']; 
		$ip=$_SERVER['REMOTE_ADDR'];
		$message = "UID:$userid IP:$ip MSG:$msg";
	
		$subject = "$msg";
		$headers = "From: test@orb.uk.net\n" .
							 "Reply-To: xxx@xxx.net\n" .
							 "X-Mailer: PHP/" . phpversion(). "\n" .
							 "MIME-Version: 1.0\n" .
							 "Content-type: text/html; charset=iso-8859-1"; 	
		
		if( !mail("xxx@xxx.co.uk", $message, $message, $headers)) {
			echo $msg . "<br>";
		}
	}
	
	
	function login_orb( $login_username, $userpassword) {
		errlog( "login_orb called\n");
		global $db, $user;

		// do not allow empty password
		if (!$userpassword) {
			return array(
				'status'	=> LOGIN_BREAK,
				'error_msg'	=> 'NO_PASSWORD_SUPPLIED',
			);
		}

		// check empty userid
		if( $login_username=="") {
			return array(
				'status'	=> LOGIN_BREAK,
				'error_msg'	=> 'LOGIN_ERROR_USERNAME',
			);		
		}

		// check password
		$passwordCorrect = true;
		require(dirname(__FILE__) . '/../../../db.inc.php'); 
		// Check user exists and get their userid
		mysql_connect(localhost,$username,$password);
		@mysql_select_db($database) or die( "Unable to select database");
		$query = "SELECT * FROM user WHERE username='$login_username' AND active=true";
		$result=mysql_query($query);
		$num=mysql_numrows($result);
		if( $num==0) {
			// No user with that username	
			mysql_close();	
			return array(
				'status'	=> LOGIN_BREAK,
				'error_msg'	=> 'LOGIN_ERROR_USERNAME',
			);				
		}

		$userid = mysql_result( $result, 0, "userid");
		$dbpassword = mysql_result( $result, 0, "userpassword");		
		if( md5($userpassword)!=$dbpassword) {
			// Incorrect password	
			mysql_close();
			return array(
				'status'		=> LOGIN_ERROR_PASSWORD,
				'error_msg'		=> 'LOGIN_ERROR_PASSWORD',
				'user_row'		=> array('user_id' => ANONYMOUS),
			);			
		}		
		session_start(); 
		$_SESSION['userid']=$userid;
		$_SESSION['ip']=$_SERVER['REMOTE_ADDR'];
		mysql_close();
		
		// Lookup phpbb user
		$sql = 'SELECT user_id, username, user_password, user_passchg, user_email, user_type FROM ' . USERS_TABLE . " WHERE username_clean = '" . 
						$db->sql_escape(utf8_clean_string($login_username)) . "'";
		$result = $db->sql_query($sql);
		$row = $db->sql_fetchrow($result);
		$db->sql_freeresult($result);

		if ($row) {
			// User inactive...
			if ($row['user_type'] == USER_INACTIVE || $row['user_type'] == USER_IGNORE)	{
					return array(
						'status'		=> LOGIN_ERROR_ACTIVE,
						'error_msg'		=> 'ACTIVE_ERROR',
						'user_row'		=> $row,
					);
			}

			// Successful login... set user_login_attempts to zero...
			errlog( "sucessful login\n" . print_r( $row, true));
			return array(
				'status'		=> LOGIN_SUCCESS,
				'error_msg'		=> false,
				'user_row'		=> $row,
			);
		} else {
			// retrieve default group id
			$sql = 'SELECT group_id	FROM ' . GROUPS_TABLE . " WHERE group_name = '" . $db->sql_escape('REGISTERED') . "' AND group_type = " . GROUP_SPECIAL;
			$result = $db->sql_query($sql);
			$row = $db->sql_fetchrow($result);
			$db->sql_freeresult($result);

			if (!$row) {
				trigger_error('NO_GROUP');
			}

			// generate user account data
			$new_user_row = array(
				'username'		=> $login_username,
				'user_password'	=> phpbb_hash($userpassword),
				'user_email'	=> '', // !!! set email
				'group_id'		=> (int) $row['group_id'],
				'user_type'		=> USER_NORMAL,  // !!! set admin?
				'user_ip'		=> $user->ip,
			);

			// this is the user's first login so create an empty profile
			return array(
				'status'		=> LOGIN_SUCCESS_CREATE_PROFILE,
				'error_msg'		=> false,
				'user_row'		=> $new_user_row,
			);
		}		
	}

	function autologin_orb() {
		global $db, $user;

		errlog( "autologin_orb login called\n");
		// Check user is logged in by seeing if their userid is stored in their session
		session_start();		
		if( !isset($_SESSION['userid'])) 	return array();
		$userid = $_SESSION['userid']; 
		
		// Check user's IP has not changed since login, if it has suspect hacking attempt and log user out
		if( $_SESSION['ip']!=$_SERVER['REMOTE_ADDR'])  return array();

		// Get phpbb username from orb user record
		require(dirname(__FILE__) . '/../../../db.inc.php'); 		
		mysql_connect(localhost,$username,$password);
		@mysql_select_db($database) or die( "Unable to select database");
		$query="SELECT * FROM user WHERE userid=$userid";
		$result=mysql_query($query);
		$num=mysql_numrows($result);
		mysql_close();
		
		if($num==0)  return array();
		
		$login_username = mysql_result( $result, 0, 'username');
		if( $login_username=="") 	return array();		// Can't login if they haven't set username		
		
		// Lookup phpbb user
		$sql = 'SELECT user_id, username, user_password, user_passchg, user_email, user_type FROM ' . USERS_TABLE . " WHERE username_clean = '" . 
						$db->sql_escape(utf8_clean_string($login_username)) . "'";
		$result = $db->sql_query($sql);
		$row = $db->sql_fetchrow($result);
		$db->sql_freeresult($result);

		if ($row) {
			// User inactive...
			if ($row['user_type'] == USER_INACTIVE || $row['user_type'] == USER_IGNORE)	{
					return array();
			}
			errlog( "sucessful autologin\n" . print_r( $row, true));
			// Successful login...
			return $row;
		} else {					
		  // User doesn't exist in phpbb
			// retrieve default group id
			$sql = 'SELECT group_id	FROM ' . GROUPS_TABLE . " WHERE group_name = '" . $db->sql_escape('REGISTERED') . "' AND group_type = " . GROUP_SPECIAL;
			$result = $db->sql_query($sql);
			$row = $db->sql_fetchrow($result);
			$db->sql_freeresult($result);

			if (!$row) {
				trigger_error('NO_GROUP');
			}

			// generate user account data
			$new_user_row = array(
				'username'		=> $login_username,
				'user_password'	=> phpbb_hash($userpassword),
				'user_email'	=> '', // !!! set email
				'group_id'		=> (int) $row['group_id'],
				'user_type'		=> USER_NORMAL,  // !!! set admin?
				'user_ip'		=> $user->ip,
			);

			// this is the user's first login so create an empty profile
			return $new_user_row;
		}
	}
	
	function validate_session_orb( $user_row) {		
		// Check user is logged in by seeing if their userid is stored in their session
		session_start();		
		if( !isset($_SESSION['userid'])) {
			errlog( "validate_session_orb called, no user logged in\n");		
		 	return false;
		}
		errlog( "validate_session_orb called, user valid " . $_SESSION['userid'] . "\n");				

		return true;
	}
	
	function logout_orb( $user_row, $new_session) {
		errlog( "logout_orb called\n");	
		session_start();
		unset($_SESSION['userid']);
		session_unset();
		session_destroy(); 		

		header("Location: ../index.php");		
		exit();  // hack to prevent phpbb create a new guest session that breaks autologin next time around
	}

?>
Here's an user row as returned from my autologin and login functions (I've replaced username, some of hashed password and user email with x's for security):

Code: Select all

Array ( [user_id] => 34 [username] => xxxxx [user_password] => $H$7/8xxxxxxxxxxxxxxxxxxx8lfU/ [user_passchg] => 1205933761 [user_email] => xxx@xxx.co.uk [user_type] => 3 )
Final thing to note, googling for the SQL error message seems to return lots of other boards having showed similar errors. I partly based my authentication plugin on looking at the apache & ldap ones so it's possible they are suffering the same problems, though I have no way of telling if the ones I googled are using custom authentication or not. Here's the google query (I've put it in a code block because the url block didn't like the [1064] in the middle:

Code: Select all

http://www.google.co.uk/search?q=You+have+an+error+in+your+SQL+syntax%3B+check+the+manual+that+corresponds+to+your+MySQL+server+version+for+the+right+syntax+to+use+near+%27AND+t.template_id+%3D+s.template_id+AND+c.theme_id+%3D+s.theme_i+[1064]&hl=en&client=firefox-a&rls=org.mozilla:en-GB:official&hs=crc&filter=0
As far as I can see my authentication plugin is correctly constructed and returning correct values - any help much apprecriated.

Nevyn.
Last edited by stickerboy on Wed Dec 03, 2008 10:08 pm, edited 1 time in total.
Reason: Moved to MOD Writers from Support

User avatar
stevemaury
Support Team Member
Support Team Member
Posts: 48140
Joined: Thu Nov 02, 2006 12:21 am
Location: The U.P.
Name: Steve
Contact:

Re: SQL Error after autologin using authentication plugin

Post by stevemaury » Wed Dec 03, 2008 8:35 pm

Welcome to phpbb.

This forum is for the support of basic phpbb. What you are asking for is our support for a third-party modification, which is beyond the scope of this forum and the board as a whole.

The preferred method of integration is to have the other site use the phpbb system, as outlined here:

http://www.phpbb.com/kb/article/phpbb3- ... tegration/

Hope that helps.
For REALLY good and VERY inexpensive hosting CLICK HERE

All unsolicited PMs will be ignored.

nev7n
Registered User
Posts: 8
Joined: Tue Oct 28, 2008 1:41 pm

Re: SQL Error after autologin using authentication plugin

Post by nev7n » Wed Dec 03, 2008 8:55 pm

Hi Steve,
thanks for your prompt reply. The site had a pre-existing authentication method which has a number of security features I'd like to keep so the authentication plugin route looks the most attractive for this site. I'm pretty sure the problem is with phpbb, not my authentication plugin - I tried to demonstrate this as much as I could in my original post.
Sorry if this was the wrong place to get support for this problem - where am I best to go for help with this?

Nevyn.

User avatar
stevemaury
Support Team Member
Support Team Member
Posts: 48140
Joined: Thu Nov 02, 2006 12:21 am
Location: The U.P.
Name: Steve
Contact:

Re: SQL Error after autologin using authentication plugin

Post by stevemaury » Wed Dec 03, 2008 9:02 pm

First, I apologize in not realizing this was the phpbb wiki, so not technically third party. But I suggest you go to http://wiki.phpbb.com/Talk:Main_Page
For REALLY good and VERY inexpensive hosting CLICK HERE

All unsolicited PMs will be ignored.

nev7n
Registered User
Posts: 8
Joined: Tue Oct 28, 2008 1:41 pm

Re: SQL Error after autologin using authentication plugin

Post by nev7n » Wed Dec 03, 2008 9:26 pm

Thanks Steve - I've posted to the wiki talk page and raised an issue in bugtracker.

In case anyone else is following this thread here's the link to the bugtracker bug for reference:
http://www.phpbb.com/bugs/phpbb3/ticket ... t_id=38175

nev7n
Registered User
Posts: 8
Joined: Tue Oct 28, 2008 1:41 pm

Re: SQL Error after autologin using authentication plugin

Post by nev7n » Wed Dec 03, 2008 9:52 pm

The bug's been flagged as a support issue and the status text suggests I raise the issue here - seems like I'm running in circles a little with this.

If no-one here can shed any more light on this issue is it possible to re-assign the bug as a bug?

Thanks,
Nevyn.

User avatar
bantu
3.0 Release Manager
3.0 Release Manager
Posts: 2522
Joined: Mon Jul 10, 2006 9:58 pm
Name: Andreas Fischer
Contact:

Re: SQL Error after autologin using authentication plugin

Post by bantu » Wed Dec 03, 2008 10:02 pm

Your array seems to miss the style_id.

Regards,
bantu.

Edit: Hmm ... style_id seems not to be required in the array, but it seems to be missing somwhere else ... still investigating ...
Powered by Coffee

nev7n
Registered User
Posts: 8
Joined: Tue Oct 28, 2008 1:41 pm

Re: SQL Error after autologin using authentication plugin

Post by nev7n » Wed Dec 03, 2008 10:13 pm

Hi Bantu - thanks for investigating this.

Indeed the style_id seems to have got lost somehow but isn't part of the user row array that's returned.

Nevyn.

User avatar
bantu
3.0 Release Manager
3.0 Release Manager
Posts: 2522
Joined: Mon Jul 10, 2006 9:58 pm
Name: Andreas Fischer
Contact:

Re: SQL Error after autologin using authentication plugin

Post by bantu » Wed Dec 03, 2008 10:14 pm

As far as I know now it does not need to be part of that array. But user_style seems to be empty/missing in your user table. Could you double check if it's there? Did you do anything within the database manually?
Powered by Coffee

nev7n
Registered User
Posts: 8
Joined: Tue Oct 28, 2008 1:41 pm

Re: SQL Error after autologin using authentication plugin

Post by nev7n » Wed Dec 03, 2008 10:20 pm

Bantu,
user_style is either 1 or 2 for all users. I haven't manually made any changes to the database.

Regards,
Nevyn.

nev7n
Registered User
Posts: 8
Joined: Tue Oct 28, 2008 1:41 pm

Re: SQL Error after autologin using authentication plugin

Post by nev7n » Wed Dec 03, 2008 10:42 pm

I'm trying to trace back through the phpbb code to see why style might be empty but can't spot anything so far.

In the config table:
default_style=1
override_user_style=0 (false)

which looks reasonable

User avatar
naderman
Consultant
Consultant
Posts: 3735
Joined: Fri Aug 01, 2003 10:06 pm
Location: Berlin, Germany
Name: Nils Adermann
Contact:

Re: SQL Error after autologin using authentication plugin

Post by naderman » Wed Dec 03, 2008 10:44 pm

In the autologin function you have to return the entire user table column (use SELECT *). Authentication plugins are still not very well documented and the interface is not exactly clean or easy to use. I guess we have to improve in that area ;-)
I appreciate gifts from my Amazon wishlist.
naderman.de twitter: @naderman

nev7n
Registered User
Posts: 8
Joined: Tue Oct 28, 2008 1:41 pm

Re: SQL Error after autologin using authentication plugin

Post by nev7n » Wed Dec 03, 2008 10:58 pm

Thanks very much - that was indeed the problem. I've updated the wiki page accordingly.

I think the authentication plugins are a really nice solution for integration into site authentication/single sign on solutions.

Nevyn.

Locked

Return to “[3.0.x] MOD Writers Discussion”

Who is online

Users browsing this forum: Google [Bot] and 15 guests