Announcement

Collapse
No announcement yet.

Where are PM's stored in the DB, got a subpoena

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Where are PM's stored in the DB, got a subpoena

    I received a subpoena for a few member's PMs but do not know how to find them in the database.

    Is there a clear text file or table for PMs or are they encrypted?

  • #2
    Just use this: http://www.vbulletin.org/forum/showthread.php?t=123166
    "Our greatest weakness lies in giving up. The most certain way to succeed is always to try just one more time!"
    "It's important to only think about what you desire, not what you fear to achieve your ultimate goal!!"
    "When doors close, tear down the walls. Never give up!"

    Comment


    • #3
      That would help me read them individually but I need to be able to pull an individual user's PMs from the database to comply with the subpoena.

      Comment


      • #4
        The pm table stores the meta information and the pmtext table stores the actual text. They are not encrypted.
        Translations provided by Google.

        Wayne Luke
        The Rabid Badger - a vBulletin Cloud demonstration site.
        vBulletin 5 API - Full / Mobile
        Vote for your favorite feature requests and the bugs you want to see fixed.

        Comment


        • #5
          Looks like they're in the pmtext table

          EDIT: Wayne beat me to it
          "Our greatest weakness lies in giving up. The most certain way to succeed is always to try just one more time!"
          "It's important to only think about what you desire, not what you fear to achieve your ultimate goal!!"
          "When doors close, tear down the walls. Never give up!"

          Comment


          • #6
            Originally posted by Wayne Luke View Post
            The pm table stores the meta information and the pmtext table stores the actual text. They are not encrypted.

            I found them in the database and can see how you Browse by using phpMyAdmin but I'm a relative newcomer with databases.
            I let mine do it's things and don't stick my grubby paws under the hood, we get along better that way.

            How would i use phpMyAdmin to isolate for download a single user's PMs?

            Comment


            • #7
              Select each one you want to download, then at the bottom, click export
              "Our greatest weakness lies in giving up. The most certain way to succeed is always to try just one more time!"
              "It's important to only think about what you desire, not what you fear to achieve your ultimate goal!!"
              "When doors close, tear down the walls. Never give up!"

              Comment


              • #8
                Originally posted by HMBeaty View Post
                Select each one you want to download, then at the bottom, click export
                Seriously?

                Wow, I've got almost 100,000 members, so I have to browse the table of everyone's PMs and select just the PMs of the individual members named in the subpoena!?!

                That will take forever! The DoJ had better be paying me for all those hours!

                Comment


                • #9
                  Try these on your test site and see if they work.

                  SELECT message FROM pmtext WHERE fromuserid = XXX;

                  SELECT message FROM pmtext WHERE touserarray LIKE '%i:XXX;s:%';

                  Change XXX to the userid you are interested in.
                  Last edited by Lynne; Fri 18th Nov '11, 10:34am. Reason: I goofed!

                  Please don't PM or VM me for support - I only help out in the threads.
                  vBulletin Manual & vBulletin 4.0 Code Documentation (API)
                  Want help modifying your vbulletin forum? Head on over to vbulletin.org
                  If I post CSS and you don't know where it goes, throw it into the additional.css template.

                  W3Schools <- awesome site for html/css help

                  Comment


                  • #10
                    I had the same situation, I used php to compile sql queries:

                    PHP Code:
                    $conn mysql_connect'host''user''password' ) or die( mysql_error() );
                    mysql_select_db'dbname'$conn) or die( mysql_error() );

                    $useridName = array(
                    // list userid => username here
                     
                    '1' => 'username1'
                    ,'2' => 'username2'
                    ,'3' => 'username3'
                    );

                    $userid array_keys$useridName );

                    // if you want to filter by date
                    $datefilter ' and pmtext.dateline >= ' mktime(000112011);


                    for(
                    $i=0$j=count($userid); $i<$j $i++){
                        
                    $sql 'select count(*) as TotalPM from pmtext where pmtext.fromuserid = \'' intval($userid[$i]) . '\'';
                        
                    $sql .= $datefilter;
                        
                    $result mysql_query$sql$conn ) or die( mysql_error());
                        if(
                    $row mysql_fetch_assoc($result)){
                            print(
                    $userid[$i] . "\t: " $useridName[$userid[$i]]  . "\t: " $row['TotalPM'] . "\n");
                        }
                        
                    #print $sql . "\n";
                    }

                    $columnList =  '';

                    $columnList .= 'pmtext.fromuserid';
                    $columnList .= ', pmtext.fromusername';
                    $columnList .= ', pmtext.title';
                    $columnList .= ', pmtext.message';
                    $columnList .= ', FROM_UNIXTIME( pmtext.dateline ) as dateline';

                    $sqlAll =  'select ' $columnList ' ';
                    $sqlAll .= 'from pmtext where ';
                    $sqlAll .= 'pmtext.fromuserid in ( ';
                    $sqlAll .= implode','$userid ); 
                    $sqlAll .= ' ) ';
                    $sqlAll .= $datefilter;


                    echo 
                    $sqlAll;

                    echo 
                    "\n";

                    mysql_close$conn );
                    exit(); 
                    Hope this help!

                    Comment


                    • #11
                      here's something you could do assuming the users did not delete their pm's. if they didn't it would work like a charm. you manually change their passwords and then log in as them. it's your site so you're god. if they did delete them then you fall back on the database extraction.

                      i don't want to hijack the thread, but will pose my own question. is there a way to make vbulletin not keep any messages or threads in the database if they are deleted? is it some legal requirement that they be retained? in fact i think i did make my own thread about this question, or a very similar one. what if i, as site owner/admin do not want pm's stored in the database once a user deletes it?

                      Comment


                      • #12
                        If a user deletes a PM, and the person who sent the PM deletes it from their sent box, then the PM will no longer be in the database. But, both users - the sender and receiver - must delete it.

                        Please don't PM or VM me for support - I only help out in the threads.
                        vBulletin Manual & vBulletin 4.0 Code Documentation (API)
                        Want help modifying your vbulletin forum? Head on over to vbulletin.org
                        If I post CSS and you don't know where it goes, throw it into the additional.css template.

                        W3Schools &lt;- awesome site for html/css help

                        Comment


                        • #13
                          Originally posted by Lynne View Post
                          ...
                          SELECT message FROM pmtext WHERE touserarray LIKE '%i:XXX;s:";
                          ...
                          Just to correct the query here:

                          PHP Code:
                          SELECT message FROM pmtext WHERE touserarray LIKE '%i:XXX;s:%'

                          Comment


                          • #14
                            Whoops! Thanks. I'll go correct mine.

                            Please don't PM or VM me for support - I only help out in the threads.
                            vBulletin Manual & vBulletin 4.0 Code Documentation (API)
                            Want help modifying your vbulletin forum? Head on over to vbulletin.org
                            If I post CSS and you don't know where it goes, throw it into the additional.css template.

                            W3Schools &lt;- awesome site for html/css help

                            Comment


                            • #15
                              To obtain all posts from a user, could we just do a similar query to the post table? Would this cover all posts by a user?

                              SELECT pagetext FROM post WHERE userid = XXX

                              Would we need to grab from postedithistory as well to get the actual original posts and any consequent edits? Anything else I'm forgetting?

                              Thanks!

                              Comment

                              widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
                              Working...
                              X