Author Topic: Search for username in users table?  (Read 14817 times)

Offline aldspamacct

  • Newbie
  • *
  • Posts: 8
Search for username in users table?
« on: June 29, 2012, 07:27:14 PM »
Hello,

This is my first Roundcube deployment (v.0.7.2).  I am doing a migration of an old sendmail server with about 3000 accounts.  In the process I need to insert the users Addressbook information into Roundcube.  To insert the record I need the user_id from the users table.  In version .0.7.2 the username is encrypted so I can't do a simple query.  Can someone tell me how the username is stored?  Perhaps there's an API I can call I can use.  All of my provisioning and migration scripts are written in Ruby.  Any information you can provide would be very much appreciated!

Thank you,

td

Offline SKaero

  • Administrator
  • Hero Member
  • *****
  • Posts: 5,876
    • SKaero - Custom Roundcube development
Re: Search for username in users table?
« Reply #1 on: June 29, 2012, 07:39:32 PM »
I'm not sure I understand your problem, you should just be able to look up the email address from the username field in the users table.

Offline aldspamacct

  • Newbie
  • *
  • Posts: 8
Re: Search for username in users table?
« Reply #2 on: June 29, 2012, 07:50:35 PM »
Hi SKaero,

Here's what lies in my users table:

Code: [Select]
user_id username mail_host alias created last_login language preferences
1 746f6e796440657365646f6e612e6e6574 216.19.2.11 2012-06-29 13:49:14 2012-06-29 14:12:03 en_US NULL
2 6166746572676c6f7740657365646f6e612e6e6574 216.19.2.11 2012-06-29 14:42:30 2012-06-29 14:42:30 en_US NULL

td

Offline SKaero

  • Administrator
  • Hero Member
  • *****
  • Posts: 5,876
    • SKaero - Custom Roundcube development
Re: Search for username in users table?
« Reply #3 on: June 29, 2012, 09:03:34 PM »
Interesting and users can login? Do you have a special user authentication setup?

Offline aldspamacct

  • Newbie
  • *
  • Posts: 8
Re: Search for username in users table?
« Reply #4 on: June 30, 2012, 01:00:37 AM »
Std Roundcube installation.  No special authentication.  And yes, users can log in.  At this stage, the only users are test user as I finalize the migration scripts.  But the two test users added to vmail, then logged in via the Web GUI leaves the username as shown.

td

Offline SKaero

  • Administrator
  • Hero Member
  • *****
  • Posts: 5,876
    • SKaero - Custom Roundcube development
Re: Search for username in users table?
« Reply #5 on: June 30, 2012, 01:57:45 AM »
Huh I haven't seen that before, maybe someone else has a better idea why it occurring. Could you turn on imap_debug and post the imap login conversation?

Offline aldspamacct

  • Newbie
  • *
  • Posts: 8
Re: Search for username in users table?
« Reply #6 on: June 30, 2012, 09:51:08 AM »
Here is the output of my imap log after enable imap_debug

