[dgoette] 416: * fix problem with unicode nicknames * fix login with case sensitive names * fix registration(new database structure wasn't applied)

Author: dgoette
Date: Mon May 18 16:44:52 2009
New Revision: 416

URL: http://svn.reactos.org/svn/reactos?rev=416&view=rev
Log:
* fix problem with unicode nicknames
* fix login with case sensitive names
* fix registration(new database structure wasn't applied)

Modified:
    branches/danny-web/www/www.reactos.org/roscms/convert3to4.sql
    branches/danny-web/www/www.reactos.org/roscms/lib/view/HTML_User_Login.clas…
    branches/danny-web/www/www.reactos.org/roscms/lib/view/HTML_User_Profile.cl…
    branches/danny-web/www/www.reactos.org/roscms/lib/view/HTML_User_Register.c…

Modified: branches/danny-web/www/www.reactos.org/roscms/convert3to4.sql
URL:
http://svn.reactos.org/svn/reactos/branches/danny-web/www/www.reactos.org/r…
==============================================================================
--- branches/danny-web/www/www.reactos.org/roscms/convert3to4.sql [iso-8859-1] (original)
+++ branches/danny-web/www/www.reactos.org/roscms/convert3to4.sql [iso-8859-1] Mon May 18
16:44:52 2009
@@ -571,7 +571,7 @@
 -- --------------------------------------------------------
 CREATE TABLE roscms_accounts (
   id bigint(20) unsigned NOT NULL auto_increment,
-  name varchar(20) collate utf8_unicode_ci NOT NULL,
+  name varchar(20) collate utf8_bin NOT NULL,
   password varchar(32) collate utf8_unicode_ci NOT NULL COMMENT 'md5 encoded',
   email varchar(150) collate utf8_unicode_ci NOT NULL,
   lang_id bigint(20) unsigned COMMENT '->languages(id)',

Modified:
branches/danny-web/www/www.reactos.org/roscms/lib/view/HTML_User_Login.clas…
URL:
http://svn.reactos.org/svn/reactos/branches/danny-web/www/www.reactos.org/r…
==============================================================================
--- branches/danny-web/www/www.reactos.org/roscms/lib/view/HTML_User_Login.clas…
[iso-8859-1] (original)
+++ branches/danny-web/www/www.reactos.org/roscms/lib/view/HTML_User_Login.clas…
[iso-8859-1] Mon May 18 16:44:52 2009
@@ -112,7 +112,7 @@
       }
 
       // get user data
-      $stmt=&DBConnection::getInstance()->prepare("SELECT id, password,
logins, disabled, match_session FROM ".ROSCMST_USERS." WHERE name = :user_name
LIMIT 1");
+      $stmt=&DBConnection::getInstance()->prepare("SELECT id, password,
logins, disabled, match_session FROM ".ROSCMST_USERS." WHERE LOWER(name) =
LOWER(:user_name) LIMIT 1");
       $stmt->bindParam('user_name',$user_name,PDO::PARAM_STR);
       $stmt->execute() or die('DB error (user login #1)!');
       $user = $stmt->fetchOnce(); 

Modified:
branches/danny-web/www/www.reactos.org/roscms/lib/view/HTML_User_Profile.cl…
URL:
http://svn.reactos.org/svn/reactos/branches/danny-web/www/www.reactos.org/r…
==============================================================================
--- branches/danny-web/www/www.reactos.org/roscms/lib/view/HTML_User_Profile.cl…
[iso-8859-1] (original)
+++ branches/danny-web/www/www.reactos.org/roscms/lib/view/HTML_User_Profile.cl…
[iso-8859-1] Mon May 18 16:44:52 2009
@@ -56,14 +56,14 @@
     if ($this->search && empty($_GET['user_id'])) {
 
       if (isset($_GET['search'])) {
-        $stmt=&DBConnection::getInstance()->prepare("SELECT COUNT(*) FROM
".ROSCMST_USERS." WHERE name LIKE :nickname OR fullname LIKE :fullname");
+        $stmt=&DBConnection::getInstance()->prepare("SELECT COUNT(*) FROM
".ROSCMST_USERS." WHERE LOWER(name) LIKE LOWER(:nickname) OR fullname LIKE
:fullname");
        
$stmt->bindValue('nickname','%'.$_GET['search'].'%',PDO::PARAM_STR);
        
$stmt->bindValue('fullname','%'.$_GET['search'].'%',PDO::PARAM_STR);
         $stmt->execute();
         $users_found = $stmt->fetchColumn();
 
         if ($users_found == 1) {
-          $stmt=&DBConnection::getInstance()->prepare("SELECT id FROM
".ROSCMST_USERS." WHERE name LIKE :nickname OR fullname LIKE :fullname LIMIT
1");
+          $stmt=&DBConnection::getInstance()->prepare("SELECT id FROM
".ROSCMST_USERS." WHERE LOWER(name) LIKE LOWER(:nickname) OR fullname LIKE
:fullname LIMIT 1");
          
$stmt->bindValue('nickname','%'.$_GET['search'].'%',PDO::PARAM_STR);
          
$stmt->bindValue('fullname','%'.$_GET['search'].'%',PDO::PARAM_STR);
           $stmt->execute();
@@ -102,7 +102,7 @@
         if (isset($_GET['search']) && $_GET['search'] !=
'') {
           echo '<ul>';
 
-          $stmt=&DBConnection::getInstance()->prepare("SELECT name, fullname,
id FROM ".ROSCMST_USERS." WHERE name LIKE :nickname OR fullname LIKE :fullname
ORDER BY name ASC LIMIT 100");
+          $stmt=&DBConnection::getInstance()->prepare("SELECT name, fullname,
id FROM ".ROSCMST_USERS." WHERE LOWER(name) LIKE LOWER(:nickname) OR fullname
LIKE :fullname ORDER BY name ASC LIMIT 100");
          
$stmt->bindValue('nickname','%'.$_GET['search'].'%',PDO::PARAM_STR);
          
$stmt->bindValue('fullname','%'.$_GET['search'].'%',PDO::PARAM_STR);
           $stmt->execute();
@@ -120,7 +120,7 @@
       }
       else {
         if (empty($user_id) || $user_id === false) {
-          $stmt=&DBConnection::getInstance()->prepare("SELECT id FROM
".ROSCMST_USERS." WHERE name = :user_name LIMIT 1");
+          $stmt=&DBConnection::getInstance()->prepare("SELECT id FROM
".ROSCMST_USERS." WHERE LOWER(name) = LOWER(:user_name) LIMIT 1");
          
$stmt->bindParam('user_name',rawurldecode(@$_GET['user_name']));
           $stmt->execute();
           $user_id = $stmt->fetchColumn();

Modified:
branches/danny-web/www/www.reactos.org/roscms/lib/view/HTML_User_Register.c…
URL:
http://svn.reactos.org/svn/reactos/branches/danny-web/www/www.reactos.org/r…
==============================================================================
--- branches/danny-web/www/www.reactos.org/roscms/lib/view/HTML_User_Register.c…
[iso-8859-1] (original)
+++ branches/danny-web/www/www.reactos.org/roscms/lib/view/HTML_User_Register.c…
[iso-8859-1] Mon May 18 16:44:52 2009
@@ -69,10 +69,10 @@
             <div class="corner_TR"></div>
           </div>');
 
-    if (isset($_POST['registerpost']) && $_POST['username'] !=
"" && strlen($_POST['username']) >=
$config->limitUserNameMin()) {
+    if (isset($_POST['registerpost']) &&
isset($_POST['username']) &&
preg_match('/^[a-z0-9_\-[:space:]\.]{'.$config->limitUserNameMin().','.$config->limitUsernameMax().'}$/i'))
{
 
       // check if another account with the same username already exists
-      $stmt=&DBConnection::getInstance()->prepare("SELECT name FROM
".ROSCMST_USERS." WHERE REPLACE(name, '_', ' ') =
REPLACE(:username, '_', ' ') LIMIT 1");
+      $stmt=&DBConnection::getInstance()->prepare("SELECT name FROM
".ROSCMST_USERS." WHERE LOWER(REPLACE(name, '_', ' ')) =
LOWER(REPLACE(:username, '_', ' ')) LIMIT 1");
       $stmt->bindParam('username',$_POST['username'],PDO::PARAM_STR);
       $stmt->execute();
       $name_exists = ($stmt->fetchColumn() !== false);
@@ -84,17 +84,11 @@
 
       // name is not forbidden -> go on
       if ($stmt->fetchColumn() === false) {
-        if (isset($_POST['registerpost']) &&
isset($_POST['userpwd1']) && $_POST['userpwd1'] != ''
&& isset($_POST['userpwd2']) && $_POST['userpwd2'] !=
'' && $_POST['userpwd1'] == $_POST['userpwd2']) {
-          $stmt=&DBConnection::getInstance()->prepare("SELECT pwd_name FROM
user_unsafepwds WHERE pwd_name = :pwd_name LIMIT 1");
-         
$stmt->bindParam('pwd_name',$_POST['userpwd1'],PDO::PARAM_STR);
-          $stmt->execute();
-          $safepwd = ($stmt->fetchColumn() !== false);
-        }
 
         if (isset($_POST['registerpost']) &&
isset($_POST['useremail']) && $_POST['useremail'] != '')
{
 
           // check if another account with the same email address already exists
-          $stmt=&DBConnection::getInstance()->prepare("SELECT user_email FROM
users WHERE user_email = :email LIMIT 1");
+          $stmt=&DBConnection::getInstance()->prepare("SELECT email FROM
".ROSCMST_USERS." WHERE email = :email LIMIT 1");
          
$stmt->bindParam('email',$_POST['useremail'],PDO::PARAM_STR);
           $stmt->execute();
           
@@ -116,7 +110,7 @@
           $activation_code = substr($activation_code, 0, rand(10, 15));
 
           // add new account
-          $stmt=&DBConnection::getInstance()->prepare("INSERT INTO users (
user_name, user_roscms_password, user_register, user_register_activation, user_email,
user_language ) VALUES ( :user_name, MD5( :password ), NOW(), :activation_code, :email,
:lang )");
+          $stmt=&DBConnection::getInstance()->prepare("INSERT INTO
".ROSCMST_USERS." ( name, password, created, activation, email, language,
modified ) VALUES ( :user_name, MD5( :password ), NOW(), :activation_code, :email, :lang,
NOW() )");
          
$stmt->bindParam('user_name',$_POST['username'],PDO::PARAM_STR);
          
$stmt->bindParam('password',$_POST['userpwd1'],PDO::PARAM_STR);
          
$stmt->bindParam('activation_code',$activation_code,PDO::PARAM_STR);
@@ -124,13 +118,13 @@
           $stmt->bindParam('lang',$userlang,PDO::PARAM_STR);
           $stmt->execute();
 
-          $stmt=&DBConnection::getInstance()->prepare("SELECT user_id FROM
users WHERE user_name = :user_name ORDER BY user_id DESC LIMIT 1");
+          $stmt=&DBConnection::getInstance()->prepare("SELECT id FROM
".ROSCMST_USERS." WHERE LOWER(name) = LOWER(:user_name)");
          
$stmt->bindParam('user_name',$_POST['username'],PDO::PARAM_INT);
           $stmt->execute();
           $user_id = $stmt->fetchColumn();
 
           // give a 'user' group membership
-          $stmt=&DBConnection::getInstance()->prepare("INSERT INTO
usergroup_members (usergroupmember_userid, usergroupmember_usergroupid) VALUES (:user_id,
'user')");
+          $stmt=&DBConnection::getInstance()->prepare("INSERT INTO
".ROSCMST_MEMBERSHIPS." (user_id, group_id) SELECT :user_id, id FROM
".ROSCMST_GROUPS." WHERE name_short = 'user' LIMIT 1");
           $stmt->bindParam('user_id',$user_id,PDO::PARAM_INT);
           $stmt->execute();