
Your Mac Keeps A Log Of Your Downloads - fcukdigg
http://www.macgasm.net/2013/01/18/good-morning-your-mac-keeps-a-log-of-all-your-downloads/
======
ehamberg
This is used to show where a program came from the first time you run it. For
example, if I download iTerm 2 and then run it, I get the following warning:

<http://i.imgur.com/IbUWj.png>

~~~
0x0
Actually I think that warning comes from data stored in the xattr
com.apple.metadata:kMDItemWhereFroms on the file itself. So this sqlite
database must be for something else.

~~~
TheCapn
I'm not a Mac user but wouldn't the test therefore be to clear the DB entries
and re-open the file. No prompt implies the link to the sqlite db and not the
file attr.

~~~
mitchty
The dialog only shows up when that xattr is set, the sqlite db has nothing to
do with this security prompt. In fact all the prompt does is unset the xattr
if you deem it ok.

------
phwd
This is amazing, whoever feels comfortable about it should band together and
see what files are in common, or domains. I want to delete this yet I don't.
This contains all (most?) of the files I have ever downloaded, those I thought
I lost when clearing browsing data from 2008.

My first few files

* Symantec_Antivirus_Mac.dmg

* [http://msdn01.e-academy.com|http://download.e-academy.com/do...](http://msdn01.e-academy.com|http://download.e-academy.com/download/DeliveryClientBuilder.aspx) (MSDN Alliance: Free Microsoft Software for Students)

* [http://download2.vmware.com/software/fusion/VMware-Fusion-1....](http://download2.vmware.com/software/fusion/VMware-Fusion-1.1.3-94249.dmg)

* <http://download.skype.com/macosx/Skype_2.7.0.330.dmg>

Scary yes (The torrent files) but so much history to look at.

    
    
        sqlite3 ~/Library/Preferences/com.apple.LaunchServices.QuarantineEvents 'SELECT datetime(LSQuarantineTimeStamp + 978307200, "unixepoch") as LSQuarantineTimeStamp, LSQuarantineAgentName, LSQuarantineOriginURLString, LSQuarantineDataURLString from LSQuarantineEvent' | sort
    

Ordered by date

~~~
danso
That's actually how I felt about the iPhone coordinates database (also in
SQLite I believe)...I saved a copy of it before upgrading to the iOS that
didn't have that "feature"

------
schluete
Nice article, but unless the database is "VACUUM"ed after the "DELETE" the
rows are still readable in the database file. This kinda defeats the purpose
of the whole article b/c the user didn't gain any more privacy than he had
before the deletion :)

~~~
vinhboy
can you share the command to do this?

~~~
schluete
sqlite3 ~/Library/Preferences/com.apple.LaunchServices.QuarantineEventsV*
'vacuum'

~~~
geuis
Tried this, but I still see all of the files in the list.

~~~
toyg
The idea is that first you run the DELETE statement from TFA, which marks the
records as deleted and "hides" them but doesn't completely "forget" them, then
you run the VACUUM one, which should basically erase any trace of those
records ever existing in the sqlite db.

------
tripzilch
Hm, they could also have tagged the file as "came from the Internet" in the
filesystem metadata associated with the file itself. That way, the "came from
the Internet" tag is only around as long as it needs to: for the lifetime of
the file. Avoiding the privacy problem, but also more sensible for another
reason:

If you'd rename or copy the file, the "came form the Internet" tag will remain
or be copied with the file. With the sqlite database approach, either this
association breaks, or you need to check the database every time a file is
copied, moved or renamed and if it's in there, update the database, if you
want to be able to track a file when it's copied or renamed.

I don't know if OSX has extra logic for this, or if they just allow the
association to break. But with the metadata tagging approach, you only have to
run the tagging logic when the file is downloaded (to set the tag) and when
it's about to be executed (to check the tag), not with every other file-
operation.

A strange choice, IMO: the sqlite approach makes it _harder_ to achieve the
intended goal because you need extra effort/logic required to track a file as
it's copied, renamed or moved, while at the same time it makes it _easier_ for
an unintended goal: tracking users by keeping the information about the file
around even when it's deleted and the "came from the Internet" warning is no
longer useful.

~~~
vicaya
As I mentioned in another comment, apple do tag "com.apple.quarantine" as an
extended attribute on the program folder/zip itself. The download log has
nothing to do with the warning dialog.

------
hoov
Am I the only one that disables this behavior by default?

defaults write com.apple.LaunchServices LSQuarantine -bool NO

Works like a charm...

~~~
mesm
It's also included in this sane defaults dotfile