Code: [Select]
[30-Jun-2012 06:49:47 -0700]: [72E7] S: * OK [CAPABILITY IMAP4rev1 LITERAL+ SASL-IR LOGIN-REFERRALS ID ENABLE STARTTLS AUTH=PLAIN AUTH=LOGIN] Dovecot ready.
[30-Jun-2012 06:49:47 -0700]: [72E7] C: A0001 ID ("name" "Roundcube Webmail" "version" "0.7.2" "php" "5.3.2-1ubuntu4.17" "os" "Linux" "command" "/mail/")
[30-Jun-2012 06:49:47 -0700]: [72E7] S: * ID NIL
[30-Jun-2012 06:49:47 -0700]: [72E7] S: A0001 OK ID completed.
[30-Jun-2012 06:49:47 -0700]: [72E7] C: A0002 AUTHENTICATE PLAIN dG9ueWRAZXNlZG9uYS5uZXQAdG9ueWRAZXNlZG9uYS5uZXQAZG9kZ2VyOWJhbGw=
[30-Jun-2012 06:49:47 -0700]: [72E7] S: A0002 OK [CAPABILITY IMAP4rev1 LITERAL+ SASL-IR LOGIN-REFERRALS ID ENABLE SORT SORT=DISPLAY THREAD=REFERENCES THREAD=REFS MULTIAPPEND UNSELECT IDLE CHILDREN NAMESPACE UIDPLUS LIST-EXTENDED I18NLEVEL=1 CONDSTORE QRESYNC ESEARCH ESORT SEARCHRES WITHIN CONTEXT=SEARCH QUOTA] Logged in
[30-Jun-2012 06:49:47 -0700]: [72E7] C: A0003 NAMESPACE
[30-Jun-2012 06:49:47 -0700]: [72E7] S: * NAMESPACE (("" "/")) (("Shared/" "/")) NIL
[30-Jun-2012 06:49:47 -0700]: [72E7] S: A0003 OK Namespace completed.
[30-Jun-2012 06:49:47 -0700]: [72E7] C: A0004 LOGOUT
[30-Jun-2012 06:49:47 -0700]: [72E7] S: * BYE Logging out
[30-Jun-2012 06:49:47 -0700]: [72E7] S: A0004 OK Logout completed.
[30-Jun-2012 06:49:47 -0700]: [22CC] S: * OK [CAPABILITY IMAP4rev1 LITERAL+ SASL-IR LOGIN-REFERRALS ID ENABLE STARTTLS AUTH=PLAIN AUTH=LOGIN] Dovecot ready.
[30-Jun-2012 06:49:47 -0700]: [22CC] C: A0001 ID ("name" "Roundcube Webmail" "version" "0.7.2" "php" "5.3.2-1ubuntu4.17" "os" "Linux" "command" "/mail/?_task=mail")
[30-Jun-2012 06:49:47 -0700]: [22CC] S: * ID NIL
[30-Jun-2012 06:49:47 -0700]: [22CC] S: A0001 OK ID completed.
[30-Jun-2012 06:49:47 -0700]: [22CC] C: A0002 AUTHENTICATE PLAIN dG9ueWRAZXNlZG9uYS5uZXQAdG9ueWRAZXNlZG9uYS5uZXQAZG9kZ2VyOWJhbGw=
[30-Jun-2012 06:49:47 -0700]: [22CC] S: A0002 OK [CAPABILITY IMAP4rev1 LITERAL+ SASL-IR LOGIN-REFERRALS ID ENABLE SORT SORT=DISPLAY THREAD=REFERENCES THREAD=REFS MULTIAPPEND UNSELECT IDLE CHILDREN NAMESPACE UIDPLUS LIST-EXTENDED I18NLEVEL=1 CONDSTORE QRESYNC ESEARCH ESORT SEARCHRES WITHIN CONTEXT=SEARCH QUOTA] Logged in
[30-Jun-2012 06:49:47 -0700]: [22CC] C: A0003 LIST (SUBSCRIBED) "" "*"
[30-Jun-2012 06:49:47 -0700]: [22CC] S: * LIST (\Subscribed) "/" "INBOX"
[30-Jun-2012 06:49:47 -0700]: [22CC] S: * LIST (\Subscribed) "/" "Sent"
[30-Jun-2012 06:49:47 -0700]: [22CC] S: * LIST (\Subscribed) "/" "Trash"
[30-Jun-2012 06:49:47 -0700]: [22CC] S: * LIST (\Subscribed) "/" "Drafts"
[30-Jun-2012 06:49:47 -0700]: [22CC] S: * LIST (\Subscribed) "/" "Junk"
[30-Jun-2012 06:49:47 -0700]: [22CC] S: A0003 OK List completed.
[30-Jun-2012 06:49:47 -0700]: [22CC] C: A0004 GETQUOTAROOT INBOX
[30-Jun-2012 06:49:47 -0700]: [22CC] S: * QUOTAROOT "INBOX" "user"
[30-Jun-2012 06:49:47 -0700]: [22CC] S: * QUOTA "user" (STORAGE 0 1048576)
[30-Jun-2012 06:49:47 -0700]: [22CC] S: A0004 OK Getquotaroot completed.
[30-Jun-2012 06:49:47 -0700]: [22CC] C: A0005 LOGOUT
[30-Jun-2012 06:49:47 -0700]: [22CC] S: * BYE Logging out
[30-Jun-2012 06:49:47 -0700]: [22CC] S: A0005 OK Logout completed.
[30-Jun-2012 06:49:48 -0700]: [8AA1] S: * OK [CAPABILITY IMAP4rev1 LITERAL+ SASL-IR LOGIN-REFERRALS ID ENABLE STARTTLS AUTH=PLAIN AUTH=LOGIN] Dovecot ready.
[30-Jun-2012 06:49:48 -0700]: [8AA1] C: A0001 ID ("name" "Roundcube Webmail" "version" "0.7.2" "php" "5.3.2-1ubuntu4.17" "os" "Linux" "command" "/mail/?_task=mail&_action=list&_mbox=INBOX&_refresh=1&_remote=1&_unlock=loading1341064205184&_=1341064205191")
[30-Jun-2012 06:49:48 -0700]: [8AA1] S: * ID NIL
[30-Jun-2012 06:49:48 -0700]: [8AA1] S: A0001 OK ID completed.
[30-Jun-2012 06:49:48 -0700]: [8AA1] C: A0002 AUTHENTICATE PLAIN dG9ueWRAZXNlZG9uYS5uZXQAdG9ueWRAZXNlZG9uYS5uZXQAZG9kZ2VyOWJhbGw=
[30-Jun-2012 06:49:48 -0700]: [AD82] S: * OK [CAPABILITY IMAP4rev1 LITERAL+ SASL-IR LOGIN-REFERRALS ID ENABLE STARTTLS AUTH=PLAIN AUTH=LOGIN] Dovecot ready.
[30-Jun-2012 06:49:48 -0700]: [AD82] C: A0001 ID ("name" "Roundcube Webmail" "version" "0.7.2" "php" "5.3.2-1ubuntu4.17" "os" "Linux" "command" "/mail/?_task=mail&_action=getunread&&_remote=1&_unlock=0&_=1341064205194")
[30-Jun-2012 06:49:48 -0700]: [AD82] S: * ID NIL
[30-Jun-2012 06:49:48 -0700]: [AD82] S: A0001 OK ID completed.
[30-Jun-2012 06:49:48 -0700]: [AD82] C: A0002 AUTHENTICATE PLAIN dG9ueWRAZXNlZG9uYS5uZXQAdG9ueWRAZXNlZG9uYS5uZXQAZG9kZ2VyOWJhbGw=
[30-Jun-2012 06:49:48 -0700]: [8AA1] S: A0002 OK [CAPABILITY IMAP4rev1 LITERAL+ SASL-IR LOGIN-REFERRALS ID ENABLE SORT SORT=DISPLAY THREAD=REFERENCES THREAD=REFS MULTIAPPEND UNSELECT IDLE CHILDREN NAMESPACE UIDPLUS LIST-EXTENDED I18NLEVEL=1 CONDSTORE QRESYNC ESEARCH ESORT SEARCHRES WITHIN CONTEXT=SEARCH QUOTA] Logged in
[30-Jun-2012 06:49:48 -0700]: [8AA1] C: A0003 STATUS INBOX (MESSAGES UNSEEN)
[30-Jun-2012 06:49:48 -0700]: [8AA1] S: * STATUS "INBOX" (MESSAGES 0 UNSEEN 0)
[30-Jun-2012 06:49:48 -0700]: [8AA1] S: A0003 OK Status completed.
[30-Jun-2012 06:49:48 -0700]: [8AA1] C: A0004 LOGOUT
[30-Jun-2012 06:49:48 -0700]: [8AA1] S: * BYE Logging out
[30-Jun-2012 06:49:48 -0700]: [8AA1] S: A0004 OK Logout completed.
[30-Jun-2012 06:49:48 -0700]: [AD82] S: A0002 OK [CAPABILITY IMAP4rev1 LITERAL+ SASL-IR LOGIN-REFERRALS ID ENABLE SORT SORT=DISPLAY THREAD=REFERENCES THREAD=REFS MULTIAPPEND UNSELECT IDLE CHILDREN NAMESPACE UIDPLUS LIST-EXTENDED I18NLEVEL=1 CONDSTORE QRESYNC ESEARCH ESORT SEARCHRES WITHIN CONTEXT=SEARCH QUOTA] Logged in
[30-Jun-2012 06:49:48 -0700]: [AD82] C: A0003 LIST (SUBSCRIBED) "" "*"
[30-Jun-2012 06:49:48 -0700]: [AD82] S: * LIST (\Subscribed) "/" "INBOX"
[30-Jun-2012 06:49:48 -0700]: [AD82] S: * LIST (\Subscribed) "/" "Sent"
[30-Jun-2012 06:49:48 -0700]: [AD82] S: * LIST (\Subscribed) "/" "Trash"
[30-Jun-2012 06:49:48 -0700]: [AD82] S: * LIST (\Subscribed) "/" "Drafts"
[30-Jun-2012 06:49:48 -0700]: [AD82] S: * LIST (\Subscribed) "/" "Junk"
[30-Jun-2012 06:49:48 -0700]: [AD82] S: A0003 OK List completed.
[30-Jun-2012 06:49:48 -0700]: [AD82] C: A0004 STATUS INBOX (MESSAGES UNSEEN)
[30-Jun-2012 06:49:48 -0700]: [AD82] S: * STATUS "INBOX" (MESSAGES 0 UNSEEN 0)
[30-Jun-2012 06:49:48 -0700]: [AD82] S: A0004 OK Status completed.
[30-Jun-2012 06:49:48 -0700]: [AD82] C: A0005 STATUS Drafts (MESSAGES UNSEEN)
[30-Jun-2012 06:49:48 -0700]: [AD82] S: * STATUS "Drafts" (MESSAGES 0 UNSEEN 0)
[30-Jun-2012 06:49:48 -0700]: [AD82] S: A0005 OK Status completed.
[30-Jun-2012 06:49:48 -0700]: [AD82] C: A0006 STATUS Sent (MESSAGES UNSEEN)
[30-Jun-2012 06:49:48 -0700]: [AD82] S: * STATUS "Sent" (MESSAGES 0 UNSEEN 0)
[30-Jun-2012 06:49:48 -0700]: [AD82] S: A0006 OK Status completed.
[30-Jun-2012 06:49:48 -0700]: [AD82] C: A0007 STATUS Junk (MESSAGES UNSEEN)
[30-Jun-2012 06:49:48 -0700]: [AD82] S: * STATUS "Junk" (MESSAGES 0 UNSEEN 0)
[30-Jun-2012 06:49:48 -0700]: [AD82] S: A0007 OK Status completed.
[30-Jun-2012 06:49:48 -0700]: [AD82] C: A0008 STATUS Trash (MESSAGES UNSEEN)
[30-Jun-2012 06:49:48 -0700]: [AD82] S: * STATUS "Trash" (MESSAGES 0 UNSEEN 0)
[30-Jun-2012 06:49:48 -0700]: [AD82] S: A0008 OK Status completed.
[30-Jun-2012 06:49:48 -0700]: [AD82] C: A0009 LOGOUT
[30-Jun-2012 06:49:48 -0700]: [AD82] S: * BYE Logging out
[30-Jun-2012 06:49:48 -0700]: [AD82] S: A0009 OK Logout completed.

