Details
-
Bug
-
Status: Closed (View Workflow)
-
Resolution: Invalid
-
3.0.3
-
None
-
None
-
PHP Environment: 4.4.8
Database: MySQL 4.1.22
Description
Your board's URL: http://orb.uk.net/forums
Version of phpBB3: 3.0.3
Was this a fresh install or a
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
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).
<?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):
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:
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.