[https://github.com/mathiasbynens/dotfiles/blob/master/.osx#L...](https://github.com/mathiasbynens/dotfiles/blob/master/.osx#L57)

~~~
DHowett
That's not so much a "sane defaults dotfile" as a highly-opinionated personal
defaults dotfile. I don't consider that a bad thing, but do at least give it
the deserved appellation.

~~~
Camillo
Words can have different meanings in a jargon than they do in the dictionary,
but that does not make those meanings invalid. For instance, in computing
"sane" means "I'm an overly opinionated git and everyone who disagrees with me
is wrong."

------
julien_p
Files get a "quarantine flag" set on them as metadata when downloaded on OS X.
Gatekeeper uses this (along with the developer signature) to check if an app
is "safe" to open or not. Not sure where this sqlite database fits in, but
it's very likely related to that.

See also <https://support.apple.com/kb/HT3662>

~~~
sp332
The why is the Mac App Store exempt?

~~~
jjcm
Trusted/controlled source.

------
JC001
Strange, on my machine this file only contains the URL from when I installed
Firefox and a bunch of URLs for Adium updates. Not any of the many other
things I've downloaded...

Is it only listing things downloaded through Safari?

~~~
johnpowell
Same here. The only thing it shows I downloaded is Firefox.

~~~
Volpe
It show's all mine, I use Chrome for everything.

Maybe Firefox downloads aren't included? Maybe it's watching the ~/Downloads
folder? Can you confirm either?

------
tehwalrus
I can't see any of my downloads, the database exists but is empty on my system
(10.6.8). (and I've sure downloaded a lot of files...)

------
cynwoody
You could try something like:

    
    
        $ sqlite3 -column ~/Library/Preferences/com.apple.LaunchServices.QuarantineEvents <<END|pbcopy
        > .mode tabs
        > .headers on
        > SELECT strftime('%Y-%m-%d %H:%M:%S', LSQuarantineTimestamp+ 978307200, 'unixepoch', 'localtime')  as date,
        > LSQuarantineAgentName as App,
        > LSQuarantineDataURLString as URL
        > FROM LSQuarantineEvent
        > --where LSQuarantineDataURLString like '%bankofamerica%'
        > order by LSQuarantineTimestamp;
        > END
        $
    

Then open your favorite spreadsheet program and paste in the results.

The 978307200 number corrects for the fact that Apple is using 2001-01-01, the
year OS 10.0 was released, as its epoch.

I got way fewer rows than expected. Apparently, downloads by Firefox are not
logged. When I screened for B of A downloads, I was puzzled to see only
downloads in the past year or so. That's because I only recently started using
Chrome to access that site.

------
binarycrusader
And I suspect that if you had a virus scanner installed on windows every file
it scanned would be logged somewhere depending on settings.

This post also fails to mention that this is specific to Safari at last check.
I don't think Google Chrome integrates with this functionality.

~~~
ehamberg
It does now:

    
    
        $ sqlite3 ~/Library/Preferences/com.apple.LaunchServices.QuarantineEventsV* 'select LSQuarantineAgentName from LSQuarantineEvent'|grep 'Google Chrome'|wc -l
              35

~~~
varikin
But firefox doesn't seem to be in the list, though I am using the Firefox
Nightly so that has something to do with it. I have Adium (lot of sparkle
update checks), Chrome (just used for testing), iChat and a couple other
random one-offs.

~~~
0x0
Firefox seems to be "blacklisted" by Apple, see
/System/Library/CoreServices/CoreTypes.bundle/Contents/Resources/Exceptions.plist
:

    
    
                    <key>org.mozilla.firefox</key>
                    <dict>
                            <key>LSFileQuarantineEnabled</key>
                            <true/>

~~~
mcpherrinm
Nightly is org.mozilla.nightly, so that explains the grandparent's behaviour
differing, since the Apple blacklist does not include it.

------
Camillo
Files that aren't quarantines don't seem to end up in that log,
unsurprisingly. No images, audio or video files, but applications and
archives.

------
nextstep
At the end of the post, the author mentions using Automator to run a script to
delete this frequently. Alternatively, you can use launchd to run scripts on a
Mac. <http://nb.nathanamy.org/2012/07/schedule-jobs-using-launchd/>

------
newman314
Actually, there are 2 versions of this file.

Depending on how long you have had your Mac, you will have both
com.apple.LaunchServices.QuarantineEvents and
com.apple.LaunchServices.QuarantineEventsV2

Naturally, both files will have to be cleansed.

------
atomical
I get 'Error: no such table: LSQuarantineEvent'

~~~
addandsubtract
Same here. OSX 10.6.8

~~~
atomical
Ditto.

------
jqqqz
Install GNU+Linux, problem solved.

~~~
sbuk
Only if you are paranoid enough to think that this is a problem. Of course,
there are other problems with installing Linux that are ignored, but that's a
topic for another discussion.

------
Evbn
Guys, guys, watch out, I just noticed that my computer has a copy of _all_ my
files on it. Who gave Apple permission to do that? Privacy is dead!

~~~
gyardley
Funny, but sometimes you do want to clean up your laptop - for example, before
crossing the US border.

I would be irked if I had taken steps to remove a downloaded file from my
laptop, including secure deletion, only to leave evidence that I'd downloaded
it in a sqlite database.

~~~
SoftwareMaven
If you are worried about that kind of thing, I'd recommend doing that stuff in
a VM with an encrypted file system. Then, when you delete it, you know
_everything_ is gone.

------
martinced
_"Your Mac Keeps A Log Of Your Downloads"_...

In your user account.

There's quite a difference right there. I thought it was some kind of "hidden"
file (not unlike the GPS location which was saved by default on any iPhone
with a GPS -- up to the latest iPhone!?) which had now been discovered.

It's just in your user account so it's no big deal.

~~~
buster
Why is it different just because it's in your user account?

Everyone with access to your Mac can access it just as well, i assume. I guess
it can also be read by applications, uploaded to the internet? I don't have a
Mac so i don't know the restrictions but i would consider it privacy
information that i would like to know if and where it's stored. There is a
reason you can delete your browser history. Or your recently accessed files
list.

~~~
fredsted
> Everyone with access to your Mac can access it just as well, i assume

If they're logged on to your account, but not if they are using a different
account.

>I guess it can also be read by applications, uploaded to the internet?

Not Mac App Store apps - they're quarantined.

~~~
buster
> If they're logged on to your account, but not if they are using a different
> account.

So, they are encrypted or how is that guaranteed?

~~~
fredsted
File permissions.

------
barista
"You should not be downloading anything that you don't want anybody else to
know" Eric Schmidt would say

~~~
fcukdigg
hahaha, good ole' Schmidt. What a gem.

~~~
w1ntermute
He's created many a Schmidtstorm with his off-the-cuff remarks.