Offline aldspamacct

  • Newbie
  • *
  • Posts: 8
Re: Search for username in users table? --SOLVED--
« Reply #7 on: June 30, 2012, 10:14:44 AM »
-- SOLVED --
Ok, I am baffled.  Firstly, when I was doing my query from code I was doing a SELECT on `USERNAME` using only the username, not @domain.tld.  So that's my first problem and why my query failed.  However, it would appear that the data displayed in phpmyadmin (for column username) is encrypted while from the mysql cli the data is not.  What gave me a clue was enabling SQL Debug in roundcubemail.  So a simple sql query using the user@domain.tld is all that is needed in either phpmyadmin sql or mysql cli (or code).  However, in phpmyadmin, the resulting username displayed is encrypted or obfuscated .  So there would appear to be a setting (though not in the current /usr/share/phpmyadmin/config.inc.php config that would indicate encrypt/decrypt option, but perhaps in the phpmyadmin docs.

IP changed to 0.0.0.0 for post

Thanks for you help!!

Code: [Select]
mysql> select * from users;
+---------+-------------------+-------------+-------+---------------------+---------------------+----------+-------------+
| user_id | username          | mail_host   | alias | created             | last_login          | language | preferences |
+---------+-------------------+-------------+-------+---------------------+---------------------+----------+-------------+
|       1 | user@domain.tld   | 0.0.0.0     |       | 2012-06-30 06:54:01 | 2012-06-30 06:54:01 | en_US    | NULL        |
+---------+-------------------+-------------+-------+---------------------+---------------------+----------+-------------+
1 row in set (0.00 sec)

[30-Jun-2012 06:54:01 -0700]: query(1): SELECT * FROM users WHERE mail_host = 0.0.0.0 AND username = 'user@domain.tld;
Code: [Select]

« Last Edit: June 30, 2012, 10:19:45 AM by aldspamacct »