Is there a way to extract iPhone text messages into a text file? Someone in my family, one of my grandsons, in fact, is in hot water over text messages he sent and received, and a file showing who texted what wuld be very useful to his family right now.
Thanks!
Comments
fgrep -aiR "msg_group" /Users/<username>/Library/MobileSync/Backup
It will list files used for text messages. You can open these in an SQLite database browser such as:
http://sqlitebrowser.sourceforge.net/
download is here:
http://sourceforge.net/projects/sqlitebrowser/
or
http://www.sqlite.org/download.html
You then open the .mddata file found in the terminal in this program and select the messages table and that will give you all the messages on the phone in a big list. To get a list of messages and sources in order, you would run a query in the execute SQL tab for example:
SELECT address, text FROM message WHERE group_id=4 ORDER BY DATE ASC
SQLite will allow you to dump the results into a text file and print it off.
OR
you can go through the message list on the phone and take a screenshot as you go down the messages by hitting the home and power buttons together. The images for that message sequence would go into the photo album and you can send those to the computer to merge in say Photoshop.
Can someone explain to me how to view or open the mddata files. I am trying to recover text messages but can not seem to view any of the files.
I believe that i am trying to open the correct files from the MobileSync/Backup folders.
Feel free to email me or reply. [email protected]
If you have access to the phone backup on the computer (make sure to do another backup/sync before checking it), which would be in /Users/<username>/Library/MobileSync/Backup, you can get hold of the SQLite database for the text messages by using an fgrep command in the terminal:
fgrep -aiR "msg_group" /Users/<username>/Library/MobileSync/Backup
It will list files used for text messages. You can open these in an SQLite database browser such as:
http://sqlitebrowser.sourceforge.net/
download is here:
http://sourceforge.net/projects/sqlitebrowser/
or
http://www.sqlite.org/download.html
You then open the .mddata file found in the terminal in this program and select the messages table and that will give you all the messages on the phone in a big list. To get a list of messages and sources in order, you would run a query in the execute SQL tab for example:
SELECT address, text FROM message WHERE group_id=4 ORDER BY DATE ASC
SQLite will allow you to dump the results into a text file and print it off.
OR
you can go through the message list on the phone and take a screenshot as you go down the messages by hitting the home and power buttons together. The images for that message sequence would go into the photo album and you can send those to the computer to merge in say Photoshop.
I need some help. I have installed the SQLite Database Browser however when i try to open a mddata file it gives me an error "File is not an SQLite 3 Database"
Can someone explain to me how to view or open the mddata files. I am trying to recover text messages but can not seem to view any of the files.
They aren't all sqlite databases, that's why you have to use the fgrep terminal command. It looks for the database table inside each of the backup files. It should give you one or two files that have the word msg_group inside. You can then copy those files out to work on them.
The path may be wrong in the last post or they may have changed the location for people who can't find it. My current one is in /Users/<username>/Library/Application Support/MobileSync/Backup.
So open up terminal.app and type in:
fgrep -aiRo "msg_group" /Users/<username>/Library/Application\\ Support/MobileSync/Backup
If you find the backup folder, just drag it into the terminal after you write "msg_group". I added an extra command above so that it makes the output easier to read. You will see something like:
/Users/<username>/Library/Application Support/MobileSync/Backup/4072bba385af5adfab748609585cebac91be73d7/3d0d7e5fb2ce288813306e4d4636395e047a3d28:msg_group
msg_group
msg_group
msg_group
msg_group
msg_group
msg_group
msg_group
msg_group
msg_group
msg_group
/Users/<username>/Library/Application Support/MobileSync/Backup/4072bba385af5adfab748609585cebac91be73d7/3d0d7e5fb2ce288813306e4d4636395e047a3d28:msg_group
msg_group
msg_group
msg_group
msg_group
/Users/<username>/Library/Application Support/MobileSync/Backup/4072bba385af5adfab748609585cebac91be73d7/3d0d7e5fb2ce288813306e4d4636395e047a3d28:msg_group
Start with the one with the most entries and open it in the database browser.
Hopefully I can revive this post for some help here.
I have been able to find the UUID file that contains the sms messages and open it in a SQLite browser called Base. SQLite Database browser would not open the file saying that it was not a database or was encrypted.
I have recovered 2357 SMS messages from the file, but when I open the UUID file in Text Edit I am able to see messages that are not showing in Base.
Any ideas why this is happening and what to do to recover all messages?
Thanks!
SQL browsers often only show limited results at a time e.g 1,000 rows. Check if you have multiple pages of results that you can flip through.
Thanks for the message Marvin. Base does only show 100 lines at a time, but I have looked through the entire list of 2395. Could it be that the data is corrupted somehow or that it does not show deleted messages? I can find a message that is right next to the hidden one in Text Edit, but it won't show in the browser.
If you check in Textedit what the id of the message is and order the database table by id ascending, you should be able to see it. The SQL browser's default listing won't always list the results in the same order as the file. If the unique row id is missing in the SQL broswer make sure it's supposed to be in the table you are browsing. If you are using an SQL query, check you aren't using a query that excludes it from the results.
Hey Marvin,
Thanks for your continued interest and ideas. This one is a stumper for sure. The data besides the textual data is all scrambled ( SQL binary data I'm guessing ). Here is an example.
streamtypedÅËÑ@ÑÑÑNSMutableAttributedStringÑÑNSAttributedStringÑÑNSObjectÖíÑÑÑNSMutableStringÑÑNSStringïÑ+Å°Hello Tim ! How are you, hope you are doing great ! . Can you let me know how many people is coming with you to machi picchu??? Because we have to pay in advanceÜÑiIÅ
°íÑÑÑNSDictionaryïÑiíÑòò__kIMMessagePartAttributeNameÜíÑÑÑNSNumberÑÑNSValueïÑ*ÑõõÜÜÜ6D4CDD2C-57BA-4E47-B0F2-B998D60C8585Madridp:AA496D93-2FC8-4D8F-97D4-0F86E7EE1C880?ûAån"%!ORáÇNo problemusKåm"%5 ORÖœ involved you in it.(usÅQål#%Ç? ORÖ±Im sorry its just been a nite mare with Charlie in town and I knew it would be. When he first called I got upset. How ever it isnt your fault. Im sorry I(us'$Å;Ñx%U)U üÄPlease change your Garage code i never want u to accuse me of stealing anything again¬†
streamtypedÅËÑ@ÑÑÑNSMutableAttributedStringÑÑNSAttributedStringÑÑNSObjectÖíÑÑÑNSMutableStringÑÑNSStringïÑ+WPlease change your Garage code i never want u to accuse me of stealing anything again¬†ÜÑiIVíÑÑÑ
The first group shows up in the Base SQLite browser, the following 2 do not. I can't see any pattern that would indicate why.
Yeah, there's no reason it shouldn't load in. You had some phone numbers pasted into your text so I removed them for you.
One thing you can try is converting the database to a standard SQL file. There is an app here:
http://www.sqlite.org/sqlite.html
The download is a command line app. Take a copy of the database file out of Mobile Sync and put it on the desktop. You then just drag sqlite3 into the terminal, then drag the database in and after this type .dump > ~/Desktop/iPhonedb.sql
The command would look something like:
~/Downloads/sqlite3 ~/Desktop/6hy73uj37h38uuh888 .dump > ~/Desktop/iPhonedb.sql
That file an ascii version of the database. You can count the messages by doing:
grep -ic "insert into message" ~/Desktop/iPhonedb.sql
This sql file can then be imported into any standard database app like Sequel Pro or Navicat although you might have to correct some syntax errors.
You can also use it to dump the data as a csv file. You'd do this by typing:
sqlite3 ~/Desktop/6hy73uj37h38uuh888
This opens up your database. Then type:
.mode csv
.output ~/Desktop/sqloutput.csv
select * from message; (remember the semi-colon)
That will dump your messages as a csv file. You can order the messages by date or by group or whatever column and you can choose to only export certain columns of data.
Thanks again Marvin. Feel like I am soooo close on this. I have the file converted to SQL and have verified the 2395 messages using grep.
Just having an error converting to csv files.
sqlite> sqlite3 /Users/my_name/Desktop/iPhonedb.sql
...> .mode csv
...> .output ~/Desktop/output.csv
...> SELECT * FROM message;
Error: near "sqlite3": syntax error
I have tried the SQL in lower case as well. Any further clues?
Those were separate export routes, the first steps are if you want to convert to e.g MySQL to open the database in another app. You do the CSV conversion on the original file, ignore the conversion to standard sql. So the first command would be
sqlite3 /Users/name/Desktop/3jki8hhd83j8 (i.e the exact copy of the MobileSync file, not the converted iPhonedb.sql)
Then do the CSV steps once you have opened that file.
I was able to get the SQL file, however just like the SQLite file it does not include all of the messages, including the specific ones needed here. I'm stuck after spending many hours trying to get this data into a format that is usable for litigation with no results. Does anyone know of a good iPhone forensics firm that may be able to get past the issue here?
If the messages show up in Textedit in the original file, I don't know why they wouldn't show in the database view. They aren't encrypted if they are visible in plain text in the file. If you want, I can take a look at the database file and see if I can extract the messages. You can upload the file using:
http://ge.tt/
You can encrypt the file using Disk Utility (put the file in a folder, then use /Applications/Utilities/Disk Utility, then file > disk image from folder, choose folder, then options compressed/encryption AES 128) and then just PM me the password and the link. If I can get the messages to show, I will either send back the list or instructions on how to extract them.
Quote:
Originally Posted by MazarothIT
Thanks for the message Marvin. Base does only show 100 lines at a time, but I have looked through the entire list of 2395. Could it be that the data is corrupted somehow or that it does not show deleted messages? I can find a message that is right next to the hidden one in Text Edit, but it won't show in the browser.
It's possible that the data could be corrupted or yes that it won't show deleted ones.
I suggest you look to see if there are multiple backups and run the same thing on each. you might find some more bits of what you are looking for.
There's also a way, although I don't have it bookmarked here, to remove everything but the SMS from a back up and then restore it to a phone. You could try that on copies of each backup folder and put them on an iPhone to see them and do the screenshot trick mentioned before. Google and I'm sure you can find a couple of sites that will tell you the parts to keep
Well,
I'm done with this one. I restored the backup onto my iPhone and was able to retrieve the same exact stuff, iMessages and SMS from the backup. I have found that deleted messages are not easily recoverable. I know there are forensics out there that can do it, but I am not that invested in this. My client unfortunately deleted the messages he wants to use in court.
Thanks everyone for all of your suggestions and help.
Quote:
Originally Posted by MazarothIT
Well,
I'm done with this one. I restored the backup onto my iPhone and was able to retrieve the same exact stuff, iMessages and SMS from the backup. I have found that deleted messages are not easily recoverable. I know there are forensics out there that can do it, but I am not that invested in this. My client unfortunately deleted the messages he wants to use in court.
Thanks everyone for all of your suggestions and help.
I wonder why he didn't just show the court the phone? the actual text message in the message history would be enough to tide them over right? maybe a screenshot could have showed a print of it for the evidence bin.
He deleted the messages in a fit of anger... I was able to produce a text file that had the messages embedded, albeit in a very garbled way with no date/timestamp or clear indication of what number/iMessage account sent the message.
How do you do this with MS-DOS?